Обсуждение: pg_get_serial_sequence Strangeness/Unreliable?
Hello everyone, I'm using "PostgreSQL 8.2.5 on amd64-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518" The issue, is that when I run pg_get_serial_sequence on a particular table/column it returns NULL. Here are my tests: simplyas_associations=> \d news_status Table "public.news_status" Column | Type | Modifiers ---------------+----------------------- +------------------------------------------------------------ status_id | integer | not null default nextval('status_status_id_seq'::regclass) newsletter_id | integer | not null status | boolean | not null indate | character varying(15) | not null Indexes: "status_pkey" PRIMARY KEY, btree (status_id) "status_newsletter_id" btree (newsletter_id) simplyas_associations=> select pg_get_serial_sequence('news_status','status_id'); pg_get_serial_sequence ------------------------ (1 row) So, for fun I added a new column to this table , and ran pg_get_serial_sequence there simplyas_associations=> alter table news_status add column test_id serial; NOTICE: ALTER TABLE will create implicit sequence "news_status_test_id_seq" for serial column "news_status.test_id" ALTER TABLE simplyas_associations=> select pg_get_serial_sequence('news_status','test_id'); pg_get_serial_sequence -------------------------------- public.news_status_test_id_seq ------------------------ (1 row) So my new column works. The only next step I could think of was to compare my 2 sequences with \d, turns out their attributes are both identical. Ideas? Thanks Jeff MacDonald
Jeff MacDonald <oss@bignose.ca> writes: > The issue, is that when I run pg_get_serial_sequence on a particular > table/column it returns NULL. Does the column actually own that sequence? Or is its default just something that was inserted manually? regards, tom lane
Did you first insert into public.news_status insert into public.news_status (status_id) values (DEFAULT) and then get the sequence? Also since you have a domain 'public' I personally always do 'set searc_path to public' before doing any SQLs -- this way I know that I do not need to prefix my table names with 'public' all the time. V S P On Tue, 25 Nov 2008 09:46:37 -0400, "Jeff MacDonald" <oss@bignose.ca> said: > Hello everyone, > > I'm using "PostgreSQL 8.2.5 on amd64-portbld-freebsd6.1, compiled by > GCC cc (GCC) 3.4.4 [FreeBSD] 20050518" > > The issue, is that when I run pg_get_serial_sequence on a particular > table/column it returns NULL. > > Here are my tests: > > simplyas_associations=> \d news_status > Table "public.news_status" > Column | Type | > Modifiers > ---------------+----------------------- > +------------------------------------------------------------ > status_id | integer | not null default > nextval('status_status_id_seq'::regclass) > newsletter_id | integer | not null > status | boolean | not null > indate | character varying(15) | not null > Indexes: > "status_pkey" PRIMARY KEY, btree (status_id) > "status_newsletter_id" btree (newsletter_id) > > simplyas_associations=> select > pg_get_serial_sequence('news_status','status_id'); > pg_get_serial_sequence > ------------------------ > > (1 row) > > So, for fun I added a new column to this table , and ran > pg_get_serial_sequence there > > simplyas_associations=> alter table news_status add column test_id > serial; > NOTICE: ALTER TABLE will create implicit sequence > "news_status_test_id_seq" for serial column "news_status.test_id" > ALTER TABLE > simplyas_associations=> select > pg_get_serial_sequence('news_status','test_id'); > pg_get_serial_sequence > -------------------------------- > public.news_status_test_id_seq > ------------------------ > > (1 row) > > So my new column works. The only next step I could think of was to > compare my 2 sequences with \d, turns out their attributes are both > identical. > > Ideas? > > Thanks > > Jeff MacDonald > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- V S P toreason@fastmail.fm -- http://www.fastmail.fm - Or how I learned to stop worrying and love email again
On 25-Nov-08, at 10:44 AM, Tom Lane wrote: > Jeff MacDonald <oss@bignose.ca> writes: >> The issue, is that when I run pg_get_serial_sequence on a particular >> table/column it returns NULL. > > Does the column actually own that sequence? Or is its default just > something that was inserted manually? > Hi Tom, so far as I know the table "owns" the serial in so much as when i do a \d of the table it says this status_id | integer | not null default nextval('status_status_id_seq'::regclass) How else can I check?
On 25-Nov-08, at 10:51 AM, V S P wrote: > > Did you first insert into > public.news_status > > insert into public.news_status (status_id) > values (DEFAULT) > > and then get the sequence? > Hi VSP I'm not sure what relevance this has, a sequence already exists whether you insert into it or not. Just sometimes if you don't insert or select, you cannot get the currval for that session. > > Also since you have a domain 'public' I personally > always do 'set searc_path to public' before doing > any SQLs -- this way I know that I do not need > to prefix my table names with 'public' all the time. I don't have a need for multiple schemas right now, so public is assumed. Thanks tho. jeff.
Jeff MacDonald wrote: > Hi Tom, so far as I know the table "owns" the serial in so much as when i > do a \d of the table it says this > > status_id | integer | not null default > nextval('status_status_id_seq'::regclass) > > How else can I check? He means ALTER SEQUENCE ... OWNED BY I don't know how you can ensure that it is, short of begin; drop table status; \d status_status_id_seq -- verify that the sequence exists; if owned, it should have been dropped too rollback; -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes: > Jeff MacDonald wrote: >> Hi Tom, so far as I know the table "owns" the serial in so much as when i >> do a \d of the table it says this > He means ALTER SEQUENCE ... OWNED BY > I don't know how you can ensure that it is, short of Well, actually, I think the fact that pg_get_serial_sequence isn't working is the most direct way of knowing that the ownership link isn't there ;-). You could grovel around in pg_depend manually but I'm pretty sure of what the outcome will be. I'd try doing an ALTER SEQUENCE OWNED BY and see if that changes the results. The worst that could happen is it takes ownership away from whichever table actually created the sequence, if there was a different one. (My bet is that you got into this state as a result of using some weird combination of pg_dump and server versions.) regards, tom lane
On Wed, Nov 26, 2008 at 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> Jeff MacDonald wrote: >>> Hi Tom, so far as I know the table "owns" the serial in so much as when i >>> do a \d of the table it says this > can we make \d show if the sequence is owned by the table (ie: serial or manually created and owned) or is a manually created and maked default sequence? maybe a flag? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Jaime Casanova wrote: > can we make \d show if the sequence is owned by the table (ie: serial > or manually created and owned) or is a manually created and maked > default sequence? maybe a flag? My thought as well -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
ALTER SEQUENCE OWNED BY worked! Thanks folks. jeff. On 26-Nov-08, at 1:04 PM, Alvaro Herrera wrote: > Jeff MacDonald wrote: > >> Hi Tom, so far as I know the table "owns" the serial in so much as >> when i >> do a \d of the table it says this >> >> status_id | integer | not null default >> nextval('status_status_id_seq'::regclass) >> >> How else can I check? > > He means ALTER SEQUENCE ... OWNED BY > > I don't know how you can ensure that it is, short of > > begin; > drop table status; > \d status_status_id_seq > -- verify that the sequence exists; if owned, it should have been > dropped too > rollback; > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Jaime Casanova wrote: > > > can we make \d show if the sequence is owned by the table (ie: serial > > or manually created and owned) or is a manually created and maked > > default sequence? maybe a flag? > > My thought as well Added to TODO: Have \d on a sequence indicate if the sequences is owned by a table -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +