lastval()

[Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org>]

Here is a small patch that implements a function lastval() that
works just like currval() except that it give the current
value of the last sequence used by nextval().

Using this function one can do:

# CREATE TABLE abc (a serial, b int);
CREATE TABLE

# SELECT lastval();
ERROR:  nextval have not been used in the current session

# INSERT INTO abc(b) VALUES (42);
INSERT 0 1

# SELECT lastval();
 lastval
---------
       1


Some comments about the implementetion
--------------------------------------

Each backend keeps a list of all used sequences in the session. This patch
adds a sequence pointer that point out one of the sequences in the list
and which is updated by nextval(). This is a simple pointer assignment so
it's very cheap (almost zero cost).

lastval() works just like currval but use the pointed out sequence
instead of geting a sequence name as an argument.

One can implement this by storing the value instead of the sequence
pointer but I decided it's a good thing that it works just like
currval(), behaving the same with respect to rights, locks and such.

General comments
----------------

I know that some of you might want to name this function the same as the
similar function in mysql (LAST_INSERT_ID), but I prefer to name it
similar to the old sequence functions. It's easy to add a LAST_INSERT_ID()  
function that call lastval() if needed. Also, LAST_INSERT_ID() in mysql
will always succeed and it returns 0 if there have not been any row
inserted (at least what I think it will do that based on a quick look in
the mysql doc). The above function does not work like that.

-- 
/Dennis Björklund

Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.245
diff -u -c -r1.245 func.sgml
*** doc/src/sgml/func.sgml	13 Apr 2005 00:20:10 -0000	1.245
--- doc/src/sgml/func.sgml	8 May 2005 16:24:16 -0000
***************
*** 6475,6480 ****
--- 6475,6483 ----
     <primary>currval</primary>
    </indexterm>
    <indexterm>
+    <primary>lastval</primary>
+   </indexterm>
+   <indexterm>
     <primary>setval</primary>
    </indexterm>
  
***************
*** 6509,6514 ****
--- 6512,6523 ----
          <entry>Return value most recently obtained with <function>nextval</function></entry>
        </row>
        <row>
+         <entry><literal><function>lastval</function>()</literal></entry>
+         <entry><type>bigint</type></entry>
+         <entry>Return the current value of the last sequence that <function>nextval</function>
+                was invoked on.</entry>
+       </row>
+       <row>
          <entry><literal><function>setval</function>(<type>text</type>, <type>bigint</type>)</literal></entry>
          <entry><type>bigint</type></entry>
          <entry>Set sequence's current value</entry>
***************
*** 6575,6580 ****
--- 6584,6600 ----
       </varlistentry>
  
       <varlistentry>
+       <term><function>lastval</function></term>
+       <listitem>
+        <para>
+         This function works exactly as <function>currval</function> except that
+         instead of taking the sequence name as an argument it will fetch the current
+ 	value of the last sequence that <function>nextval</function> was used on.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
+      <varlistentry>
        <term><function>setval</function></term>
        <listitem>
         <para>
Index: src/backend/commands/sequence.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/sequence.c,v
retrieving revision 1.119
diff -u -c -r1.119 sequence.c
*** src/backend/commands/sequence.c	31 Dec 2004 21:59:41 -0000	1.119
--- src/backend/commands/sequence.c	8 May 2005 16:24:18 -0000
***************
*** 68,74 ****
--- 68,80 ----
  
  static SeqTable seqtab = NULL;	/* Head of list of SeqTable items */
  
+ /*
+  * last_used_seq is updated by nextval() to point out the last used
+  * sequence. It is the sequence used by lastval()
+  */
+ static SeqTableData *last_used_seq = NULL;
  
+ static void acquire_share_lock (Relation seqrel, SeqTableData *data);
  static void init_sequence(RangeVar *relation,
  			  SeqTable *p_elm, Relation *p_rel);
  static Form_pg_sequence read_info(SeqTable elm, Relation rel, Buffer *buf);
***************
*** 402,407 ****
--- 408,414 ----
  	if (elm->last != elm->cached)		/* some numbers were cached */
  	{
  		elm->last += elm->increment;
+ 		last_used_seq = elm;
  		relation_close(seqrel, NoLock);
  		PG_RETURN_INT64(elm->last);
  	}
***************
*** 522,527 ****
--- 529,536 ----
  	elm->last = result;			/* last returned number */
  	elm->cached = last;			/* last fetched number */
  
+ 	last_used_seq = elm;
+ 
  	START_CRIT_SECTION();
  
  	/* XLOG stuff */
***************
*** 604,609 ****
--- 613,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("nextval have not been used in the current session")));
+ 	}
+ 
+ 	seqrel = relation_open(last_used_seq->relid, NoLock);
+ 
+ 	acquire_share_lock (seqrel, last_used_seq);
+ 
+ 	if (pg_class_aclcheck(last_used_seq->relid, GetUserId(), ACL_SELECT) != ACLCHECK_OK)
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ 				 errmsg("permission denied for sequence with OID %d",
+ 						last_used_seq->relid)));
+ 
+ 	if (last_used_seq->increment == 0)	/* nextval/read_info were not called */
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ 				 errmsg("currval of sequence with OID %d is not yet defined in this session",
+ 						last_used_seq->relid)));
+ 
+ 	result = last_used_seq->last;
+ 
+ 	relation_close(seqrel, NoLock);
+ 
+ 	PG_RETURN_INT64(result);
+ }
+ 
  /*
   * Main internal procedure that handles 2 & 3 arg forms of SETVAL.
   *
***************
*** 745,750 ****
--- 789,831 ----
  
  
  /*
+  * 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,
+ 					SeqTableData *data)
+ {
+ 	TransactionId thisxid = GetTopTransactionId();
+ 
+ 	if (data->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. */
+ 		data->xid = thisxid;
+ 	}
+ }
+ 
+ /*
   * Given a relation name, open and lock the sequence.  p_elm and p_rel are
   * output parameters.
   */
***************
*** 752,758 ****
  init_sequence(RangeVar *relation, SeqTable *p_elm, Relation *p_rel)
  {
  	Oid			relid = RangeVarGetRelid(relation, false);
- 	TransactionId thisxid = GetTopTransactionId();
  	volatile SeqTable elm;
  	Relation	seqrel;
  
--- 833,838 ----
***************
*** 800,834 ****
  		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;
--- 880,886 ----
  		seqtab = elm;
  	}
  
! 	acquire_share_lock (seqrel, elm);
  
  	*p_elm = elm;
  	*p_rel = seqrel;
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.360
diff -u -c -r1.360 pg_proc.h
*** src/include/catalog/pg_proc.h	30 Apr 2005 20:31:38 -0000	1.360
--- src/include/catalog/pg_proc.h	8 May 2005 16:24:21 -0000
***************
*** 3636,3641 ****
--- 3636,3643 ----
  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: /cvsroot/pgsql/src/include/commands/sequence.h,v
retrieving revision 1.30
diff -u -c -r1.30 sequence.h
*** src/include/commands/sequence.h	31 Dec 2004 22:03:28 -0000	1.30
--- src/include/commands/sequence.h	8 May 2005 16:24:21 -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);