From 657c098e41b0bb29d30d13d9aa1ac858a07f3493 Mon Sep 17 00:00:00 2001 From: Neil Conway Date: Tue, 7 Jun 2005 07:08:35 +0000 Subject: [PATCH] =?utf8?q?Add=20a=20function=20lastval(),=20which=20return?= =?utf8?q?s=20the=20value=20returned=20by=20the=20last=20nextval()=20or=20?= =?utf8?q?setval()=20performed=20by=20the=20current=20session.=20Update=20?= =?utf8?q?the=20docs,=20add=20regression=20tests,=20and=20bump=20the=20cat?= =?utf8?q?alog=20version.=20Patch=20from=20Dennis=20Bj=C3=B6rklund,=20vari?= =?utf8?q?ous=20improvements=20by=20Neil=20Conway.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- doc/src/sgml/func.sgml | 27 +++++++- src/backend/commands/sequence.c | 114 ++++++++++++++++++++++++--------- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_proc.h | 4 +- src/include/commands/sequence.h | 3 +- src/test/regress/expected/sequence.out | 59 +++++++++++++++++ src/test/regress/sql/sequence.sql | 27 ++++++++ 7 files changed, 202 insertions(+), 36 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 5bd29793e8..7eb2c4c6b1 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -6488,6 +6488,9 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT currval + lastval + + setval @@ -6519,6 +6522,12 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT currval(text) bigint + Return value most recently obtained with + nextval for specified sequence + + + lastval() + bigint Return value most recently obtained with nextval @@ -6588,6 +6597,22 @@ nextval('foo') searches search path for fo + lastval + + + Return the value most recently returned by + nextval in the current session. This function is + identical to currval, except that instead + of taking the sequence name as an argument it fetches the + value of the last sequence that nextval + was used on in the current session. It is an error to call + lastval if nextval + has not yet been called in the current session. + + + + + setval diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c index 78b9225b83..5af9ba55b6 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/sequence.c,v 1.122 2005/06/06 20:22:57 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/sequence.c,v 1.123 2005/06/07 07:08:34 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -24,6 +24,7 @@ #include "utils/acl.h" #include "utils/builtins.h" #include "utils/resowner.h" +#include "utils/syscache.h" /* @@ -68,7 +69,13 @@ typedef SeqTableData *SeqTable; static SeqTable seqtab = NULL; /* Head of list of SeqTable items */ +/* + * last_used_seq is updated by nextval() to point to the last used + * sequence. + */ +static SeqTableData *last_used_seq = NULL; +static void acquire_share_lock(Relation seqrel, SeqTable seq); static void init_sequence(RangeVar *relation, SeqTable *p_elm, Relation *p_rel); static Form_pg_sequence read_info(SeqTable elm, Relation rel, Buffer *buf); @@ -400,6 +407,7 @@ nextval(PG_FUNCTION_ARGS) if (elm->last != elm->cached) /* some numbers were cached */ { + last_used_seq = elm; elm->last += elm->increment; relation_close(seqrel, NoLock); PG_RETURN_INT64(elm->last); @@ -521,6 +529,8 @@ nextval(PG_FUNCTION_ARGS) elm->last = result; /* last returned number */ elm->cached = last; /* last fetched number */ + last_used_seq = elm; + START_CRIT_SECTION(); /* XLOG stuff */ @@ -602,6 +612,42 @@ currval(PG_FUNCTION_ARGS) PG_RETURN_INT64(result); } +Datum +lastval(PG_FUNCTION_ARGS) +{ + Relation seqrel; + int64 result; + + if (last_used_seq == NULL) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("lastval is not yet defined in this session"))); + + /* Someone may have dropped the sequence since the last nextval() */ + if (!SearchSysCacheExists(RELOID, + ObjectIdGetDatum(last_used_seq->relid), + 0, 0, 0)) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("lastval is not yet defined in this session"))); + + seqrel = relation_open(last_used_seq->relid, NoLock); + acquire_share_lock(seqrel, last_used_seq); + + /* nextval() must have already been called for this sequence */ + Assert(last_used_seq->increment != 0); + + if (pg_class_aclcheck(last_used_seq->relid, GetUserId(), ACL_SELECT) != ACLCHECK_OK) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied for sequence %s", + RelationGetRelationName(seqrel)))); + + result = last_used_seq->last; + relation_close(seqrel, NoLock); + PG_RETURN_INT64(result); +} + /* * Main internal procedure that handles 2 & 3 arg forms of SETVAL. * @@ -741,6 +787,41 @@ setval_and_iscalled(PG_FUNCTION_ARGS) /* + * If we haven't touched the sequence already in this transaction, + * we need to acquire AccessShareLock. We arrange for the lock to + * be owned by the top transaction, so that we don't need to do it + * more than once per xact. + */ +static void +acquire_share_lock(Relation seqrel, SeqTable seq) +{ + TransactionId thisxid = GetTopTransactionId(); + + if (seq->xid != thisxid) + { + ResourceOwner currentOwner; + + currentOwner = CurrentResourceOwner; + PG_TRY(); + { + CurrentResourceOwner = TopTransactionResourceOwner; + LockRelation(seqrel, AccessShareLock); + } + PG_CATCH(); + { + /* Ensure CurrentResourceOwner is restored on error */ + CurrentResourceOwner = currentOwner; + PG_RE_THROW(); + } + PG_END_TRY(); + CurrentResourceOwner = currentOwner; + + /* Flag that we have a lock in the current xact. */ + seq->xid = thisxid; + } +} + +/* * Given a relation name, open and lock the sequence. p_elm and p_rel are * output parameters. */ @@ -748,7 +829,6 @@ static void init_sequence(RangeVar *relation, SeqTable *p_elm, Relation *p_rel) { Oid relid = RangeVarGetRelid(relation, false); - TransactionId thisxid = GetTopTransactionId(); volatile SeqTable elm; Relation seqrel; @@ -796,35 +876,7 @@ init_sequence(RangeVar *relation, SeqTable *p_elm, Relation *p_rel) seqtab = elm; } - /* - * If we haven't touched the sequence already in this transaction, - * we need to acquire AccessShareLock. We arrange for the lock to - * be owned by the top transaction, so that we don't need to do it - * more than once per xact. - */ - if (elm->xid != thisxid) - { - ResourceOwner currentOwner; - - currentOwner = CurrentResourceOwner; - PG_TRY(); - { - CurrentResourceOwner = TopTransactionResourceOwner; - - LockRelation(seqrel, AccessShareLock); - } - PG_CATCH(); - { - /* Ensure CurrentResourceOwner is restored on error */ - CurrentResourceOwner = currentOwner; - PG_RE_THROW(); - } - PG_END_TRY(); - CurrentResourceOwner = currentOwner; - - /* Flag that we have a lock in the current xact. */ - elm->xid = thisxid; - } + acquire_share_lock(seqrel, elm); *p_elm = elm; *p_rel = seqrel; diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index f10bdab770..a58ec7b5a5 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.272 2005/05/30 20:59:17 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.273 2005/06/07 07:08:34 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200505302 +#define CATALOG_VERSION_NO 200506071 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index ebf15f8c19..9f5c2d8e0e 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.363 2005/05/20 01:29:55 neilc Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.364 2005/06/07 07:08:34 neilc Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -3644,6 +3644,8 @@ DATA(insert OID = 2557 ( bool PGNSP PGUID 12 f f t f i 1 16 "23" _null_ _ DESCR("convert int4 to boolean"); DATA(insert OID = 2558 ( int4 PGNSP PGUID 12 f f t f i 1 23 "16" _null_ _null_ _null_ bool_int4 - _null_ )); DESCR("convert boolean to int4"); +DATA(insert OID = 2559 ( lastval PGNSP PGUID 12 f f t f v 0 20 "" _null_ _null_ _null_ lastval - _null_ )); +DESCR("current value from last used sequence"); /* diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h index 4801753886..38538236c7 100644 --- a/src/include/commands/sequence.h +++ b/src/include/commands/sequence.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/commands/sequence.h,v 1.31 2005/06/06 17:01:25 tgl Exp $ + * $PostgreSQL: pgsql/src/include/commands/sequence.h,v 1.32 2005/06/07 07:08:35 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -82,6 +82,7 @@ typedef struct xl_seq_rec extern Datum nextval(PG_FUNCTION_ARGS); extern Datum currval(PG_FUNCTION_ARGS); +extern Datum lastval(PG_FUNCTION_ARGS); extern Datum setval(PG_FUNCTION_ARGS); extern Datum setval_and_iscalled(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out index 040506d4de..6e919d1f1d 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -76,3 +76,62 @@ 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) + +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; +BEGIN; +SET LOCAL SESSION AUTHORIZATION seq_user; +CREATE SEQUENCE seq3; +SELECT nextval('seq3'); + nextval +--------- + 1 +(1 row) + +REVOKE ALL ON seq3 FROM seq_user; +SELECT lastval(); +ERROR: permission denied for sequence seq3 +ROLLBACK; +DROP USER seq_user; +DROP SEQUENCE seq; diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql index 07f5765faf..a8b73c02bf 100644 --- a/src/test/regress/sql/sequence.sql +++ b/src/test/regress/sql/sequence.sql @@ -42,3 +42,30 @@ COMMENT ON SEQUENCE asdf IS 'won''t work'; COMMENT ON SEQUENCE sequence_test2 IS 'will work'; COMMENT ON SEQUENCE sequence_test2 IS NULL; +-- Test lastval() +CREATE SEQUENCE seq; +SELECT nextval('seq'); +SELECT lastval(); +SELECT setval('seq', 99); +SELECT lastval(); + +CREATE SEQUENCE seq2; +SELECT nextval('seq2'); +SELECT lastval(); + +DROP SEQUENCE seq2; +-- should fail +SELECT lastval(); + +CREATE USER seq_user; + +BEGIN; +SET LOCAL SESSION AUTHORIZATION seq_user; +CREATE SEQUENCE seq3; +SELECT nextval('seq3'); +REVOKE ALL ON seq3 FROM seq_user; +SELECT lastval(); +ROLLBACK; + +DROP USER seq_user; +DROP SEQUENCE seq; \ No newline at end of file -- 2.11.0