Neil Conway wrote:
If you're busy, I can clean this up and apply it.
Attached is a revised patch. Per subsequent discussion, I stuck with
your approach of keeping a pointer to the sequence object, rather than
just the last int64 produced by nextval(). That means we emit an error on:
CREATE SEQUENCE seq;
SELECT nextval('seq');
DROP SEQUENCE seq;
SELECT lastval();
It also means that setval() _does_ affect lastval(), and that we do
permission checks properly. Barring any objections I'll apply this later
tonight or tomorrow.
BTW, I noticed that the "permission denied" messages throughout the
source don't quote the name of the identifier for which permission has
been denied. This violates the error code conventions: "Use quotes
always to delimit file names, user-supplied identifiers, and other
variables that might contain words." Is there a reason for this?
-Neil
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.251
diff -c -r1.251 func.sgml
*** doc/src/sgml/func.sgml 6 Jun 2005 16:29:01 -0000 1.251
--- doc/src/sgml/func.sgml 7 Jun 2005 04:05:29 -0000
***************
*** 6488,6493 ****
--- 6488,6496 ----
<primary>currval</primary>
</indexterm>
<indexterm>
+ <primary>lastval</primary>
+ </indexterm>
+ <indexterm>
<primary>setval</primary>
</indexterm>
***************
*** 6519,6524 ****
--- 6522,6533 ----
<row>
<entry><literal><function>currval</function>(<type>text</type>)</literal></entry>
<entry><type>bigint</type></entry>
+ <entry>Return value most recently obtained with
+ <function>nextval</function> for specified sequence</entry>
+ </row>
+ <row>
+ <entry><literal><function>lastval</function>()</literal></entry>
+ <entry><type>bigint</type></entry>
<entry>Return value most recently obtained with <function>nextval</function></entry>
</row>
<row>
***************
*** 6588,6593 ****
--- 6597,6618 ----
</varlistentry>
<varlistentry>
+ <term><function>lastval</function></term>
+ <listitem>
+ <para>
+ Return the value most recently returned by
+ <function>nextval</> in the current session. This function is
+ identical to <function>currval</function>, except that instead
+ of taking the sequence name as an argument it fetches the
+ value of the last sequence that <function>nextval</function>
+ was used on in the current session. It is an error to call
+ <function>lastval</function> if <function>nextval</function>
+ has not yet been called in the current session.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><function>setval</function></term>
<listitem>
<para>
Index: src/backend/commands/sequence.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/commands/sequence.c,v
retrieving revision 1.122
diff -c -r1.122 sequence.c
*** src/backend/commands/sequence.c 6 Jun 2005 20:22:57 -0000 1.122
--- src/backend/commands/sequence.c 7 Jun 2005 03:52:05 -0000
***************
*** 24,29 ****
--- 24,30 ----
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/resowner.h"
+ #include "utils/syscache.h"
/*
***************
*** 68,74 ****
--- 69,81 ----
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,405 ****
--- 407,413 ----
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,526 ****
--- 529,536 ----
elm->last = result; /* last returned number */
elm->cached = last; /* last fetched number */
+ last_used_seq = elm;
+
START_CRIT_SECTION();
/* XLOG stuff */
***************
*** 602,607 ****
--- 612,653 ----
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,746 ****
--- 787,827 ----
/*
+ * 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,754 ****
init_sequence(RangeVar *relation, SeqTable *p_elm, Relation *p_rel)
{
Oid relid = RangeVarGetRelid(relation, false);
- TransactionId thisxid = GetTopTransactionId();
volatile SeqTable elm;
Relation seqrel;
--- 829,834 ----
***************
*** 796,830 ****
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;
! }
*p_elm = elm;
*p_rel = seqrel;
--- 876,882 ----
seqtab = elm;
}
! acquire_share_lock(seqrel, elm);
*p_elm = elm;
*p_rel = seqrel;
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.363
diff -c -r1.363 pg_proc.h
*** src/include/catalog/pg_proc.h 20 May 2005 01:29:55 -0000 1.363
--- src/include/catalog/pg_proc.h 7 Jun 2005 03:08:40 -0000
***************
*** 3644,3649 ****
--- 3644,3651 ----
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");
/*
Index: src/include/commands/sequence.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/commands/sequence.h,v
retrieving revision 1.31
diff -c -r1.31 sequence.h
*** src/include/commands/sequence.h 6 Jun 2005 17:01:25 -0000 1.31
--- src/include/commands/sequence.h 7 Jun 2005 03:08:40 -0000
***************
*** 82,87 ****
--- 82,88 ----
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);
Index: src/test/regress/expected/sequence.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/sequence.out,v
retrieving revision 1.6
diff -c -r1.6 sequence.out
*** src/test/regress/expected/sequence.out 10 Jun 2004 17:56:01 -0000 1.6
--- src/test/regress/expected/sequence.out 7 Jun 2005 03:57:25 -0000
***************
*** 76,78 ****
--- 76,137 ----
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;
Index: src/test/regress/sql/sequence.sql
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/sql/sequence.sql,v
retrieving revision 1.2
diff -c -r1.2 sequence.sql
*** src/test/regress/sql/sequence.sql 21 Nov 2003 22:32:49 -0000 1.2
--- src/test/regress/sql/sequence.sql 7 Jun 2005 03:50:36 -0000
***************
*** 42,44 ****
--- 42,71 ----
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
