On 11/8/16 6:43 PM, Andreas Karlsson wrote:
> - A worry is that it might get a bit confusing to have both the future
> catalog pg_sequence and the view pg_sequences.
We already have this in other cases: pg_index/pg_indexes,
pg_user_mapping/pg_user_mappings. It's an established naming system by now.
> - I think it would be useful to include is_cycled in the view.
It's there under the name "cycle".
> - When creating a temporary sequences and then running "SELECT * FROM
> pg_sequences" in another session I get the following error.
>
> ERROR: cannot access temporary tables of other sessions
Fixed that by adding pg_is_other_temp_schema() to the view definition.
We use that in the information schema but not in the system views so
far. That might be worth looking into.
> - Shouldn't last_value be NULL directly after we have created the
> sequence but nobody has called nextval() yet?
>
> - I noticed that last_value includes the cached values, but that also
> seems to me like the correct thing to do.
The documentation now emphasizes that this is the value stored on disk.
This matches what Oracle does.
> - I do not like the name of the new function, lastval(regclass). I think
> like you suggested it would be better with something more verbose.
> sequence_lastval()? sequence_last_value()?
changed
> - There is an XXX comment still in the code. It is about the name of the
> lastval1() function.
fixed
> - The documentation does not mention the last_value column.
fixed
> - The extra empty line after "</table>" does not fit with the formatting
> of the rest of the SGML file.
fixed
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 189910cc0f470f11ec1f76073acb7b91258c76fd Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Wed, 9 Nov 2016 12:00:00 -0500
Subject: [PATCH v2] 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 | 97 ++++++++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 17 ++++++
src/backend/commands/sequence.c | 44 ++++++++++++++-
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, 212 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index bac169a..fcc9038 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -7395,6 +7395,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>
@@ -9135,6 +9140,98 @@ <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>
+ <row>
+ <entry><structfield>last_value</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry></entry>
+ <entry>The last sequence value written to disk. If caching is used,
+ this value can be greater than the last value handed out from 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..e011af1 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -158,6 +158,23 @@ 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,
+ pg_sequence_last_value(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 NOT pg_is_other_temp_schema(N.oid)
+ AND 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..71c3f92 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -1534,8 +1534,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 +1551,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 +1562,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 +1576,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);
@@ -1581,6 +1584,41 @@ pg_sequence_parameters(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, isnull));
}
+/*
+ * Return the last value from the sequence
+ *
+ * Note: This has a completely different meaning than lastval().
+ */
+Datum
+pg_sequence_last_value(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);
+}
+
void
seq_redo(XLogReaderState *record)
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 17ec71d..047a1ce 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -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
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 = 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_ ));
+DESCR("sequence last value");
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_ ));
DESCR("I/O");
diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h
index 392a626..6695bbe 100644
--- a/src/include/commands/sequence.h
+++ b/src/include/commands/sequence.h
@@ -73,6 +73,7 @@ extern Datum setval3_oid(PG_FUNCTION_ARGS);
extern Datum lastval(PG_FUNCTION_ARGS);
extern Datum pg_sequence_parameters(PG_FUNCTION_ARGS);
+extern Datum pg_sequence_last_value(PG_FUNCTION_ARGS);
extern ObjectAddress DefineSequence(ParseState *pstate, CreateSeqStmt *stmt);
extern ObjectAddress AlterSequence(ParseState *pstate, AlterSeqStmt *stmt);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 5e2962c..031e8c2 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,
+ pg_sequence_last_value((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 ((NOT pg_is_other_temp_schema(n.oid)) AND (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.2
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
