Extended SERIAL parsing - Mailing list pgsql-hackers

Hi,

after some experimentation, I came up with the attached patch,
which implements parsing the following SERIAL types:

SERIAL
SERIAL GENERATED { ALWAYS | BY DEFAULT }
SERIAL GENERATED [ ALWAYS | BY DEFAULT ] AS IDENTITY( sequence options )

The underlying type is still int4 or int8,
so the problems you discussed aren't solved.
But at least the current semantics is kept.

It passes all regression tests, and it works, too:

# create table proba (i serial generated as identity(minvalue 5 maxvalue
10) primary key, t text);
NOTICE:  CREATE TABLE will create implicit sequence "proba_i_seq" for
serial column "proba.i"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"proba_pkey" for table "proba"
CREATE TABLE
# insert into proba (t) values ('a');
INSERT 0 1
# select * from proba;
 i | t
---+---
 5 | a
(1 row)

For now, GENERATED { ALWAYS | BY DEFAULT }
are just fillings.

The condition (column->is_serial && column->force_default)
can help enforcing GENERATED ALWAYS at INSERT time
and can also help fixing the two TODO entries about SERIAL.

Best regards,
Zoltán Böszörményi

diff -ur postgresql-8.2/src/backend/parser/analyze.c postgresql-8.2-serial/src/backend/parser/analyze.c
--- postgresql-8.2/src/backend/parser/analyze.c    2006-04-30 20:30:39.000000000 +0200
+++ postgresql-8.2-serial/src/backend/parser/analyze.c    2006-06-11 23:36:22.000000000 +0200
@@ -825,40 +825,17 @@
 transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt,
                           ColumnDef *column)
 {
-    bool        is_serial;
     bool        saw_nullable;
     Constraint *constraint;
     ListCell   *clist;

     cxt->columns = lappend(cxt->columns, column);

-    /* Check for SERIAL pseudo-types */
-    is_serial = false;
-    if (list_length(column->typename->names) == 1)
-    {
-        char       *typname = strVal(linitial(column->typename->names));
-
-        if (strcmp(typname, "serial") == 0 ||
-            strcmp(typname, "serial4") == 0)
-        {
-            is_serial = true;
-            column->typename->names = NIL;
-            column->typename->typeid = INT4OID;
-        }
-        else if (strcmp(typname, "bigserial") == 0 ||
-                 strcmp(typname, "serial8") == 0)
-        {
-            is_serial = true;
-            column->typename->names = NIL;
-            column->typename->typeid = INT8OID;
-        }
-    }
-
     /* Do necessary work on the column type declaration */
     transformColumnType(pstate, column);

     /* Special actions for SERIAL pseudo-types */
-    if (is_serial)
+    if (column->is_serial)
     {
         Oid            snamespaceid;
         char       *snamespace;
@@ -898,7 +875,7 @@
          */
         seqstmt = makeNode(CreateSeqStmt);
         seqstmt->sequence = makeRangeVar(snamespace, sname);
-        seqstmt->options = NIL;
+        seqstmt->options = column->seq_opts;

         cxt->blist = lappend(cxt->blist, seqstmt);

diff -ur postgresql-8.2/src/backend/parser/gram.y postgresql-8.2-serial/src/backend/parser/gram.y
--- postgresql-8.2/src/backend/parser/gram.y    2006-05-27 19:38:45.000000000 +0200
+++ postgresql-8.2-serial/src/backend/parser/gram.y    2006-06-11 23:42:02.000000000 +0200
@@ -275,6 +275,7 @@
 %type <boolean> opt_instead opt_analyze
 %type <boolean> index_opt_unique opt_verbose opt_full
 %type <boolean> opt_freeze opt_default opt_recheck
+%type <boolean>    ColIdGen ColOptIdGen
 %type <defelt>    opt_binary opt_oids copy_delimiter

 %type <boolean> copy_from opt_hold
@@ -284,8 +285,8 @@

 %type <node>    fetch_direction select_limit_value select_offset_value

-%type <list>    OptSeqList
-%type <defelt>    OptSeqElem
+%type <list>    OptSeqList OptSerialSeqList
+%type <defelt>    OptSeqElem OptSerialSeqElem

 %type <istmt>    insert_rest

@@ -313,7 +314,7 @@
 %type <range>    relation_expr_opt_alias
 %type <target>    target_el insert_target_el update_target_el insert_column_item

-%type <typnam>    Typename SimpleTypename ConstTypename
+%type <typnam>    Typename SimpleTypename SerialTypename ConstTypename
                 GenericType Numeric opt_float
                 Character ConstCharacter
                 CharacterWithLength CharacterWithoutLength
@@ -357,10 +358,10 @@

 /* ordinary key words in alphabetical order */
 %token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
-    AGGREGATE ALL ALSO ALTER ANALYSE ANALYZE AND ANY ARRAY AS ASC
+    AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
     ASSERTION ASSIGNMENT ASYMMETRIC AT AUTHORIZATION

-    BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
+    BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BIGSERIAL BINARY BIT
     BOOLEAN_P BOTH BY

     CACHE CALLED CASCADE CASCADED CASE CAST CHAIN CHAR_P
@@ -380,11 +381,11 @@
     FALSE_P FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
     FREEZE FROM FULL FUNCTION

-    GLOBAL GRANT GRANTED GREATEST GROUP_P
+    GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P

     HANDLER HAVING HEADER_P HOLD HOUR_P

-    IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
+    IDENTITY IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
     INDEX INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P
     INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
     INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -417,7 +418,7 @@
     ROLE ROLLBACK ROW ROWS RULE

     SAVEPOINT SCHEMA SCROLL SECOND_P SECURITY SELECT SEQUENCE
-    SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
+    SERIAL SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
     SHOW SIMILAR SIMPLE SMALLINT SOME STABLE START STATEMENT
     STATISTICS STDIN STDOUT STORAGE STRICT_P SUBSTRING SUPERUSER_P SYMMETRIC
     SYSID SYSTEM_P
@@ -1798,17 +1799,83 @@
             | TableConstraint                    { $$ = $1; }
         ;

-columnDef:    ColId Typename ColQualList
+/*
+ * SERIAL is special, handle it here. Possible cases:
+ * ColID SERIAL
+ * ColID SERIAL GENERATED {ALWAYS | BY DEFAULT}
+ * ColID SERIAL GENERATED [ALWAYS | BY DEFAULT] AS IDENTITY(...)
+ */
+columnDef:    ColId SerialTypename GENERATED ColOptIdGen AS IDENTITY '(' OptSerialSeqList ')' ColQualList
+                {
+                    ColumnDef *n = makeNode(ColumnDef);
+                    n->colname = $1;
+                    n->typename = $2;
+                    n->constraints = $10;
+                    n->is_local = true;
+                    n->is_serial = true;
+                    n->force_default = $4;
+                    n->seq_opts = $8;
+                    $$ = (Node *)n;
+                }
+            | ColId SerialTypename GENERATED ColIdGen ColQualList
+                {
+                    ColumnDef *n = makeNode(ColumnDef);
+                    n->colname = $1;
+                    n->typename = $2;
+                    n->constraints = $5;
+                    n->is_local = true;
+                    n->is_serial = true;
+                    n->force_default = $4;
+                    n->seq_opts = NIL;
+                    $$ = (Node *)n;
+                }
+            | ColId SerialTypename ColQualList
+                {
+                    ColumnDef *n = makeNode(ColumnDef);
+                    n->colname = $1;
+                    n->typename = $2;
+                    n->constraints = $3;
+                    n->is_local = true;
+                    n->is_serial = true;
+                    n->force_default = false;
+                    n->seq_opts = NIL;
+                    $$ = (Node *)n;
+                }
+            | ColId Typename ColQualList
                 {
                     ColumnDef *n = makeNode(ColumnDef);
                     n->colname = $1;
                     n->typename = $2;
                     n->constraints = $3;
                     n->is_local = true;
+                    n->is_serial = false;
+                    n->force_default = false;
+                    n->seq_opts = NIL;
                     $$ = (Node *)n;
                 }
         ;

+SerialTypename:    SERIAL
+                {
+                    $$ = SystemTypeName("int4");
+                }
+            | BIGSERIAL
+                {
+                    $$ = SystemTypeName("int8");
+                }
+        ;
+
+ColOptIdGen:
+        ALWAYS                                        { $$ = true; }
+            | BY DEFAULT                                { $$ = false; }
+            | /*EMPTY*/                                { $$ = false; }
+        ;
+
+ColIdGen:
+        ALWAYS                                        { $$ = true; }
+            | BY DEFAULT                                { $$ = false; }
+        ;
+
 ColQualList:
             ColQualList ColConstraint                { $$ = lappend($1, $2); }
             | /*EMPTY*/                                { $$ = NIL; }
@@ -2319,6 +2386,48 @@
                 }
         ;

+OptSerialSeqList: OptSerialSeqList OptSerialSeqElem                    { $$ = lappend($1, $2); }
+            | /*EMPTY*/                                { $$ = NIL; }
+        ;
+
+OptSerialSeqElem: CYCLE
+                {
+                    $$ = makeDefElem("cycle", (Node *)makeInteger(TRUE));
+                }
+            | NO CYCLE
+                {
+                    $$ = makeDefElem("cycle", (Node *)makeInteger(FALSE));
+                }
+            | INCREMENT opt_by NumericOnly
+                {
+                    $$ = makeDefElem("increment", (Node *)$3);
+                }
+            | MAXVALUE NumericOnly
+                {
+                    $$ = makeDefElem("maxvalue", (Node *)$2);
+                }
+            | MINVALUE NumericOnly
+                {
+                    $$ = makeDefElem("minvalue", (Node *)$2);
+                }
+            | NO MAXVALUE
+                {
+                    $$ = makeDefElem("maxvalue", NULL);
+                }
+            | NO MINVALUE
+                {
+                    $$ = makeDefElem("minvalue", NULL);
+                }
+            | START opt_with NumericOnly
+                {
+                    $$ = makeDefElem("start", (Node *)$3);
+                }
+            | RESTART opt_with NumericOnly
+                {
+                    $$ = makeDefElem("restart", (Node *)$3);
+                }
+        ;
+
 opt_by:        BY                {}
             | /* empty */    {}
       ;
@@ -8349,6 +8458,7 @@
             | AGGREGATE
             | ALSO
             | ALTER
+            | ALWAYS
             | ASSERTION
             | ASSIGNMENT
             | AT
@@ -8408,6 +8518,7 @@
             | FORCE
             | FORWARD
             | FUNCTION
+            | GENERATED
             | GLOBAL
             | GRANTED
             | HANDLER
@@ -8561,6 +8672,7 @@
  */
 col_name_keyword:
               BIGINT
+            | BIGSERIAL
             | BIT
             | BOOLEAN_P
             | CHAR_P
@@ -8589,6 +8701,7 @@
             | PRECISION
             | REAL
             | ROW
+            | SERIAL
             | SETOF
             | SMALLINT
             | SUBSTRING
@@ -8676,6 +8789,7 @@
             | GRANT
             | GROUP_P
             | HAVING
+            | IDENTITY
             | IN_P
             | INITIALLY
             | INTERSECT
diff -ur postgresql-8.2/src/backend/parser/keywords.c postgresql-8.2-serial/src/backend/parser/keywords.c
--- postgresql-8.2/src/backend/parser/keywords.c    2006-03-05 16:58:32.000000000 +0100
+++ postgresql-8.2-serial/src/backend/parser/keywords.c    2006-06-11 23:29:39.000000000 +0200
@@ -41,6 +41,7 @@
     {"all", ALL},
     {"also", ALSO},
     {"alter", ALTER},
+    {"always", ALWAYS},
     {"analyse", ANALYSE},        /* British spelling */
     {"analyze", ANALYZE},
     {"and", AND},
@@ -58,6 +59,7 @@
     {"begin", BEGIN_P},
     {"between", BETWEEN},
     {"bigint", BIGINT},
+    {"bigserial", BIGSERIAL},
     {"binary", BINARY},
     {"bit", BIT},
     {"boolean", BOOLEAN_P},
@@ -150,6 +152,7 @@
     {"from", FROM},
     {"full", FULL},
     {"function", FUNCTION},
+    {"generated", GENERATED},
     {"global", GLOBAL},
     {"grant", GRANT},
     {"granted", GRANTED},
@@ -160,6 +163,7 @@
     {"header", HEADER_P},
     {"hold", HOLD},
     {"hour", HOUR_P},
+    {"identity", IDENTITY},
     {"if", IF_P},
     {"ilike", ILIKE},
     {"immediate", IMMEDIATE},
@@ -297,6 +301,9 @@
     {"security", SECURITY},
     {"select", SELECT},
     {"sequence", SEQUENCE},
+    {"serial", SERIAL},
+    {"serial4", SERIAL},
+    {"serial8", BIGSERIAL},
     {"serializable", SERIALIZABLE},
     {"session", SESSION},
     {"session_user", SESSION_USER},
diff -ur postgresql-8.2/src/include/nodes/parsenodes.h postgresql-8.2-serial/src/include/nodes/parsenodes.h
--- postgresql-8.2/src/include/nodes/parsenodes.h    2006-04-30 20:30:40.000000000 +0200
+++ postgresql-8.2-serial/src/include/nodes/parsenodes.h    2006-06-11 23:37:15.000000000 +0200
@@ -394,6 +394,9 @@
     char       *cooked_default; /* nodeToString representation */
     List       *constraints;    /* other constraints on column */
     RangeVar   *support;        /* supporting relation, if any */
+    bool        is_serial;    /* the column is SERIAL */
+    bool        force_default;    /* the column is SERIAL GENERATED ALWAYS */
+    List       *seq_opts;        /* the column is SERIAL ... AS IDENTITY(...) */
 } ColumnDef;

 /*