On Wed, Mar 4, 2015 at 12:49 AM, Andres Freund <and...@2ndquadrant.com> wrote:
> I every now and then run installcheck against a primary, verify that
> replay works without errors, and then compare pg_dumpall from both
> clusters. Unfortunately that currently requires hand inspection of
> dumps, there are differences like:
> -SELECT pg_catalog.setval('default_seq', 1, true);
> +SELECT pg_catalog.setval('default_seq', 33, true);
> Does anybody have a good idea how to get rid of that difference? One way
> to do that would be to log the value the standby is sure to have - but
> that's not entirely trivial.
SEQ_LOG_VALS has been added some time ago, so perhaps time have
changed and we could live without it:
commit: 741510521caea7e1ca12b4db0701bbc2db346a5f
author: Vadim B. Mikheev <vadi...@yahoo.com>
date: Thu, 30 Nov 2000 01:47:33 +0000
XLOG stuff for sequences.
CommitDelay in guc.c
However performance is really a problem, for example with the patch
attached and the following test case:
DO $$DECLARE count integer; count2 integer;
begin
for count in 1 .. 1000000
loop
select nextval('toto') into count2;
end loop;
END$$;
Patched, this takes 9.5ms and generates 191 MB of WAL on my laptop.
With master unpatched, this generates 6MB of WAL (records are divided
by 32) and takes 7.5s.
There are a couple of other possibilities we could consider as well:
1) Trick pg_dump such as it does not dump the current value of master
but one consistent with what a standby would expect. We would need
then something like nextval_standby() or similar.
2) Filter out lines with pg_catalog.setval in a home-made wrapper.
> I'd very much like to add a automated test like this to the tree, but I
> don't see a way to do that sanely without a comparison tool...
That's definitely worth having IMO.
Regards,
--
Michael
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 0070c4f..da503fe 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -42,13 +42,6 @@
/*
- * We don't want to log each fetching of a value from a sequence,
- * so we pre-log a few fetches in advance. In the event of
- * crash we can lose (skip over) as many values as we pre-logged.
- */
-#define SEQ_LOG_VALS 32
-
-/*
* The "special area" of a sequence's buffer page looks like this.
*/
#define SEQ_MAGIC 0x1717
@@ -206,11 +199,6 @@ DefineSequence(CreateSeqStmt *seq)
coldef->colname = "cache_value";
value[i - 1] = Int64GetDatumFast(new.cache_value);
break;
- case SEQ_COL_LOG:
- coldef->typeName = makeTypeNameFromOid(INT8OID, -1);
- coldef->colname = "log_cnt";
- value[i - 1] = Int64GetDatum((int64) 0);
- break;
case SEQ_COL_CYCLE:
coldef->typeName = makeTypeNameFromOid(BOOLOID, -1);
coldef->colname = "is_cycled";
@@ -297,7 +285,6 @@ ResetSequence(Oid seq_relid)
seq = (Form_pg_sequence) GETSTRUCT(tuple);
seq->last_value = seq->start_value;
seq->is_called = false;
- seq->log_cnt = 0;
/*
* Create a new storage file for the sequence. We want to keep the
@@ -538,13 +525,11 @@ nextval_internal(Oid relid)
maxv,
minv,
cache,
- log,
fetch,
last;
int64 result,
next,
rescnt = 0;
- bool logit = false;
/* open and AccessShareLock sequence */
init_sequence(relid, &elm, &seqrel);
@@ -579,7 +564,6 @@ nextval_internal(Oid relid)
maxv = seq->max_value;
minv = seq->min_value;
fetch = cache = seq->cache_value;
- log = seq->log_cnt;
if (!seq->is_called)
{
@@ -587,35 +571,7 @@ nextval_internal(Oid relid)
fetch--;
}
- /*
- * Decide whether we should emit a WAL log record. If so, force up the
- * fetch count to grab SEQ_LOG_VALS more values than we actually need to
- * cache. (These will then be usable without logging.)
- *
- * If this is the first nextval after a checkpoint, we must force a new
- * WAL record to be written anyway, else replay starting from the
- * checkpoint would fail to advance the sequence past the logged values.
- * In this case we may as well fetch extra values.
- */
- if (log < fetch || !seq->is_called)
- {
- /* forced log to satisfy local demand for values */
- fetch = log = fetch + SEQ_LOG_VALS;
- logit = true;
- }
- else
- {
- XLogRecPtr redoptr = GetRedoRecPtr();
-
- if (PageGetLSN(page) <= redoptr)
- {
- /* last update of seq was before checkpoint */
- fetch = log = fetch + SEQ_LOG_VALS;
- logit = true;
- }
- }
-
- while (fetch) /* try to fetch cache [+ log ] numbers */
+ while (fetch) /* try to fetch cache numbers */
{
/*
* Check MAXVALUE for ascending sequences and MINVALUE for descending
@@ -670,7 +626,6 @@ nextval_internal(Oid relid)
fetch--;
if (rescnt < cache)
{
- log--;
rescnt++;
last = next;
if (rescnt == 1) /* if it's first result - */
@@ -678,9 +633,6 @@ nextval_internal(Oid relid)
}
}
- log -= fetch; /* adjust for any unfetched numbers */
- Assert(log >= 0);
-
/* save info in local cache */
elm->last = result; /* last returned number */
elm->cached = last; /* last fetched number */
@@ -695,7 +647,7 @@ nextval_internal(Oid relid)
* no need to assign xids subxacts, that'll already trigger a appropriate
* wait. (Have to do that here, so we're outside the critical section)
*/
- if (logit && RelationNeedsWAL(seqrel))
+ if (RelationNeedsWAL(seqrel))
GetTopTransactionId();
/* ready to change the on-disk (or really, in-buffer) tuple */
@@ -713,7 +665,7 @@ nextval_internal(Oid relid)
MarkBufferDirty(buf);
/* XLOG stuff */
- if (logit && RelationNeedsWAL(seqrel))
+ if (RelationNeedsWAL(seqrel))
{
xl_seq_rec xlrec;
XLogRecPtr recptr;
@@ -730,7 +682,6 @@ nextval_internal(Oid relid)
/* set values that will be saved in xlog */
seq->last_value = next;
seq->is_called = true;
- seq->log_cnt = 0;
xlrec.node = seqrel->rd_node;
@@ -745,7 +696,6 @@ nextval_internal(Oid relid)
/* Now update sequence tuple to the intended final state */
seq->last_value = last; /* last fetched number */
seq->is_called = true;
- seq->log_cnt = log; /* how much is logged */
END_CRIT_SECTION();
@@ -895,7 +845,6 @@ do_setval(Oid relid, int64 next, bool iscalled)
seq->last_value = next; /* last fetched number */
seq->is_called = iscalled;
- seq->log_cnt = 0;
MarkBufferDirty(buf);
@@ -1231,13 +1180,6 @@ init_params(List *options, bool isInit,
defel->defname);
}
- /*
- * We must reset log_cnt when isInit or when changing any parameters that
- * would affect future nextval allocations.
- */
- if (isInit)
- new->log_cnt = 0;
-
/* INCREMENT BY */
if (increment_by != NULL)
{
@@ -1246,7 +1188,6 @@ init_params(List *options, bool isInit,
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("INCREMENT must not be zero")));
- new->log_cnt = 0;
}
else if (isInit)
new->increment_by = 1;
@@ -1256,7 +1197,6 @@ init_params(List *options, bool isInit,
{
new->is_cycled = intVal(is_cycled->arg);
Assert(BoolIsValid(new->is_cycled));
- new->log_cnt = 0;
}
else if (isInit)
new->is_cycled = false;
@@ -1265,7 +1205,6 @@ init_params(List *options, bool isInit,
if (max_value != NULL && max_value->arg)
{
new->max_value = defGetInt64(max_value);
- new->log_cnt = 0;
}
else if (isInit || max_value != NULL)
{
@@ -1273,14 +1212,12 @@ init_params(List *options, bool isInit,
new->max_value = SEQ_MAXVALUE; /* ascending seq */
else
new->max_value = -1; /* descending seq */
- new->log_cnt = 0;
}
/* MINVALUE (null arg means NO MINVALUE) */
if (min_value != NULL && min_value->arg)
{
new->min_value = defGetInt64(min_value);
- new->log_cnt = 0;
}
else if (isInit || min_value != NULL)
{
@@ -1288,7 +1225,6 @@ init_params(List *options, bool isInit,
new->min_value = 1; /* ascending seq */
else
new->min_value = SEQ_MINVALUE; /* descending seq */
- new->log_cnt = 0;
}
/* crosscheck min/max */
@@ -1350,7 +1286,6 @@ init_params(List *options, bool isInit,
else
new->last_value = new->start_value;
new->is_called = false;
- new->log_cnt = 0;
}
else if (isInit)
{
@@ -1398,7 +1333,6 @@ init_params(List *options, bool isInit,
errmsg("CACHE (%s) must be greater than zero",
buf)));
}
- new->log_cnt = 0;
}
else if (isInit)
new->cache_value = 1;
diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h
index 1baf43d..aeb8e61 100644
--- a/src/include/commands/sequence.h
+++ b/src/include/commands/sequence.h
@@ -29,7 +29,6 @@ typedef struct FormData_pg_sequence
int64 max_value;
int64 min_value;
int64 cache_value;
- int64 log_cnt;
bool is_cycled;
bool is_called;
} FormData_pg_sequence;
@@ -47,9 +46,8 @@ typedef FormData_pg_sequence *Form_pg_sequence;
#define SEQ_COL_MAXVALUE 5
#define SEQ_COL_MINVALUE 6
#define SEQ_COL_CACHE 7
-#define SEQ_COL_LOG 8
-#define SEQ_COL_CYCLE 9
-#define SEQ_COL_CALLED 10
+#define SEQ_COL_CYCLE 8
+#define SEQ_COL_CALLED 9
#define SEQ_COL_FIRSTCOL SEQ_COL_NAME
#define SEQ_COL_LASTCOL SEQ_COL_CALLED
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 8783ca6..4f728e4 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -173,9 +173,9 @@ DROP SEQUENCE sequence_test;
CREATE SEQUENCE foo_seq;
ALTER TABLE foo_seq RENAME TO foo_seq_new;
SELECT * FROM foo_seq_new;
- sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
- foo_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | f
+ sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | is_cycled | is_called
+---------------+------------+-------------+--------------+---------------------+-----------+-------------+-----------+-----------
+ foo_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | f | f
(1 row)
SELECT nextval('foo_seq_new');
@@ -191,9 +191,9 @@ SELECT nextval('foo_seq_new');
(1 row)
SELECT * FROM foo_seq_new;
- sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
- foo_seq | 2 | 1 | 1 | 9223372036854775807 | 1 | 1 | 31 | f | t
+ sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | is_cycled | is_called
+---------------+------------+-------------+--------------+---------------------+-----------+-------------+-----------+-----------
+ foo_seq | 2 | 1 | 1 | 9223372036854775807 | 1 | 1 | f | t
(1 row)
DROP SEQUENCE foo_seq_new;
diff --git a/src/test/regress/expected/sequence_1.out b/src/test/regress/expected/sequence_1.out
deleted file mode 100644
index 951fc9e..0000000
--- a/src/test/regress/expected/sequence_1.out
+++ /dev/null
@@ -1,519 +0,0 @@
----
---- test creation of SERIAL column
----
-CREATE TABLE serialTest (f1 text, f2 serial);
-INSERT INTO serialTest VALUES ('foo');
-INSERT INTO serialTest VALUES ('bar');
-INSERT INTO serialTest VALUES ('force', 100);
-INSERT INTO serialTest VALUES ('wrong', NULL);
-ERROR: null value in column "f2" violates not-null constraint
-DETAIL: Failing row contains (wrong, null).
-SELECT * FROM serialTest;
- f1 | f2
--------+-----
- foo | 1
- bar | 2
- force | 100
-(3 rows)
-
--- test smallserial / bigserial
-CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
- f5 bigserial, f6 serial8);
-INSERT INTO serialTest2 (f1)
- VALUES ('test_defaults');
-INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6)
- VALUES ('test_max_vals', 2147483647, 32767, 32767, 9223372036854775807,
- 9223372036854775807),
- ('test_min_vals', -2147483648, -32768, -32768, -9223372036854775808,
- -9223372036854775808);
--- All these INSERTs should fail:
-INSERT INTO serialTest2 (f1, f3)
- VALUES ('bogus', -32769);
-ERROR: smallint out of range
-INSERT INTO serialTest2 (f1, f4)
- VALUES ('bogus', -32769);
-ERROR: smallint out of range
-INSERT INTO serialTest2 (f1, f3)
- VALUES ('bogus', 32768);
-ERROR: smallint out of range
-INSERT INTO serialTest2 (f1, f4)
- VALUES ('bogus', 32768);
-ERROR: smallint out of range
-INSERT INTO serialTest2 (f1, f5)
- VALUES ('bogus', -9223372036854775809);
-ERROR: bigint out of range
-INSERT INTO serialTest2 (f1, f6)
- VALUES ('bogus', -9223372036854775809);
-ERROR: bigint out of range
-INSERT INTO serialTest2 (f1, f5)
- VALUES ('bogus', 9223372036854775808);
-ERROR: bigint out of range
-INSERT INTO serialTest2 (f1, f6)
- VALUES ('bogus', 9223372036854775808);
-ERROR: bigint out of range
-SELECT * FROM serialTest2 ORDER BY f2 ASC;
- f1 | f2 | f3 | f4 | f5 | f6
----------------+-------------+--------+--------+----------------------+----------------------
- test_min_vals | -2147483648 | -32768 | -32768 | -9223372036854775808 | -9223372036854775808
- test_defaults | 1 | 1 | 1 | 1 | 1
- test_max_vals | 2147483647 | 32767 | 32767 | 9223372036854775807 | 9223372036854775807
-(3 rows)
-
-SELECT nextval('serialTest2_f2_seq');
- nextval
----------
- 2
-(1 row)
-
-SELECT nextval('serialTest2_f3_seq');
- nextval
----------
- 2
-(1 row)
-
-SELECT nextval('serialTest2_f4_seq');
- nextval
----------
- 2
-(1 row)
-
-SELECT nextval('serialTest2_f5_seq');
- nextval
----------
- 2
-(1 row)
-
-SELECT nextval('serialTest2_f6_seq');
- nextval
----------
- 2
-(1 row)
-
--- basic sequence operations using both text and oid references
-CREATE SEQUENCE sequence_test;
-CREATE SEQUENCE IF NOT EXISTS sequence_test;
-NOTICE: relation "sequence_test" already exists, skipping
-SELECT nextval('sequence_test'::text);
- nextval
----------
- 1
-(1 row)
-
-SELECT nextval('sequence_test'::regclass);
- nextval
----------
- 2
-(1 row)
-
-SELECT currval('sequence_test'::text);
- currval
----------
- 2
-(1 row)
-
-SELECT currval('sequence_test'::regclass);
- currval
----------
- 2
-(1 row)
-
-SELECT setval('sequence_test'::text, 32);
- setval
---------
- 32
-(1 row)
-
-SELECT nextval('sequence_test'::regclass);
- nextval
----------
- 33
-(1 row)
-
-SELECT setval('sequence_test'::text, 99, false);
- setval
---------
- 99
-(1 row)
-
-SELECT nextval('sequence_test'::regclass);
- nextval
----------
- 99
-(1 row)
-
-SELECT setval('sequence_test'::regclass, 32);
- setval
---------
- 32
-(1 row)
-
-SELECT nextval('sequence_test'::text);
- nextval
----------
- 33
-(1 row)
-
-SELECT setval('sequence_test'::regclass, 99, false);
- setval
---------
- 99
-(1 row)
-
-SELECT nextval('sequence_test'::text);
- nextval
----------
- 99
-(1 row)
-
-DISCARD SEQUENCES;
-SELECT currval('sequence_test'::regclass);
-ERROR: currval of sequence "sequence_test" is not yet defined in this session
-DROP SEQUENCE sequence_test;
--- renaming sequences
-CREATE SEQUENCE foo_seq;
-ALTER TABLE foo_seq RENAME TO foo_seq_new;
-SELECT * FROM foo_seq_new;
- sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
- foo_seq | 1 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | f
-(1 row)
-
-SELECT nextval('foo_seq_new');
- nextval
----------
- 1
-(1 row)
-
-SELECT nextval('foo_seq_new');
- nextval
----------
- 2
-(1 row)
-
-SELECT * FROM foo_seq_new;
- sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
- foo_seq | 2 | 1 | 1 | 9223372036854775807 | 1 | 1 | 32 | f | t
-(1 row)
-
-DROP SEQUENCE foo_seq_new;
--- renaming serial sequences
-ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo;
-INSERT INTO serialTest VALUES ('more');
-SELECT * FROM serialTest;
- f1 | f2
--------+-----
- foo | 1
- bar | 2
- force | 100
- more | 3
-(4 rows)
-
---
--- Check dependencies of serial and ordinary sequences
---
-CREATE TEMP SEQUENCE myseq2;
-CREATE TEMP SEQUENCE myseq3;
-CREATE TEMP TABLE t1 (
- f1 serial,
- f2 int DEFAULT nextval('myseq2'),
- f3 int DEFAULT nextval('myseq3'::text)
-);
--- Both drops should fail, but with different error messages:
-DROP SEQUENCE t1_f1_seq;
-ERROR: cannot drop sequence t1_f1_seq because other objects depend on it
-DETAIL: default for table t1 column f1 depends on sequence t1_f1_seq
-HINT: Use DROP ... CASCADE to drop the dependent objects too.
-DROP SEQUENCE myseq2;
-ERROR: cannot drop sequence myseq2 because other objects depend on it
-DETAIL: default for table t1 column f2 depends on sequence myseq2
-HINT: Use DROP ... CASCADE to drop the dependent objects too.
--- This however will work:
-DROP SEQUENCE myseq3;
-DROP TABLE t1;
--- Fails because no longer existent:
-DROP SEQUENCE t1_f1_seq;
-ERROR: sequence "t1_f1_seq" does not exist
--- Now OK:
-DROP SEQUENCE myseq2;
---
--- Alter sequence
---
-ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
- INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
-NOTICE: relation "sequence_test2" does not exist, skipping
-CREATE SEQUENCE sequence_test2 START WITH 32;
-SELECT nextval('sequence_test2');
- nextval
----------
- 32
-(1 row)
-
-ALTER SEQUENCE sequence_test2 RESTART WITH 24
- INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
-SELECT nextval('sequence_test2');
- nextval
----------
- 24
-(1 row)
-
-SELECT nextval('sequence_test2');
- nextval
----------
- 28
-(1 row)
-
-SELECT nextval('sequence_test2');
- nextval
----------
- 32
-(1 row)
-
-SELECT nextval('sequence_test2');
- nextval
----------
- 36
-(1 row)
-
-SELECT nextval('sequence_test2');
- nextval
----------
- 5
-(1 row)
-
-ALTER SEQUENCE sequence_test2 RESTART;
-SELECT nextval('sequence_test2');
- nextval
----------
- 32
-(1 row)
-
-SELECT nextval('sequence_test2');
- nextval
----------
- 36
-(1 row)
-
-SELECT nextval('sequence_test2');
- nextval
----------
- 5
-(1 row)
-
--- Information schema
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
- ('sequence_test2', '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 | 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)
-
--- Test comments
-COMMENT ON SEQUENCE asdf IS 'won''t work';
-ERROR: relation "asdf" does not exist
-COMMENT ON SEQUENCE sequence_test2 IS 'will work';
-COMMENT ON SEQUENCE sequence_test2 IS NULL;
--- Test lastval()
-CREATE SEQUENCE seq;
-SELECT nextval('seq');
- nextval
----------
- 1
-(1 row)
-
-SELECT lastval();
- lastval
----------
- 1
-(1 row)
-
-SELECT setval('seq', 99);
- setval
---------
- 99
-(1 row)
-
-SELECT lastval();
- lastval
----------
- 99
-(1 row)
-
-DISCARD SEQUENCES;
-SELECT lastval();
-ERROR: lastval is not yet defined in this session
-CREATE SEQUENCE seq2;
-SELECT nextval('seq2');
- nextval
----------
- 1
-(1 row)
-
-SELECT lastval();
- lastval
----------
- 1
-(1 row)
-
-DROP SEQUENCE seq2;
--- should fail
-SELECT lastval();
-ERROR: lastval is not yet defined in this session
-CREATE USER seq_user;
--- privileges tests
--- nextval
-BEGIN;
-SET LOCAL SESSION AUTHORIZATION seq_user;
-CREATE SEQUENCE seq3;
-REVOKE ALL ON seq3 FROM seq_user;
-GRANT SELECT ON seq3 TO seq_user;
-SELECT nextval('seq3');
-ERROR: permission denied for sequence seq3
-ROLLBACK;
-BEGIN;
-SET LOCAL SESSION AUTHORIZATION seq_user;
-CREATE SEQUENCE seq3;
-REVOKE ALL ON seq3 FROM seq_user;
-GRANT UPDATE ON seq3 TO seq_user;
-SELECT nextval('seq3');
- nextval
----------
- 1
-(1 row)
-
-ROLLBACK;
-BEGIN;
-SET LOCAL SESSION AUTHORIZATION seq_user;
-CREATE SEQUENCE seq3;
-REVOKE ALL ON seq3 FROM seq_user;
-GRANT USAGE ON seq3 TO seq_user;
-SELECT nextval('seq3');
- nextval
----------
- 1
-(1 row)
-
-ROLLBACK;
--- currval
-BEGIN;
-SET LOCAL SESSION AUTHORIZATION seq_user;
-CREATE SEQUENCE seq3;
-SELECT nextval('seq3');
- nextval
----------
- 1
-(1 row)
-
-REVOKE ALL ON seq3 FROM seq_user;
-GRANT SELECT ON seq3 TO seq_user;
-SELECT currval('seq3');
- currval
----------
- 1
-(1 row)
-
-ROLLBACK;
-BEGIN;
-SET LOCAL SESSION AUTHORIZATION seq_user;
-CREATE SEQUENCE seq3;
-SELECT nextval('seq3');
- nextval
----------
- 1
-(1 row)
-
-REVOKE ALL ON seq3 FROM seq_user;
-GRANT UPDATE ON seq3 TO seq_user;
-SELECT currval('seq3');
-ERROR: permission denied for sequence seq3
-ROLLBACK;
-BEGIN;
-SET LOCAL SESSION AUTHORIZATION seq_user;
-CREATE SEQUENCE seq3;
-SELECT nextval('seq3');
- nextval
----------
- 1
-(1 row)
-
-REVOKE ALL ON seq3 FROM seq_user;
-GRANT USAGE ON seq3 TO seq_user;
-SELECT currval('seq3');
- currval
----------
- 1
-(1 row)
-
-ROLLBACK;
--- lastval
-BEGIN;
-SET LOCAL SESSION AUTHORIZATION seq_user;
-CREATE SEQUENCE seq3;
-SELECT nextval('seq3');
- nextval
----------
- 1
-(1 row)
-
-REVOKE ALL ON seq3 FROM seq_user;
-GRANT SELECT ON seq3 TO seq_user;
-SELECT lastval();
- lastval
----------
- 1
-(1 row)
-
-ROLLBACK;
-BEGIN;
-SET LOCAL SESSION AUTHORIZATION seq_user;
-CREATE SEQUENCE seq3;
-SELECT nextval('seq3');
- nextval
----------
- 1
-(1 row)
-
-REVOKE ALL ON seq3 FROM seq_user;
-GRANT UPDATE ON seq3 TO seq_user;
-SELECT lastval();
-ERROR: permission denied for sequence seq3
-ROLLBACK;
-BEGIN;
-SET LOCAL SESSION AUTHORIZATION seq_user;
-CREATE SEQUENCE seq3;
-SELECT nextval('seq3');
- nextval
----------
- 1
-(1 row)
-
-REVOKE ALL ON seq3 FROM seq_user;
-GRANT USAGE ON seq3 TO seq_user;
-SELECT lastval();
- lastval
----------
- 1
-(1 row)
-
-ROLLBACK;
--- Sequences should get wiped out as well:
-DROP TABLE serialTest, serialTest2;
--- Make sure sequences are gone:
-SELECT * FROM information_schema.sequences WHERE sequence_name IN
- ('sequence_test2', '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
-(1 row)
-
-DROP USER seq_user;
-DROP SEQUENCE seq;
test.sql
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
