public inbox for [email protected]
help / color / mirror / Atom feedImprove logical replication usability when tables lack primary keys
26+ messages / 7 participants
[nested] [flat]
* Improve logical replication usability when tables lack primary keys
@ 2025-11-10 08:06 Chao Li <[email protected]>
0 siblings, 2 replies; 26+ messages in thread
From: Chao Li @ 2025-11-10 08:06 UTC (permalink / raw)
To: Postgres hackers <[email protected]>
* BACKGROUND
This requirement comes from several users operating large deployments,
particularly in HIS (Hospital Information Systems). The situation can be
summarized as follows:
- A central DB operations team maintains the main database and configures
logical replication for all tables.
- Multiple third-party application vendors are allowed to create new tables
in that database.
- Some of these newly created tables lack a primary key. Since logical
replication with `REPLICATION IDENTITY DEFAULT` requires a primary key,
such tables silently fail to replicate.
- The DB operations team must then spend significant effort identifying the
affected tables and correcting them manually.
In practice, these environments would benefit from a safe fallback: if a
table has no primary key, logical replication should automatically switch
from `REPLICATION IDENTITY DEFAULT` to `FULL`, ensuring replication
continues rather than breaking.
I don't intend to debate whether this operational model is ideal; it is
simply the reality in many deployments. These database operations teams
have developed and refined their practices over many years, and as a
database vendor we have limited influence over how they manage their
environments.
* PROPOSED SOLUTION
I evaluated a few approaches and am proposing the following:
- Introduce a new GUC: `logical_replication_fallback_to_full_identity`.
- When enabled, if a table being logically replicated has no primary key,
the system automatically uses `REPLICATION IDENTITY FULL` for that table.
- This setting can be applied at the database level, so large systems do
not need to enable it cluster-wide unless desired.
- When the WAL sender transmits relation metadata, if fallback has
occurred, it explicitly reports `FULL` as the replication identity to the
subscriber, so there is limited impact on the subscriber.
* NEXT STEPS
The attached patch is an initial implementation. It does not yet include
tests or documentation updates. I would appreciate feedback on the design
approach first. If the direction seems reasonable, I will proceed with
refining the patch and adding documentation and tests.
Thanks in advance for your review.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v1-0001-Fallback-default-replication-identity-to-full.patch (8.8K, 3-v1-0001-Fallback-default-replication-identity-to-full.patch)
download | inline diff:
From 79169a50a53da93ea5d2ba03239a884b4d660158 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Mon, 10 Nov 2025 15:28:03 +0800
Subject: [PATCH v1] Fallback default replication identity to full
Author: Chao Li <[email protected]>
---
src/backend/access/heap/heapam.c | 7 ++---
src/backend/commands/publicationcmds.c | 5 ++--
src/backend/executor/execReplication.c | 2 +-
src/backend/replication/logical/proto.c | 6 ++++-
src/backend/replication/logical/relation.c | 26 +++++++++++++++++++
src/backend/utils/misc/guc_parameters.dat | 7 +++++
src/backend/utils/misc/postgresql.conf.sample | 2 ++
src/include/replication/logicalrelation.h | 4 +++
8 files changed, 52 insertions(+), 7 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 36fee9c994e..6c47d140bd1 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -45,6 +45,7 @@
#include "commands/vacuum.h"
#include "pgstat.h"
#include "port/pg_bitutils.h"
+#include "replication/logicalrelation.h"
#include "storage/lmgr.h"
#include "storage/predicate.h"
#include "storage/procarray.h"
@@ -3119,7 +3120,7 @@ l1:
if (old_key_tuple != NULL)
{
- if (relation->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
+ if (logicalrep_identity_is_full(relation))
xlrec.flags |= XLH_DELETE_CONTAINS_OLD_TUPLE;
else
xlrec.flags |= XLH_DELETE_CONTAINS_OLD_KEY;
@@ -8941,7 +8942,7 @@ log_heap_update(Relation reln, Buffer oldbuf,
xlrec.flags |= XLH_UPDATE_CONTAINS_NEW_TUPLE;
if (old_key_tuple)
{
- if (reln->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
+ if (logicalrep_identity_is_full(reln))
xlrec.flags |= XLH_UPDATE_CONTAINS_OLD_TUPLE;
else
xlrec.flags |= XLH_UPDATE_CONTAINS_OLD_KEY;
@@ -9167,7 +9168,7 @@ ExtractReplicaIdentity(Relation relation, HeapTuple tp, bool key_required,
if (replident == REPLICA_IDENTITY_NOTHING)
return NULL;
- if (replident == REPLICA_IDENTITY_FULL)
+ if (logicalrep_identity_is_full(relation))
{
/*
* When logging the entire old tuple, it very well could contain
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 1faf3a8c372..d3fa42123e2 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -37,6 +37,7 @@
#include "parser/parse_clause.h"
#include "parser/parse_collate.h"
#include "parser/parse_relation.h"
+#include "replication/logicalrelation.h"
#include "rewrite/rewriteHandler.h"
#include "storage/lmgr.h"
#include "utils/acl.h"
@@ -281,7 +282,7 @@ pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
* FULL means all columns are in the REPLICA IDENTITY, so all columns are
* allowed in the row filter and we can skip the validation.
*/
- if (relation->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
+ if (logicalrep_identity_is_full(relation))
return false;
/*
@@ -389,7 +390,7 @@ pub_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
pub = GetPublication(pubid);
check_and_fetch_column_list(pub, publish_as_relid, NULL, &columns);
- if (relation->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
+ if (logicalrep_identity_is_full(relation))
{
/* With REPLICA IDENTITY FULL, no column list is allowed. */
*invalid_column_list = (columns != NULL);
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index def32774c90..22863c80154 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -1088,7 +1088,7 @@ CheckCmdReplicaIdentity(Relation rel, CmdType cmd)
return;
/* REPLICA IDENTITY FULL is also good for UPDATE/DELETE. */
- if (rel->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
+ if (logicalrep_identity_is_full(rel))
return;
/*
diff --git a/src/backend/replication/logical/proto.c b/src/backend/replication/logical/proto.c
index f0a913892b9..aa23e6e75f9 100644
--- a/src/backend/replication/logical/proto.c
+++ b/src/backend/replication/logical/proto.c
@@ -17,6 +17,7 @@
#include "catalog/pg_type.h"
#include "libpq/pqformat.h"
#include "replication/logicalproto.h"
+#include "replication/logicalrelation.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
@@ -669,6 +670,7 @@ logicalrep_write_rel(StringInfo out, TransactionId xid, Relation rel,
PublishGencolsType include_gencols_type)
{
char *relname;
+ char relreplident = rel->rd_rel->relreplident;
pq_sendbyte(out, LOGICAL_REP_MSG_RELATION);
@@ -685,7 +687,9 @@ logicalrep_write_rel(StringInfo out, TransactionId xid, Relation rel,
pq_sendstring(out, relname);
/* send replica identity */
- pq_sendbyte(out, rel->rd_rel->relreplident);
+ if (logicalrep_identity_is_full(rel))
+ relreplident = REPLICA_IDENTITY_FULL;
+ pq_sendbyte(out, relreplident);
/* send the attribute info */
logicalrep_write_attrs(out, rel, columns, include_gencols_type);
diff --git a/src/backend/replication/logical/relation.c b/src/backend/replication/logical/relation.c
index 745fd3bab64..2c756fca469 100644
--- a/src/backend/replication/logical/relation.c
+++ b/src/backend/replication/logical/relation.c
@@ -30,6 +30,7 @@
#include "utils/lsyscache.h"
#include "utils/syscache.h"
+bool logical_replication_fallback_to_full_identity = false;
static MemoryContext LogicalRepRelMapContext = NULL;
@@ -958,3 +959,28 @@ FindLogicalRepLocalIndex(Relation localrel, LogicalRepRelation *remoterel,
return InvalidOid;
}
+
+/*
+ * logicalrep_identity_is_full
+ *
+ * Check whether the replica identity of the relation is full or not.
+ * When a table's replica identity is default, but there is no primary key,
+ * if logical_replication_fallback_to_full_identity is true, we consider the
+ * replica identity as full. This funciton should only be called on the
+ * publisher.
+ */
+bool
+logicalrep_identity_is_full(Relation relation)
+{
+ Form_pg_class relform = RelationGetForm(relation);
+
+ if (relform->relreplident == REPLICA_IDENTITY_FULL)
+ return true;
+
+ if (relform->relreplident == REPLICA_IDENTITY_DEFAULT &&
+ logical_replication_fallback_to_full_identity &&
+ !OidIsValid(RelationGetReplicaIndex(relation)))
+ return true;
+
+ return false;
+}
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 1128167c025..a07edb05254 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -1833,6 +1833,13 @@
max => 'MAX_KILOBYTES',
},
+{ name => 'logical_replication_fallback_to_full_identity', type => 'bool', context => 'PGC_SIGHUP', group => 'REPLICATION_SENDING',
+ short_desc => 'Use REPLICA IDENTITY FULL automatically when a table with DEFAULT identity has no primary key.',
+ long_desc => 'When enabled, logical replication will automatically send full-row data for tables that specify REPLICA IDENTITY DEFAULT but lack a primary key, instead of raising an error.',
+ variable => 'logical_replication_fallback_to_full_identity',
+ boot_val => 'false',
+},
+
{ name => 'maintenance_io_concurrency', type => 'int', context => 'PGC_USERSET', group => 'RESOURCES_IO',
short_desc => 'A variant of "effective_io_concurrency" that is used for maintenance work.',
long_desc => '0 disables simultaneous requests.',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index f62b61967ef..dc17caa2c2a 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -346,6 +346,8 @@
#wal_sender_timeout = 60s # in milliseconds; 0 disables
#track_commit_timestamp = off # collect timestamp of transaction commit
# (change requires restart)
+#logical_replication_fallback_to_full_identity = off # fallback default
+ # replication identity to full if no primary key
# - Primary Server -
diff --git a/src/include/replication/logicalrelation.h b/src/include/replication/logicalrelation.h
index 7a561a8e8d8..0c6299d3bca 100644
--- a/src/include/replication/logicalrelation.h
+++ b/src/include/replication/logicalrelation.h
@@ -16,6 +16,9 @@
#include "catalog/index.h"
#include "replication/logicalproto.h"
+/* GUC variables */
+extern PGDLLIMPORT bool logical_replication_fallback_to_full_identity;
+
typedef struct LogicalRepRelMapEntry
{
LogicalRepRelation remoterel; /* key is remoterel.remoteid */
@@ -48,6 +51,7 @@ extern LogicalRepRelMapEntry *logicalrep_partition_open(LogicalRepRelMapEntry *r
Relation partrel, AttrMap *map);
extern void logicalrep_rel_close(LogicalRepRelMapEntry *rel,
LOCKMODE lockmode);
+extern bool logicalrep_identity_is_full(Relation relation);
extern bool IsIndexUsableForReplicaIdentityFull(Relation idxrel, AttrMap *attrmap);
extern Oid GetRelationIdentityOrPK(Relation rel);
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-11-11 11:18 Amit Kapila <[email protected]>
parent: Chao Li <[email protected]>
1 sibling, 1 reply; 26+ messages in thread
From: Amit Kapila @ 2025-11-11 11:18 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Postgres hackers <[email protected]>
On Mon, Nov 10, 2025 at 1:36 PM Chao Li <[email protected]> wrote:
>
> * BACKGROUND
>
> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information Systems). The situation can be summarized as follows:
>
> - A central DB operations team maintains the main database and configures logical replication for all tables.
> - Multiple third-party application vendors are allowed to create new tables in that database.
> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY DEFAULT` requires a primary key, such tables silently fail to replicate.
> - The DB operations team must then spend significant effort identifying the affected tables and correcting them manually.
>
Can you share an example of how we silently fail to replicate? Won't
in such cases UPDATE/DELETE will anyway raise an ERROR?
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-11-11 12:09 Euler Taveira <[email protected]>
parent: Chao Li <[email protected]>
1 sibling, 2 replies; 26+ messages in thread
From: Euler Taveira @ 2025-11-11 12:09 UTC (permalink / raw)
To: Chao Li <[email protected]>; Postgres hackers <[email protected]>
On Mon, Nov 10, 2025, at 5:06 AM, Chao Li wrote:
> I evaluated a few approaches and am proposing the following:
>
> - Introduce a new GUC: `logical_replication_fallback_to_full_identity`.
> - When enabled, if a table being logically replicated has no primary
> key, the system automatically uses `REPLICATION IDENTITY FULL` for that
> table.
> - This setting can be applied at the database level, so large systems
> do not need to enable it cluster-wide unless desired.
> - When the WAL sender transmits relation metadata, if fallback has
> occurred, it explicitly reports `FULL` as the replication identity to
> the subscriber, so there is limited impact on the subscriber.
>
If I understand your proposal correctly, you want to add a new fallback to
replica identity. We already have a fallback for DEFAULT that means no primary
key is the same as NOTHING. I didn't like your proposal. It is too restrictive.
However, I see some usefulness in introducing a GUC default_replica_identity.
The proposal is similar to access method (default_table_access_method). The
DEFAULT option selects the replica identity sets as default_replica_identity
parameter. You need to add a new option (PRIMARY KEY); that should be the
default value. (If we don't want to break the backward compatibility, this new
option should fallback to NOTHING if there is no primary key. Another
alternative is to have a strict and non-strict option. I prefer the former.) Of
course, the USING INDEX option cannot be used. For pg_dump, you need to use SET
command to inform the default_replica_identity value so tables with the same
option as default_replica_identity doesn't emit an ALTER TABLE command.
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-11-11 12:40 Chao Li <[email protected]>
parent: Amit Kapila <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Chao Li @ 2025-11-11 12:40 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: Postgres hackers <[email protected]>
Hi Amit,
Thanks for asking.
> On Nov 11, 2025, at 19:18, Amit Kapila <[email protected]> wrote:
>
> On Mon, Nov 10, 2025 at 1:36 PM Chao Li <[email protected]> wrote:
>>
>> * BACKGROUND
>>
>> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information Systems). The situation can be summarized as follows:
>>
>> - A central DB operations team maintains the main database and configures logical replication for all tables.
>> - Multiple third-party application vendors are allowed to create new tables in that database.
>> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY DEFAULT` requires a primary key, such tables silently fail to replicate.
>> - The DB operations team must then spend significant effort identifying the affected tables and correcting them manually.
>>
>
> Can you share an example of how we silently fail to replicate? Won't
> in such cases UPDATE/DELETE will anyway raise an ERROR?
>
Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the update/delete.
However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may silently fail to replicate.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-11-12 02:21 Chao Li <[email protected]>
parent: Euler Taveira <[email protected]>
1 sibling, 0 replies; 26+ messages in thread
From: Chao Li @ 2025-11-12 02:21 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; +Cc: Postgres hackers <[email protected]>
> On Nov 11, 2025, at 20:09, Euler Taveira <[email protected]> wrote:
>
> On Mon, Nov 10, 2025, at 5:06 AM, Chao Li wrote:
>> I evaluated a few approaches and am proposing the following:
>>
>> - Introduce a new GUC: `logical_replication_fallback_to_full_identity`.
>> - When enabled, if a table being logically replicated has no primary
>> key, the system automatically uses `REPLICATION IDENTITY FULL` for that
>> table.
>> - This setting can be applied at the database level, so large systems
>> do not need to enable it cluster-wide unless desired.
>> - When the WAL sender transmits relation metadata, if fallback has
>> occurred, it explicitly reports `FULL` as the replication identity to
>> the subscriber, so there is limited impact on the subscriber.
>>
>
> If I understand your proposal correctly, you want to add a new fallback to
> replica identity. We already have a fallback for DEFAULT that means no primary
> key is the same as NOTHING. I didn't like your proposal. It is too restrictive.
>
> However, I see some usefulness in introducing a GUC default_replica_identity.
> The proposal is similar to access method (default_table_access_method). The
> DEFAULT option selects the replica identity sets as default_replica_identity
> parameter. You need to add a new option (PRIMARY KEY); that should be the
> default value. (If we don't want to break the backward compatibility, this new
> option should fallback to NOTHING if there is no primary key. Another
> alternative is to have a strict and non-strict option. I prefer the former.) Of
> course, the USING INDEX option cannot be used. For pg_dump, you need to use SET
> command to inform the default_replica_identity value so tables with the same
> option as default_replica_identity doesn't emit an ALTER TABLE command.
>
Hi Euler,
Thank you very much for the valuable feedback. These are a lot of useful information. As I mentioned in my first email, my proposal was just an initial implementation, I am open for discussion from the design perspective.
Actually I explored the solution of adding a GUC for default_replication_identify. Let me briefly list solutions I explored:
1. The first solution I explored was adding a GUC for replication_identify_fallback_method, possible options are “nothing” and “full”. I gave up that because the solution is also an equivalent to the one I proposed of a bool option (false->nothing, true->full) and a bool option is easier to use.
2. Then I considered to add a GUC for default replication identity which is the same as you suggested. I gave up that because this solution would require to update all existing tables’ replication identities.
3. I also considered to add a new replication identity, I hadn't named it, but meaning was using primary key and fallback to full. I gave up that because it’s too much complicated than other solutions, and that would also required to update all existing tables’ replication identities.
4. Finally I decided the one I proposed. The main reason I chose it is because 1) production deployments wouldn't need to update existing table’s replication identity; 2) the change only needs to be applied in the wal-sender side; 3) without turning on the GUC option, no any impact.
Given there is a similar GUC option default_table_access_method (I wasn’t aware of that), I think 2 as you suggested might be the direction to go along with.
Let’s wait a few more days to see if other folks may comment as well.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-15 01:57 Chao Li <[email protected]>
parent: Euler Taveira <[email protected]>
1 sibling, 1 reply; 26+ messages in thread
From: Chao Li @ 2025-12-15 01:57 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; +Cc: Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
> On Nov 11, 2025, at 20:09, Euler Taveira <[email protected]> wrote:
>
> On Mon, Nov 10, 2025, at 5:06 AM, Chao Li wrote:
>> I evaluated a few approaches and am proposing the following:
>>
>> - Introduce a new GUC: `logical_replication_fallback_to_full_identity`.
>> - When enabled, if a table being logically replicated has no primary
>> key, the system automatically uses `REPLICATION IDENTITY FULL` for that
>> table.
>> - This setting can be applied at the database level, so large systems
>> do not need to enable it cluster-wide unless desired.
>> - When the WAL sender transmits relation metadata, if fallback has
>> occurred, it explicitly reports `FULL` as the replication identity to
>> the subscriber, so there is limited impact on the subscriber.
>>
>
> If I understand your proposal correctly, you want to add a new fallback to
> replica identity. We already have a fallback for DEFAULT that means no primary
> key is the same as NOTHING. I didn't like your proposal. It is too restrictive.
>
> However, I see some usefulness in introducing a GUC default_replica_identity.
> The proposal is similar to access method (default_table_access_method). The
> DEFAULT option selects the replica identity sets as default_replica_identity
> parameter. You need to add a new option (PRIMARY KEY); that should be the
> default value. (If we don't want to break the backward compatibility, this new
> option should fallback to NOTHING if there is no primary key. Another
> alternative is to have a strict and non-strict option. I prefer the former.) Of
> course, the USING INDEX option cannot be used. For pg_dump, you need to use SET
> command to inform the default_replica_identity value so tables with the same
> option as default_replica_identity doesn't emit an ALTER TABLE command.
>
I’ve thought this over and discussed it with our field teams. It looks to us that introducing a new GUC like default_replica_identity does not really address our pain point.
Our core requirement is to allow tables without a primary key to use FULL as the replica identity, while tables with a primary key should continue to use DEFAULT.
If we add default_replica_identity and set it to FULL, then a newly created table that does have a primary key would also end up using FULL, which is definitely not what we want.
As you mentioned, PostgreSQL already has a fallback from DEFAULT to NOTHING. What we actually want is the ability to customize this fallback, so that users can choose whether DEFAULT falls back to NOTHING or to FULL. Customizing the fallback via a new GUC would also allow field teams to set this option per database.
If we do want to add default_replica_identity, I think that should be treated as a separate topic. By the way, could you explain what use case you have in mind for it?
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-15 03:28 Dilip Kumar <[email protected]>
parent: Chao Li <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Dilip Kumar @ 2025-12-15 03:28 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Amit Kapila <[email protected]>; Postgres hackers <[email protected]>
On Tue, Nov 11, 2025 at 6:11 PM Chao Li <[email protected]> wrote:
>
> Hi Amit,
>
> Thanks for asking.
>
> > On Nov 11, 2025, at 19:18, Amit Kapila <[email protected]> wrote:
> >
> > On Mon, Nov 10, 2025 at 1:36 PM Chao Li <[email protected]> wrote:
> >>
> >> * BACKGROUND
> >>
> >> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information Systems). The situation can be summarized as follows:
> >>
> >> - A central DB operations team maintains the main database and configures logical replication for all tables.
> >> - Multiple third-party application vendors are allowed to create new tables in that database.
> >> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY DEFAULT` requires a primary key, such tables silently fail to replicate.
> >> - The DB operations team must then spend significant effort identifying the affected tables and correcting them manually.
> >>
> >
> > Can you share an example of how we silently fail to replicate? Won't
> > in such cases UPDATE/DELETE will anyway raise an ERROR?
> >
>
> Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the update/delete.
>
> However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may silently fail to replicate.
But other than UPDATE/DELETE for what operation we need RI, I mean
INSERT would work without any RI and UPDATE/DELETE will fail on the
publisher itself without setting RI, so can you explain the exact case
where it will silently fail to replicate?
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-15 03:35 Chao Li <[email protected]>
parent: Dilip Kumar <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Chao Li @ 2025-12-15 03:35 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: Amit Kapila <[email protected]>; Postgres hackers <[email protected]>
> On Dec 15, 2025, at 11:28, Dilip Kumar <[email protected]> wrote:
>
> On Tue, Nov 11, 2025 at 6:11 PM Chao Li <[email protected]> wrote:
>>
>> Hi Amit,
>>
>> Thanks for asking.
>>
>>> On Nov 11, 2025, at 19:18, Amit Kapila <[email protected]> wrote:
>>>
>>> On Mon, Nov 10, 2025 at 1:36 PM Chao Li <[email protected]> wrote:
>>>>
>>>> * BACKGROUND
>>>>
>>>> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information Systems). The situation can be summarized as follows:
>>>>
>>>> - A central DB operations team maintains the main database and configures logical replication for all tables.
>>>> - Multiple third-party application vendors are allowed to create new tables in that database.
>>>> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY DEFAULT` requires a primary key, such tables silently fail to replicate.
>>>> - The DB operations team must then spend significant effort identifying the affected tables and correcting them manually.
>>>>
>>>
>>> Can you share an example of how we silently fail to replicate? Won't
>>> in such cases UPDATE/DELETE will anyway raise an ERROR?
>>>
>>
>> Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the update/delete.
>>
>> However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may silently fail to replicate.
>
> But other than UPDATE/DELETE for what operation we need RI, I mean
> INSERT would work without any RI and UPDATE/DELETE will fail on the
> publisher itself without setting RI, so can you explain the exact case
> where it will silently fail to replicate?
>
> --
> Regards,
> Dilip Kumar
> Google
Hi Dilip,
Thanks for asking. When fallback to FULL, UPDATE/DELETE will be allowed in the publisher side. In my first email, attached v1 patch is a PoC that has implemented the logic.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-15 05:48 Amit Kapila <[email protected]>
parent: Chao Li <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Amit Kapila @ 2025-12-15 05:48 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
On Mon, Dec 15, 2025 at 9:06 AM Chao Li <[email protected]> wrote:
>
> > On Dec 15, 2025, at 11:28, Dilip Kumar <[email protected]> wrote:
> >
> > On Tue, Nov 11, 2025 at 6:11 PM Chao Li <[email protected]> wrote:
> >>
> >> Hi Amit,
> >>
> >> Thanks for asking.
> >>
> >>> On Nov 11, 2025, at 19:18, Amit Kapila <[email protected]> wrote:
> >>>
> >>> On Mon, Nov 10, 2025 at 1:36 PM Chao Li <[email protected]> wrote:
> >>>>
> >>>> * BACKGROUND
> >>>>
> >>>> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information Systems). The situation can be summarized as follows:
> >>>>
> >>>> - A central DB operations team maintains the main database and configures logical replication for all tables.
> >>>> - Multiple third-party application vendors are allowed to create new tables in that database.
> >>>> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY DEFAULT` requires a primary key, such tables silently fail to replicate.
> >>>> - The DB operations team must then spend significant effort identifying the affected tables and correcting them manually.
> >>>>
> >>>
> >>> Can you share an example of how we silently fail to replicate? Won't
> >>> in such cases UPDATE/DELETE will anyway raise an ERROR?
> >>>
> >>
> >> Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the update/delete.
> >>
> >> However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may silently fail to replicate.
> >
> > But other than UPDATE/DELETE for what operation we need RI, I mean
> > INSERT would work without any RI and UPDATE/DELETE will fail on the
> > publisher itself without setting RI, so can you explain the exact case
> > where it will silently fail to replicate?
> >
>
> Thanks for asking. When fallback to FULL, UPDATE/DELETE will be allowed in the publisher side. In my first email, attached v1 patch is a PoC that has implemented the logic.
>
So, without patch, there is no way we can silently replicate the
UPDATE/DELETE. Ideally, users should alter the tables and make RI as
FULL in such cases if they don't have PK for such tables. Falling back
to FULL for DEFAULT when the table doesn't have PK based on GUC has a
downside that it will increase WAL volume by a large amount. I think
it should be done specific to tables that users want to replicate. I
don't know what is a good way to give to users who don't want to do
the required setup but if we really want to provide something, it is
better to allow such a thing via the publication option instead. I
think it would be good to do such an enhancement if we have more
community support and some other users also appreciate such a feature.
Otherwise, adding something which is specific to a particular user
sounds like a recipe of maintenance burden especially when we already
provide a way to achieve the same thing as is required by the user.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-16 01:47 Euler Taveira <[email protected]>
parent: Chao Li <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Euler Taveira @ 2025-12-16 01:47 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Postgres hackers <[email protected]>; Amit Kapila <[email protected]>
On Sun, Dec 14, 2025, at 10:57 PM, Chao Li wrote:
> I’ve thought this over and discussed it with our field teams. It looks
> to us that introducing a new GUC like default_replica_identity does not
> really address our pain point.
>
> Our core requirement is to allow tables without a primary key to use
> FULL as the replica identity, while tables with a primary key should
> continue to use DEFAULT.
>
As Amit said in [1], this proposal is not viable because of WAL volume. Since
you don't know if table foo without primary key will replicate, it defaults to
log the old tuple even if you never add this table to a publication.
> If we add default_replica_identity and set it to FULL, then a newly
> created table that does have a primary key would also end up using
> FULL, which is definitely not what we want.
>
You can propose a different behavior. Let's say FULL_NO_PK.
> As you mentioned, PostgreSQL already has a fallback from DEFAULT to
> NOTHING. What we actually want is the ability to customize this
> fallback, so that users can choose whether DEFAULT falls back to
> NOTHING or to FULL. Customizing the fallback via a new GUC would also
> allow field teams to set this option per database.
>
> If we do want to add default_replica_identity, I think that should be
> treated as a separate topic. By the way, could you explain what use
> case you have in mind for it?
>
It is an alternative way for ALTER TABLE ... REPLICA IDENTITY to set a
non-default value. It also has the advantage of applying to multiple tables if
you are in the same session. If the majority of your tables has a non-default
replica identity, it would drastically reduce the number of ALTER TABLE ...
REPLICA IDENTITY lines in your dump.
I think it is unlikely that we would change the replica identity default value
but we could consider additional ones. Hence, the default_replica_identity plus
a new value could improve your scenario. I don't have the complete picture so I
cannot propose a feasible solution. Maybe what I said is enough or maybe not.
Regarding the default_replica_identity proposal, it is just a value to use
while creating a table.
[1] postgr.es/m/CAA4eK1KzjxO-qWjWSox6e6AWH4FVU5ZPEgeZ+na=eyov7umutg@mail.gmail.com
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-16 06:47 Chao Li <[email protected]>
parent: Amit Kapila <[email protected]>
0 siblings, 2 replies; 26+ messages in thread
From: Chao Li @ 2025-12-16 06:47 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
> On Dec 15, 2025, at 13:48, Amit Kapila <[email protected]> wrote:
>
> On Mon, Dec 15, 2025 at 9:06 AM Chao Li <[email protected]> wrote:
>>
>>> On Dec 15, 2025, at 11:28, Dilip Kumar <[email protected]> wrote:
>>>
>>> On Tue, Nov 11, 2025 at 6:11 PM Chao Li <[email protected]> wrote:
>>>>
>>>> Hi Amit,
>>>>
>>>> Thanks for asking.
>>>>
>>>>> On Nov 11, 2025, at 19:18, Amit Kapila <[email protected]> wrote:
>>>>>
>>>>> On Mon, Nov 10, 2025 at 1:36 PM Chao Li <[email protected]> wrote:
>>>>>>
>>>>>> * BACKGROUND
>>>>>>
>>>>>> This requirement comes from several users operating large deployments, particularly in HIS (Hospital Information Systems). The situation can be summarized as follows:
>>>>>>
>>>>>> - A central DB operations team maintains the main database and configures logical replication for all tables.
>>>>>> - Multiple third-party application vendors are allowed to create new tables in that database.
>>>>>> - Some of these newly created tables lack a primary key. Since logical replication with `REPLICATION IDENTITY DEFAULT` requires a primary key, such tables silently fail to replicate.
>>>>>> - The DB operations team must then spend significant effort identifying the affected tables and correcting them manually.
>>>>>>
>>>>>
>>>>> Can you share an example of how we silently fail to replicate? Won't
>>>>> in such cases UPDATE/DELETE will anyway raise an ERROR?
>>>>>
>>>>
>>>> Yes, UPDATE/DELETE will fail. That’s the easy case to expose the error. Actually my patch will allow the update/delete.
>>>>
>>>> However, some tables, like dictionary tables, they are important, but don’t have much update/delete, they may silently fail to replicate.
>>>
>>> But other than UPDATE/DELETE for what operation we need RI, I mean
>>> INSERT would work without any RI and UPDATE/DELETE will fail on the
>>> publisher itself without setting RI, so can you explain the exact case
>>> where it will silently fail to replicate?
>>>
>>
>> Thanks for asking. When fallback to FULL, UPDATE/DELETE will be allowed in the publisher side. In my first email, attached v1 patch is a PoC that has implemented the logic.
>>
>
> So, without patch, there is no way we can silently replicate the
> UPDATE/DELETE. Ideally, users should alter the tables and make RI as
> FULL in such cases if they don't have PK for such tables. Falling back
> to FULL for DEFAULT when the table doesn't have PK based on GUC has a
> downside that it will increase WAL volume by a large amount.
I agree that this downside exists, but it is an inherent cost that users must accept if they choose to replicate all tables, including those without a primary key. In practice, users who opt into such a configuration are typically aware of the WAL overhead and make that trade-off consciously.
> I think it should be done specific to tables that users want to replicate.
That is why I mentioned earlier that the new GUC should only be configurable at the database level (via ALTER DATABASE). However, I agree that there is still a risk that a user could mistakenly set it in postgresql.conf, thereby making it effective for the entire cluster.
> I don't know what is a good way to give to users who don't want to do
> the required setup but if we really want to provide something, it is
> better to allow such a thing via the publication option instead.
Using a publication-level option could also work. One complication, however, is that a table can belong to multiple publications. For example, if table_a belongs to both pub_a and pub_b, and only pub_a is configured with fallback_to_full while pub_b keeps the default behavior (fallback_to_none), then the effective behavior for table_a would need to remain fallback_to_none, meaning that UPDATE/DELETE would still not be allowed if table_a has not a primary key.
> I think it would be good to do such an enhancement if we have more
> community support and some other users also appreciate such a feature.
> Otherwise, adding something which is specific to a particular user
> sounds like a recipe of maintenance burden especially when we already
> provide a way to achieve the same thing as is required by the user.
Let me elaborate on that point.
My company has a very large user base in China, with over 100K deployments across multiple industries. However, there is currently a significant gap between this large user population and direct participation in the PG community. I joined the company in July this year as a full-time contributor to the PG community, and one of my responsibilities is to help bridge this gap and bring real-world user feedback into community discussions.
As I mentioned in my earlier email, this requirement comes from large-scale deployments. The database owners in these environments have operational models that may not always align with what we consider the ideal or fully optimized setup, but they are the result of years of accumulated practice and operational experience. For these users, the proposed feature would significantly simplify their day-to-day operations and reduce operational friction.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-17 09:32 GRANT ZHOU <[email protected]>
parent: Chao Li <[email protected]>
1 sibling, 0 replies; 26+ messages in thread
From: GRANT ZHOU @ 2025-12-17 09:32 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
On Tue, Dec 16, 2025 at 4:59 PM Chao Li <[email protected]> wrote:
> > On Dec 15, 2025, at 13:48, Amit Kapila <[email protected]> wrote:
>
> > So, without patch, there is no way we can silently replicate the
> > UPDATE/DELETE. Ideally, users should alter the tables and make RI as
> > FULL in such cases if they don't have PK for such tables. Falling back
> > to FULL for DEFAULT when the table doesn't have PK based on GUC has a
> > downside that it will increase WAL volume by a large amount.
>
> I agree that this downside exists, but it is an inherent cost that users
> must accept if they choose to replicate all tables, including those without
> a primary key. In practice, users who opt into such a configuration are
> typically aware of the WAL overhead and make that trade-off consciously.
>
> > I don't know what is a good way to give to users who don't want to do
> > the required setup but if we really want to provide something, it is
> > better to allow such a thing via the publication option instead.
>
> Using a publication-level option could also work. One complication,
> however, is that a table can belong to multiple publications. For example,
> if table_a belongs to both pub_a and pub_b, and only pub_a is configured
> with fallback_to_full while pub_b keeps the default behavior
> (fallback_to_none), then the effective behavior for table_a would need to
> remain fallback_to_none, meaning that UPDATE/DELETE would still not be
> allowed if table_a has not a primary key.
>
> > I think it would be good to do such an enhancement if we have more
> > community support and some other users also appreciate such a feature.
> > Otherwise, adding something which is specific to a particular user
> > sounds like a recipe of maintenance burden especially when we already
> > provide a way to achieve the same thing as is required by the user.
>
> Let me elaborate on that point.
>
> My company has a very large user base in China, with over 100K deployments
> across multiple industries. However, there is currently a significant gap
> between this large user population and direct participation in the PG
> community. I joined the company in July this year as a full-time
> contributor to the PG community, and one of my responsibilities is to help
> bridge this gap and bring real-world user feedback into community
> discussions.
>
> As I mentioned in my earlier email, this requirement comes from
> large-scale deployments. The database owners in these environments have
> operational models that may not always align with what we consider the
> ideal or fully optimized setup, but they are the result of years of
> accumulated practice and operational experience. For these users, the
> proposed feature would significantly simplify their day-to-day operations
> and reduce operational friction.
>
+1 on the importance of addressing these large-scale operational realities.
Beyond the scale issue, I believe there is a noticeable inconsistency
between the documentation's promise of automation and the actual behavior
of Replica Identity.
1. The "Practical Gap" of Schema Automation
According to the documentation for FOR TABLES IN SCHEMA [1], the feature
matches "all tables in the specified list of schemas, including tables
created in the future". This explicitly promises an unattended, automated
workflow for new tables.
However, this promise is immediately broken by the default Replica Identity
rules:
1) New tables are created with REPLICA IDENTITY DEFAULT [2] by standard.
2) For tables without a primary key, DEFAULT identity "cannot support
UPDATE or DELETE operations" and "attempting such operations will result in
an error on the publisher"[3].
This creates a logical trap: The system automatically adds the new table to
the publication (as promised), but then immediately fails on the first
UPDATE operation because the table creates with an incompatible default
identity.
This forces manual intervention (ALTER TABLE) in what is supposed to be an
automated workflow.
2. Regarding the solution:
I support Amit's suggestion of a Publication Option. It avoids the risks of
a global GUC while allowing users to explicitly opt-in to the trade-off
(accepting higher WAL volume) to ensure the automation provided by FOR
TABLES IN SCHEMA is functionally complete.
[1]
https://www.postgresql.org/docs/current/sql-createpublication.html#SQL-CREATEPUBLICATION-PARAMS-FOR-...
[2]
https://www.postgresql.org/docs/18/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY
[3]
https://www.postgresql.org/docs/18/logical-replication-publication.html#LOGICAL-REPLICATION-PUBLICAT...
--
Grant Zhou at Highgo Software
^ permalink raw reply [nested|flat] 26+ messages in thread
* RE: Improve logical replication usability when tables lack primary keys
@ 2025-12-17 11:09 Zhijie Hou (Fujitsu) <[email protected]>
parent: Chao Li <[email protected]>
1 sibling, 1 reply; 26+ messages in thread
From: Zhijie Hou (Fujitsu) @ 2025-12-17 11:09 UTC (permalink / raw)
To: Chao Li <[email protected]>; Amit Kapila <[email protected]>; +Cc: Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
On Tuesday, December 16, 2025 2:47 PM Chao Li <[email protected]> wrote:
> > On Dec 15, 2025, at 13:48, Amit Kapila <[email protected]> wrote:
> >
> > So, without patch, there is no way we can silently replicate the
> > UPDATE/DELETE. Ideally, users should alter the tables and make RI as
> > FULL in such cases if they don't have PK for such tables. Falling back
> > to FULL for DEFAULT when the table doesn't have PK based on GUC has a
> > downside that it will increase WAL volume by a large amount.
>
> I agree that this downside exists, but it is an inherent cost that users must
> accept if they choose to replicate all tables, including those without a primary
> key. In practice, users who opt into such a configuration are typically aware of
> the WAL overhead and make that trade-off consciously.
>
> > I think it should be done specific to tables that users want to replicate.
>
> That is why I mentioned earlier that the new GUC should only be configurable
> at the database level (via ALTER DATABASE). However, I agree that there is
> still a risk that a user could mistakenly set it in postgresql.conf, thereby
> making it effective for the entire cluster.
>
> > I don't know what is a good way to give to users who don't want to do
> > the required setup but if we really want to provide something, it is
> > better to allow such a thing via the publication option instead.
>
> Using a publication-level option could also work. One complication, however,
> is that a table can belong to multiple publications. For example, if table_a
> belongs to both pub_a and pub_b, and only pub_a is configured with
> fallback_to_full while pub_b keeps the default behavior (fallback_to_none),
> then the effective behavior for table_a would need to remain
> fallback_to_none, meaning that UPDATE/DELETE would still not be allowed if
> table_a has not a primary key.
I think the common approach for combining options between publications is to
use an "OR" logic. For example, if at least one publication's option is true, we
treat the option as true for a given table. This pattern is evident in
CheckCmdReplicaIdentity(), where we conduct replica identity checks if any
publication replicates INSERTs/UPDATEs for the table even if some other publications
do not replicate.
And I also prefer using a publication option as it's always beneficial to
minimize unnecessary WAL generation whenever possible.
Best Regards,
Hou zj
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-17 20:49 Euler Taveira <[email protected]>
parent: Zhijie Hou (Fujitsu) <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Euler Taveira @ 2025-12-17 20:49 UTC (permalink / raw)
To: [email protected] <[email protected]>; Chao Li <[email protected]>; Amit Kapila <[email protected]>; +Cc: Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
On Wed, Dec 17, 2025, at 8:09 AM, Zhijie Hou (Fujitsu) wrote:
>
> And I also prefer using a publication option as it's always beneficial to
> minimize unnecessary WAL generation whenever possible.
>
The ship has sailed a long time ago (version 9.4 to be precise -- commit
07cacba983ef). The row identifier property was defined as an SQL command (ALTER
TABLE ... REPLICA IDENTITY) and *not* a publication property. IMO that's the
correct design because row identifier is a table property. Extend this concept
to a publication property is the wrong direction. It is confusing and complex.
Each table needs to say what's its row identifier. The user created a table
without primary key. Well, create a primary key. There are dozens of thousands
of objects. Use a script. I would suggest a way to disallow or add a warning
message while creating the publication or adding new tables, however, the FOR
ALL TABLES and FOR TABLES IN SCHEMA were mentioned. There isn't a reliable way
to guarantee that a publication with UPDATE and/or DELETE option contains only
tables with pk, RI FULL or RI USING INDEX. The fact that there is no rows in the
pg_publication_rel for these clauses, makes validating the CREATE/ALTER
PUBLICATION commands more difficult. (I prefer deterministic commands and when I
saw an object definition saying "including objects created in the future", my
first question is: what's the drawbacks and caveats?)
I don't think the current behavior is lacking documentation; the REPLICA
IDENTITY concept is explicitly in the logical replication chapter [1].
[1] https://www.postgresql.org/docs/current/logical-replication-publication.html
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-17 21:43 GRANT ZHOU <[email protected]>
parent: Euler Taveira <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: GRANT ZHOU @ 2025-12-17 21:43 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; +Cc: [email protected] <[email protected]>; Chao Li <[email protected]>; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
> The ship has sailed a long time ago (version 9.4 to be precise -- commit
> 07cacba983ef). The row identifier property was defined as an SQL command
> (ALTER
> TABLE ... REPLICA IDENTITY) and *not* a publication property. IMO that's
> the
> correct design because row identifier is a table property. Extend this
> concept
> to a publication property is the wrong direction. It is confusing and
> complex.
>
Thanks for the detailed history. I completely understand and respect that
Replica Identity is designed as a table property.
> Each table needs to say what's its row identifier. The user created a table
> without primary key. Well, create a primary key. There are dozens of
> thousands
> of objects. Use a script.
However, I’d like to share a user perspective regarding the "use a script"
approach. The main value of FOR TABLES IN SCHEMA is *in-database automation*.
If users still need to maintain external scripts to monitor and ALTER new
tables to prevent replication errors, it significantly diminishes the value
of that automation.
Additionally, tables without Primary Keys are valid SQL and extremely
common in enterprise environments (e.g., audit logs, data warehousing). In
large-scale deployments, enforcing PKs on every single table isn't always
practical.
> I would suggest a way to disallow or add a warning
> message while creating the publication or adding new tables, however, the
> FOR
> ALL TABLES and FOR TABLES IN SCHEMA were mentioned. There isn't a reliable
> way
> to guarantee that a publication with UPDATE and/or DELETE option contains
> only
> tables with pk, RI FULL or RI USING INDEX. The fact that there is no rows
> in the
> pg_publication_rel for these clauses, makes validating the CREATE/ALTER
> PUBLICATION commands more difficult. (I prefer deterministic commands and
> when I
> saw an object definition saying "including objects created in the future",
> my
> first question is: what's the drawbacks and caveats?)
>
> I don't think the current behavior is lacking documentation; the REPLICA
> IDENTITY concept is explicitly in the logical replication chapter [1].
I think the goal of this proposal is not to change the underlying table
property design, but rather to seek a mechanism (like a Publication option)
to ensure this automation functions safely without external intervention.
It is simply about allowing the database to handle these valid, common
scenarios gracefully when automation is enabled.
--
Grant Zhou
Highgo Software
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-18 14:49 Euler Taveira <[email protected]>
parent: GRANT ZHOU <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Euler Taveira @ 2025-12-18 14:49 UTC (permalink / raw)
To: GRANT ZHOU <[email protected]>; +Cc: [email protected] <[email protected]>; Chao Li <[email protected]>; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
>> Each table needs to say what's its row identifier. The user created a table
>> without primary key. Well, create a primary key. There are dozens of thousands
>> of objects. Use a script.
> However, I’d like to share a user perspective regarding the "use a
> script" approach. The main value of `FOR TABLES IN SCHEMA` is
> *in-database automation*. If users still need to maintain external
> scripts to monitor and `ALTER` new tables to prevent replication
> errors, it significantly diminishes the value of that automation.
>
As I tried to explain in the previous email, the problem with FOR ALL TABLES
and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
relations; the list of relations is collected at runtime.
When I suggested "use a script" I was referring to fix the logical replication
setup regarding the lack of primary key. There is no need to have an automation
outside the database, use an event trigger. If your lazy user doesn't create
the primary key, assign REPLICA IDENTITY FULL. Something like
-- This example is far from being a complete solution for fixing the lack of
-- primary key in a logical replication scenario.
-- ALTER TABLE should be supported too
CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
obj record;
rec record;
ricnt integer := 0;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
IF obj.command_tag = 'CREATE TABLE' THEN
SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
RAISE NOTICE 'ricnt: %', ricnt;
IF ricnt = 0 THEN
EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
END IF;
END IF;
END LOOP;
END;
$$;
CREATE EVENT TRIGGER event_trigger_for_replica_identity
ON ddl_command_end
EXECUTE FUNCTION event_trigger_for_replica_identity();
CREATE TABLE event_trigger_test_1 (a int);
\d+ event_trigger_test_1
CREATE TABLE event_trigger_test_2 (a int primary key);
\d+ event_trigger_test_2
CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
\d+ event_trigger_test_3
--ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
--\d+ event_trigger_test_3
DROP EVENT TRIGGER event_trigger_for_replica_identity;
DROP FUNCTION event_trigger_for_replica_identity;
DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
8<----------------------------------------------------------------------------8<
> Additionally, tables without Primary Keys are valid SQL and extremely
> common in enterprise environments (e.g., audit logs, data warehousing).
> In large-scale deployments, enforcing PKs on every single table isn't
> always practical.
>
I'm not saying users shouldn't create tables without a primary key. I'm arguing
that this decision should take into account what adjustments need to be made to
use these tables in logical replication.
>
> I think the goal of this proposal is not to change the underlying table
> property design, but rather to seek a mechanism (like a Publication
> option) to ensure this automation functions safely without external
> intervention. It is simply about allowing the database to handle these
> valid, common scenarios gracefully when automation is enabled.
>
You didn't get it. You already have one property to handle it and you are
proposing to add a second property to handle it.
I think you are pursuing the wrong solution. IMO we need a solution to enforce
that the logical replication contract is valid. If you create or modify a table
that is part of a publication, there is no validation that that table complies
with the publication properties (update and delete properties should require an
appropriate replica identity). We should close the gaps in both publication and
table.
--
Euler Taveira
EDB https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-19 08:08 Chao Li <[email protected]>
parent: Euler Taveira <[email protected]>
0 siblings, 2 replies; 26+ messages in thread
From: Chao Li @ 2025-12-19 08:08 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; +Cc: GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
> On Dec 18, 2025, at 22:49, Euler Taveira <[email protected]> wrote:
>
> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
>> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
>>> Each table needs to say what's its row identifier. The user created a table
>>> without primary key. Well, create a primary key. There are dozens of thousands
>>> of objects. Use a script.
>> However, I’d like to share a user perspective regarding the "use a
>> script" approach. The main value of `FOR TABLES IN SCHEMA` is
>> *in-database automation*. If users still need to maintain external
>> scripts to monitor and `ALTER` new tables to prevent replication
>> errors, it significantly diminishes the value of that automation.
>>
>
> As I tried to explain in the previous email, the problem with FOR ALL TABLES
> and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
> relations; the list of relations is collected at runtime.
>
> When I suggested "use a script" I was referring to fix the logical replication
> setup regarding the lack of primary key. There is no need to have an automation
> outside the database, use an event trigger. If your lazy user doesn't create
> the primary key, assign REPLICA IDENTITY FULL. Something like
>
> -- This example is far from being a complete solution for fixing the lack of
> -- primary key in a logical replication scenario.
> -- ALTER TABLE should be supported too
> CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
> RETURNS event_trigger LANGUAGE plpgsql AS $$
> DECLARE
> obj record;
> rec record;
> ricnt integer := 0;
> BEGIN
> FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
> LOOP
> IF obj.command_tag = 'CREATE TABLE' THEN
> SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
> RAISE NOTICE 'ricnt: %', ricnt;
> IF ricnt = 0 THEN
> EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
> END IF;
> END IF;
> END LOOP;
> END;
> $$;
>
> CREATE EVENT TRIGGER event_trigger_for_replica_identity
> ON ddl_command_end
> EXECUTE FUNCTION event_trigger_for_replica_identity();
>
> CREATE TABLE event_trigger_test_1 (a int);
> \d+ event_trigger_test_1
> CREATE TABLE event_trigger_test_2 (a int primary key);
> \d+ event_trigger_test_2
> CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
> \d+ event_trigger_test_3
> --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
> --\d+ event_trigger_test_3
>
> DROP EVENT TRIGGER event_trigger_for_replica_identity;
> DROP FUNCTION event_trigger_for_replica_identity;
> DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
>
> 8<----------------------------------------------------------------------------8<
>
>> Additionally, tables without Primary Keys are valid SQL and extremely
>> common in enterprise environments (e.g., audit logs, data warehousing).
>> In large-scale deployments, enforcing PKs on every single table isn't
>> always practical.
>>
>
> I'm not saying users shouldn't create tables without a primary key. I'm arguing
> that this decision should take into account what adjustments need to be made to
> use these tables in logical replication.
>
>>
>> I think the goal of this proposal is not to change the underlying table
>> property design, but rather to seek a mechanism (like a Publication
>> option) to ensure this automation functions safely without external
>> intervention. It is simply about allowing the database to handle these
>> valid, common scenarios gracefully when automation is enabled.
>>
>
> You didn't get it. You already have one property to handle it and you are
> proposing to add a second property to handle it.
>
> I think you are pursuing the wrong solution. IMO we need a solution to enforce
> that the logical replication contract is valid. If you create or modify a table
> that is part of a publication, there is no validation that that table complies
> with the publication properties (update and delete properties should require an
> appropriate replica identity). We should close the gaps in both publication and
> table.
>
If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG we generally prefer explicit over implicit behavior, and predictability over magic.
Based on the discussion so far, I think we share the following design goals:
1) Keep replica identity as a table property.
2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
3) Avoid global or implicit behavior changes.
4) Preserve explicit opt-in for higher WAL cost.
5) Keep the logical replication contract explicit and enforceable.
I’ve been thinking about whether adding a new replica identity could meet these goals.
Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our focus on the design, not argue the name for now.)
With this approach:
1) Replica identity remains a table property.
2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
3) No new GUCs are required.
4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a primary key later does not silently break UPDATE/DELETE replication.
5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE replication even without a PK, at the cost of higher WAL volume.
This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already has conditional behavior (PK fallback
to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
After that, we could consider a database-level default_replica_identity setting, applied at table creation time, for environments that want this behavior consistently. But that would only make sense if we first agree on the table-level mechanism.
I’m interested in whether this direction aligns better with the goals above.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-22 11:48 Amit Kapila <[email protected]>
parent: Chao Li <[email protected]>
1 sibling, 1 reply; 26+ messages in thread
From: Amit Kapila @ 2025-12-22 11:48 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Euler Taveira <[email protected]>; GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
On Fri, Dec 19, 2025 at 1:39 PM Chao Li <[email protected]> wrote:
>
> > On Dec 18, 2025, at 22:49, Euler Taveira <[email protected]> wrote:
> >
> > On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
> >> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
> >>> Each table needs to say what's its row identifier. The user created a table
> >>> without primary key. Well, create a primary key. There are dozens of thousands
> >>> of objects. Use a script.
> >> However, I’d like to share a user perspective regarding the "use a
> >> script" approach. The main value of `FOR TABLES IN SCHEMA` is
> >> *in-database automation*. If users still need to maintain external
> >> scripts to monitor and `ALTER` new tables to prevent replication
> >> errors, it significantly diminishes the value of that automation.
> >>
> >
> > As I tried to explain in the previous email, the problem with FOR ALL TABLES
> > and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
> > relations; the list of relations is collected at runtime.
> >
> > When I suggested "use a script" I was referring to fix the logical replication
> > setup regarding the lack of primary key. There is no need to have an automation
> > outside the database, use an event trigger. If your lazy user doesn't create
> > the primary key, assign REPLICA IDENTITY FULL. Something like
> >
> > -- This example is far from being a complete solution for fixing the lack of
> > -- primary key in a logical replication scenario.
> > -- ALTER TABLE should be supported too
> > CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
> > RETURNS event_trigger LANGUAGE plpgsql AS $$
> > DECLARE
> > obj record;
> > rec record;
> > ricnt integer := 0;
> > BEGIN
> > FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
> > LOOP
> > IF obj.command_tag = 'CREATE TABLE' THEN
> > SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
> > RAISE NOTICE 'ricnt: %', ricnt;
> > IF ricnt = 0 THEN
> > EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
> > END IF;
> > END IF;
> > END LOOP;
> > END;
> > $$;
> >
> > CREATE EVENT TRIGGER event_trigger_for_replica_identity
> > ON ddl_command_end
> > EXECUTE FUNCTION event_trigger_for_replica_identity();
> >
> > CREATE TABLE event_trigger_test_1 (a int);
> > \d+ event_trigger_test_1
> > CREATE TABLE event_trigger_test_2 (a int primary key);
> > \d+ event_trigger_test_2
> > CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
> > \d+ event_trigger_test_3
> > --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
> > --\d+ event_trigger_test_3
> >
> > DROP EVENT TRIGGER event_trigger_for_replica_identity;
> > DROP FUNCTION event_trigger_for_replica_identity;
> > DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
> >
> > 8<----------------------------------------------------------------------------8<
> >
> >> Additionally, tables without Primary Keys are valid SQL and extremely
> >> common in enterprise environments (e.g., audit logs, data warehousing).
> >> In large-scale deployments, enforcing PKs on every single table isn't
> >> always practical.
> >>
> >
> > I'm not saying users shouldn't create tables without a primary key. I'm arguing
> > that this decision should take into account what adjustments need to be made to
> > use these tables in logical replication.
> >
> >>
> >> I think the goal of this proposal is not to change the underlying table
> >> property design, but rather to seek a mechanism (like a Publication
> >> option) to ensure this automation functions safely without external
> >> intervention. It is simply about allowing the database to handle these
> >> valid, common scenarios gracefully when automation is enabled.
> >>
> >
> > You didn't get it. You already have one property to handle it and you are
> > proposing to add a second property to handle it.
> >
> > I think you are pursuing the wrong solution. IMO we need a solution to enforce
> > that the logical replication contract is valid. If you create or modify a table
> > that is part of a publication, there is no validation that that table complies
> > with the publication properties (update and delete properties should require an
> > appropriate replica identity). We should close the gaps in both publication and
> > table.
> >
If we want, we can ensure that any table added to that specific
publication (that has an option replica_identy='full') would
automatically override the default to FULL, if PK is not available.
This information can be cached to avoid overhead.
>
> If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG we generally prefer explicit over implicit behavior, and predictability over magic.
>
You haven't told why we can't consider a custom event trigger as
suggested by Euler for customers who are not willing to change the RI
default explicitly for each table. I think it is worth considering
providing a custom solution outside core-postgres for your customers
for this specific case.
> Based on the discussion so far, I think we share the following design goals:
>
> 1) Keep replica identity as a table property.
> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
> 3) Avoid global or implicit behavior changes.
> 4) Preserve explicit opt-in for higher WAL cost.
> 5) Keep the logical replication contract explicit and enforceable.
>
> I’ve been thinking about whether adding a new replica identity could meet these goals.
>
> Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
>
> What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our focus on the design, not argue the name for now.)
>
> With this approach:
>
> 1) Replica identity remains a table property.
> 2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
> 3) No new GUCs are required.
> 4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a primary key later does not silently break UPDATE/DELETE replication.
>
> 5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE replication even without a PK, at the cost of higher WAL volume.
>
> This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already has conditional behavior (PK fallback
> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
>
> After that, we could consider a database-level default_replica_identity setting, applied at table creation time, for environments that want this behavior consistently. But that would only make sense if we first agree on the table-level mechanism.
>
I don't much like the database-level option as it expects a new
default to be introduced. I think the internal working will almost be
same as the option at publication-level.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2025-12-30 08:07 Chao Li <[email protected]>
parent: Amit Kapila <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Chao Li @ 2025-12-30 08:07 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: Euler Taveira <[email protected]>; GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
> On Dec 22, 2025, at 19:48, Amit Kapila <[email protected]> wrote:
>
> On Fri, Dec 19, 2025 at 1:39 PM Chao Li <[email protected]> wrote:
>>
>>> On Dec 18, 2025, at 22:49, Euler Taveira <[email protected]> wrote:
>>>
>>> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
>>>> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
>>>>> Each table needs to say what's its row identifier. The user created a table
>>>>> without primary key. Well, create a primary key. There are dozens of thousands
>>>>> of objects. Use a script.
>>>> However, I’d like to share a user perspective regarding the "use a
>>>> script" approach. The main value of `FOR TABLES IN SCHEMA` is
>>>> *in-database automation*. If users still need to maintain external
>>>> scripts to monitor and `ALTER` new tables to prevent replication
>>>> errors, it significantly diminishes the value of that automation.
>>>>
>>>
>>> As I tried to explain in the previous email, the problem with FOR ALL TABLES
>>> and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
>>> relations; the list of relations is collected at runtime.
>>>
>>> When I suggested "use a script" I was referring to fix the logical replication
>>> setup regarding the lack of primary key. There is no need to have an automation
>>> outside the database, use an event trigger. If your lazy user doesn't create
>>> the primary key, assign REPLICA IDENTITY FULL. Something like
>>>
>>> -- This example is far from being a complete solution for fixing the lack of
>>> -- primary key in a logical replication scenario.
>>> -- ALTER TABLE should be supported too
>>> CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
>>> RETURNS event_trigger LANGUAGE plpgsql AS $$
>>> DECLARE
>>> obj record;
>>> rec record;
>>> ricnt integer := 0;
>>> BEGIN
>>> FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
>>> LOOP
>>> IF obj.command_tag = 'CREATE TABLE' THEN
>>> SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
>>> RAISE NOTICE 'ricnt: %', ricnt;
>>> IF ricnt = 0 THEN
>>> EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
>>> END IF;
>>> END IF;
>>> END LOOP;
>>> END;
>>> $$;
>>>
>>> CREATE EVENT TRIGGER event_trigger_for_replica_identity
>>> ON ddl_command_end
>>> EXECUTE FUNCTION event_trigger_for_replica_identity();
>>>
>>> CREATE TABLE event_trigger_test_1 (a int);
>>> \d+ event_trigger_test_1
>>> CREATE TABLE event_trigger_test_2 (a int primary key);
>>> \d+ event_trigger_test_2
>>> CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
>>> \d+ event_trigger_test_3
>>> --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
>>> --\d+ event_trigger_test_3
>>>
>>> DROP EVENT TRIGGER event_trigger_for_replica_identity;
>>> DROP FUNCTION event_trigger_for_replica_identity;
>>> DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
>>>
>>> 8<----------------------------------------------------------------------------8<
>>>
>>>> Additionally, tables without Primary Keys are valid SQL and extremely
>>>> common in enterprise environments (e.g., audit logs, data warehousing).
>>>> In large-scale deployments, enforcing PKs on every single table isn't
>>>> always practical.
>>>>
>>>
>>> I'm not saying users shouldn't create tables without a primary key. I'm arguing
>>> that this decision should take into account what adjustments need to be made to
>>> use these tables in logical replication.
>>>
>>>>
>>>> I think the goal of this proposal is not to change the underlying table
>>>> property design, but rather to seek a mechanism (like a Publication
>>>> option) to ensure this automation functions safely without external
>>>> intervention. It is simply about allowing the database to handle these
>>>> valid, common scenarios gracefully when automation is enabled.
>>>>
>>>
>>> You didn't get it. You already have one property to handle it and you are
>>> proposing to add a second property to handle it.
>>>
>>> I think you are pursuing the wrong solution. IMO we need a solution to enforce
>>> that the logical replication contract is valid. If you create or modify a table
>>> that is part of a publication, there is no validation that that table complies
>>> with the publication properties (update and delete properties should require an
>>> appropriate replica identity). We should close the gaps in both publication and
>>> table.
>>>
>
> If we want, we can ensure that any table added to that specific
> publication (that has an option replica_identy='full') would
> automatically override the default to FULL, if PK is not available.
> This information can be cached to avoid overhead.
>
>>
>> If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG we generally prefer explicit over implicit behavior, and predictability over magic.
>>
>
> You haven't told why we can't consider a custom event trigger as
> suggested by Euler for customers who are not willing to change the RI
> default explicitly for each table. I think it is worth considering
> providing a custom solution outside core-postgres for your customers
> for this specific case.
Thanks for raising this. Let me clarify why we don’t consider a custom event trigger a satisfactory solution in practice, even though it is technically possible.
I discussed this with our field teams, and some customers have indeed experimented with event-trigger-based solutions before. However, they generally don’t prefer them for this use case.
First, the required logic is non-trivial and fragile. The trigger would need to track table creation, primary key creation and removal, and distinguish between cases where REPLICA IDENTITY FULL was set implicitly versus explicitly by the user. Handling all these cases correctly makes the solution feel like a workaround rather than a robust enforcement mechanism.
Second, event triggers introduce operational risk. They need to be installed, monitored, and maintained separately from the core system. If a trigger is accidentally dropped, disabled, or modified, the behavior silently changes, which is particularly risky for replication semantics.
Third, customers place much higher trust in core PostgreSQL behavior than in custom scripts layered on top. Issues caused by core behavior are seen as something that can be understood, worked around, or fixed by upgrading, whereas failures caused by custom triggers are harder to diagnose and are often attributed to the overall solution quality.
For these reasons, while event triggers can work as a stopgap, our customers strongly prefer a solution where the replication contract is enforced by core PostgreSQL rather than external mechanisms.
>
>> Based on the discussion so far, I think we share the following design goals:
>>
>> 1) Keep replica identity as a table property.
>> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
>> 3) Avoid global or implicit behavior changes.
>> 4) Preserve explicit opt-in for higher WAL cost.
>> 5) Keep the logical replication contract explicit and enforceable.
>>
>> I’ve been thinking about whether adding a new replica identity could meet these goals.
>>
>> Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
>>
>> What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our focus on the design, not argue the name for now.)
>>
>> With this approach:
>>
>> 1) Replica identity remains a table property.
>> 2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
>> 3) No new GUCs are required.
>> 4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a primary key later does not silently break UPDATE/DELETE replication.
>>
>> 5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE replication even without a PK, at the cost of higher WAL volume.
>>
>> This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already has conditional behavior (PK fallback
>> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
>>
>> After that, we could consider a database-level default_replica_identity setting, applied at table creation time, for environments that want this behavior consistently. But that would only make sense if we first agree on the table-level mechanism.
>>
>
> I don't much like the database-level option as it expects a new
> default to be introduced. I think the internal working will almost be
> same as the option at publication-level.
That’s fair. I agree that a database-level option wouldn’t be fundamentally different from a publication-level solution and would likely share most of the same internal mechanics.
At this point nothing is decided yet; we’re still exploring different approaches and trying to understand the trade-offs.
I have a question to better understand how a publication-level approach would behave in edge cases.
Since replica identity is defined on tables and a table can belong to multiple publications, how should UPDATE/DELETE be handled if the same table is added to two publications with different expectations?
For example, suppose a table without a PK is added to:
- pub_a, which does not require FULL (or effectively falls back to NONE)
- pub_b, which requires FULL for UPDATE/DELETE
In this case, should UPDATE/DELETE on the table be allowed at all, and if so, based on which publication’s semantics? What do you think?
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2026-04-08 10:22 shveta malik <[email protected]>
parent: Chao Li <[email protected]>
1 sibling, 1 reply; 26+ messages in thread
From: shveta malik @ 2026-04-08 10:22 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Euler Taveira <[email protected]>; GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>; shveta malik <[email protected]>
On Fri, Dec 19, 2025 at 1:39 PM Chao Li <[email protected]> wrote:
>
>
>
> > On Dec 18, 2025, at 22:49, Euler Taveira <[email protected]> wrote:
> >
> > On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
> >> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
> >>> Each table needs to say what's its row identifier. The user created a table
> >>> without primary key. Well, create a primary key. There are dozens of thousands
> >>> of objects. Use a script.
> >> However, I’d like to share a user perspective regarding the "use a
> >> script" approach. The main value of `FOR TABLES IN SCHEMA` is
> >> *in-database automation*. If users still need to maintain external
> >> scripts to monitor and `ALTER` new tables to prevent replication
> >> errors, it significantly diminishes the value of that automation.
> >>
> >
> > As I tried to explain in the previous email, the problem with FOR ALL TABLES
> > and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
> > relations; the list of relations is collected at runtime.
> >
> > When I suggested "use a script" I was referring to fix the logical replication
> > setup regarding the lack of primary key. There is no need to have an automation
> > outside the database, use an event trigger. If your lazy user doesn't create
> > the primary key, assign REPLICA IDENTITY FULL. Something like
> >
> > -- This example is far from being a complete solution for fixing the lack of
> > -- primary key in a logical replication scenario.
> > -- ALTER TABLE should be supported too
> > CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
> > RETURNS event_trigger LANGUAGE plpgsql AS $$
> > DECLARE
> > obj record;
> > rec record;
> > ricnt integer := 0;
> > BEGIN
> > FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
> > LOOP
> > IF obj.command_tag = 'CREATE TABLE' THEN
> > SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
> > RAISE NOTICE 'ricnt: %', ricnt;
> > IF ricnt = 0 THEN
> > EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
> > END IF;
> > END IF;
> > END LOOP;
> > END;
> > $$;
> >
> > CREATE EVENT TRIGGER event_trigger_for_replica_identity
> > ON ddl_command_end
> > EXECUTE FUNCTION event_trigger_for_replica_identity();
> >
> > CREATE TABLE event_trigger_test_1 (a int);
> > \d+ event_trigger_test_1
> > CREATE TABLE event_trigger_test_2 (a int primary key);
> > \d+ event_trigger_test_2
> > CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
> > \d+ event_trigger_test_3
> > --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
> > --\d+ event_trigger_test_3
> >
> > DROP EVENT TRIGGER event_trigger_for_replica_identity;
> > DROP FUNCTION event_trigger_for_replica_identity;
> > DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
> >
> > 8<----------------------------------------------------------------------------8<
> >
> >> Additionally, tables without Primary Keys are valid SQL and extremely
> >> common in enterprise environments (e.g., audit logs, data warehousing).
> >> In large-scale deployments, enforcing PKs on every single table isn't
> >> always practical.
> >>
> >
> > I'm not saying users shouldn't create tables without a primary key. I'm arguing
> > that this decision should take into account what adjustments need to be made to
> > use these tables in logical replication.
> >
> >>
> >> I think the goal of this proposal is not to change the underlying table
> >> property design, but rather to seek a mechanism (like a Publication
> >> option) to ensure this automation functions safely without external
> >> intervention. It is simply about allowing the database to handle these
> >> valid, common scenarios gracefully when automation is enabled.
> >>
> >
> > You didn't get it. You already have one property to handle it and you are
> > proposing to add a second property to handle it.
> >
> > I think you are pursuing the wrong solution. IMO we need a solution to enforce
> > that the logical replication contract is valid. If you create or modify a table
> > that is part of a publication, there is no validation that that table complies
> > with the publication properties (update and delete properties should require an
> > appropriate replica identity). We should close the gaps in both publication and
> > table.
> >
>
> If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG we generally prefer explicit over implicit behavior, and predictability over magic.
>
> Based on the discussion so far, I think we share the following design goals:
>
> 1) Keep replica identity as a table property.
> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
> 3) Avoid global or implicit behavior changes.
> 4) Preserve explicit opt-in for higher WAL cost.
> 5) Keep the logical replication contract explicit and enforceable.
>
> I’ve been thinking about whether adding a new replica identity could meet these goals.
>
> Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
>
> What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our focus on the design, not argue the name for now.)
>
> With this approach:
>
> 1) Replica identity remains a table property.
> 2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
> 3) No new GUCs are required.
> 4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a primary key later does not silently break UPDATE/DELETE replication.
> 5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE replication even without a PK, at the cost of higher WAL volume.
>
> This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already has conditional behavior (PK fallback
> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
Even though I like the idea of keeping replica identity as a table
property, this raises a question about the need for a new FORCE
option. With this approach, the user must explicitly opt REPLICA
IDENTITY FORCE for tables without a primary key (pt 4 in your design).
But if the user is already expected to take an explicit action, they
could simply set REPLICA IDENTITY FULL themselves at table creation
time or via ALTER TABLE. From what I understood, the original
requirement was to ensure that tables without a primary key do not
silently fail replication, and that this is handled automatically
without manual intervention. In contrast, the FORCE approach still
requires users to explicitly configure replica identity for each
table, which seems to reintroduce the same operational burden.
For example, instead of:
ALTER TABLE t1 REPLICA IDENTITY FORCE;
the user could simply do:
ALTER TABLE t1 REPLICA IDENTITY FULL
By the time, the user is explicitly configuring replica identity, they
would already need to understand the replication semantics, so it's
not clear what additional benefit FORCE provides over FULL. Am I
missing something here?
thanks
Shveta
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2026-04-08 11:43 shveta malik <[email protected]>
parent: Chao Li <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: shveta malik @ 2026-04-08 11:43 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Amit Kapila <[email protected]>; Euler Taveira <[email protected]>; GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>; shveta malik <[email protected]>
On Wed, Feb 4, 2026 at 12:51 PM Chao Li <[email protected]> wrote:
>
>
>
> > On Dec 30, 2025, at 16:07, Chao Li <[email protected]> wrote:
> >
> >
> >
> >> On Dec 22, 2025, at 19:48, Amit Kapila <[email protected]> wrote:
> >>
> >> On Fri, Dec 19, 2025 at 1:39 PM Chao Li <[email protected]> wrote:
> >>>
> >>>> On Dec 18, 2025, at 22:49, Euler Taveira <[email protected]> wrote:
> >>>>
> >>>> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
> >>>>> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
> >>>>>> Each table needs to say what's its row identifier. The user created a table
> >>>>>> without primary key. Well, create a primary key. There are dozens of thousands
> >>>>>> of objects. Use a script.
> >>>>> However, I’d like to share a user perspective regarding the "use a
> >>>>> script" approach. The main value of `FOR TABLES IN SCHEMA` is
> >>>>> *in-database automation*. If users still need to maintain external
> >>>>> scripts to monitor and `ALTER` new tables to prevent replication
> >>>>> errors, it significantly diminishes the value of that automation.
> >>>>>
> >>>>
> >>>> As I tried to explain in the previous email, the problem with FOR ALL TABLES
> >>>> and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
> >>>> relations; the list of relations is collected at runtime.
> >>>>
> >>>> When I suggested "use a script" I was referring to fix the logical replication
> >>>> setup regarding the lack of primary key. There is no need to have an automation
> >>>> outside the database, use an event trigger. If your lazy user doesn't create
> >>>> the primary key, assign REPLICA IDENTITY FULL. Something like
> >>>>
> >>>> -- This example is far from being a complete solution for fixing the lack of
> >>>> -- primary key in a logical replication scenario.
> >>>> -- ALTER TABLE should be supported too
> >>>> CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
> >>>> RETURNS event_trigger LANGUAGE plpgsql AS $$
> >>>> DECLARE
> >>>> obj record;
> >>>> rec record;
> >>>> ricnt integer := 0;
> >>>> BEGIN
> >>>> FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
> >>>> LOOP
> >>>> IF obj.command_tag = 'CREATE TABLE' THEN
> >>>> SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
> >>>> RAISE NOTICE 'ricnt: %', ricnt;
> >>>> IF ricnt = 0 THEN
> >>>> EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
> >>>> END IF;
> >>>> END IF;
> >>>> END LOOP;
> >>>> END;
> >>>> $$;
> >>>>
> >>>> CREATE EVENT TRIGGER event_trigger_for_replica_identity
> >>>> ON ddl_command_end
> >>>> EXECUTE FUNCTION event_trigger_for_replica_identity();
> >>>>
> >>>> CREATE TABLE event_trigger_test_1 (a int);
> >>>> \d+ event_trigger_test_1
> >>>> CREATE TABLE event_trigger_test_2 (a int primary key);
> >>>> \d+ event_trigger_test_2
> >>>> CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
> >>>> \d+ event_trigger_test_3
> >>>> --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
> >>>> --\d+ event_trigger_test_3
> >>>>
> >>>> DROP EVENT TRIGGER event_trigger_for_replica_identity;
> >>>> DROP FUNCTION event_trigger_for_replica_identity;
> >>>> DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
> >>>>
> >>>> 8<----------------------------------------------------------------------------8<
> >>>>
> >>>>> Additionally, tables without Primary Keys are valid SQL and extremely
> >>>>> common in enterprise environments (e.g., audit logs, data warehousing).
> >>>>> In large-scale deployments, enforcing PKs on every single table isn't
> >>>>> always practical.
> >>>>>
> >>>>
> >>>> I'm not saying users shouldn't create tables without a primary key. I'm arguing
> >>>> that this decision should take into account what adjustments need to be made to
> >>>> use these tables in logical replication.
> >>>>
> >>>>>
> >>>>> I think the goal of this proposal is not to change the underlying table
> >>>>> property design, but rather to seek a mechanism (like a Publication
> >>>>> option) to ensure this automation functions safely without external
> >>>>> intervention. It is simply about allowing the database to handle these
> >>>>> valid, common scenarios gracefully when automation is enabled.
> >>>>>
> >>>>
> >>>> You didn't get it. You already have one property to handle it and you are
> >>>> proposing to add a second property to handle it.
> >>>>
> >>>> I think you are pursuing the wrong solution. IMO we need a solution to enforce
> >>>> that the logical replication contract is valid. If you create or modify a table
> >>>> that is part of a publication, there is no validation that that table complies
> >>>> with the publication properties (update and delete properties should require an
> >>>> appropriate replica identity). We should close the gaps in both publication and
> >>>> table.
> >>>>
> >>
> >> If we want, we can ensure that any table added to that specific
> >> publication (that has an option replica_identy='full') would
> >> automatically override the default to FULL, if PK is not available.
> >> This information can be cached to avoid overhead.
> >>
> >>>
> >>> If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG we generally prefer explicit over implicit behavior, and predictability over magic.
> >>>
> >>
> >> You haven't told why we can't consider a custom event trigger as
> >> suggested by Euler for customers who are not willing to change the RI
> >> default explicitly for each table. I think it is worth considering
> >> providing a custom solution outside core-postgres for your customers
> >> for this specific case.
> >
> > Thanks for raising this. Let me clarify why we don’t consider a custom event trigger a satisfactory solution in practice, even though it is technically possible.
> >
> > I discussed this with our field teams, and some customers have indeed experimented with event-trigger-based solutions before. However, they generally don’t prefer them for this use case.
> >
> > First, the required logic is non-trivial and fragile. The trigger would need to track table creation, primary key creation and removal, and distinguish between cases where REPLICA IDENTITY FULL was set implicitly versus explicitly by the user. Handling all these cases correctly makes the solution feel like a workaround rather than a robust enforcement mechanism.
> >
> > Second, event triggers introduce operational risk. They need to be installed, monitored, and maintained separately from the core system. If a trigger is accidentally dropped, disabled, or modified, the behavior silently changes, which is particularly risky for replication semantics.
> >
> > Third, customers place much higher trust in core PostgreSQL behavior than in custom scripts layered on top. Issues caused by core behavior are seen as something that can be understood, worked around, or fixed by upgrading, whereas failures caused by custom triggers are harder to diagnose and are often attributed to the overall solution quality.
> >
> > For these reasons, while event triggers can work as a stopgap, our customers strongly prefer a solution where the replication contract is enforced by core PostgreSQL rather than external mechanisms.
> >
> >>
> >>> Based on the discussion so far, I think we share the following design goals:
> >>>
> >>> 1) Keep replica identity as a table property.
> >>> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
> >>> 3) Avoid global or implicit behavior changes.
> >>> 4) Preserve explicit opt-in for higher WAL cost.
> >>> 5) Keep the logical replication contract explicit and enforceable.
> >>>
> >>> I’ve been thinking about whether adding a new replica identity could meet these goals.
> >>>
> >>> Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
> >>>
> >>> What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our focus on the design, not argue the name for now.)
> >>>
> >>> With this approach:
> >>>
> >>> 1) Replica identity remains a table property.
> >>> 2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
> >>> 3) No new GUCs are required.
> >>> 4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a primary key later does not silently break UPDATE/DELETE replication.
> >>>
> >>> 5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE replication even without a PK, at the cost of higher WAL volume.
> >>>
> >>> This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already has conditional behavior (PK fallback
> >>> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
> >>>
> >>> After that, we could consider a database-level default_replica_identity setting, applied at table creation time, for environments that want this behavior consistently. But that would only make sense if we first agree on the table-level mechanism.
> >>>
> >>
> >> I don't much like the database-level option as it expects a new
> >> default to be introduced. I think the internal working will almost be
> >> same as the option at publication-level.
> >
> > That’s fair. I agree that a database-level option wouldn’t be fundamentally different from a publication-level solution and would likely share most of the same internal mechanics.
> >
> > At this point nothing is decided yet; we’re still exploring different approaches and trying to understand the trade-offs.
> >
> > I have a question to better understand how a publication-level approach would behave in edge cases.
> >
> > Since replica identity is defined on tables and a table can belong to multiple publications, how should UPDATE/DELETE be handled if the same table is added to two publications with different expectations?
> >
> > For example, suppose a table without a PK is added to:
> > - pub_a, which does not require FULL (or effectively falls back to NONE)
> > - pub_b, which requires FULL for UPDATE/DELETE
> >
> > In this case, should UPDATE/DELETE on the table be allowed at all, and if so, based on which publication’s semantics? What do you think?
> >
> > Best regards,
> > --
> > Chao Li (Evan)
> > HighGo Software Co., Ltd.
> > https://www.highgo.com/
>
>
> Hi Amit,
>
> Following your suggestion, I implemented a PoC that adds a new publication parameter (tentatively named fallbackfull) to make the DEFAULT → FULL fallback behavior per-publication. I’m not attached to the parameter name — if we decide to go with the publication approach, I’m happy to adjust naming based on feedback.
>
> After playing with this implementation for a couple of days, I ran into a few concerns:
>
> 1. Protocol extension required
>
> If the DEFAULT → FULL fallback is triggered, the subscriber needs to know whether the corresponding publication has fallbackfull enabled in order to decide how to apply UPDATE/DELETE. That means we’d need to extend the logical replication protocol, e.g., by adding a new field to the RELATION message to carry the fallbackfull flag.
If the DEFAULT → FULL fallback is triggered, can we send 'full' to
the subscriber? I think subscriber need not to know if FULl was set by
user or is FORCED and the protocol need not be extended.
>
> 2. Impact on decoding plugins
>
> Decoding plugins would need to understand this new flag. In my PoC, I updated pgoutput, but there may be third-party plugins that would also need changes. That feels like a compatibility risk.
If we make the change described in pt1, I think the protocol and
subscriber sides will not need to change, but pgoutput would still
need to be updated when sending the RELATION metadata. Specifically,
instead of using the table’s configured replica identity directly, it
should use the effective replica identity.
pq_sendbyte(out, rel->rd_rel->relreplident -->effect _RI);
So IIUC, third-party plugins may also need such change.
>
> 3. Potential data-integrity issues
>
> This is the most concerning part to me.
>
> Consider a table t1 with REPLICA IDENTITY DEFAULT and no primary key, included in publication p1. By design, UPDATE/DELETE on t1 are not allowed.
>
> However, a user could work around this by creating a dummy publication, adding t1 to it, and setting fallbackfull = true on that publication. This would effectively enable UPDATE/DELETE on t1.
>
> Later, if the owner of p1 decides to enable fallbackfull on p1 to replicate t1, the subscriber of p1 may already be out of sync due to the earlier updates/deletes performed via the dummy publication. At that point, subsequent UPDATE/DELETE replication may fail or behave incorrectly.
>
> From this perspective, allowing fallbackfull at the publication level seems to open the door to cross-publication interference and data divergence.
>
> Given these concerns, I’m leaning toward keeping fallbackfull as a per-table option rather than a per-publication one. Curious to hear your thoughts.
>
I see your point. One possible approach could be that when any
publication is altered to enable the fallback option, the effective
replica identity for the affected table is promoted to FULL across all
publications.
i.e. for t1:
If RI_DEFAULT + no PK + pub->fallbackfull=true;
effective_replica_identity for t1 = FULL (across all pubs)
But then this brought us to the point that if we are deciding
effective_replica_identity for a table irrespective of publication,
then why not to make it table property?
thanks
Shveta
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2026-04-14 03:47 Chao Li <[email protected]>
parent: shveta malik <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: Chao Li @ 2026-04-14 03:47 UTC (permalink / raw)
To: shveta malik <[email protected]>; +Cc: Euler Taveira <[email protected]>; GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
> On Apr 8, 2026, at 18:22, shveta malik <[email protected]> wrote:
>
> On Fri, Dec 19, 2025 at 1:39 PM Chao Li <[email protected]> wrote:
>>
>>
>>
>>> On Dec 18, 2025, at 22:49, Euler Taveira <[email protected]> wrote:
>>>
>>> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
>>>> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
>>>>> Each table needs to say what's its row identifier. The user created a table
>>>>> without primary key. Well, create a primary key. There are dozens of thousands
>>>>> of objects. Use a script.
>>>> However, I’d like to share a user perspective regarding the "use a
>>>> script" approach. The main value of `FOR TABLES IN SCHEMA` is
>>>> *in-database automation*. If users still need to maintain external
>>>> scripts to monitor and `ALTER` new tables to prevent replication
>>>> errors, it significantly diminishes the value of that automation.
>>>>
>>>
>>> As I tried to explain in the previous email, the problem with FOR ALL TABLES
>>> and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
>>> relations; the list of relations is collected at runtime.
>>>
>>> When I suggested "use a script" I was referring to fix the logical replication
>>> setup regarding the lack of primary key. There is no need to have an automation
>>> outside the database, use an event trigger. If your lazy user doesn't create
>>> the primary key, assign REPLICA IDENTITY FULL. Something like
>>>
>>> -- This example is far from being a complete solution for fixing the lack of
>>> -- primary key in a logical replication scenario.
>>> -- ALTER TABLE should be supported too
>>> CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
>>> RETURNS event_trigger LANGUAGE plpgsql AS $$
>>> DECLARE
>>> obj record;
>>> rec record;
>>> ricnt integer := 0;
>>> BEGIN
>>> FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
>>> LOOP
>>> IF obj.command_tag = 'CREATE TABLE' THEN
>>> SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
>>> RAISE NOTICE 'ricnt: %', ricnt;
>>> IF ricnt = 0 THEN
>>> EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
>>> END IF;
>>> END IF;
>>> END LOOP;
>>> END;
>>> $$;
>>>
>>> CREATE EVENT TRIGGER event_trigger_for_replica_identity
>>> ON ddl_command_end
>>> EXECUTE FUNCTION event_trigger_for_replica_identity();
>>>
>>> CREATE TABLE event_trigger_test_1 (a int);
>>> \d+ event_trigger_test_1
>>> CREATE TABLE event_trigger_test_2 (a int primary key);
>>> \d+ event_trigger_test_2
>>> CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
>>> \d+ event_trigger_test_3
>>> --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
>>> --\d+ event_trigger_test_3
>>>
>>> DROP EVENT TRIGGER event_trigger_for_replica_identity;
>>> DROP FUNCTION event_trigger_for_replica_identity;
>>> DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
>>>
>>> 8<----------------------------------------------------------------------------8<
>>>
>>>> Additionally, tables without Primary Keys are valid SQL and extremely
>>>> common in enterprise environments (e.g., audit logs, data warehousing).
>>>> In large-scale deployments, enforcing PKs on every single table isn't
>>>> always practical.
>>>>
>>>
>>> I'm not saying users shouldn't create tables without a primary key. I'm arguing
>>> that this decision should take into account what adjustments need to be made to
>>> use these tables in logical replication.
>>>
>>>>
>>>> I think the goal of this proposal is not to change the underlying table
>>>> property design, but rather to seek a mechanism (like a Publication
>>>> option) to ensure this automation functions safely without external
>>>> intervention. It is simply about allowing the database to handle these
>>>> valid, common scenarios gracefully when automation is enabled.
>>>>
>>>
>>> You didn't get it. You already have one property to handle it and you are
>>> proposing to add a second property to handle it.
>>>
>>> I think you are pursuing the wrong solution. IMO we need a solution to enforce
>>> that the logical replication contract is valid. If you create or modify a table
>>> that is part of a publication, there is no validation that that table complies
>>> with the publication properties (update and delete properties should require an
>>> appropriate replica identity). We should close the gaps in both publication and
>>> table.
>>>
>>
>> If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG we generally prefer explicit over implicit behavior, and predictability over magic.
>>
>> Based on the discussion so far, I think we share the following design goals:
>>
>> 1) Keep replica identity as a table property.
>> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
>> 3) Avoid global or implicit behavior changes.
>> 4) Preserve explicit opt-in for higher WAL cost.
>> 5) Keep the logical replication contract explicit and enforceable.
>>
>> I’ve been thinking about whether adding a new replica identity could meet these goals.
>>
>> Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
>>
>> What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our focus on the design, not argue the name for now.)
>>
>> With this approach:
>>
>> 1) Replica identity remains a table property.
>> 2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
>> 3) No new GUCs are required.
>> 4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a primary key later does not silently break UPDATE/DELETE replication.
>> 5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE replication even without a PK, at the cost of higher WAL volume.
>>
>> This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already has conditional behavior (PK fallback
>> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
>
>
Hi Shveta,
Thanks for your feedback.
> Even though I like the idea of keeping replica identity as a table
> property, this raises a question about the need for a new FORCE
> option. With this approach, the user must explicitly opt REPLICA
> IDENTITY FORCE for tables without a primary key (pt 4 in your design).
> But if the user is already expected to take an explicit action, they
> could simply set REPLICA IDENTITY FULL themselves at table creation
> time or via ALTER TABLE.
This is the tricky part. The need for this feature comes from some complicated operational models in practice. Table creation is often handled by an application team, which focuses on data logic and usually does not care about replication details. Data replication, on the other hand, is maintained by a different team, which does not care what data the tables contain, but only needs to ensure that all data is replicated correctly. In that kind of setup, we cannot expect REPLICA IDENTITY to be configured properly at table creation time.
Such operational models also imply that the database operations team is usually the central database team, with superuser privileges, while an application typically has privileges only on the tables it owns. The central team can set configurations at the database or cluster level. In most cases, what they want is to replicate all tables by default, except for a small number of tables that are explicitly excluded.
Actually, this feature request is coming from the replication team. Today, they have to manually identify newly created tables, usually by running some script, and then configure REPLICA IDENTITY for them. That takes effort and is error-prone. So what they want is a DEFAULT-to-FULL fallback when a table has no primary key.
That’s why my initial proposal was to add a GUC to control the fallback behavior, which would be convenient for the central database team.
> From what I understood, the original
> requirement was to ensure that tables without a primary key do not
> silently fail replication, and that this is handled automatically
> without manual intervention. In contrast, the FORCE approach still
> requires users to explicitly configure replica identity for each
> table, which seems to reintroduce the same operational burden.
>
> For example, instead of:
> ALTER TABLE t1 REPLICA IDENTITY FORCE;
> the user could simply do:
> ALTER TABLE t1 REPLICA IDENTITY FULL
>
> By the time, the user is explicitly configuring replica identity, they
> would already need to understand the replication semantics, so it's
> not clear what additional benefit FORCE provides over FULL. Am I
> missing something here?
>
> thanks
> Shveta
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2026-04-14 03:57 Chao Li <[email protected]>
parent: shveta malik <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Chao Li @ 2026-04-14 03:57 UTC (permalink / raw)
To: shveta malik <[email protected]>; +Cc: Amit Kapila <[email protected]>; Euler Taveira <[email protected]>; GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
> On Apr 8, 2026, at 19:43, shveta malik <[email protected]> wrote:
>
> On Wed, Feb 4, 2026 at 12:51 PM Chao Li <[email protected]> wrote:
>>
>>
>>
>>> On Dec 30, 2025, at 16:07, Chao Li <[email protected]> wrote:
>>>
>>>
>>>
>>>> On Dec 22, 2025, at 19:48, Amit Kapila <[email protected]> wrote:
>>>>
>>>> On Fri, Dec 19, 2025 at 1:39 PM Chao Li <[email protected]> wrote:
>>>>>
>>>>>> On Dec 18, 2025, at 22:49, Euler Taveira <[email protected]> wrote:
>>>>>>
>>>>>> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
>>>>>>> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
>>>>>>>> Each table needs to say what's its row identifier. The user created a table
>>>>>>>> without primary key. Well, create a primary key. There are dozens of thousands
>>>>>>>> of objects. Use a script.
>>>>>>> However, I’d like to share a user perspective regarding the "use a
>>>>>>> script" approach. The main value of `FOR TABLES IN SCHEMA` is
>>>>>>> *in-database automation*. If users still need to maintain external
>>>>>>> scripts to monitor and `ALTER` new tables to prevent replication
>>>>>>> errors, it significantly diminishes the value of that automation.
>>>>>>>
>>>>>>
>>>>>> As I tried to explain in the previous email, the problem with FOR ALL TABLES
>>>>>> and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
>>>>>> relations; the list of relations is collected at runtime.
>>>>>>
>>>>>> When I suggested "use a script" I was referring to fix the logical replication
>>>>>> setup regarding the lack of primary key. There is no need to have an automation
>>>>>> outside the database, use an event trigger. If your lazy user doesn't create
>>>>>> the primary key, assign REPLICA IDENTITY FULL. Something like
>>>>>>
>>>>>> -- This example is far from being a complete solution for fixing the lack of
>>>>>> -- primary key in a logical replication scenario.
>>>>>> -- ALTER TABLE should be supported too
>>>>>> CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
>>>>>> RETURNS event_trigger LANGUAGE plpgsql AS $$
>>>>>> DECLARE
>>>>>> obj record;
>>>>>> rec record;
>>>>>> ricnt integer := 0;
>>>>>> BEGIN
>>>>>> FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
>>>>>> LOOP
>>>>>> IF obj.command_tag = 'CREATE TABLE' THEN
>>>>>> SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
>>>>>> RAISE NOTICE 'ricnt: %', ricnt;
>>>>>> IF ricnt = 0 THEN
>>>>>> EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
>>>>>> END IF;
>>>>>> END IF;
>>>>>> END LOOP;
>>>>>> END;
>>>>>> $$;
>>>>>>
>>>>>> CREATE EVENT TRIGGER event_trigger_for_replica_identity
>>>>>> ON ddl_command_end
>>>>>> EXECUTE FUNCTION event_trigger_for_replica_identity();
>>>>>>
>>>>>> CREATE TABLE event_trigger_test_1 (a int);
>>>>>> \d+ event_trigger_test_1
>>>>>> CREATE TABLE event_trigger_test_2 (a int primary key);
>>>>>> \d+ event_trigger_test_2
>>>>>> CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
>>>>>> \d+ event_trigger_test_3
>>>>>> --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
>>>>>> --\d+ event_trigger_test_3
>>>>>>
>>>>>> DROP EVENT TRIGGER event_trigger_for_replica_identity;
>>>>>> DROP FUNCTION event_trigger_for_replica_identity;
>>>>>> DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
>>>>>>
>>>>>> 8<----------------------------------------------------------------------------8<
>>>>>>
>>>>>>> Additionally, tables without Primary Keys are valid SQL and extremely
>>>>>>> common in enterprise environments (e.g., audit logs, data warehousing).
>>>>>>> In large-scale deployments, enforcing PKs on every single table isn't
>>>>>>> always practical.
>>>>>>>
>>>>>>
>>>>>> I'm not saying users shouldn't create tables without a primary key. I'm arguing
>>>>>> that this decision should take into account what adjustments need to be made to
>>>>>> use these tables in logical replication.
>>>>>>
>>>>>>>
>>>>>>> I think the goal of this proposal is not to change the underlying table
>>>>>>> property design, but rather to seek a mechanism (like a Publication
>>>>>>> option) to ensure this automation functions safely without external
>>>>>>> intervention. It is simply about allowing the database to handle these
>>>>>>> valid, common scenarios gracefully when automation is enabled.
>>>>>>>
>>>>>>
>>>>>> You didn't get it. You already have one property to handle it and you are
>>>>>> proposing to add a second property to handle it.
>>>>>>
>>>>>> I think you are pursuing the wrong solution. IMO we need a solution to enforce
>>>>>> that the logical replication contract is valid. If you create or modify a table
>>>>>> that is part of a publication, there is no validation that that table complies
>>>>>> with the publication properties (update and delete properties should require an
>>>>>> appropriate replica identity). We should close the gaps in both publication and
>>>>>> table.
>>>>>>
>>>>
>>>> If we want, we can ensure that any table added to that specific
>>>> publication (that has an option replica_identy='full') would
>>>> automatically override the default to FULL, if PK is not available.
>>>> This information can be cached to avoid overhead.
>>>>
>>>>>
>>>>> If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG we generally prefer explicit over implicit behavior, and predictability over magic.
>>>>>
>>>>
>>>> You haven't told why we can't consider a custom event trigger as
>>>> suggested by Euler for customers who are not willing to change the RI
>>>> default explicitly for each table. I think it is worth considering
>>>> providing a custom solution outside core-postgres for your customers
>>>> for this specific case.
>>>
>>> Thanks for raising this. Let me clarify why we don’t consider a custom event trigger a satisfactory solution in practice, even though it is technically possible.
>>>
>>> I discussed this with our field teams, and some customers have indeed experimented with event-trigger-based solutions before. However, they generally don’t prefer them for this use case.
>>>
>>> First, the required logic is non-trivial and fragile. The trigger would need to track table creation, primary key creation and removal, and distinguish between cases where REPLICA IDENTITY FULL was set implicitly versus explicitly by the user. Handling all these cases correctly makes the solution feel like a workaround rather than a robust enforcement mechanism.
>>>
>>> Second, event triggers introduce operational risk. They need to be installed, monitored, and maintained separately from the core system. If a trigger is accidentally dropped, disabled, or modified, the behavior silently changes, which is particularly risky for replication semantics.
>>>
>>> Third, customers place much higher trust in core PostgreSQL behavior than in custom scripts layered on top. Issues caused by core behavior are seen as something that can be understood, worked around, or fixed by upgrading, whereas failures caused by custom triggers are harder to diagnose and are often attributed to the overall solution quality.
>>>
>>> For these reasons, while event triggers can work as a stopgap, our customers strongly prefer a solution where the replication contract is enforced by core PostgreSQL rather than external mechanisms.
>>>
>>>>
>>>>> Based on the discussion so far, I think we share the following design goals:
>>>>>
>>>>> 1) Keep replica identity as a table property.
>>>>> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
>>>>> 3) Avoid global or implicit behavior changes.
>>>>> 4) Preserve explicit opt-in for higher WAL cost.
>>>>> 5) Keep the logical replication contract explicit and enforceable.
>>>>>
>>>>> I’ve been thinking about whether adding a new replica identity could meet these goals.
>>>>>
>>>>> Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
>>>>>
>>>>> What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our focus on the design, not argue the name for now.)
>>>>>
>>>>> With this approach:
>>>>>
>>>>> 1) Replica identity remains a table property.
>>>>> 2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
>>>>> 3) No new GUCs are required.
>>>>> 4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a primary key later does not silently break UPDATE/DELETE replication.
>>>>>
>>>>> 5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE replication even without a PK, at the cost of higher WAL volume.
>>>>>
>>>>> This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already has conditional behavior (PK fallback
>>>>> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
>>>>>
>>>>> After that, we could consider a database-level default_replica_identity setting, applied at table creation time, for environments that want this behavior consistently. But that would only make sense if we first agree on the table-level mechanism.
>>>>>
>>>>
>>>> I don't much like the database-level option as it expects a new
>>>> default to be introduced. I think the internal working will almost be
>>>> same as the option at publication-level.
>>>
>>> That’s fair. I agree that a database-level option wouldn’t be fundamentally different from a publication-level solution and would likely share most of the same internal mechanics.
>>>
>>> At this point nothing is decided yet; we’re still exploring different approaches and trying to understand the trade-offs.
>>>
>>> I have a question to better understand how a publication-level approach would behave in edge cases.
>>>
>>> Since replica identity is defined on tables and a table can belong to multiple publications, how should UPDATE/DELETE be handled if the same table is added to two publications with different expectations?
>>>
>>> For example, suppose a table without a PK is added to:
>>> - pub_a, which does not require FULL (or effectively falls back to NONE)
>>> - pub_b, which requires FULL for UPDATE/DELETE
>>>
>>> In this case, should UPDATE/DELETE on the table be allowed at all, and if so, based on which publication’s semantics? What do you think?
>>>
>>> Best regards,
>>> --
>>> Chao Li (Evan)
>>> HighGo Software Co., Ltd.
>>> https://www.highgo.com/
>>
>>
>> Hi Amit,
>>
>> Following your suggestion, I implemented a PoC that adds a new publication parameter (tentatively named fallbackfull) to make the DEFAULT → FULL fallback behavior per-publication. I’m not attached to the parameter name — if we decide to go with the publication approach, I’m happy to adjust naming based on feedback.
>>
>> After playing with this implementation for a couple of days, I ran into a few concerns:
>>
>> 1. Protocol extension required
>>
>> If the DEFAULT → FULL fallback is triggered, the subscriber needs to know whether the corresponding publication has fallbackfull enabled in order to decide how to apply UPDATE/DELETE. That means we’d need to extend the logical replication protocol, e.g., by adding a new field to the RELATION message to carry the fallbackfull flag.
>
> If the DEFAULT → FULL fallback is triggered, can we send 'full' to
> the subscriber? I think subscriber need not to know if FULl was set by
> user or is FORCED and the protocol need not be extended.
>
>>
>> 2. Impact on decoding plugins
>>
>> Decoding plugins would need to understand this new flag. In my PoC, I updated pgoutput, but there may be third-party plugins that would also need changes. That feels like a compatibility risk.
>
> If we make the change described in pt1, I think the protocol and
> subscriber sides will not need to change, but pgoutput would still
> need to be updated when sending the RELATION metadata. Specifically,
> instead of using the table’s configured replica identity directly, it
> should use the effective replica identity.
>
> pq_sendbyte(out, rel->rd_rel->relreplident -->effect _RI);
>
> So IIUC, third-party plugins may also need such change.
>
>>
>> 3. Potential data-integrity issues
>>
>> This is the most concerning part to me.
>>
>> Consider a table t1 with REPLICA IDENTITY DEFAULT and no primary key, included in publication p1. By design, UPDATE/DELETE on t1 are not allowed.
>>
>> However, a user could work around this by creating a dummy publication, adding t1 to it, and setting fallbackfull = true on that publication. This would effectively enable UPDATE/DELETE on t1.
>>
>> Later, if the owner of p1 decides to enable fallbackfull on p1 to replicate t1, the subscriber of p1 may already be out of sync due to the earlier updates/deletes performed via the dummy publication. At that point, subsequent UPDATE/DELETE replication may fail or behave incorrectly.
>>
>> From this perspective, allowing fallbackfull at the publication level seems to open the door to cross-publication interference and data divergence.
>>
>> Given these concerns, I’m leaning toward keeping fallbackfull as a per-table option rather than a per-publication one. Curious to hear your thoughts.
>>
>
> I see your point. One possible approach could be that when any
> publication is altered to enable the fallback option, the effective
> replica identity for the affected table is promoted to FULL across all
> publications.
>
> i.e. for t1:
> If RI_DEFAULT + no PK + pub->fallbackfull=true;
> effective_replica_identity for t1 = FULL (across all pubs)
>
> But then this brought us to the point that if we are deciding
> effective_replica_identity for a table irrespective of publication,
> then why not to make it table property?
>
This is true, but going back to the motivation for this feature, users do not want just another table-level property, because they can already do ALTER TABLE t REPLICA IDENTITY FULL today. A table-level property would therefore not help much. What users want from this feature is a mechanism that allows new tables to fall back from DEFAULT to FULL automatically.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2026-04-14 04:57 shveta malik <[email protected]>
parent: Chao Li <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: shveta malik @ 2026-04-14 04:57 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Euler Taveira <[email protected]>; GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>; shveta malik <[email protected]>
On Tue, Apr 14, 2026 at 9:17 AM Chao Li <[email protected]> wrote:
>
>
>
> > On Apr 8, 2026, at 18:22, shveta malik <[email protected]> wrote:
> >
> > On Fri, Dec 19, 2025 at 1:39 PM Chao Li <[email protected]> wrote:
> >>
> >>
> >>
> >>> On Dec 18, 2025, at 22:49, Euler Taveira <[email protected]> wrote:
> >>>
> >>> On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
> >>>> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
> >>>>> Each table needs to say what's its row identifier. The user created a table
> >>>>> without primary key. Well, create a primary key. There are dozens of thousands
> >>>>> of objects. Use a script.
> >>>> However, I’d like to share a user perspective regarding the "use a
> >>>> script" approach. The main value of `FOR TABLES IN SCHEMA` is
> >>>> *in-database automation*. If users still need to maintain external
> >>>> scripts to monitor and `ALTER` new tables to prevent replication
> >>>> errors, it significantly diminishes the value of that automation.
> >>>>
> >>>
> >>> As I tried to explain in the previous email, the problem with FOR ALL TABLES
> >>> and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
> >>> relations; the list of relations is collected at runtime.
> >>>
> >>> When I suggested "use a script" I was referring to fix the logical replication
> >>> setup regarding the lack of primary key. There is no need to have an automation
> >>> outside the database, use an event trigger. If your lazy user doesn't create
> >>> the primary key, assign REPLICA IDENTITY FULL. Something like
> >>>
> >>> -- This example is far from being a complete solution for fixing the lack of
> >>> -- primary key in a logical replication scenario.
> >>> -- ALTER TABLE should be supported too
> >>> CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
> >>> RETURNS event_trigger LANGUAGE plpgsql AS $$
> >>> DECLARE
> >>> obj record;
> >>> rec record;
> >>> ricnt integer := 0;
> >>> BEGIN
> >>> FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
> >>> LOOP
> >>> IF obj.command_tag = 'CREATE TABLE' THEN
> >>> SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid AND indisprimary;
> >>> RAISE NOTICE 'ricnt: %', ricnt;
> >>> IF ricnt = 0 THEN
> >>> EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA IDENTITY FULL';
> >>> END IF;
> >>> END IF;
> >>> END LOOP;
> >>> END;
> >>> $$;
> >>>
> >>> CREATE EVENT TRIGGER event_trigger_for_replica_identity
> >>> ON ddl_command_end
> >>> EXECUTE FUNCTION event_trigger_for_replica_identity();
> >>>
> >>> CREATE TABLE event_trigger_test_1 (a int);
> >>> \d+ event_trigger_test_1
> >>> CREATE TABLE event_trigger_test_2 (a int primary key);
> >>> \d+ event_trigger_test_2
> >>> CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
> >>> \d+ event_trigger_test_3
> >>> --ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
> >>> --\d+ event_trigger_test_3
> >>>
> >>> DROP EVENT TRIGGER event_trigger_for_replica_identity;
> >>> DROP FUNCTION event_trigger_for_replica_identity;
> >>> DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
> >>>
> >>> 8<----------------------------------------------------------------------------8<
> >>>
> >>>> Additionally, tables without Primary Keys are valid SQL and extremely
> >>>> common in enterprise environments (e.g., audit logs, data warehousing).
> >>>> In large-scale deployments, enforcing PKs on every single table isn't
> >>>> always practical.
> >>>>
> >>>
> >>> I'm not saying users shouldn't create tables without a primary key. I'm arguing
> >>> that this decision should take into account what adjustments need to be made to
> >>> use these tables in logical replication.
> >>>
> >>>>
> >>>> I think the goal of this proposal is not to change the underlying table
> >>>> property design, but rather to seek a mechanism (like a Publication
> >>>> option) to ensure this automation functions safely without external
> >>>> intervention. It is simply about allowing the database to handle these
> >>>> valid, common scenarios gracefully when automation is enabled.
> >>>>
> >>>
> >>> You didn't get it. You already have one property to handle it and you are
> >>> proposing to add a second property to handle it.
> >>>
> >>> I think you are pursuing the wrong solution. IMO we need a solution to enforce
> >>> that the logical replication contract is valid. If you create or modify a table
> >>> that is part of a publication, there is no validation that that table complies
> >>> with the publication properties (update and delete properties should require an
> >>> appropriate replica identity). We should close the gaps in both publication and
> >>> table.
> >>>
> >>
> >> If I summarize Euler’s position in short words: discipline over convenience. I actually strongly agree with that. In PG we generally prefer explicit over implicit behavior, and predictability over magic.
> >>
> >> Based on the discussion so far, I think we share the following design goals:
> >>
> >> 1) Keep replica identity as a table property.
> >> 2) Avoid silent runtime failures when FOR TABLES IN SCHEMA pulls in tables without primary keys.
> >> 3) Avoid global or implicit behavior changes.
> >> 4) Preserve explicit opt-in for higher WAL cost.
> >> 5) Keep the logical replication contract explicit and enforceable.
> >>
> >> I’ve been thinking about whether adding a new replica identity could meet these goals.
> >>
> >> Today we have four replica identities: DEFAULT (PK, fallback to NONE), INDEX, FULL, and NONE.
> >>
> >> What if we introduce a new replica identity, tentatively called “FORCE”: PK with fallback to FULL. (Let’s keep our focus on the design, not argue the name for now.)
> >>
> >> With this approach:
> >>
> >> 1) Replica identity remains a table property.
> >> 2) Publication membership is still evaluated at runtime, so FOR TABLES IN SCHEMA is not special-cased.
> >> 3) No new GUCs are required.
> >> 4) The user must explicitly opt in by setting the replica identity. Once FORCE is chosen, adding or dropping a primary key later does not silently break UPDATE/DELETE replication.
> >> 5) The logical replication contract remains explicit; the table declares that it is safe for UPDATE/DELETE replication even without a PK, at the cost of higher WAL volume.
> >>
> >> This feels like a small, explicit extension of the existing RI semantics. Notably, REPLICA IDENTITY DEFAULT already has conditional behavior (PK fallback
> >> to NONE), so conditional RI behavior is not new, this would just make a different fallback explicit and user-chosen.
> >
> >
>
> Hi Shveta,
>
> Thanks for your feedback.
>
> > Even though I like the idea of keeping replica identity as a table
> > property, this raises a question about the need for a new FORCE
> > option. With this approach, the user must explicitly opt REPLICA
> > IDENTITY FORCE for tables without a primary key (pt 4 in your design).
> > But if the user is already expected to take an explicit action, they
> > could simply set REPLICA IDENTITY FULL themselves at table creation
> > time or via ALTER TABLE.
>
> This is the tricky part. The need for this feature comes from some complicated operational models in practice. Table creation is often handled by an application team, which focuses on data logic and usually does not care about replication details. Data replication, on the other hand, is maintained by a different team, which does not care what data the tables contain, but only needs to ensure that all data is replicated correctly. In that kind of setup, we cannot expect REPLICA IDENTITY to be configured properly at table creation time.
>
> Such operational models also imply that the database operations team is usually the central database team, with superuser privileges, while an application typically has privileges only on the tables it owns. The central team can set configurations at the database or cluster level. In most cases, what they want is to replicate all tables by default, except for a small number of tables that are explicitly excluded.
>
> Actually, this feature request is coming from the replication team. Today, they have to manually identify newly created tables, usually by running some script, and then configure REPLICA IDENTITY for them. That takes effort and is error-prone. So what they want is a DEFAULT-to-FULL fallback when a table has no primary key.
Okay, I see. If the goal is specifically to address this use-case,
introducing a FORCE table property does not seem to solve the
underlying problem. Using 'ALTER TABLE t REPLICA IDENTITY FORCE' is
effectively no different from 'ALTER TABLE t REPLICA IDENTITY FULL'.
In both cases, a manual action must still be taken on each table,
which is precisely what the replication team is trying to avoid.
> That’s why my initial proposal was to add a GUC to control the fallback behavior, which would be convenient for the central database team.
>
> > From what I understood, the original
> > requirement was to ensure that tables without a primary key do not
> > silently fail replication, and that this is handled automatically
> > without manual intervention. In contrast, the FORCE approach still
> > requires users to explicitly configure replica identity for each
> > table, which seems to reintroduce the same operational burden.
> >
> > For example, instead of:
> > ALTER TABLE t1 REPLICA IDENTITY FORCE;
> > the user could simply do:
> > ALTER TABLE t1 REPLICA IDENTITY FULL
> >
> > By the time, the user is explicitly configuring replica identity, they
> > would already need to understand the replication semantics, so it's
> > not clear what additional benefit FORCE provides over FULL. Am I
> > missing something here?
> >
> > thanks
> > Shveta
>
>
> --
> Chao Li (Evan)
> HighGo Software Co., Ltd.
> https://www.highgo.com/
>
>
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2026-04-14 05:49 shveta malik <[email protected]>
parent: shveta malik <[email protected]>
0 siblings, 1 reply; 26+ messages in thread
From: shveta malik @ 2026-04-14 05:49 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Euler Taveira <[email protected]>; GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>; shveta malik <[email protected]>
On thinking more about the initial design with a GUC-based approach, I
believe we already have a similar precedent where both a GUC and a
table/column-level property coexist. For example, the
default_toast_compression GUC allows setting a default compression
method globally, while users can still override it at the column level
during CREATE TABLE or ALTER TABLE. A similar approach could work in
our case as well.
Regarding the publication-level property, apart from the potential
data-integrity issues discussed earlier, I also have another concern.
If we introduce a publication-level fallback, we would effectively be
deciding what gets logged in WAL for a particular table (i.e., whether
to log REPLICA IDENTITY FULL) based on a publication parameter. This
does not seem quite right to me. Shouldn't WAL logging typically be
independent of publication configuration? Or do we already have a case
where WAL logging behavior depends on publication-level properties?
Thanks,
Shveta
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Improve logical replication usability when tables lack primary keys
@ 2026-04-14 06:19 Chao Li <[email protected]>
parent: shveta malik <[email protected]>
0 siblings, 0 replies; 26+ messages in thread
From: Chao Li @ 2026-04-14 06:19 UTC (permalink / raw)
To: shveta malik <[email protected]>; Amit Kapila <[email protected]>; +Cc: Euler Taveira <[email protected]>; GRANT ZHOU <[email protected]>; [email protected] <[email protected]>; Dilip Kumar <[email protected]>; Postgres hackers <[email protected]>
> On Apr 14, 2026, at 13:49, shveta malik <[email protected]> wrote:
>
> On thinking more about the initial design with a GUC-based approach, I
> believe we already have a similar precedent where both a GUC and a
> table/column-level property coexist. For example, the
> default_toast_compression GUC allows setting a default compression
> method globally, while users can still override it at the column level
> during CREATE TABLE or ALTER TABLE. A similar approach could work in
> our case as well.
Thanks for pointing out this.
Yes, one of solutions I initially considered was to introduce a GUC, say default_replica_identity_fallback, with a default value of “NONE”, which is the current behavior. We just need to support one more value “FULL”, then when a table has no primary key, its replica identity falls back to FULL. Users may freely set the table's replica identity to any value. And the database administrator can set the GUC at either cluster level or database level based on their operation practices.
>
> Regarding the publication-level property, apart from the potential
> data-integrity issues discussed earlier, I also have another concern.
> If we introduce a publication-level fallback, we would effectively be
> deciding what gets logged in WAL for a particular table (i.e., whether
> to log REPLICA IDENTITY FULL) based on a publication parameter. This
> does not seem quite right to me. Shouldn't WAL logging typically be
> independent of publication configuration? Or do we already have a case
> where WAL logging behavior depends on publication-level properties?
>
> Thanks,
> Shveta
For this, I really want to hear back from Amit.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2026-04-14 06:19 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-10 08:06 Improve logical replication usability when tables lack primary keys Chao Li <[email protected]>
2025-11-11 11:18 ` Amit Kapila <[email protected]>
2025-11-11 12:40 ` Chao Li <[email protected]>
2025-12-15 03:28 ` Dilip Kumar <[email protected]>
2025-12-15 03:35 ` Chao Li <[email protected]>
2025-12-15 05:48 ` Amit Kapila <[email protected]>
2025-12-16 06:47 ` Chao Li <[email protected]>
2025-12-17 09:32 ` GRANT ZHOU <[email protected]>
2025-12-17 11:09 ` Zhijie Hou (Fujitsu) <[email protected]>
2025-12-17 20:49 ` Euler Taveira <[email protected]>
2025-12-17 21:43 ` GRANT ZHOU <[email protected]>
2025-12-18 14:49 ` Euler Taveira <[email protected]>
2025-12-19 08:08 ` Chao Li <[email protected]>
2025-12-22 11:48 ` Amit Kapila <[email protected]>
2025-12-30 08:07 ` Chao Li <[email protected]>
2026-04-08 11:43 ` shveta malik <[email protected]>
2026-04-14 03:57 ` Chao Li <[email protected]>
2026-04-08 10:22 ` shveta malik <[email protected]>
2026-04-14 03:47 ` Chao Li <[email protected]>
2026-04-14 04:57 ` shveta malik <[email protected]>
2026-04-14 05:49 ` shveta malik <[email protected]>
2026-04-14 06:19 ` Chao Li <[email protected]>
2025-11-11 12:09 ` Euler Taveira <[email protected]>
2025-11-12 02:21 ` Chao Li <[email protected]>
2025-12-15 01:57 ` Chao Li <[email protected]>
2025-12-16 01:47 ` Euler Taveira <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox