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@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.
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. +
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
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@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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
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
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/difftext/x-diffDownload
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)
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