Thread: [BUGS] BUG #14813: pg_get_serial_sequence does not return seqence name forIDENTITY columns
[BUGS] BUG #14813: pg_get_serial_sequence does not return seqence name forIDENTITY columns
From
zam6ak@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 14813 Logged by: zam zam Email address: zam6ak@gmail.com PostgreSQL version: 10beta4 Operating system: windows Description: Function pg_get_serial_sequence returns NULL on columns defined as IDENTITY CREATE TABLE IF NOT EXISTS public.test ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, code text, CONSTRAINT test_pk PRIMARY KEY (id) ) SELECT pg_get_serial_sequence('public.test', 'id'); -- returns NULL instead of 'test_id_seq'! -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14813: pg_get_serial_sequence does not return seqencename for IDENTITY columns
From
"David G. Johnston"
Date:
The following bug has been logged on the website:
Bug reference: 14813
Logged by: zam zam
Email address: zam6ak@gmail.com
PostgreSQL version: 10beta4
Operating system: windows
Description:
Function pg_get_serial_sequence returns NULL on columns defined as
IDENTITY
CREATE TABLE IF NOT EXISTS public.test (
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
code text,
CONSTRAINT test_pk PRIMARY KEY (id)
)
SELECT pg_get_serial_sequence('public.test', 'id'); -- returns NULL instead
of 'test_id_seq'!
I suspect this is an opportunity for documentation enhancement as opposed to a bug.
The CREATE TABLE docs say that: "It will have an implicit sequence attached to it." I take the word "implicit" to mean that an actual external sequence object is not created.
David J.
Re: [BUGS] BUG #14813: pg_get_serial_sequence does not return seqencename for IDENTITY columns
From
zambak zambak
Date:
On Tue, Sep 12, 2017 at 5:54 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
The following bug has been logged on the website:
Bug reference: 14813
Logged by: zam zam
Email address: zam6ak@gmail.com
PostgreSQL version: 10beta4
Operating system: windows
Description:
Function pg_get_serial_sequence returns NULL on columns defined as
IDENTITY
CREATE TABLE IF NOT EXISTS public.test (
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
code text,
CONSTRAINT test_pk PRIMARY KEY (id)
)
SELECT pg_get_serial_sequence('public.test', 'id'); -- returns NULL instead
of 'test_id_seq'!I suspect this is an opportunity for documentation enhancement as opposed to a bug.The CREATE TABLE docs say that: "It will have an implicit sequence attached to it." I take the word "implicit" to mean that an actual external sequence object is not created.David J.
But the sequence 'test_id_seq' is created....
To my understanding (and I may be wrong), identity columns are backed by sequences in PG 10...
Re: [BUGS] BUG #14813: pg_get_serial_sequence does not return seqence name for IDENTITY columns
From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Tue, Sep 12, 2017 at 2:20 PM, <zam6ak@gmail.com> wrote: >> Function pg_get_serial_sequence returns NULL on columns defined as >> IDENTITY This seems like a reasonable complaint to me. > I suspect this is an opportunity for documentation enhancement as opposed > to a bug. The reason appears to be that the dependency setup for a sequence created to support IDENTITY is randomly different from that for a serial sequence. I'm not sure this is a good thing. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14813: pg_get_serial_sequence does not returnseqence name for IDENTITY columns
From
Alvaro Herrera
Date:
Tom Lane wrote: > "David G. Johnston" <david.g.johnston@gmail.com> writes: > > I suspect this is an opportunity for documentation enhancement as opposed > > to a bug. > > The reason appears to be that the dependency setup for a sequence created > to support IDENTITY is randomly different from that for a serial sequence. > I'm not sure this is a good thing. It would be bad to break queries all over the internet that assume that sequences for columns can be found using pg_get_serial_sequence, so I agree this should be fixed. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14813: pg_get_serial_sequence does not return seqencename for IDENTITY columns
From
Peter Eisentraut
Date:
On 9/13/17 09:25, Alvaro Herrera wrote: > Tom Lane wrote: >> "David G. Johnston" <david.g.johnston@gmail.com> writes: > >>> I suspect this is an opportunity for documentation enhancement as opposed >>> to a bug. >> >> The reason appears to be that the dependency setup for a sequence created >> to support IDENTITY is randomly different from that for a serial sequence. >> I'm not sure this is a good thing. > > It would be bad to break queries all over the internet that assume that > sequences for columns can be found using pg_get_serial_sequence, so I > agree this should be fixed. Proposed patch attached. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Attachment
Re: [BUGS] BUG #14813: pg_get_serial_sequence does not return seqencename for IDENTITY columns
From
Peter Eisentraut
Date:
On 9/14/17 17:15, Peter Eisentraut wrote: > On 9/13/17 09:25, Alvaro Herrera wrote: >> Tom Lane wrote: >>> "David G. Johnston" <david.g.johnston@gmail.com> writes: >> >>>> I suspect this is an opportunity for documentation enhancement as opposed >>>> to a bug. >>> >>> The reason appears to be that the dependency setup for a sequence created >>> to support IDENTITY is randomly different from that for a serial sequence. >>> I'm not sure this is a good thing. >> >> It would be bad to break queries all over the internet that assume that >> sequences for columns can be found using pg_get_serial_sequence, so I >> agree this should be fixed. > > Proposed patch attached. committed -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs