Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit67dc4cc

Browse files
committed
Add pg_sequences view
Like pg_tables, pg_views, and others, this view contains informationabout sequences in a way that is independent of the system cataloglayout but more comprehensive than the information schema.To help implement the view, add a new internal functionpg_sequence_last_value() to return the last value of a sequence. Thisis kept separate from pg_sequence_parameters() to separate queryingrun-time state from catalog-like information.Reviewed-by: Andreas Karlsson <andreas@proxel.se>
1 parent8f91f32 commit67dc4cc

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 TON (T.oid=I.reltablespace)
159159
WHEREC.relkindIN ('r','m')ANDI.relkind='i';
160160

161+
CREATE OR REPLACEVIEWpg_sequencesAS
162+
SELECT
163+
N.nspnameAS schemaname,
164+
C.relnameAS sequencename,
165+
pg_get_userbyid(C.relowner)AS sequenceowner,
166+
p.start_valueAS start_value,
167+
p.minimum_valueAS min_value,
168+
p.maximum_valueAS max_value,
169+
p.incrementAS increment_by,
170+
p.cycle_optionAS cycle,
171+
p.cache_sizeAS cache_size,
172+
pg_sequence_last_value(C.oid)AS last_value
173+
FROM pg_class CLEFT JOIN pg_namespace NON (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
CREATEVIEWpg_stats WITH (security_barrier)AS
162179
SELECT
163180
nspnameAS 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
Oidrelid=PG_GETARG_OID(0);
15361536
TupleDesctupdesc;
1537-
Datumvalues[5];
1538-
boolisnull[5];
1537+
Datumvalues[6];
1538+
boolisnull[6];
15391539
SeqTableelm;
15401540
Relationseqrel;
15411541
Bufferbuf;
@@ -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
returnHeapTupleGetDatum(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+
Oidrelid=PG_GETARG_OID(0);
1596+
SeqTableelm;
1597+
Relationseqrel;
1598+
Bufferbuf;
1599+
HeapTupleDataseqtuple;
1600+
Form_pg_sequenceseq;
1601+
boolis_called;
1602+
int64result;
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-
#defineCATALOG_VERSION_NO201611041
56+
#defineCATALOG_VERSION_NO201611181
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 ( setvalPGNSP 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 ( setvalPGNSP 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_parametersPGNSP 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_parametersPGNSP 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_valuePGNSP 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_inPGNSP 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
externDatumlastval(PG_FUNCTION_ARGS);
7474

7575
externDatumpg_sequence_parameters(PG_FUNCTION_ARGS);
76+
externDatumpg_sequence_last_value(PG_FUNCTION_ARGS);
7677

7778
externObjectAddressDefineSequence(ParseState*pstate,CreateSeqStmt*stmt);
7879
externObjectAddressAlterSequence(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+
CREATESEQUENCEsequence_test3;-- not read from, to test is_called
143+
144+
141145
-- Information schema
142146
SELECT*FROMinformation_schema.sequencesWHERE sequence_nameIN
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_nameASC;
146150

151+
SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
152+
FROM pg_sequences
153+
WHERE sequencenameIN
154+
('sequence_test2','sequence_test3','serialtest2_f2_seq','serialtest2_f3_seq',
155+
'serialtest2_f4_seq','serialtest2_f5_seq','serialtest2_f6_seq')
156+
ORDER BY sequencenameASC;
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)
[8]ページ先頭
©2009-2025 Movatter.jp