Re: 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(at)bignose(dot)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(at)bignose(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
V S P
toreason(at)fastmail(dot)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(at)bignose(dot)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(at)commandprompt(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +