Skip to content

Commit 67dc4cc

Browse files
peterepetere
committed
Add pg_sequences view
Like pg_tables, pg_views, and others, this view contains information about sequences in a way that is independent of the system catalog layout but more comprehensive than the information schema. To help implement the view, add a new internal function pg_sequence_last_value() to return the last value of a sequence. This is kept separate from pg_sequence_parameters() to separate querying run-time state from catalog-like information. Reviewed-by: Andreas Karlsson <andreas@proxel.se>
1 parent 8f91f32 commit 67dc4cc

File tree

10 files changed

+233
-10
lines changed

10 files changed

+233
-10
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7394,6 +7394,11 @@
73947394
<entry>security labels</entry>
73957395
</row>
73967396

7397+
<row>
7398+
<entry><link linkend="view-pg-sequences"><structname>pg_sequences</structname></link></entry>
7399+
<entry>sequences</entry>
7400+
</row>
7401+
73977402
<row>
73987403
<entry><link linkend="view-pg-settings"><structname>pg_settings</structname></link></entry>
73997404
<entry>parameter settings</entry>
@@ -9135,6 +9140,98 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
91359140
</table>
91369141
</sect1>
91379142

9143+
<sect1 id="view-pg-sequences">
9144+
<title><structname>pg_sequences</structname></title>
9145+
9146+
<indexterm zone="view-pg-sequences">
9147+
<primary>pg_sequences</primary>
9148+
</indexterm>
9149+
9150+
<para>
9151+
The view <structname>pg_sequences</structname> provides access to
9152+
useful information about each sequence in the database.
9153+
</para>
9154+
9155+
<table>
9156+
<title><structname>pg_sequences</> Columns</title>
9157+
9158+
<tgroup cols="4">
9159+
<thead>
9160+
<row>
9161+
<entry>Name</entry>
9162+
<entry>Type</entry>
9163+
<entry>References</entry>
9164+
<entry>Description</entry>
9165+
</row>
9166+
</thead>
9167+
<tbody>
9168+
<row>
9169+
<entry><structfield>schemaname</structfield></entry>
9170+
<entry><type>name</type></entry>
9171+
<entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
9172+
<entry>Name of schema containing sequence</entry>
9173+
</row>
9174+
<row>
9175+
<entry><structfield>sequencename</structfield></entry>
9176+
<entry><type>name</type></entry>
9177+
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
9178+
<entry>Name of sequence</entry>
9179+
</row>
9180+
<row>
9181+
<entry><structfield>sequenceowner</structfield></entry>
9182+
<entry><type>name</type></entry>
9183+
<entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.rolname</literal></entry>
9184+
<entry>Name of sequence's owner</entry>
9185+
</row>
9186+
<row>
9187+
<entry><structfield>start_value</structfield></entry>
9188+
<entry><type>bigint</type></entry>
9189+
<entry></entry>
9190+
<entry>Start value of the sequence</entry>
9191+
</row>
9192+
<row>
9193+
<entry><structfield>min_value</structfield></entry>
9194+
<entry><type>bigint</type></entry>
9195+
<entry></entry>
9196+
<entry>Minimum value of the sequence</entry>
9197+
</row>
9198+
<row>
9199+
<entry><structfield>max_value</structfield></entry>
9200+
<entry><type>bigint</type></entry>
9201+
<entry></entry>
9202+
<entry>Maximum value of the sequence</entry>
9203+
</row>
9204+
<row>
9205+
<entry><structfield>increment_by</structfield></entry>
9206+
<entry><type>bigint</type></entry>
9207+
<entry></entry>
9208+
<entry>Increment value of the sequence</entry>
9209+
</row>
9210+
<row>
9211+
<entry><structfield>cycle</structfield></entry>
9212+
<entry><type>boolean</type></entry>
9213+
<entry></entry>
9214+
<entry>Whether the sequence cycles</entry>
9215+
</row>
9216+
<row>
9217+
<entry><structfield>cache_size</structfield></entry>
9218+
<entry><type>bigint</type></entry>
9219+
<entry></entry>
9220+
<entry>Cache size of the sequence</entry>
9221+
</row>
9222+
<row>
9223+
<entry><structfield>last_value</structfield></entry>
9224+
<entry><type>bigint</type></entry>
9225+
<entry></entry>
9226+
<entry>The last sequence value written to disk. If caching is used,
9227+
this value can be greater than the last value handed out from the
9228+
sequence. Null if the sequence has not been read from yet.</entry>
9229+
</row>
9230+
</tbody>
9231+
</tgroup>
9232+
</table>
9233+
</sect1>
9234+
91389235
<sect1 id="view-pg-settings">
91399236
<title><structname>pg_settings</structname></title>
91409237

