pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #2907: pg_get_serial_sequence quoting
9+ messages / 4 participants
[nested] [flat]
* BUG #2907: pg_get_serial_sequence quoting
@ 2007-01-19 05:52 Adriaan van Os <postgres@microbizz.nl>
0 siblings, 1 reply; 9+ messages in thread
From: Adriaan van Os @ 2007-01-19 05:52 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
The following bug has been logged online:
Bug reference: 2907
Logged by: Adriaan van Os
Email address: postgres@microbizz.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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #2907: pg_get_serial_sequence quoting
@ 2007-01-20 01:38 Bruce Momjian <bruce@momjian.us>
parent: Adriaan van Os <postgres@microbizz.nl>
0 siblings, 2 replies; 9+ messages in thread
From: Bruce Momjian @ 2007-01-20 01:38 UTC (permalink / raw)
To: Adriaan van Os <postgres@microbizz.nl>; +Cc: pgsql-bugs@postgresql.org
Adriaan van Os wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2907
> Logged by: Adriaan van Os
> Email address: postgres@microbizz.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@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #2907: pg_get_serial_sequence quoting
@ 2007-01-20 05:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2007-01-20 05:03 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Adriaan van Os <postgres@microbizz.nl>; pgsql-bugs@postgresql.org
Bruce Momjian <bruce@momjian.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #2907: pg_get_serial_sequence quoting
@ 2007-01-20 09:20 Adriaan van Os <postgres@microbizz.nl>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 9+ messages in thread
From: Adriaan van Os @ 2007-01-20 09:20 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [BUGS] BUG #2907: pg_get_serial_sequence quoting
@ 2007-01-20 14:27 Bruce Momjian <bruce@momjian.us>
parent: Adriaan van Os <postgres@microbizz.nl>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2007-01-20 14:27 UTC (permalink / raw)
To: Adriaan van Os <postgres@microbizz.nl>; +Cc: pgsql-hackers
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@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [BUGS] BUG #2907: pg_get_serial_sequence quoting
@ 2007-01-21 01:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2007-01-21 01:56 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Adriaan van Os <postgres@microbizz.nl>; pgsql-hackers
Bruce Momjian <bruce@momjian.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [HACKERS] BUG #2907: pg_get_serial_sequence quoting
@ 2007-01-22 09:19 Adriaan van Os <adriaan@microbizz.nl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Adriaan van Os @ 2007-01-22 09:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers; pgsql-bugs@postgresql.org
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [HACKERS] [BUGS] BUG #2907: pg_get_serial_sequence quoting
@ 2007-01-30 02:34 Bruce Momjian <bruce@momjian.us>
parent: Adriaan van Os <adriaan@microbizz.nl>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2007-01-30 02:34 UTC (permalink / raw)
To: Adriaan van Os <adriaan@microbizz.nl>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL-patches <pgsql-patches@postgresql.org>
Adriaan van Os wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.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@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachments:
/rtmp/diff (text/x-diff, 2.7K) download
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.352
diff -c -c -r1.352 func.sgml
*** doc/src/sgml/func.sgml 20 Jan 2007 21:59:34 -0000 1.352
--- doc/src/sgml/func.sgml 30 Jan 2007 02:16:17 -0000
***************
*** 9892,9903 ****
</para>
<para>
! <function>pg_get_serial_sequence</function> fetches the name of the
! sequence associated with a column, or NULL if there is no sequence
! associated with the column. The result is suitably formatted for passing
! to the sequence functions (see <xref linkend="functions-sequence">).
! This association can be modified or removed with <command>ALTER SEQUENCE
! OWNED BY</>. (The function probably should have been called
<function>pg_get_owned_sequence</function>; its name reflects the fact
that it's typically used with <type>serial</> or <type>bigserial</>
columns.)
--- 9892,9909 ----
</para>
<para>
! <function>pg_get_serial_sequence</function> returns the name of the
! sequence associated with a column, or NULL if no sequence is associated
! with the column. The first input parameter is a table name with
! optional schema, and the second parameter is a column name. Because
! the first parameter is potentially a schema and table, it is not treated
! as a double-quoted identifier, meaning it is lowercased by default,
! while the second parameter, being just a column name, is treated as
! double-quoted and has its case preserved. The function returns a value
! suitably formatted for passing to the sequence functions (see <xref
! linkend="functions-sequence">). This association can be modified or
! removed with <command>ALTER SEQUENCE OWNED BY</>. (The function
! probably should have been called
<function>pg_get_owned_sequence</function>; its name reflects the fact
that it's typically used with <type>serial</> or <type>bigserial</>
columns.)
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.246
diff -c -c -r1.246 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 25 Jan 2007 04:17:46 -0000 1.246
--- src/backend/utils/adt/ruleutils.c 30 Jan 2007 02:16:19 -0000
***************
*** 1265,1270 ****
--- 1265,1272 ----
* pg_get_serial_sequence
* Get the name of the sequence used by a serial column,
* formatted suitably for passing to setval, nextval or currval.
+ * First parameter is not treated as double-quoted, second parameter
+ * is --- see documentation for reason.
*/
Datum
pg_get_serial_sequence(PG_FUNCTION_ARGS)
* Re: [HACKERS] [BUGS] BUG #2907: pg_get_serial_sequence quoting
@ 2007-01-30 15:10 Adriaan van Os <postgres@microbizz.nl>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 9+ messages in thread
From: Adriaan van Os @ 2007-01-30 15:10 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: PostgreSQL-patches <pgsql-patches@postgresql.org>
Bruce Momjian wrote:
> Adriaan van Os wrote:
>> Tom Lane wrote:
>>> Bruce Momjian <bruce@momjian.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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-01-30 15:10 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-19 05:52 BUG #2907: pg_get_serial_sequence quoting Adriaan van Os <postgres@microbizz.nl>
2007-01-20 01:38 ` Bruce Momjian <bruce@momjian.us>
2007-01-20 05:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-20 09:20 ` Adriaan van Os <postgres@microbizz.nl>
2007-01-20 14:27 ` Bruce Momjian <bruce@momjian.us>
2007-01-21 01:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-01-22 09:19 ` Adriaan van Os <adriaan@microbizz.nl>
2007-01-30 02:34 ` Bruce Momjian <bruce@momjian.us>
2007-01-30 15:10 ` Adriaan van Os <postgres@microbizz.nl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox