Re: BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is) - Mailing list pgsql-bugs

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] 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.