Subject: current version: [PATCHES] Patch - Have psql show current values for a sequence
From: Dhanaraj M <[email-masked]>
To: Dhanaraj M <[email-masked]>
Date: Tue, 02 May 2006 16:04:14 +0530

sorry for sending the old version in the previous mail . Here I attach 
the recent version of the patch file.

Dhanaraj M wrote:

> I saw the following in the TODO list.
> clients-psql
> ============
> 1. Have psql show current values for a sequence
>
> Hence, this patch displays the current seq. value in a separate column 
> when \ds is executed.
> I attach the patch here. The display format may have to be changed. I 
> would like to change the display format as you will suggest me.
> I am awaiting for the review.
>
> Thanks
> Dhanaraj
>
>
>

*** ./src/backend/commands/sequence.c.orig	Tue May  2 14:51:03 2006
--- ./src/backend/commands/sequence.c	Tue May  2 15:00:19 2006
***************
*** 1183,1185 ****
--- 1183,1274 ----
  			xlrec->node.spcNode, xlrec->node.dbNode, xlrec->node.relNode);
  }
  
+ 
+ /* Returns the current sequence value even if not available in the cache */
+ Datum
+ retrieveval_oid(PG_FUNCTION_ARGS)
+ {
+ 
+         Oid                     relid = PG_GETARG_DATUM(0);
+         int64           result;
+         SeqTable        elm;
+         Relation        seqrel;
+         Form_pg_sequence seq;
+         Buffer          buf;
+ 
+         /* open and AccessShareLock sequence */
+         init_sequence(relid, &elm, &seqrel);
+ 
+         if (pg_class_aclcheck(elm->relid, GetUserId(), ACL_SELECT) != ACLCHECK_OK &&
+                 pg_class_aclcheck(elm->relid, GetUserId(), ACL_USAGE) != ACLCHECK_OK)
+                 ereport(ERROR,
+                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                  errmsg("permission denied for sequence %s",
+                                                 RelationGetRelationName(seqrel))));
+ 
+ 
+         if ((elm->increment != 0) ||(elm->last != elm->cached)) /* some numbers were cached */
+         {
+                 result = elm->last;
+                 relation_close(seqrel, NoLock);
+                 PG_RETURN_INT64(result);
+         }
+ 
+         /* lock page' buffer and read tuple if not cached */
+         seq = read_info(elm, seqrel, &buf);
+         result = seq->last_value;
+ 
+ 	  UnlockReleaseBuffer(buf);
+         relation_close(seqrel, NoLock);
+ 
+         seqtab = elm->next;
+         free(elm);
+ 
+         PG_RETURN_INT64(result);
+ }
+ 
+ 
+ /* Checks whether the sequence value is already used or not */
+ Datum
+ retrievecheck_oid(PG_FUNCTION_ARGS)
+ {
+ 
+         Oid                     relid = PG_GETARG_DATUM(0);
+         SeqTable        elm;
+         Relation        seqrel;
+         Form_pg_sequence seq;
+         Buffer          buf;
+         bool            result;
+ 
+         /* open and AccessShareLock sequence */
+         init_sequence(relid, &elm, &seqrel);
+ 
+         if (pg_class_aclcheck(elm->relid, GetUserId(), ACL_SELECT) != ACLCHECK_OK &&
+                 pg_class_aclcheck(elm->relid, GetUserId(), ACL_USAGE) != ACLCHECK_OK)
+                 ereport(ERROR,
+                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                  errmsg("permission denied for sequence %s",
+                                                 RelationGetRelationName(seqrel))));
+ 
+ 
+         if ((elm->increment != 0) ||(elm->last != elm->cached)) /* some numbers were cached */
+         {
+                 relation_close(seqrel, NoLock);
+                 PG_RETURN_INT64(0);
+         }
+ 
+         /* lock page' buffer and read tuple */
+         seq = read_info(elm, seqrel, &buf);
+         result = seq->is_called;
+ 
+ 	  UnlockReleaseBuffer(buf);        
+         relation_close(seqrel, NoLock);
+ 
+         seqtab = elm->next;
+         free(elm);
+ 
+         if (result == true)
+                 PG_RETURN_INT64(0);
+         else
+                 PG_RETURN_INT64(1);
+ }
*** ./src/backend/utils/fmgroids.h.orig	Tue May  2 14:19:25 2006
--- ./src/backend/utils/fmgroids.h	Tue May  2 15:11:08 2006
***************
*** 901,906 ****
--- 901,908 ----
  #define F_NEXTVAL_OID 1574
  #define F_CURRVAL_OID 1575
  #define F_SETVAL_OID 1576
