Skip to content

Commit 3012061

Browse files
peterepetere
committed
Apply pg_get_serial_sequence() to identity column sequences as well
Bug: #14813
1 parent f0e60ee commit 3012061

File tree

6 files changed

+44
-20
lines changed

6 files changed

+44
-20
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 22 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -17034,8 +17034,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1703417034
<row>
1703517035
<entry><literal><function>pg_get_serial_sequence(<parameter>table_name</parameter>, <parameter>column_name</parameter>)</function></literal></entry>
1703617036
<entry><type>text</type></entry>
17037-
<entry>get name of the sequence that a <type>serial</type>, <type>smallserial</type> or <type>bigserial</type> column
17038-
uses</entry>
17037+
<entry>get name of the sequence that a serial or identity column uses</entry>
1703917038
</row>
1704017039
<row>
1704117040
<entry><literal><function>pg_get_statisticsobjdef(<parameter>statobj_oid</parameter>)</function></literal></entry>
@@ -17223,19 +17222,27 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
1722317222
<para>
1722417223
<function>pg_get_serial_sequence</function> returns the name of the
1722517224
sequence associated with a column, or NULL if no sequence is associated
17226-
with the column. The first input parameter is a table name with
17227-
optional schema, and the second parameter is a column name. Because
17228-
the first parameter is potentially a schema and table, it is not treated
17229-
as a double-quoted identifier, meaning it is lower cased by default,
17230-
while the second parameter, being just a column name, is treated as
17231-
double-quoted and has its case preserved. The function returns a value
17232-
suitably formatted for passing to sequence functions (see <xref
17233-
linkend="functions-sequence">). This association can be modified or
17234-
removed with <command>ALTER SEQUENCE OWNED BY</>. (The function
17235-
probably should have been called
17236-
<function>pg_get_owned_sequence</function>; its current name reflects the fact
17237-
that it's typically used with <type>serial</> or <type>bigserial</>
17238-
columns.)
17225+
with the column. If the column is an identity column, the associated
17226+
sequence is the sequence internally created for the identity column. For
17227+
columns created using one of the serial types
17228+
(<type>serial</type>, <type>smallserial</type>, <type>bigserial</type>), it
17229+
is the sequence created for that serial column definition. In the latter
17230+
case, this association can be modified or removed with <command>ALTER
17231+
SEQUENCE OWNED BY</>. (The function probably should have been called
17232+
<function>pg_get_owned_sequence</function>; its current name reflects the
17233+
fact that it has typically been used with <type>serial</>
17234+
or <type>bigserial</> columns.) The first input parameter is a table name
17235+
with optional schema, and the second parameter is a column name. Because
17236+
the first parameter is potentially a schema and table, it is not treated as
17237+
a double-quoted identifier, meaning it is lower cased by default, while the
17238+
second parameter, being just a column name, is treated as double-quoted and
17239+
has its case preserved. The function returns a value suitably formatted
17240+
for passing to sequence functions
17241+
(see <xref linkend="functions-sequence">). A typical use is in reading the
17242+
current value of a sequence for an identity or serial column, for example:
17243+
<programlisting>
17244+
SELECT currval(pg_get_serial_sequence('sometable', 'id'));
17245+
</programlisting>
1723917246
</para>
1724017247

1724117248
<para>

‎src/backend/utils/adt/ruleutils.c

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2322,7 +2322,7 @@ pg_get_userbyid(PG_FUNCTION_ARGS)
23222322

23232323
/*
23242324
* pg_get_serial_sequence
2325-
* Get the name of the sequence used by a serial column,
2325+
* Get the name of the sequence used by an identity or serial column,
23262326
* formatted suitably for passing to setval, nextval or currval.
23272327
* First parameter is not treated as double-quoted, second parameter
23282328
* is --- see documentation for reason.
@@ -2380,13 +2380,14 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
23802380
Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup);
23812381

23822382
/*
2383-
* We assume any auto dependency of a sequence on a column must be
2384-
* what we are looking for. (We need the relkind test because indexes
2385-
* can also have auto dependencies on columns.)
2383+
* Look for an auto dependency (serial column) or internal dependency
2384+
* (identity column) of a sequence on a column. (We need the relkind
2385+
* test because indexes can also have auto dependencies on columns.)
23862386
*/
23872387
if (deprec->classid == RelationRelationId &&
23882388
deprec->objsubid == 0 &&
2389-
deprec->deptype == DEPENDENCY_AUTO &&
2389+
(deprec->deptype == DEPENDENCY_AUTO ||
2390+
deprec->deptype == DEPENDENCY_INTERNAL) &&
23902391
get_rel_relkind(deprec->objid) == RELKIND_SEQUENCE)
23912392
{
23922393
sequenceId = deprec->objid;

‎src/test/regress/expected/identity.out

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,12 @@ SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE
2626
---------------
2727
(0 rows)
2828

29+
SELECT pg_get_serial_sequence('itest1', 'a');
30+
pg_get_serial_sequence
31+
------------------------
32+
public.itest1_a_seq
33+
(1 row)
34+
2935
CREATE TABLE itest4 (a int, b text);
3036
ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL
3137
ERROR: column "a" of relation "itest4" must be declared NOT NULL before identity can be added

‎src/test/regress/expected/sequence.out

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -79,6 +79,12 @@ SELECT * FROM serialTest1;
7979
force | 100
8080
(3 rows)
8181

82+
SELECT pg_get_serial_sequence('serialTest1', 'f2');
83+
pg_get_serial_sequence
84+
---------------------------
85+
public.serialtest1_f2_seq
86+
(1 row)
87+
8288
-- test smallserial / bigserial
8389
CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
8490
f5 bigserial, f6 serial8);

‎src/test/regress/sql/identity.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,8 @@ SELECT table_name, column_name, column_default, is_nullable, is_identity, identi
1212
-- internal sequences should not be shown here
1313
SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%';
1414

15+
SELECT pg_get_serial_sequence('itest1', 'a');
16+
1517
CREATE TABLE itest4 (a int, b text);
1618
ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL
1719
ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL;

‎src/test/regress/sql/sequence.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,8 @@ INSERT INTO serialTest1 VALUES ('wrong', NULL);
6161

6262
SELECT * FROM serialTest1;
6363

64+
SELECT pg_get_serial_sequence('serialTest1', 'f2');
65+
6466
-- test smallserial / bigserial
6567
CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
6668
f5 bigserial, f6 serial8);

0 commit comments

Comments
 (0)