Shulou(Shulou.com)06/01 Report--

No zuo no die series, from wiki of pg.

This section is about: don't use serial.

The reason is:

The serial types have some weird behaviors that make schema, dependency, and permission management unnecessarily cumbersome.

There are certain behaviors in this type that can cause unnecessary trouble for patterns, dependencies, and rights management.

Basic usage

[local]: 5432 pg12@testdb=# drop table if exists tasking serialtic drop TABLETime: 158.910 ms [local]: 5432 pg12@testdb=# CREATE TABLE t_serial (pg12@testdb (# id serial PRIMARY KEY,pg12@testdb (# C1 varcharpg12@testdb (#); CREATE TABLETime: 9.424 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# INSERT INTO t_serial (C1) VALUES ('a'), ('b'), ('c') RETURNING * Id | C1-+-1 | a 2 | b 3 | c (3 rows) INSERT 0 3Time: 3.076 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# select * from tasking series; id | C1-+-1 | a 2 | b 3 | c (3 rows) Time: 0.847 ms [local]: 5432 pg12@testdb=#

The functions of serial and GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY are very similar.

[local]: 5432 pg12@testdb=# CREATE TABLE t_identify (pg12@testdb (# id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,pg12@testdb (# C1 textpg12@testdb #); CREATE TABLETime: 5.215 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# INSERT INTO t_identify (C1) VALUES ('a'), ('b'), ('c') RETURNING * Id | C1-+-1 | a 2 | b 3 | c (3 rows) INSERT 0 3Time: 1.127 ms [local]: 5432 pg12@testdb=#

In fact, serial is compatible with the SQL standard, while the syntax of GENERATED BY DEFAULT AS IDENTITY is PG is not compatible.

Authority

The first problem with the serial type is that the sequence associated with the serial column needs to be handled separately

[local]: 5432 pg12@testdb=# drop user if exists user1029;NOTICE: role "user1029" does not exist, skippingDROP ROLETime: 0.422 ms [local]: 5432 pg12@testdb=# CREATE USER user1029 with password 'test';CREATE ROLETime: 0.543 ms [local]: 5432 pg12@testdb=# GRANT INSERT ON t_serial TO user1029;GRANTTime: 1.297 ms [local]: 5432 pg12@testdb=# GRANT INSERT ON t_identify TO user1029;GRANTTime: 3.729 ms [local]: 5432 pg12@testdb=# SET SESSION AUTHORIZATION user1029 SETTime: 1.243 ms [local]: 5432 user1029@testdb= > INSERT INTO t_serial (C1) VALUES ('d'); ERROR: permission denied for sequence t_serial_id_seqTime: 2.705 ms [local]: 5432 user1029@testdb= > INSERT INTO t_identify (C1) VALUES ('d'); INSERT 0 1Time: 3.340 ms [local]: 5432 user1029@testdb= >

As you can see, the underlying implementation of type serial depends on the fact that the sequence corresponding to the sequence,id column is t_serial_id_seq.

GENERATED BY DEFAULT AS IDENTITY, on the other hand, does not need to rely on it, so there is no error in execution.

This problem can be solved through authorization

-- pg12 [local]: 5432 pg12@testdb=# GRANT USAGE ON SEQUENCE t_serial_id_seq to user1029;GRANTTime: 5.291 ms [local]: 5432 pg12@testdb=#-- user1029 [local]: 5432 user1029@testdb= > INSERT INTO t_serial (C1) VALUES ('d'); INSERT 0 1Time: 3.791 ms [local]: 5432 user1029@testdb= >

Since the serial type depends on sequence, what happens if we do something about sequence?

[local]: 5432 pg12@testdb=# drop sequence: default value for column id of table t_serial depends on sequence t_serial_id_seqHINT: Use DROP. CASCADE to drop the dependent objects too.Time: 1.056 ms

There is a dependency, an error will be reported when deleting, and the cascade option is added.

[local]: 5432 pg12@testdb=# drop sequence t_serial_id_seq cascade NOTICE: drop cascades to default value for column id of table t_serialDROP SEQUENCETime: 10.075 ms [local]: 5432 pg12@testdb=#\ d t_serial Table "public.t_serial" Column | Type | Collation | Nullable | Default-+- +-id | integer | | not null | C1 | character varying | Indexes: "t_serial_pkey" PRIMARY KEY Btree (id) [local]: 5432 pg12@testdb=#

The t_serial column becomes a normal int field.

Although it has some shortcomings, it can still be used.

Identity columns This is the SQL standard-conforming variant of PostgreSQL's serialcolumns. It fixes a few usability issues that serial columns have:- CREATE TABLE / LIKE copies default but refers to same sequence- cannot add/drop serialness with ALTER TABLE- dropping default does not drop sequence- need to grant separate privileges to sequence- other slight weirdnesses because serial is some kind of special macro

references

Don't Do This

PostgreSQL 10 identity columns explained

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.