Re: BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is)

On Fri, 24 Sep 2021 at 05:14, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17202
> Logged by: Erik Huelsmann
> Email address: ehuels(at)gmail(dot)com
> PostgreSQL version: 12.8
> Operating system: Ubuntu Linux 20.04 (running a Docker container)
> Description:
>
> While changing the definition of a parent table from the non-standard SERIAL
> column type to the SQL ANSI standard "integer GENERATED BY DEFAULT AS
> IDENTITY", I'm finding a difference on the resulting columns in the child
> table.
>
> With the "SERIAL" column declaration and these table definitions:
>
> CREATE TABLE note (id serial primary key,
> note_class integer not null references note_class(id),
> note text not null,
> vector tsvector not null default '',
> created timestamp not null default now(),
> created_by text DEFAULT SESSION_USER,
> ref_key integer not null,
> subject text);
>
> CREATE TABLE entity_note(
> entity_id int references entity(id),
> primary key(id)) INHERITS (note);
>
> I'm getting this output for '\d':
>
>
> existing=# \d note
> Table "public.note"
> Column | Type | Collation | Nullable |
> Default
> ------------+-----------------------------+-----------+----------+----------------------------------
> id | integer | | not null |
> nextval('note_id_seq'::regclass)
> note_class
> | integer | | not null |
> note | text | | not null |
> vector | tsvector | | not null |
> ''::tsvector
> created | timestamp without time zone | | not null | now()
> created_by | text | | |
> SESSION_USER
> ref_key | integer | | not null |
> subject | text | | |
> Indexes:
> "note_pkey" PRIMARY KEY, btree (id)
> Foreign-key constraints:
> "note_note_class_fkey" FOREIGN KEY (note_class) REFERENCES
> note_class(id)
> Number of child tables: 6 (Use \d+ to list them.)
>
> existing=# \d invoice_note
> Table "public.invoice_note"
> Column | Type | Collation | Nullable |
> Default
> ------------+-----------------------------+-----------+----------+----------------------------------
> id | integer | | not null |
> nextval('note_id_seq'::regclass)
> note_class | integer | | not null |
> note | text | | not null |
> vector | tsvector | | not null |
> ''::tsvector
> created | timestamp without time zone | | not null | now()
> created_by | text | | |
> SESSION_USER
> ref_key | integer | | not null |
> subject | text | | |
> Indexes:
> "invoice_note_pkey" PRIMARY KEY, btree (id)
> "invoice_note_id_idx" btree (id)
> "invoice_note_vectors_idx" gist (vector)
> Foreign-key constraints:
> "invoice_note_ref_key_fkey" FOREIGN KEY (ref_key) REFERENCES
> invoice(id)
> Inherits: note
>
> As you can see, both the "note" and "invoice_note" tables have a default
> "nextval" function applied to the "id" column. When I change "SERIAL" to
> "integer generated by default AS IDENTITY primary key" as demonstrated
> below, the output of '\d' changes to:
>
> REATE TABLE note (id integer generated by default AS IDENTITY primary key,
> note_class integer not null references note_class(id),
> note text not null,
> vector tsvector not null default '',
> created timestamp not null default now(),
> created_by text DEFAULT SESSION_USER,
> ref_key integer not null,
> subject text);
>
> CREATE TABLE invoice_note(primary key(id)) INHERITS (note);
>
>
> new=# \d note
> Table "public.note"
> Column | Type | Collation | Nullable |
> Default
> ------------+-----------------------------+-----------+----------+----------------------------------
> id | integer | | not null | generated
> by default as identity
> note_class | integer | | not null |
> note | text | | not null |
> vector | tsvector | | not null |
> ''::tsvector
> created | timestamp without time zone | | not null | now()
> created_by | text | | |
> SESSION_USER
> ref_key | integer | | not null |
> subject | text | | |
> Indexes:
> "note_pkey" PRIMARY KEY, btree (id)
> Foreign-key constraints:
> "note_note_class_fkey" FOREIGN KEY (note_class) REFERENCES
> note_class(id)
> Number of child tables: 6 (Use \d+ to list them.)
>
> new=# \d invoice_note
> Table "public.invoice_note"
> Column | Type | Collation | Nullable | Default
>
> ------------+-----------------------------+-----------+----------+--------------
> id | integer | | not null |
> note_class | integer | | not null |
> note | text | | not null |
> vector | tsvector | | not null |
> ''::tsvector
> created | timestamp without time zone | | not null | now()
> created_by | text | | |
> SESSION_USER
> ref_key | integer | | not null |
> subject | text | | |
> Indexes:
> "invoice_note_pkey" PRIMARY KEY, btree (id)
> "invoice_note_id_idx" btree (id)
> "invoice_note_vectors_idx" gist (vector)
> Foreign-key constraints:
> "invoice_note_ref_key_fkey" FOREIGN KEY (ref_key) REFERENCES
> invoice(id)
> Inherits: note
>
>
> Note that the "id" column of the "invoice_note" table doesn't have the
> "generated by default as identity". I'm expecting the "invoice_note" table's
> "id" column to have exactly the same definition as the "id" column in the
> "note" table in both situations because the column isn't repeated in the
> definition of the "invoice_note" definition.

The documentation for CREATE TABLE [1] INHERITS says:

If a column in the parent table is an identity column, that property is not
inherited. A column in the child table can be declared identity column if
desired.

[1] https://www.postgresql.org/docs/13/sql-createtable.html

--
Regrads,
Japin Li.