+ #define F_RETRIEVEVAL_OID 1577
+ #define F_RETRIEVECHECK_OID 1578
  #define F_VARBIT_IN 1579
  #define F_VARBIT_OUT 1580
  #define F_BITEQ 1581
*** ./src/backend/utils/fmgrtab.c.orig	Tue May  2 14:19:25 2006
--- ./src/backend/utils/fmgrtab.c	Tue May  2 15:11:08 2006
***************
*** 952,957 ****
--- 952,959 ----
  extern Datum nextval_oid (PG_FUNCTION_ARGS);
  extern Datum currval_oid (PG_FUNCTION_ARGS);
  extern Datum setval_oid (PG_FUNCTION_ARGS);
+ extern Datum retrieveval_oid (PG_FUNCTION_ARGS);
+ extern Datum retrievecheck_oid (PG_FUNCTION_ARGS);
  extern Datum varbit_in (PG_FUNCTION_ARGS);
  extern Datum varbit_out (PG_FUNCTION_ARGS);
  extern Datum biteq (PG_FUNCTION_ARGS);
***************
*** 2661,2666 ****
--- 2663,2670 ----
    { 1574, "nextval_oid", 1, true, false, nextval_oid },
    { 1575, "currval_oid", 1, true, false, currval_oid },
    { 1576, "setval_oid", 2, true, false, setval_oid },
+   { 1577, "retrieveval_oid", 1, true, false, retrieveval_oid },
+   { 1578, "retrievecheck_oid", 1, true, false, retrievecheck_oid },
    { 1579, "varbit_in", 3, true, false, varbit_in },
    { 1580, "varbit_out", 1, true, false, varbit_out },
    { 1581, "biteq", 2, true, false, biteq },
*** ./src/bin/psql/describe.c.orig	Thu Apr 27 04:45:45 2006
--- ./src/bin/psql/describe.c	Tue May  2 15:22:49 2006
***************
*** 1479,1484 ****
--- 1479,1489 ----
  					  _("Schema"), _("Name"),
  					  _("table"), _("view"), _("index"), _("sequence"),
  					  _("special"), _("Type"), _("Owner"));
+ 	if (showSeq && !showTables)
+ 		appendPQExpBuffer(&buf,
+ 					",\n retrieveval(c.oid) as \"%s\""
+ 					",\n CASE retrievecheck(c.oid) WHEN '1' THEN '%s' WHEN '0' THEN '%s' END as \"%s\"",
+ 					_("value"),_("  ***"),_(""),_("Start from"));
  
  	if (showIndexes)
  		appendPQExpBuffer(&buf,
***************
*** 1546,1551 ****
--- 1551,1559 ----
  		myopt.nullPrint = NULL;
  		myopt.title = _("List of relations");
  
+ 		if (showSeq && !showTables)
+ 			myopt.title = _("List of relations (*** - specifies the starting value and it has not yet been used)");
+ 
  		printQuery(res, &myopt, pset.queryFout, pset.logfile);
  	}
  
*** ./src/include/catalog/pg_proc.h.orig	Tue May  2 15:06:15 2006
--- ./src/include/catalog/pg_proc.h	Tue May  2 15:07:33 2006
***************
*** 2057,2062 ****
--- 2057,2067 ----
  DESCR("sequence current value");
  DATA(insert OID = 1576 (  setval			PGNSP PGUID 12 f f t f v 2 20 "2205 20" _null_ _null_ _null_  setval_oid - _null_ ));
  DESCR("set sequence value");
+ DATA(insert OID = 1577 (  retrieveval                   PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_  retrieveval_oid - _null_ ));
+ DESCR("sequence current value");
+ DATA(insert OID = 1578 (  retrievecheck                 PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_  retrievecheck_oid - _null_ ));
+ DESCR("sequence current value check");
+ 
  DATA(insert OID = 1765 (  setval			PGNSP PGUID 12 f f t f v 3 20 "2205 20 16" _null_ _null_ _null_ setval3_oid - _null_ ));
  DESCR("set sequence value and iscalled status");
  
*** ./src/include/commands/sequence.h.orig	Tue May  2 14:52:23 2006
--- ./src/include/commands/sequence.h	Tue May  2 15:00:06 2006
***************
*** 86,91 ****
--- 86,93 ----
  extern Datum setval_oid(PG_FUNCTION_ARGS);
  extern Datum setval3_oid(PG_FUNCTION_ARGS);
  extern Datum lastval(PG_FUNCTION_ARGS);
+ extern Datum retrieveval_oid(PG_FUNCTION_ARGS);
+ extern Datum retrievecheck_oid(PG_FUNCTION_ARGS);
  
  extern void DefineSequence(CreateSeqStmt *stmt);
  extern void AlterSequence(AlterSeqStmt *stmt);