Re: lastval() - Mailing list pgsql-patches
From | Neil Conway |
---|---|
Subject | Re: lastval() |
Date | |
Msg-id | 42A51F4B.6090505@samurai.com Whole thread Raw |
In response to | Re: lastval() (Neil Conway <neilc@samurai.com>) |
Responses |
Re: lastval()
|
List | pgsql-patches |
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
pgsql-patches by date: