In PostgreSQL 10, we added identity columns, as an alternative to serial
columns (since 6.something). They mostly work the same. Identity
columns are SQL-conforming, have some more features (e.g., overriding
clause), and are a bit more robust in schema management. Some of that
was described in [0]. AFAICT, there have been no complaints since that
identity columns lack features or are somehow a regression over serial
columns.
But clearly, the syntax "serial" is more handy, and most casual examples
use that syntax. So it seems like we are stuck with maintaining these
two variants in parallel forever. I was thinking we could nudge this a
little by remapping "serial" internally to create an identity column
instead. At least then over time, the use of the older serial
mechanisms would go away.
Note that pg_dump dumps a serial column in pieces (CREATE SEQUENCE +
ALTER SEQUENCE ... OWNED BY + ALTER TABLE ... SET DEFAULT). So if we
did this, any existing databases would keep their old semantics, and
those who really need it can manually create the old semantics as well.
Attached is a demo patch how the implementation of this change would
look like. This creates a bunch of regression test failures, but
AFAICT, those are mainly display differences and some very peculiar test
setups that are intentionally examining some edge cases. These would
need to be investigated in more detail, of course.
[0]:
https://www.enterprisedb.com/blog/postgresql-10-identity-columns-explainedFrom e94d059db4f19ee2c58ddb5d6522c8bef17576d2 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 4 Oct 2022 09:38:12 +0200
Subject: [PATCH] WIP: Change serial types to map to identity columns
This changes serial types to convert internally to identity columns,
instead of the custom construction involving nextval defaults that
they previously did.
---
src/backend/parser/parse_utilcmd.c | 44 ++----------------------------
1 file changed, 2 insertions(+), 42 deletions(-)
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index bd068bba05e4..491c60834fda 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -588,51 +588,11 @@ transformColumnDefinition(CreateStmtContext *cxt,
ColumnDef *column)
/* Special actions for SERIAL pseudo-types */
if (is_serial)
{
- char *snamespace;
- char *sname;
- char *qstring;
- A_Const *snamenode;
- TypeCast *castnode;
- FuncCall *funccallnode;
Constraint *constraint;
- generateSerialExtraStmts(cxt, column,
-
column->typeName->typeOid, NIL,
- false, false,
- &snamespace,
&sname);
-
- /*
- * Create appropriate constraints for SERIAL. We do this in
full,
- * rather than shortcutting, so that we will detect any
conflicting
- * constraints the user wrote (like a different DEFAULT).
- *
- * Create an expression tree representing the function call
- * nextval('sequencename'). We cannot reduce the raw tree to
cooked
- * form until after the sequence is created, but there's no
need to do
- * so.
- */
- qstring = quote_qualified_identifier(snamespace, sname);
- snamenode = makeNode(A_Const);
- snamenode->val.node.type = T_String;
- snamenode->val.sval.sval = qstring;
- snamenode->location = -1;
- castnode = makeNode(TypeCast);
- castnode->typeName = SystemTypeName("regclass");
- castnode->arg = (Node *) snamenode;
- castnode->location = -1;
- funccallnode = makeFuncCall(SystemFuncName("nextval"),
-
list_make1(castnode),
-
COERCE_EXPLICIT_CALL,
- -1);
- constraint = makeNode(Constraint);
- constraint->contype = CONSTR_DEFAULT;
- constraint->location = -1;
- constraint->raw_expr = (Node *) funccallnode;
- constraint->cooked_expr = NULL;
- column->constraints = lappend(column->constraints, constraint);
-
constraint = makeNode(Constraint);
- constraint->contype = CONSTR_NOTNULL;
+ constraint->contype = CONSTR_IDENTITY;
+ constraint->generated_when = ATTRIBUTE_IDENTITY_BY_DEFAULT;
constraint->location = -1;
column->constraints = lappend(column->constraints, constraint);
}
--
2.37.3
