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@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 17202
Logged by: Erik Huelsmann
Email address: ehuels@gmail.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]https://www.postgresql.org/docs/13/sql-createtable.html 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.

On Fri, Sep 24, 2021 at 4:32 AM Japin Li <japinli@hotmail.com> wrote:

[ snip ]

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

Thanks for your reply! I was reading the documentation about Generated
Columns [1]https://www.postgresql.org/docs/current/ddl-generated-columns.html which says:

If a parent column is a generated column, a child column must also be
a generated column using the same expression. In the definition of the
child column, leave off the GENERATED clause, as it will be copied from the
parent.

which I read to indicate that the identity generator in the child should
have been copied from the parent (the "expression" being referred to being
the implied expression of the generator function).

Concluding: It's not a bug and it's also not a functional equivalent of
SERIAL in light of table inheritance.

[1]: https://www.postgresql.org/docs/current/ddl-generated-columns.html

--
Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.