<entry>security labels</entry>
</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>
</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. Null if the sequence has not been read from yet.</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="view-pg-settings">
<title><structname>pg_settings</structname></title>
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,
{
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;
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",
INT8OID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 5, "cycle_option",
BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "cache_size",
+ INT8OID, -1, 0);
BlessTupleDesc(tupdesc);
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);
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;
+ bool is_called;
+ 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);
+
+ is_called = seq->is_called;
+ result = seq->last_value;
+
+ UnlockReleaseBuffer(buf);
+ relation_close(seqrel, NoLock);
+
+ if (is_called)
+ PG_RETURN_INT64(result);
+ else
+ PG_RETURN_NULL();
+}
+
void
seq_redo(XLogReaderState *record)
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 201611041
+#define CATALOG_VERSION_NO 201611181
#endif
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");
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);
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,
5
(1 row)
+CREATE SEQUENCE sequence_test3; -- not read from, to test is_called
-- Information schema
SELECT * FROM information_schema.sequences WHERE sequence_name IN
- ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+ ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
ORDER BY sequence_name ASC;
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
+ regression | public | sequence_test3 | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f2_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f3_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f4_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f5_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
-(6 rows)
+(7 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', 'sequence_test3', '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 | sequence_test3 | 1 | 1 | 9223372036854775807 | 1 | f | 1 |
+ 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
+(7 rows)
-- Test comments
COMMENT ON SEQUENCE asdf IS 'won''t work';
5
(1 row)
+CREATE SEQUENCE sequence_test3; -- not read from, to test is_called
-- Information schema
SELECT * FROM information_schema.sequences WHERE sequence_name IN
- ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+ ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
ORDER BY sequence_name ASC;
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
+ regression | public | sequence_test3 | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f2_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f3_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f4_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f5_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
regression | public | serialtest2_f6_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
-(6 rows)
+(7 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', 'sequence_test3', '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 | sequence_test3 | 1 | 1 | 9223372036854775807 | 1 | f | 1 |
+ 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
+(7 rows)
-- Test comments
COMMENT ON SEQUENCE asdf IS 'won''t work';
SELECT nextval('sequence_test2');
SELECT nextval('sequence_test2');
+
+CREATE SEQUENCE sequence_test3; -- not read from, to test is_called
+
+
-- Information schema
SELECT * FROM information_schema.sequences WHERE sequence_name IN
- ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
+ ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
'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', 'sequence_test3', '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';
projects / postgresql.git / commitdiff