Allow REPLICA IDENTITY with CREATE TABLE statement
Hi hackers!
In the nearby thread[0]/messages/by-id/202508061804.wwf4zc7npayp@alvherre.pgsql, enhancements for CREATE TABLE LIKE/REPLICA
IDENTITY were proposed.
To me, it makes sense to copy the replica identity definition if
INCLUDING INDEXES or INCLUDING CONSTRAINTS is given.
I agree that this can be beneficial.
But I decided to start with some simpler task, which is also sane (to
my belief) and can be used to improve user experience with this
feature. This is to allow you to specify REPLICA IDENTITY with CREATE
TABLE statement. Currently, it requires 2 (two) DDL statements to
define RI for relation (that is CREATE TABLE + ALTER TABLE). My patch
indent to enhance this.
This was actually proposed 12 years ago in the original thread, see
[1]: /messages/by-id/20131021181629.GI2968@awork2.anarazel.de -- Best regards, Kirill Reshke
replication to work, so was abandoned.
My v1 patch misses documentation and tests, yes, but this is just the
initial version to receive feedback, if this has a chance of being
committed or not, and general thoughts on the subject.
WDYT?
[0]: /messages/by-id/202508061804.wwf4zc7npayp@alvherre.pgsql
[1]: /messages/by-id/20131021181629.GI2968@awork2.anarazel.de -- Best regards, Kirill Reshke
--
Best regards,
Kirill Reshke
Attachments:
0001-Allow-to-explicitly-set-REPLICA-IDENTITY-with-relati.patchapplication/octet-stream; name=0001-Allow-to-explicitly-set-REPLICA-IDENTITY-with-relati.patchDownload
From aa9435c9c6f4dfc01d23babbfe8ba15fb55ee8cb Mon Sep 17 00:00:00 2001
From: reshke <reshke@double.cloud>
Date: Thu, 7 Aug 2025 12:29:39 +0000
Subject: [PATCH] Allow to explicitly set REPLICA IDENTITY with relation
creating.
Previously, the only way to define relation's REPLICA IDENTITY was
to CREATE relation and then ALTER TABLE ... REPLICA IDENTITY .. with it.
This commit allows single-DDL to this.
---
src/backend/commands/tablecmds.c | 5 +++
src/backend/parser/gram.y | 63 +++++++++++++++++++-------------
src/include/nodes/parsenodes.h | 1 +
3 files changed, 43 insertions(+), 26 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cb811520c29..282c0fd1110 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1088,6 +1088,11 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
*/
rel = relation_open(relationId, AccessExclusiveLock);
+ /* Create REPLICA IDENTITY if told to */
+ if (stmt->replicaIdentity != NULL) {
+ ATExecReplicaIdentity(rel, stmt->replicaIdentity, AccessExclusiveLock);
+ }
+
/*
* Now add any newly specified column default and generation expressions
* to the new relation. These are passed to us in the form of raw
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index db43034b9db..f50e936f8c9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -328,7 +328,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> add_drop opt_asc_desc opt_nulls_order
%type <node> alter_table_cmd alter_type_cmd opt_collate_clause
- replica_identity partition_cmd index_partition_cmd
+ replica_identity opt_replica_identity partition_cmd index_partition_cmd
%type <list> alter_table_cmds alter_type_cmds
%type <list> alter_identity_column_option_list
%type <defelt> alter_identity_column_option
@@ -3101,6 +3101,11 @@ replica_identity:
}
;
+
+opt_replica_identity: REPLICA IDENTITY_P replica_identity { $$ = $3; }
+ | /* EMPTY */ { $$ = NIL; }
+ ;
+
reloptions:
'(' reloption_list ')' { $$ = $2; }
;
@@ -3646,8 +3651,8 @@ copy_generic_opt_arg_list_item:
*****************************************************************************/
CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
- OptInherit OptPartitionSpec table_access_method_clause OptWith
- OnCommitOption OptTableSpace
+ OptInherit OptPartitionSpec table_access_method_clause opt_replica_identity
+ OptWith OnCommitOption OptTableSpace
{
CreateStmt *n = makeNode(CreateStmt);
@@ -3659,15 +3664,16 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->ofTypename = NULL;
n->constraints = NIL;
n->accessMethod = $10;
- n->options = $11;
- n->oncommit = $12;
- n->tablespacename = $13;
+ n->replicaIdentity = $11;
+ n->options = $12;
+ n->oncommit = $13;
+ n->tablespacename = $14;
n->if_not_exists = false;
$$ = (Node *) n;
}
| CREATE OptTemp TABLE IF_P NOT EXISTS qualified_name '('
OptTableElementList ')' OptInherit OptPartitionSpec table_access_method_clause
- OptWith OnCommitOption OptTableSpace
+ opt_replica_identity OptWith OnCommitOption OptTableSpace
{
CreateStmt *n = makeNode(CreateStmt);
@@ -3679,15 +3685,16 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->ofTypename = NULL;
n->constraints = NIL;
n->accessMethod = $13;
- n->options = $14;
- n->oncommit = $15;
- n->tablespacename = $16;
+ n->replicaIdentity = (ReplicaIdentityStmt *) $14;
+ n->options = $15;
+ n->oncommit = $16;
+ n->tablespacename = $17;
n->if_not_exists = true;
$$ = (Node *) n;
}
| CREATE OptTemp TABLE qualified_name OF any_name
OptTypedTableElementList OptPartitionSpec table_access_method_clause
- OptWith OnCommitOption OptTableSpace
+ opt_replica_identity OptWith OnCommitOption OptTableSpace
{
CreateStmt *n = makeNode(CreateStmt);
@@ -3700,15 +3707,16 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->ofTypename->location = @6;
n->constraints = NIL;
n->accessMethod = $9;
- n->options = $10;
- n->oncommit = $11;
- n->tablespacename = $12;
+ n->replicaIdentity = (ReplicaIdentityStmt *) $10;
+ n->options = $11;
+ n->oncommit = $12;
+ n->tablespacename = $13;
n->if_not_exists = false;
$$ = (Node *) n;
}
| CREATE OptTemp TABLE IF_P NOT EXISTS qualified_name OF any_name
OptTypedTableElementList OptPartitionSpec table_access_method_clause
- OptWith OnCommitOption OptTableSpace
+ opt_replica_identity OptWith OnCommitOption OptTableSpace
{
CreateStmt *n = makeNode(CreateStmt);
@@ -3721,15 +3729,16 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->ofTypename->location = @9;
n->constraints = NIL;
n->accessMethod = $12;
- n->options = $13;
- n->oncommit = $14;
- n->tablespacename = $15;
+ n->replicaIdentity = (ReplicaIdentityStmt *) $13;
+ n->options = $14;
+ n->oncommit = $15;
+ n->tablespacename = $16;
n->if_not_exists = true;
$$ = (Node *) n;
}
| CREATE OptTemp TABLE qualified_name PARTITION OF qualified_name
OptTypedTableElementList PartitionBoundSpec OptPartitionSpec
- table_access_method_clause OptWith OnCommitOption OptTableSpace
+ table_access_method_clause opt_replica_identity OptWith OnCommitOption OptTableSpace
{
CreateStmt *n = makeNode(CreateStmt);
@@ -3742,15 +3751,16 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->ofTypename = NULL;
n->constraints = NIL;
n->accessMethod = $11;
- n->options = $12;
- n->oncommit = $13;
- n->tablespacename = $14;
+ n->replicaIdentity = (ReplicaIdentityStmt *) $12;
+ n->options = $13;
+ n->oncommit = $14;
+ n->tablespacename = $15;
n->if_not_exists = false;
$$ = (Node *) n;
}
| CREATE OptTemp TABLE IF_P NOT EXISTS qualified_name PARTITION OF
qualified_name OptTypedTableElementList PartitionBoundSpec OptPartitionSpec
- table_access_method_clause OptWith OnCommitOption OptTableSpace
+ table_access_method_clause opt_replica_identity OptWith OnCommitOption OptTableSpace
{
CreateStmt *n = makeNode(CreateStmt);
@@ -3763,9 +3773,10 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->ofTypename = NULL;
n->constraints = NIL;
n->accessMethod = $14;
- n->options = $15;
- n->oncommit = $16;
- n->tablespacename = $17;
+ n->replicaIdentity = (ReplicaIdentityStmt *) $15;
+ n->options = $16;
+ n->oncommit = $17;
+ n->tablespacename = $18;
n->if_not_exists = true;
$$ = (Node *) n;
}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..ddb1ff40f41 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2757,6 +2757,7 @@ typedef struct CreateStmt
OnCommitAction oncommit; /* what do we do at COMMIT? */
char *tablespacename; /* table space to use, or NULL */
char *accessMethod; /* table access method */
+ ReplicaIdentityStmt *replicaIdentity; /* replica identity for relation, or NULL */
bool if_not_exists; /* just do nothing if it already exists? */
} CreateStmt;
--
2.43.0
On Thu, Aug 7, 2025 at 6:31 PM Kirill Reshke <reshkekirill@gmail.com> wrote:
Hi hackers!
In the nearby thread[0], enhancements for CREATE TABLE LIKE/REPLICA
IDENTITY were proposed.To me, it makes sense to copy the replica identity definition if
INCLUDING INDEXES or INCLUDING CONSTRAINTS is given.
I agree that this can be beneficial.
But I decided to start with some simpler task, which is also sane (to
my belief) and can be used to improve user experience with this
feature. This is to allow you to specify REPLICA IDENTITY with CREATE
TABLE statement. Currently, it requires 2 (two) DDL statements to
define RI for relation (that is CREATE TABLE + ALTER TABLE). My patch
indent to enhance this.This was actually proposed 12 years ago in the original thread, see
[1]. But at that time this was not considered vital for logical
replication to work, so was abandoned.My v1 patch misses documentation and tests, yes, but this is just the
initial version to receive feedback, if this has a chance of being
committed or not, and general thoughts on the subject.
I bumped into this requirement recently when working for a customer
case. The previous discussion isn't thorough and doesn't show that we
didn't want this feature that time. +1 for the feature.
I haven't reviewed the patches though.
--
Best Wishes,
Ashutosh Bapat