Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_get_serial_sequence is inconsistent

pg_get_serial_sequence is inconsistent

From
Tom Lane
Date:
pg_get_serial_sequence() does dequoting/downcasing on its relation-name
argument, but not on its column-name argument.

regression=# create table "FOO" ("Ff1" serial);
NOTICE:  CREATE TABLE will create implicit sequence "FOO_Ff1_seq" for serial column "FOO.Ff1"
CREATE TABLE
regression=# select pg_get_serial_sequence('FOO','Ff1');
ERROR:  column "Ff1" of relation "foo" does not exist
regression=# select pg_get_serial_sequence('"FOO"','Ff1');pg_get_serial_sequence 
------------------------public."FOO_Ff1_seq"
(1 row)

regression=# select pg_get_serial_sequence('"FOO"','"Ff1"');
ERROR:  column ""Ff1"" of relation "FOO" does not exist
regression=# 

This seems pretty inconsistent.  I was expecting to find that it broke
pg_dump, as well, but it looks like pg_dump is expecting exactly
this behavior.

Should we change it?  We need the quote-aware behavior for the relation
name (because that could be schema-qualified) so the choices seem to be
status quo or add dequoting/downcasing to the column name argument.
        regards, tom lane

Re: pg_get_serial_sequence is inconsistent

From
Christopher Kings-Lynne
Date:
> pg_get_serial_sequence() does dequoting/downcasing on its relation-name
> argument, but not on its column-name argument.
> 
> regression=# create table "FOO" ("Ff1" serial);
> NOTICE:  CREATE TABLE will create implicit sequence "FOO_Ff1_seq" for serial column "FOO.Ff1"
> CREATE TABLE
> regression=# select pg_get_serial_sequence('FOO','Ff1');
> ERROR:  column "Ff1" of relation "foo" does not exist
> regression=# select pg_get_serial_sequence('"FOO"','Ff1');
>  pg_get_serial_sequence 
> ------------------------
>  public."FOO_Ff1_seq"
> (1 row)

I presume the reason for that is that the first paramater can be qualified:

select pg_get_serial_sequence('"public"."FOO"', 'Ff1');

Chris

Re: pg_get_serial_sequence is inconsistent

From
Tom Lane
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> pg_get_serial_sequence() does dequoting/downcasing on its relation-name
>> argument, but not on its column-name argument.

> I presume the reason for that is that the first paramater can be qualified:

Right.  From a bare-functionality point of view there's nothing wrong
with it, it just seems inconsistent and therefore likely to trip someone
up in future.

But it seems no one else cares, so I'll shut up about it ...
        regards, tom lane

Re: pg_get_serial_sequence is inconsistent

From
Bruce Momjian
Date:
Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> >> pg_get_serial_sequence() does dequoting/downcasing on its relation-name
> >> argument, but not on its column-name argument.
> 
> > I presume the reason for that is that the first paramater can be qualified:
> 
> Right.  From a bare-functionality point of view there's nothing wrong
> with it, it just seems inconsistent and therefore likely to trip someone
> up in future.
> 
> But it seems no one else cares, so I'll shut up about it ...

That behavior does seem strange to me.  Would make it consistent have
any downsides?  If not, we should do it.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073

Re: pg_get_serial_sequence is inconsistent

From
Christopher Kings-Lynne
Date:
> Right.  From a bare-functionality point of view there's nothing wrong
> with it, it just seems inconsistent and therefore likely to trip someone
> up in future.
> 
> But it seems no one else cares, so I'll shut up about it ...

I'm happy to have it fixed or changed :)  I was just pointing out why it 
was like it is.  It uses two different parsing functions.

Chris

Re: pg_get_serial_sequence is inconsistent

From
Bruce Momjian
Date:
Should we fix this or document it somewhere?  It is the issue of
pg_get_serial_sequence handling upper/lower case differently for its two
arguments, if I remember correctly.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> > Right.  From a bare-functionality point of view there's nothing wrong
> > with it, it just seems inconsistent and therefore likely to trip someone
> > up in future.
> > 
> > But it seems no one else cares, so I'll shut up about it ...
> 
> I'm happy to have it fixed or changed :)  I was just pointing out why it 
> was like it is.  It uses two different parsing functions.
> 
> Chris
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073