Re: BUG #2907: pg_get_serial_sequence quoting

The following bug has been logged online:

Bug reference: 2907
Logged by: Adriaan van Os
Email address: postgres(at)microbizz(dot)nl
PostgreSQL version: 8.1.4
Operating system: Mac OS X 10.4.6, intel
Description: pg_get_serial_sequence quoting
Details:

In order to work with capitals (etc.), the table_name parameter of
pg_get_serial_sequence needs double quotes inside single quotes, the
column_name parameter requires a name within single quotes only.

Adriaan van Os wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2907
> Logged by: Adriaan van Os
> Email address: postgres(at)microbizz(dot)nl
> PostgreSQL version: 8.1.4
> Operating system: Mac OS X 10.4.6, intel
> Description: pg_get_serial_sequence quoting
> Details:
>
> In order to work with capitals (etc.), the table_name parameter of
> pg_get_serial_sequence needs double quotes inside single quotes, the
> column_name parameter requires a name within single quotes only.

I can confirm this is still a problem in current CVS:

test=> CREATE TABLE "Test" ("Xx" SERIAL);
NOTICE: CREATE TABLE will create implicit sequence "Test_Xx_seq" for serial column "Test.Xx"
CREATE TABLE
test=> SELECT pg_get_serial_sequence('Test', 'xX');
ERROR: relation "test" does not exist
test=> SELECT pg_get_serial_sequence('"Test"', 'Xx');
pg_get_serial_sequence
------------------------
PUBLIC."Test_Xx_seq"
(1 row)

test=> SELECT pg_get_serial_sequence('"Test"', 'xx');
ERROR: column "xx" of relation "Test" does not exist

Strangely, this was reported before, but not until November of 2006:

http://archives.postgresql.org/pgsql-general/2006-11/msg01111.php

We have it in the queue to review for 8.3. Hopefully there will a
change or documentation addition for this in 8.3.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Adriaan van Os wrote:
>> In order to work with capitals (etc.), the table_name parameter of
>> pg_get_serial_sequence needs double quotes inside single quotes, the
>> column_name parameter requires a name within single quotes only.

> I can confirm this is still a problem in current CVS:

This is not a bug, only a documentation issue.

regards, tom lane

Bruce Momjian wrote:

> Strangely, this was reported before, but not until November of 2006:
>
> http://archives.postgresql.org/pgsql-general/2006-11/msg01111.php

That was a follow up on this thread
<http://archives.postgresql.org/pgsql-hackers/2004-10/msg00964.php>.

Regards,

Adriaan van Os

Adriaan van Os wrote:
> Bruce Momjian wrote:
>
> > Strangely, this was reported before, but not until November of 2006:
> >
> > http://archives.postgresql.org/pgsql-general/2006-11/msg01111.php
>
> That was a follow up on this thread
> <http://archives.postgresql.org/pgsql-hackers/2004-10/msg00964.php>.

Interesting. In this thread the reason for the current behavior is
given as:

> I presume the reason for that is that the first paramater can be
> qualified:
>
> select pg_get_serial_sequence('"public"."FOO"', 'Ff1');

Would someone explain why qualification makes us lowercase the first
parameter by default? I don't understand it well enough to document it.
I notice this does not work:

test=> SELECT pg_get_serial_sequence('"Test"', '"Xx"');

So do we just say because quotes might be needed to distinguish the
schame from the table name, we don't auto-quote the first parameter?

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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

Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> I presume the reason for that is that the first paramater can be
>> qualified:
>> select pg_get_serial_sequence('"public"."FOO"', 'Ff1');

> Would someone explain why qualification makes us lowercase the first
> parameter by default? I don't understand it well enough to document it.

The point is that we have to parse the first parameter, whereas the
second one can be taken literally.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>> I presume the reason for that is that the first paramater can be
>>> qualified:
>>> select pg_get_serial_sequence('"public"."FOO"', 'Ff1');
>
>> Would someone explain why qualification makes us lowercase the first
>> parameter by default? I don't understand it well enough to document it.
>
> The point is that we have to parse the first parameter, whereas the
> second one can be taken literally.

It still looks inconsistent and ugly. I think the design mistake of pg_get_serial_sequence is that
it takes two parameters rather than one (a fully qualified doublequoted columnname path) or three
(optionally empty schema, tablename, columnname, all three literal).

Regards,

Adriaan van Os

Adriaan van Os wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> >>> I presume the reason for that is that the first paramater can be
> >>> qualified:
> >>> select pg_get_serial_sequence('"public"."FOO"', 'Ff1');
> >
> >> Would someone explain why qualification makes us lowercase the first
> >> parameter by default? I don't understand it well enough to document it.
> >
> > The point is that we have to parse the first parameter, whereas the
> > second one can be taken literally.
>
> It still looks inconsistent and ugly. I think the design mistake of pg_get_serial_sequence is that
> it takes two parameters rather than one (a fully qualified doublequoted columnname path) or three
> (optionally empty schema, tablename, columnname, all three literal).

I did my best to document the behavior of pg_get_serial_sequence().
There actually is a technical reason why we can't auto-quote the first
parameter. Patch applied to HEAD and 8.2.X.

Example of identifiers with embedded periods:

test=> CREATE TABLE "a.b" ("c.d" SERIAL);
NOTICE: CREATE TABLE will create implicit sequence "a.b_c.d_seq" for serial column "a.b.c.d"
CREATE TABLE

test=> SELECT pg_get_serial_sequence('a.b', 'c.d');
ERROR: schema "a" does not exist

test=> SELECT pg_get_serial_sequence('"a.b"', 'c.d');
pg_get_serial_sequence
------------------------
PUBLIC."a.b_c.d_seq"
(1 row)

test=> SELECT pg_get_serial_sequence('"a.b"', '"c.d"');
ERROR: column ""c.""d of relation "a.b" does not exist

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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

Bruce Momjian wrote:
> Adriaan van Os wrote:
>> Tom Lane wrote:
>>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>>>> I presume the reason for that is that the first paramater can be
>>>>> qualified:
>>>>> select pg_get_serial_sequence('"public"."FOO"', 'Ff1');
>>>> Would someone explain why qualification makes us lowercase the first
>>>> parameter by default? I don't understand it well enough to document it.
>>> The point is that we have to parse the first parameter, whereas the
>>> second one can be taken literally.
>> It still looks inconsistent and ugly. I think the design mistake of pg_get_serial_sequence is that
>> it takes two parameters rather than one (a fully qualified doublequoted columnname path) or three
>> (optionally empty schema, tablename, columnname, all three literal).
>
> I did my best to document the behavior of pg_get_serial_sequence().
> There actually is a technical reason why we can't auto-quote the first
> parameter. Patch applied to HEAD and 8.2.X.

Thanks for the doc change.

Adriaan van Os