On 9/5/16 10:35 PM, Tom Lane wrote:
> In this viewpoint, we'd keep the sequence-specific data in a pg_sequence
> catalog. pg_sequence rows would be extensions of the associated pg_class
> rows in much the same way that pg_index rows extend the pg_class entries
> for indexes. We should supply a view pg_sequences that performs the
> implied join, and encourage users to select from that rather than directly
> from pg_sequence (compare pg_indexes view).
Let's start with that. Here is a patch that adds a pg_sequences view in
the style of pg_tables, pg_indexes, etc. This seems useful independent
of anything else, but would give us more freedom to change things around
behind the scenes.
A slight naming wart: I added a function lastval(regclass) for internal
use to get a sequence's "last value". But we already have a public
function lastval(), which gets the most recent nextval() result of any
sequence. Those are two quite different things. I don't want to
abandon the term "last value" here, however, because that is what the
sequence relation uses internally, and also Oracle uses it in its system
views with the same semantics that I propose here. We could use a more
verbose name like sequence_last_value(regclass), perhaps.
lastval has been kept separate from pg_sequence_parameters, because if
we were to go ahead with a new catalog layout later, then
pg_sequence_parameters would become obsolescent while we would possibly
still need a lastval function.
The column names of the new view have been deliberately tuned to use a
more conventional style than the information schema while avoiding what
I would consider some past naming mistakes. (For example, I hate
"is_cycled", which reads like "sequence has wrapped around at least once
in the past").
Here are some similar views in other places:
https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2053.htm
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0004203.html
https://msdn.microsoft.com/en-us/library/ff877934.aspx
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From f74e1cc1f6ee4a56abc9f46c413c0af5086e1e40 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Fri, 9 Sep 2016 12:00:00 -0400
Subject: [PATCH] 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.
---
doc/src/sgml/catalogs.sgml | 90 ++++++++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 16 ++++++
src/backend/commands/sequence.c | 45 ++++++++++++++--
src/include/catalog/pg_proc.h | 4 +-
src/include/commands/sequence.h | 1 +
src/test/regress/expected/rules.out | 14 +++++
src/test/regress/expected/sequence.out | 16 ++++++
src/test/regress/expected/sequence_1.out | 16 ++++++
src/test/regress/sql/sequence.sql | 7 +++
9 files changed, 205 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 322d8d6..1c440e3 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -7379,6 +7379,11 @@ <title>System Views</title>
</row>
<row>
+ <entry><link linkend="view-pg-sequences"><structname>pg_sequences</structname></link></entry>
+ <entry>sequences</entry>
+ </row>
+
+ <row>
<entry><link linkend="view-pg-settings"><structname>pg_settings</structname></link></entry>
<entry>parameter settings</entry>
</row>
@@ -9114,6 +9119,91 @@ <title><structname>pg_seclabels</> Columns</title>
</table>
</sect1>
+ <sect1 id="view-pg-sequences">
+ <title><structname>pg_sequences</structname></title>
+
+ <indexterm zone="view-pg-sequences">
+ <primary>pg_sequences</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_sequences</structname> provides access to
+ useful information about each sequence in the database.
+ </para>
+
+ <table>
+ <title><structname>pg_sequences</> Columns</title>
+
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><structfield>schemaname</structfield></entry>
+ <entry><type>name</type></entry>
+ <entry><literal><link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.nspname</literal></entry>
+ <entry>Name of schema containing sequence</entry>
+ </row>
+ <row>
+ <entry><structfield>sequencename</structfield></entry>
+ <entry><type>name</type></entry>
+ <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.relname</literal></entry>
+ <entry>Name of sequence</entry>
+ </row>
+ <row>
+ <entry><structfield>sequenceowner</structfield></entry>
+ <entry><type>name</type></entry>
+ <entry><literal><link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.rolname</literal></entry>
+ <entry>Name of sequence's owner</entry>
+ </row>
+ <row>
+ <entry><structfield>start_value</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry></entry>
+ <entry>Start value of the sequence</entry>
+ </row>
+ <row>
+ <entry><structfield>min_value</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry></entry>
+ <entry>Minimum value of the sequence</entry>
+ </row>
+ <row>
+ <entry><structfield>max_value</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry></entry>
+ <entry>Maximum value of the sequence</entry>
+ </row>
+ <row>
+ <entry><structfield>increment_by</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry></entry>
+ <entry>Increment value of the sequence</entry>
+ </row>
+ <row>
+ <entry><structfield>cycle</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry>Whether the sequence cycles</entry>
+ </row>
+ <row>
+ <entry><structfield>cache_size</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry></entry>
+ <entry>Cache size of the sequence</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
<sect1 id="view-pg-settings">
<title><structname>pg_settings</structname></title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index ada2142..99a9b41 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -158,6 +158,22 @@ CREATE VIEW pg_indexes AS
LEFT JOIN pg_tablespace T ON (T.oid = I.reltablespace)
WHERE C.relkind IN ('r', 'm') AND I.relkind = 'i';
+CREATE OR REPLACE VIEW pg_sequences AS
+ SELECT
+ N.nspname AS schemaname,
+ C.relname AS sequencename,
+ pg_get_userbyid(C.relowner) AS sequenceowner,
+ p.start_value AS start_value,
+ p.minimum_value AS min_value,
+ p.maximum_value AS max_value,
+ p.increment AS increment_by,
+ p.cycle_option AS cycle,
+ p.cache_size AS cache_size,
+ lastval(C.oid) AS last_value
+ FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace),
+ LATERAL pg_sequence_parameters(C.oid) p
+ WHERE relkind = 'S';
+
CREATE VIEW pg_stats WITH (security_barrier) AS
SELECT
nspname AS schemaname,
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index fc3a8ee..4f3ca0e 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -835,6 +835,42 @@ lastval(PG_FUNCTION_ARGS)
}
/*
+ * Return the last value from the sequence
+ *
+ * XXX This has completely different semantics from lastval() without
+ * arguments.
+ */
+Datum
+lastval1(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ SeqTable elm;
+ Relation seqrel;
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence seq;
+ int64 result;
+
+ /* open and AccessShareLock sequence */
+ init_sequence(relid, &elm, &seqrel);
+
+ if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_USAGE) != ACLCHECK_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for sequence %s",
+ RelationGetRelationName(seqrel))));
+
+ seq = read_seq_tuple(elm, seqrel, &buf, &seqtuple);
+
+ result = seq->last_value;
+
+ UnlockReleaseBuffer(buf);
+ relation_close(seqrel, NoLock);
+
+ PG_RETURN_INT64(result);
+}
+
+/*
* Main internal procedure that handles 2 & 3 arg forms of SETVAL.
*
* Note that the 3 arg version (which sets the is_called flag) is
@@ -1534,8 +1570,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
TupleDesc tupdesc;
- Datum values[5];
- bool isnull[5];
+ Datum values[6];
+ bool isnull[6];
SeqTable elm;
Relation seqrel;
Buffer buf;
@@ -1551,7 +1587,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
errmsg("permission denied for sequence %s",
RelationGetRelationName(seqrel))));
- tupdesc = CreateTemplateTupleDesc(5, false);
+ tupdesc = CreateTemplateTupleDesc(6, false);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "start_value",
INT8OID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "minimum_value",
@@ -1562,6 +1598,8 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
INT8OID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "cycle_option",
BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "cache_size",
+ INT8OID, -1, 0);
BlessTupleDesc(tupdesc);
@@ -1574,6 +1612,7 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
values[2] = Int64GetDatum(seq->max_value);
values[3] = Int64GetDatum(seq->increment_by);
values[4] = BoolGetDatum(seq->is_cycled);
+ values[5] = Int64GetDatum(seq->cache_value);
UnlockReleaseBuffer(buf);
relation_close(seqrel, NoLock);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index e2d08ba..1220bad 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1762,11 +1762,13 @@ DATA(insert OID = 1574 ( nextval PGNSP PGUID 12 1 0 0 0 f f f f t f v u 1 0 2
DESCR("sequence next value");
DATA(insert OID = 1575 ( currval PGNSP PGUID 12 1 0 0 0 f f f f t f v u 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ currval_oid _null_ _null_ _null_ ));
DESCR("sequence current value");
+DATA(insert OID = 4032 ( lastval PGNSP PGUID 12 1 0 0 0 f f f f t f v u 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ lastval1 _null_ _null_ _null_ ));
+DESCR("sequence last value");
DATA(insert OID = 1576 ( setval PGNSP PGUID 12 1 0 0 0 f f f f t f v u 2 0 20 "2205 20" _null_ _null_ _null_ _null_ _null_ setval_oid _null_ _null_ _null_ ));
DESCR("set sequence value");
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_ ));
DESCR("set sequence value and is_called status");
-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_));
+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_));
DESCR("sequence parameters, for use by information schema");
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_ ));
diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h
index 392a626..0b115d6 100644
--- a/src/include/commands/sequence.h
+++ b/src/include/commands/sequence.h
@@ -71,6 +71,7 @@ extern Datum currval_oid(PG_FUNCTION_ARGS);
extern Datum setval_oid(PG_FUNCTION_ARGS);
extern Datum setval3_oid(PG_FUNCTION_ARGS);
extern Datum lastval(PG_FUNCTION_ARGS);
+extern Datum lastval1(PG_FUNCTION_ARGS);
extern Datum pg_sequence_parameters(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 00700f2..0ce8b24 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1615,6 +1615,20 @@ UNION ALL
l.label
FROM (pg_shseclabel l
JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
+pg_sequences| SELECT n.nspname AS schemaname,
+ c.relname AS sequencename,
+ pg_get_userbyid(c.relowner) AS sequenceowner,
+ p.start_value,
+ p.minimum_value AS min_value,
+ p.maximum_value AS max_value,
+ p.increment AS increment_by,
+ p.cycle_option AS cycle,
+ p.cache_size,
+ lastval((c.oid)::regclass) AS last_value
+ FROM (pg_class c
+ LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))),
+ LATERAL pg_sequence_parameters(c.oid) p(start_value, minimum_value, maximum_value, increment, cycle_option, cache_size)
+ WHERE (c.relkind = 'S'::"char");
pg_settings| SELECT a.name,
a.setting,
a.unit,
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 4ffbe92..5231c27 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -315,6 +315,22 @@ SELECT * FROM information_schema.sequences WHERE sequence_name IN
regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
(6 rows)
+SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
+FROM pg_sequences
+WHERE sequencename IN
+ ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+ 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+ ORDER BY sequencename ASC;
+ schemaname | sequencename | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
+------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
+ public | sequence_test2 | 32 | 5 | 36 | 4 | t | 1 | 5
+ public | serialtest2_f2_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+ public | serialtest2_f3_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+ public | serialtest2_f4_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+ public | serialtest2_f5_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+ public | serialtest2_f6_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+(6 rows)
+
-- Test comments
COMMENT ON SEQUENCE asdf IS 'won''t work';
ERROR: relation "asdf" does not exist
diff --git a/src/test/regress/expected/sequence_1.out b/src/test/regress/expected/sequence_1.out
index 05da2bf..977bf02 100644
--- a/src/test/regress/expected/sequence_1.out
+++ b/src/test/regress/expected/sequence_1.out
@@ -315,6 +315,22 @@ SELECT * FROM information_schema.sequences WHERE sequence_name IN
regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
(6 rows)
+SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
+FROM pg_sequences
+WHERE sequencename IN
+ ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+ 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+ ORDER BY sequencename ASC;
+ schemaname | sequencename | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
+------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
+ public | sequence_test2 | 32 | 5 | 36 | 4 | t | 1 | 5
+ public | serialtest2_f2_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+ public | serialtest2_f3_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+ public | serialtest2_f4_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+ public | serialtest2_f5_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+ public | serialtest2_f6_seq | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 2
+(6 rows)
+
-- Test comments
COMMENT ON SEQUENCE asdf IS 'won''t work';
ERROR: relation "asdf" does not exist
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 98a2e7d..5333b20 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -144,6 +144,13 @@ CREATE SEQUENCE sequence_test2 START WITH 32;
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
ORDER BY sequence_name ASC;
+SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
+FROM pg_sequences
+WHERE sequencename IN
+ ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+ 'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
+ ORDER BY sequencename ASC;
+
-- Test comments
COMMENT ON SEQUENCE asdf IS 'won''t work';
COMMENT ON SEQUENCE sequence_test2 IS 'will work';
--
2.10.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