‎src/backend/catalog/system_views.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -158,6 +158,23 @@ CREATE VIEW pg_indexes AS
158158
LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace)
159159
WHERE C.relkind IN ('r', 'm') AND I.relkind = 'i';
160160

161+
CREATE OR REPLACE VIEW pg_sequences AS
162+
SELECT
163+
N.nspname AS schemaname,
164+
C.relname AS sequencename,
165+
pg_get_userbyid(C.relowner) AS sequenceowner,
166+
p.start_value AS start_value,
167+
p.minimum_value AS min_value,
168+
p.maximum_value AS max_value,
169+
p.increment AS increment_by,
170+
p.cycle_option AS cycle,
171+
p.cache_size AS cache_size,
172+
pg_sequence_last_value(C.oid) AS last_value
173+
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace),
174+
LATERAL pg_sequence_parameters(C.oid) p
175+
WHERE NOT pg_is_other_temp_schema(N.oid)
176+
AND relkind = 'S';
177+
161178
CREATE VIEW pg_stats WITH (security_barrier) AS
162179
SELECT
163180
nspname AS schemaname,

‎src/backend/commands/sequence.c

Lines changed: 46 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1534,8 +1534,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
15341534
{
15351535
Oid relid = PG_GETARG_OID(0);
15361536
TupleDesc tupdesc;
1537-
Datum values[5];
1538-
bool isnull[5];
1537+
Datum values[6];
1538+
bool isnull[6];
15391539
SeqTable elm;
15401540
Relation seqrel;
15411541
Buffer buf;
@@ -1551,7 +1551,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
15511551
errmsg("permission denied for sequence %s",
15521552
RelationGetRelationName(seqrel))));
15531553

1554-
tupdesc = CreateTemplateTupleDesc(5, false);
1554+
tupdesc = CreateTemplateTupleDesc(6, false);
15551555
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "start_value",
15561556
INT8OID, -1, 0);
15571557
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "minimum_value",
@@ -1562,6 +1562,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
15621562
INT8OID, -1, 0);
15631563
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "cycle_option",
15641564
BOOLOID, -1, 0);
1565+
TupleDescInitEntry(tupdesc, (AttrNumber) 6, "cache_size",
1566+
INT8OID, -1, 0);
15651567

15661568
BlessTupleDesc(tupdesc);
15671569

@@ -1574,13 +1576,54 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
15741576
values[2] = Int64GetDatum(seq->max_value);
15751577
values[3] = Int64GetDatum(seq->increment_by);
15761578
values[4] = BoolGetDatum(seq->is_cycled);
1579+
values[5] = Int64GetDatum(seq->cache_value);
15771580

15781581
UnlockReleaseBuffer(buf);
15791582
relation_close(seqrel, NoLock);
15801583

15811584
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
15821585
}
15831586

1587+
/*
1588+
* Return the last value from the sequence
1589+
*
1590+
* Note: This has a completely different meaning than lastval().
1591+
*/
1592+
Datum
1593+
pg_sequence_last_value(PG_FUNCTION_ARGS)
1594+
{
1595+
Oid relid = PG_GETARG_OID(0);
1596+
SeqTable elm;
1597+
Relation seqrel;
1598+
Buffer buf;
1599+
HeapTupleData seqtuple;
1600+
Form_pg_sequence seq;
1601+
bool is_called;
1602+
int64 result;
1603+
1604+
/* open and AccessShareLock sequence */
1605+
init_sequence(relid, &elm, &seqrel);
1606+
1607+
if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_USAGE) != ACLCHECK_OK)
1608+
ereport(ERROR,
1609+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1610+
errmsg("permission denied for sequence %s",
1611+
RelationGetRelationName(seqrel))));
1612+
1613+
seq = read_seq_tuple(elm, seqrel, &buf, &seqtuple);
1614+
1615+
is_called = seq->is_called;
1616+
result = seq->last_value;
1617+
1618+
UnlockReleaseBuffer(buf);
1619+
relation_close(seqrel, NoLock);
1620+
1621+
if (is_called)
1622+
PG_RETURN_INT64(result);
1623+
else
1624+
PG_RETURN_NULL();
1625+
}
1626+
15841627

15851628
void
15861629
seq_redo(XLogReaderState *record)

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201611041
56+
#define CATALOG_VERSION_NO 201611181
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1763,8 +1763,10 @@ DATA(insert OID = 1576 ( setval PGNSP PGUID 12 1 0 0 0 f f f f t f v u 2 0 20
17631763
DESCR("set sequence value");
17641764
DATA(insert OID = 1765 ( setval PGNSP PGUID 12 1 0 0 0 f f f f t f v u 3 0 20 "2205 20 16" _null_ _null_ _null_ _null_ _null_ setval3_oid _null_ _null_ _null_ ));
17651765
DESCR("set sequence value and is_called status");
1766-
DATA(insert OID = 3078 ( pg_sequence_parameters PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16}" "{i,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
1766+
DATA(insert OID = 3078 ( pg_sequence_parameters PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 2249 "26" "{26,20,20,20,20,16,20}" "{i,o,o,o,o,o,o}" "{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option,cache_size}" _null_ _null_ pg_sequence_parameters _null_ _null_ _null_));
17671767
DESCR("sequence parameters, for use by information schema");
1768+
DATA(insert OID = 4032 ( pg_sequence_last_value PGNSP PGUID 12 1 0 0 0 f f f f t f v u 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ pg_sequence_last_value _null_ _null_ _null_ ));
1769+
DESCR("sequence last value");
17681770

17691771
DATA(insert OID = 1579 ( varbit_in PGNSP PGUID 12 1 0 0 0 f f f f t f i s 3 0 1562 "2275 26 23" _null_ _null_ _null_ _null_ _null_ varbit_in _null_ _null_ _null_ ));
17701772
DESCR("I/O");

‎src/include/commands/sequence.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -73,6 +73,7 @@ extern Datum setval3_oid(PG_FUNCTION_ARGS);
7373
extern Datum lastval(PG_FUNCTION_ARGS);
7474

7575
extern Datum pg_sequence_parameters(PG_FUNCTION_ARGS);
76+
extern Datum pg_sequence_last_value(PG_FUNCTION_ARGS);
7677

7778
extern ObjectAddress DefineSequence(ParseState *pstate, CreateSeqStmt *stmt);
7879
extern ObjectAddress AlterSequence(ParseState *pstate, AlterSeqStmt *stmt);

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

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1615,6 +1615,20 @@ UNION ALL
16151615
l.label
16161616
FROM (pg_shseclabel l
16171617
JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
1618+
pg_sequences| SELECT n.nspname AS schemaname,
1619+
c.relname AS sequencename,
1620+
pg_get_userbyid(c.relowner) AS sequenceowner,
1621+
p.start_value,
1622+
p.minimum_value AS min_value,
1623+
p.maximum_value AS max_value,
1624+
p.increment AS increment_by,
1625+
p.cycle_option AS cycle,
1626+
p.cache_size,
1627+
pg_sequence_last_value((c.oid)::regclass) AS last_value
1628+
FROM (pg_class c
1629+
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))),
1630+
LATERAL pg_sequence_parameters(c.oid) p(start_value, minimum_value, maximum_value, increment, cycle_option, cache_size)
1631+
WHERE ((NOT pg_is_other_temp_schema(n.oid)) AND (c.relkind = 'S'::"char"));
16181632
pg_settings| SELECT a.name,
16191633
a.setting,
16201634
a.unit,

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

Lines changed: 21 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -300,20 +300,39 @@ SELECT nextval('sequence_test2');
300300
5
301301
(1 row)
302302

303+
CREATE SEQUENCE sequence_test3; -- not read from, to test is_called
303304
-- Information schema
304305
SELECT * FROM information_schema.sequences WHERE sequence_name IN
305-
('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
306+
('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
306307
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
307308
ORDER BY sequence_name ASC;
308309
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
309310
------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
310311
regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
312+
regression | public | sequence_test3 | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
311313
regression | public | serialtest2_f2_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
312314
regression | public | serialtest2_f3_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
313315
regression | public | serialtest2_f4_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
314316
regression | public | serialtest2_f5_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
315317
regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
316-
(6 rows)
318+
(7 rows)
319+
320+
SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
321+
FROM pg_sequences
322+
WHERE sequencename IN
323+
('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
324+
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
325+
ORDER BY sequencename ASC;
326+
schemaname | sequencename | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
327+
------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
328+
public | sequence_test2 | 32 | 5 | 36 | 4 | t | 1 | 5
329+
public | sequence_test3 | 1 | 1 | 9223372036854775807 | 1 | f | 1 |
330+
public | serialtest2_f2_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
331+
public | serialtest2_f3_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
332+
public | serialtest2_f4_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
333+
public | serialtest2_f5_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
334+
public | serialtest2_f6_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
335+
(7 rows)
317336

318337
-- Test comments
319338
COMMENT ON SEQUENCE asdf IS 'won''t work';

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

Lines changed: 21 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -300,20 +300,39 @@ SELECT nextval('sequence_test2');
300300
5
301301
(1 row)
302302

303+
CREATE SEQUENCE sequence_test3; -- not read from, to test is_called
303304
-- Information schema
304305
SELECT * FROM information_schema.sequences WHERE sequence_name IN
305-
('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
306+
('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
306307
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
307308
ORDER BY sequence_name ASC;
308309
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
309310
------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
310311
regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
312+
regression | public | sequence_test3 | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
311313
regression | public | serialtest2_f2_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
312314
regression | public | serialtest2_f3_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
313315
regression | public | serialtest2_f4_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
314316
regression | public | serialtest2_f5_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
315317
regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
316-
(6 rows)
318+
(7 rows)
319+
320+
SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
321+
FROM pg_sequences
322+
WHERE sequencename IN
323+
('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
324+
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
325+
ORDER BY sequencename ASC;
326+
schemaname | sequencename | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
327+
------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
328+
public | sequence_test2 | 32 | 5 | 36 | 4 | t | 1 | 5
329+
public | sequence_test3 | 1 | 1 | 9223372036854775807 | 1 | f | 1 |
330+
public | serialtest2_f2_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
331+
public | serialtest2_f3_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
332+
public | serialtest2_f4_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
333+
public | serialtest2_f5_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
334+
public | serialtest2_f6_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
335+
(7 rows)
317336

318337
-- Test comments
319338
COMMENT ON SEQUENCE asdf IS 'won''t work';

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

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -138,12 +138,23 @@ SELECT nextval('sequence_test2');
138138
SELECT nextval('sequence_test2');
139139
SELECT nextval('sequence_test2');
140140

141+
142+
CREATE SEQUENCE sequence_test3; -- not read from, to test is_called
143+
144+
141145
-- Information schema
142146
SELECT * FROM information_schema.sequences WHERE sequence_name IN
143-
('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
147+
('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
144148
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
145149
ORDER BY sequence_name ASC;
146150

151+
SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
152+
FROM pg_sequences
153+
WHERE sequencename IN
154+
('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
155+
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
156+
ORDER BY sequencename ASC;
157+
147158
-- Test comments
148159
COMMENT ON SEQUENCE asdf IS 'won''t work';
149160
COMMENT ON SEQUENCE sequence_test2 IS 'will work';

0 commit comments

Comments
 (0)