Postgres provides serial and bigserial column types for which it
implicitly creates sequence.
As far as this mechanism is somehow hidden from user, it may be
confusing that table
created with CREATE TABLE LIKE has no associated sequence.
But what is worse, even if experienced user knows that serial types are
implemented in Postgres by specifying
nextval(seq) default value for this column and default values are copied
by CREATE TABLE LIKE only if is it explicitly requested (including all),
then two tables will share the same sequence:
create table t1(x serial primary key, val int);
create table t2(like t1 including all);
postgres=# \d+ t1;
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage
| Stats target | Description
--------+---------+-----------+----------+-------------------------------+---------+--------------+-------------
x | integer | | not null |
nextval('t1_x_seq'::regclass) | plain | |
val | integer | | | |
plain | |
Indexes:
"t1_pkey" PRIMARY KEY, btree (x)
Access method: heap
postgres=# \d+ t2;
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage
| Stats target | Description
--------+---------+-----------+----------+-------------------------------+---------+--------------+-------------
x | integer | | not null |
nextval('t1_x_seq'::regclass) | plain | |
val | integer | | | |
plain | |
Indexes:
"t2_pkey" PRIMARY KEY, btree (x)
Access method: heap
Please notice that index is correctly replaced, but sequence - not.
I consider such behavior more like bug than a feature.
And it can be fixed using relatively small patch.
Thoughts?
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 27b596cb59..cb56f9f246 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -831,6 +831,19 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
{
CookedConstraint *cooked;
+ /*
+ * If column has identity sequence but is not marked as identity column,
+ * then it means that this sequence was created by CREATE TABLE LIKE (see comment in
+ * transformTableLikeClause). We have to patch default expression, storing new sequence OID.
+ */
+ if (!colDef->identity && colDef->identitySequence)
+ {
+ FuncExpr* nextval = ((FuncExpr*)colDef->cooked_default)->funcid == F_NEXTVAL_OID
+ ? (FuncExpr*)colDef->cooked_default
+ : (FuncExpr*)linitial(((FuncExpr*)colDef->cooked_default)->args);
+ ((Const*)linitial(nextval->args))->constvalue = RangeVarGetRelid(colDef->identitySequence, NoLock, false);
+ colDef->identitySequence = NULL;
+ }
cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint));
cooked->contype = CONSTR_DEFAULT;
cooked->conoid = InvalidOid; /* until created */
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 25abc544fc..70296d02a2 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -64,6 +64,7 @@
#include "rewrite/rewriteManip.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/partcache.h"
#include "utils/rel.h"
@@ -913,6 +914,19 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
}
}
+/*
+ * Check if sequence was implicitly created by Postgres for SERIAL column
+ */
+static bool
+is_autogenerated_sequence(Relation rel, char* colname, Oid seqid)
+{
+ char* seqname = makeObjectName(RelationGetRelationName(rel),
+ colname,
+ "seq");
+ return strcmp(get_rel_name(seqid), seqname) == 0;
+}
+
+
/*
* transformTableLikeClause
*
@@ -1072,7 +1086,32 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
{
if (attrdef[i].adnum == parent_attno)
{
+ FuncExpr* nextval;
this_default = stringToNode(attrdef[i].adbin);
+ /*
+ * If sequence is implicitly generated for SERIAL or BIGSERIAL field,
+ * then default expression has for nextval('REL_COL_seq') or nextval('REL_COL_seq')::int
+ * Let's create new sequence for the target relation in this case.
+ * Since default expression is referencing sequence OID and we do not have it
+ * at this moment, then let DefineRelation do the work of patching default expression.
+ */
+ if (def->identitySequence == NULL
+ && IsA(this_default, FuncExpr)
+ && ((nextval = (FuncExpr*)this_default)->funcid == F_NEXTVAL_OID
+ || (((FuncExpr*)this_default)->funcid == F_INT84
+ && IsA(linitial(((FuncExpr*)this_default)->args), FuncExpr)
+ && (nextval = (FuncExpr*)linitial(((FuncExpr*)this_default)->args))->funcid == F_NEXTVAL_OID)))
+ {
+ Oid seq_relid = ((Const*)linitial(nextval->args))->constvalue;
+ List *seq_options = sequence_options(seq_relid);
+ if (is_autogenerated_sequence(relation, def->colname, seq_relid))
+ {
+ generateSerialExtraStmts(cxt, def,
+ InvalidOid, seq_options,
+ false, false,
+ NULL, NULL);
+ }
+ }
break;
}
}
