Re: lastval() - Mailing 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