identity columns - Mailing list pgsql-hackers

Here is another attempt to implement identity columns.  This is a
standard-conforming variant of PostgreSQL's serial columns.  It also
fixes a few usability issues that serial columns have:

- need to set permissions on sequence in addition to table (*)
- CREATE TABLE / LIKE copies default but refers to same sequence
- cannot add/drop serialness with ALTER TABLE
- dropping default does not drop sequence
- slight weirdnesses because serial is some kind of special macro

(*) Not actually implemented yet, because I wanted to make use of the
NEXT VALUE FOR stuff I had previously posted, but I have more work to do
there.

There have been previous attempts at this between 2003 and 2007.  The
latest effort failed because it tried to implement identity columns and
generated columns in one go, but then discovered that they have wildly
different semantics.  But AFAICT, there weren't any fundamental issues
with the identity parts of the patch.

Here some interesting threads of old:

https://www.postgresql.org/message-id/flat/xzp1xw2x5jo.fsf%40dwp.des.no#xzp1xw2x5jo.fsf@dwp.des.no
https://www.postgresql.org/message-id/flat/1146360362.839.104.camel%40home#1146360362.839.104.camel@home
https://www.postgresql.org/message-id/23436.1149629297%40sss.pgh.pa.us
https://www.postgresql.org/message-id/flat/448C9B7A.6010000%40dunaweb.hu#448C9B7A.6010000@dunaweb.hu
https://www.postgresql.org/message-id/flat/45DACD1E.2000207%40dunaweb.hu#45DACD1E.2000207@dunaweb.hu
https://www.postgresql.org/message-id/flat/18812.1178572575%40sss.pgh.pa.us

Some comments on the implementation, and where it differs from previous
patches:

- The new attidentity column stores whether a column is an identity
column and when it is generated (always/by default).  I kept this
independent from atthasdef mainly for he benefit of existing (client)
code querying those catalogs, but I kept confusing myself by this, so
I'm not sure about that choice.  Basically we need to store four
distinct states (nothing, default, identity always, identity by default)
somehow.

- The way attidentity is initialized in some places is a bit hackish.  I
haven't focused on that so much without having a clear resolution to the
previous question.

- One previous patch managed to make GENERATED an unreserved key word.
I have it reserved right now, but perhaps with a bit more trying it can
be unreserved.

- I did not implement the restriction of one identity column per table.
That didn't seem necessary.

- I did not implement an override clause on COPY.  If COPY supplies a
value, it is always used.

- pg_dump is as always a mess.  Some of that is because of the way
pg_upgrade works: It only gives out one OID at a time, so you need to
create the table and sequences in separate entries.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services