pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is)
3+ messages / 3 participants
[nested] [flat]
* BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is)
@ 2021-09-23 21:14 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2021-09-23 21:14 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ehuels@gmail.com
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is)
@ 2021-09-24 02:32 Japin Li <japinli@hotmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Japin Li @ 2021-09-24 02:32 UTC (permalink / raw)
To: ehuels@gmail.com; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is)
@ 2021-09-24 10:04 Erik Huelsmann <ehuels@gmail.com>
parent: Japin Li <japinli@hotmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Erik Huelsmann @ 2021-09-24 10:04 UTC (permalink / raw)
To: Japin Li <japinli@hotmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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] 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-09-24 10:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-23 21:14 BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is) PG Bug reporting form <noreply@postgresql.org>
2021-09-24 02:32 ` Japin Li <japinli@hotmail.com>
2021-09-24 10:04 ` Erik Huelsmann <ehuels@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox