Proposal: Conflict log history table for Logical Replication

On Tue, Aug 5, 2025 at 5:54 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

Currently we log conflicts to the server's log file and updates, this
approach has limitations, 1) Difficult to query and analyze, parsing
plain text log files for conflict details is inefficient. 2) Lack of
structured data, key conflict attributes (table, operation, old/new
data, LSN, etc.) are not readily available in a structured, queryable
format. 3) Difficult for external monitoring tools or custom
resolution scripts to consume conflict data directly.

This proposal aims to address these limitations by introducing a
conflict log history table, providing a structured, and queryable
record of all logical replication conflicts. This should be a
configurable option whether to log into the conflict log history
table, server logs or both.

+1 for the idea.

This proposal has two main design questions:
===================================

1. How do we store conflicting tuples from different tables?
Using a JSON column to store the row data seems like the most flexible
solution, as it can accommodate different table schemas.

Yes, that is one option. I have not looked into details myself, but
you can also explore 'anyarray' used in pg_statistics to store 'Column
data values of the appropriate kind'.

2. Should this be a system table or a user table?
a) System Table: Storing this in a system catalog is simple, but
catalogs aren't designed for ever-growing data. While pg_large_object
is an exception, this is not what we generally do IMHO.
b) User Table: This offers more flexibility. We could allow a user to
specify the table name during CREATE SUBSCRIPTION. Then we choose to
either create the table internally or let the user create the table
with a predefined schema.

A potential drawback is that a user might drop or alter the table.
However, we could mitigate this risk by simply logging a WARNING if
the table is configured but an insertion fails.

I believe it makes more sense for this to be a catalog table rather
than a user table. I wanted to check if we already have a large
catalog table of this kind, and I think pg_statistic could be an
example of a sizable catalog table. To get a rough idea of how size
scales with data, I ran a quick experiment: I created 1000 tables,
each with 2 JSON columns, 1 text column, and 2 integer columns. Then,
I inserted 1000 rows into each table and ran ANALYZE to collect
statistics. Here’s what I observed on a fresh database before and
after:

Before:
pg_statistic row count: 412
Table size: ~256 kB

After:
pg_statistic row count: 6,412
Table size: ~5.3 MB

Although it isn’t an exact comparison, this gives us some insight into
how the statistics catalog table size grows with the number of rows.
It doesn’t seem excessively large with 6k rows, given the fact that
pg_statistic itself is a complex table having many 'anyarray'-type
columns.

That said, irrespective of what we decide, it would be ideal to offer
users an option for automatic purging, perhaps via a retention period
parameter like conflict_stats_retention_period (say default to 30
days), or a manual purge API such as purge_conflict_stats('older than
date'). I wasn’t able to find any such purge mechanism for PostgreSQL
stats tables, but Oracle does provide such purging options for some of
their statistics tables (not related to conflicts), see [1]https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_STATS.html#GUID-8E6413D5-F827-4F57-9FAD-7EC56362A98C, [2]https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_STATS.html#GUID-A04AE1C0-5DE1-4AFC-91F8-D35D41DF98A2.
And to manage it better, it could be range partitioned on timestamp.

I am currently working on a POC patch for the same, but will post that
once we have some thoughts on design choices.

Schema for the conflict log history table may look like this, although
there is a room for discussion on this.

Note: I think these fields are self explanatory so I haven't
explained them here.

conflict_log_table (
logid SERIAL PRIMARY KEY,
subid OID,
schema_id OID,
table_id OID,
conflict_type TEXT NOT NULL,
operation_type TEXT NOT NULL,

I feel operation_type is not needed when we already have
conflict_type. The name of 'conflict_type' is enough to give us info
on operation-type.

replication_origin TEXT,
remote_commit_ts TIMESTAMPTZ,
local_commit_ts TIMESTAMPTZ,
ri_key JSON,
remote_tuple JSON,
local_tuple JSON,
);

Credit: Thanks to Amit Kapila for discussing this offlist and
providing some valuable suggestions.

[1]: https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_STATS.html#GUID-8E6413D5-F827-4F57-9FAD-7EC56362A98C
https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_STATS.html#GUID-8E6413D5-F827-4F57-9FAD-7EC56362A98C

[2]: https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_STATS.html#GUID-A04AE1C0-5DE1-4AFC-91F8-D35D41DF98A2
https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_STATS.html#GUID-A04AE1C0-5DE1-4AFC-91F8-D35D41DF98A2

thanks
Shveta

On Thu, Aug 7, 2025 at 12:25 PM shveta malik <shveta.malik@gmail.com> wrote:

On Tue, Aug 5, 2025 at 5:54 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

Currently we log conflicts to the server's log file and updates, this
approach has limitations, 1) Difficult to query and analyze, parsing
plain text log files for conflict details is inefficient. 2) Lack of
structured data, key conflict attributes (table, operation, old/new
data, LSN, etc.) are not readily available in a structured, queryable
format. 3) Difficult for external monitoring tools or custom
resolution scripts to consume conflict data directly.

This proposal aims to address these limitations by introducing a
conflict log history table, providing a structured, and queryable
record of all logical replication conflicts. This should be a
configurable option whether to log into the conflict log history
table, server logs or both.

+1 for the idea.

This proposal has two main design questions:
===================================

1. How do we store conflicting tuples from different tables?
Using a JSON column to store the row data seems like the most flexible
solution, as it can accommodate different table schemas.

Yes, that is one option. I have not looked into details myself, but
you can also explore 'anyarray' used in pg_statistics to store 'Column
data values of the appropriate kind'.

2. Should this be a system table or a user table?
a) System Table: Storing this in a system catalog is simple, but
catalogs aren't designed for ever-growing data. While pg_large_object
is an exception, this is not what we generally do IMHO.
b) User Table: This offers more flexibility. We could allow a user to
specify the table name during CREATE SUBSCRIPTION. Then we choose to
either create the table internally or let the user create the table
with a predefined schema.

A potential drawback is that a user might drop or alter the table.
However, we could mitigate this risk by simply logging a WARNING if
the table is configured but an insertion fails.

I believe it makes more sense for this to be a catalog table rather
than a user table. I wanted to check if we already have a large
catalog table of this kind, and I think pg_statistic could be an
example of a sizable catalog table. To get a rough idea of how size
scales with data, I ran a quick experiment: I created 1000 tables,
each with 2 JSON columns, 1 text column, and 2 integer columns. Then,
I inserted 1000 rows into each table and ran ANALYZE to collect
statistics. Here’s what I observed on a fresh database before and
after:

Before:
pg_statistic row count: 412
Table size: ~256 kB

After:
pg_statistic row count: 6,412
Table size: ~5.3 MB

Although it isn’t an exact comparison, this gives us some insight into
how the statistics catalog table size grows with the number of rows.
It doesn’t seem excessively large with 6k rows, given the fact that
pg_statistic itself is a complex table having many 'anyarray'-type
columns.

That said, irrespective of what we decide, it would be ideal to offer
users an option for automatic purging, perhaps via a retention period
parameter like conflict_stats_retention_period (say default to 30
days), or a manual purge API such as purge_conflict_stats('older than
date'). I wasn’t able to find any such purge mechanism for PostgreSQL
stats tables, but Oracle does provide such purging options for some of
their statistics tables (not related to conflicts), see [1], [2].
And to manage it better, it could be range partitioned on timestamp.

It seems BDR also has one such conflict-log table which is a catalog
table and is also partitioned on time. It has a default retention
period of 30 days. See 'bdr.conflict_history' mentioned under
'catalogs' in [1]https://www.enterprisedb.com/docs/pgd/latest/reference/tables-views-functions/#user-visible-catalogs-and-views

[1]: https://www.enterprisedb.com/docs/pgd/latest/reference/tables-views-functions/#user-visible-catalogs-and-views

thanks
Shveta

On Thu, Aug 7, 2025 at 1:43 PM shveta malik <shveta.malik@gmail.com> wrote:

On Thu, Aug 7, 2025 at 12:25 PM shveta malik <shveta.malik@gmail.com> wrote:

Thanks Shveta for your opinion on the design.

On Tue, Aug 5, 2025 at 5:54 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

This proposal aims to address these limitations by introducing a
conflict log history table, providing a structured, and queryable
record of all logical replication conflicts. This should be a
configurable option whether to log into the conflict log history
table, server logs or both.

+1 for the idea.

Thanks

This proposal has two main design questions:
===================================

1. How do we store conflicting tuples from different tables?
Using a JSON column to store the row data seems like the most flexible
solution, as it can accommodate different table schemas.

Yes, that is one option. I have not looked into details myself, but
you can also explore 'anyarray' used in pg_statistics to store 'Column
data values of the appropriate kind'.

I think conversion from row to json and json to row is convenient and
also other extensions like pgactive/bdr also provide as JSON. But we
can explore this alternative options as well, thanks

2. Should this be a system table or a user table?
a) System Table: Storing this in a system catalog is simple, but
catalogs aren't designed for ever-growing data. While pg_large_object
is an exception, this is not what we generally do IMHO.
b) User Table: This offers more flexibility. We could allow a user to
specify the table name during CREATE SUBSCRIPTION. Then we choose to
either create the table internally or let the user create the table
with a predefined schema.

A potential drawback is that a user might drop or alter the table.
However, we could mitigate this risk by simply logging a WARNING if
the table is configured but an insertion fails.

I believe it makes more sense for this to be a catalog table rather
than a user table. I wanted to check if we already have a large
catalog table of this kind, and I think pg_statistic could be an
example of a sizable catalog table. To get a rough idea of how size
scales with data, I ran a quick experiment: I created 1000 tables,
each with 2 JSON columns, 1 text column, and 2 integer columns. Then,
I inserted 1000 rows into each table and ran ANALYZE to collect
statistics. Here’s what I observed on a fresh database before and
after:

Before:
pg_statistic row count: 412
Table size: ~256 kB

After:
pg_statistic row count: 6,412
Table size: ~5.3 MB

Although it isn’t an exact comparison, this gives us some insight into
how the statistics catalog table size grows with the number of rows.
It doesn’t seem excessively large with 6k rows, given the fact that
pg_statistic itself is a complex table having many 'anyarray'-type
columns.

Yeah that's good analysis, apart from this pg_largeobject is also a
catalog which grows with each large object and growth rate for that
will be very high because it stores large object data in catalog.

That said, irrespective of what we decide, it would be ideal to offer
users an option for automatic purging, perhaps via a retention period
parameter like conflict_stats_retention_period (say default to 30
days), or a manual purge API such as purge_conflict_stats('older than
date'). I wasn’t able to find any such purge mechanism for PostgreSQL
stats tables, but Oracle does provide such purging options for some of
their statistics tables (not related to conflicts), see [1], [2].
And to manage it better, it could be range partitioned on timestamp.

Yeah that's an interesting suggestion to timestamp based partitioning
it for purging.

It seems BDR also has one such conflict-log table which is a catalog
table and is also partitioned on time. It has a default retention
period of 30 days. See 'bdr.conflict_history' mentioned under
'catalogs' in [1]

[1]: https://www.enterprisedb.com/docs/pgd/latest/reference/tables-views-functions/#user-visible-catalogs-and-views

Actually bdr is an extension and this table is under extension
namespace (bdr.conflict_history) so this is not really a catalog but
its a extension managed table. So logically for PostgreSQL its an
user table but yeah this is created and managed by the extension.

--
Regards,
Dilip Kumar
Google

On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Thu, Aug 7, 2025 at 1:43 PM shveta malik <shveta.malik@gmail.com> wrote:

On Thu, Aug 7, 2025 at 12:25 PM shveta malik <shveta.malik@gmail.com> wrote:

Thanks Shveta for your opinion on the design.

On Tue, Aug 5, 2025 at 5:54 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

This proposal aims to address these limitations by introducing a
conflict log history table, providing a structured, and queryable
record of all logical replication conflicts. This should be a
configurable option whether to log into the conflict log history
table, server logs or both.

+1 for the idea.

Thanks

This proposal has two main design questions:
===================================

1. How do we store conflicting tuples from different tables?
Using a JSON column to store the row data seems like the most flexible
solution, as it can accommodate different table schemas.

Yes, that is one option. I have not looked into details myself, but
you can also explore 'anyarray' used in pg_statistics to store 'Column
data values of the appropriate kind'.

I think conversion from row to json and json to row is convenient and
also other extensions like pgactive/bdr also provide as JSON.

Okay. Agreed.

But we
can explore this alternative options as well, thanks

2. Should this be a system table or a user table?
a) System Table: Storing this in a system catalog is simple, but
catalogs aren't designed for ever-growing data. While pg_large_object
is an exception, this is not what we generally do IMHO.
b) User Table: This offers more flexibility. We could allow a user to
specify the table name during CREATE SUBSCRIPTION. Then we choose to
either create the table internally or let the user create the table
with a predefined schema.

A potential drawback is that a user might drop or alter the table.
However, we could mitigate this risk by simply logging a WARNING if
the table is configured but an insertion fails.

I believe it makes more sense for this to be a catalog table rather
than a user table. I wanted to check if we already have a large
catalog table of this kind, and I think pg_statistic could be an
example of a sizable catalog table. To get a rough idea of how size
scales with data, I ran a quick experiment: I created 1000 tables,
each with 2 JSON columns, 1 text column, and 2 integer columns. Then,
I inserted 1000 rows into each table and ran ANALYZE to collect
statistics. Here’s what I observed on a fresh database before and
after:

Before:
pg_statistic row count: 412
Table size: ~256 kB

After:
pg_statistic row count: 6,412
Table size: ~5.3 MB

Although it isn’t an exact comparison, this gives us some insight into
how the statistics catalog table size grows with the number of rows.
It doesn’t seem excessively large with 6k rows, given the fact that
pg_statistic itself is a complex table having many 'anyarray'-type
columns.

Yeah that's good analysis, apart from this pg_largeobject is also a
catalog which grows with each large object and growth rate for that
will be very high because it stores large object data in catalog.

That said, irrespective of what we decide, it would be ideal to offer
users an option for automatic purging, perhaps via a retention period
parameter like conflict_stats_retention_period (say default to 30
days), or a manual purge API such as purge_conflict_stats('older than
date'). I wasn’t able to find any such purge mechanism for PostgreSQL
stats tables, but Oracle does provide such purging options for some of
their statistics tables (not related to conflicts), see [1], [2].
And to manage it better, it could be range partitioned on timestamp.

Yeah that's an interesting suggestion to timestamp based partitioning
it for purging.

It seems BDR also has one such conflict-log table which is a catalog
table and is also partitioned on time. It has a default retention
period of 30 days. See 'bdr.conflict_history' mentioned under
'catalogs' in [1]

[1]: https://www.enterprisedb.com/docs/pgd/latest/reference/tables-views-functions/#user-visible-catalogs-and-views

Actually bdr is an extension and this table is under extension
namespace (bdr.conflict_history) so this is not really a catalog but
its a extension managed table.

Yes, right. Sorry for confusion.

So logically for PostgreSQL its an
user table but yeah this is created and managed by the extension.

Any idea if the user can alter/drop or perform any DML on it? I could
not find any details on this part.

On Fri, Aug 8, 2025 at 8:58 AM shveta malik <shveta.malik@gmail.com> wrote:

On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

So logically for PostgreSQL its an
user table but yeah this is created and managed by the extension.

Any idea if the user can alter/drop or perform any DML on it? I could
not find any details on this part.

In my experience, for such extension managed tables where we want them
to behave like catalog, generally users are just granted with SELECT
permission. So although it is not a catalog but for accessibility
wise for non admin users it is like a catalog. IMHO, even if we
choose to create a user table for conflict log history we can also
control the permissions similarly. What's your opinion on this?

--
Regards,
Dilip Kumar
Google

On Fri, Aug 8, 2025 at 10:01 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Fri, Aug 8, 2025 at 8:58 AM shveta malik <shveta.malik@gmail.com> wrote:

On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

So logically for PostgreSQL its an
user table but yeah this is created and managed by the extension.

Any idea if the user can alter/drop or perform any DML on it? I could
not find any details on this part.

In my experience, for such extension managed tables where we want them
to behave like catalog, generally users are just granted with SELECT
permission. So although it is not a catalog but for accessibility
wise for non admin users it is like a catalog. IMHO, even if we
choose to create a user table for conflict log history we can also
control the permissions similarly.

Yes, it can be done. Technically there is nothing preventing us from
doing it. But in my experience, I have never seen any
system-maintained statistics tables to be a user table rather than
catalog table. Extensions are a different case; they typically manage
their own tables, which are not part of the system catalog. But if any
such stats related functionality is part of the core database, it
generally makes more sense to implement it as a catalog table
(provided there are no major obstacles to doing so). But I am curious
to know what others think here.

thanks
Shveta

On Fri, Aug 8, 2025 at 10:01 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Fri, Aug 8, 2025 at 8:58 AM shveta malik <shveta.malik@gmail.com> wrote:

On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

So logically for PostgreSQL its an
user table but yeah this is created and managed by the extension.

Any idea if the user can alter/drop or perform any DML on it? I could
not find any details on this part.

In my experience, for such extension managed tables where we want them
to behave like catalog, generally users are just granted with SELECT
permission. So although it is not a catalog but for accessibility
wise for non admin users it is like a catalog. IMHO, even if we
choose to create a user table for conflict log history we can also
control the permissions similarly. What's your opinion on this?

Yes, I think it is important to control permissions on this table even
if it is a user table. How about giving SELECT, DELETE, TRUNCATE
permissions to subscription owner assuming we create one such table
per subscription?

It should be a user table due to following reasons (a) It is an ever
growing table by definition and we need some level of user control to
manage it (like remove the old data); (b) We may want some sort of
partitioning streategy to manage it, even though, we decide to do it
ourselves now but in future, we should allow user to also specify it;
(c) We may also want user to specify what exact information she wants
to get stored considering in future we want resolutions to also be
stored in it. See a somewhat similar proposal to store errors during
copy by Tom [1]/messages/by-id/752672.1699474336@sss.pgh.pa.us; (d) In a near-by thread, we are discussing storing
errors during copy in user table [2]/messages/by-id/CACJufxH_OJpVra=0c4ow8fbxHj7heMcVaTNEPa5vAurSeNA-6Q@mail.gmail.com and we have some similarity with
that proposal as well.

If we agree on this then the next thing to consider is whether we
allow users to create such a table or do it ourselves. In the long
term, we may want both but for simplicity, we can auto-create
ourselves during CREATE SUBSCRIPTION with some option. BTW, if we
decide to let user create it then we can consider the idea of TYPED
tables as discussed in emails [3]/messages/by-id/28c420cf-f25d-44f1-89fd-04ef0b2dd3db@dunslane.net[4]/messages/by-id/CADrsxdYG++K=iKjRm35u03q-Nb0tQPJaqjxnA2mGt5O=Dht7sw@mail.gmail.com.

For user tables, we need to consider how to avoid replicating these
tables for publications that use FOR ALL TABLES specifier. One idea is
to use EXCLUDE table functionality as being discussed in thread [5]/messages/by-id/CANhcyEW+uJB_bvQLEaZCgoRTc1=i+QnrPPHxZ2=0SBSCyj9pkg@mail.gmail.com
but that would also be a bit tricky especially if we decide to create
such a table automatically. One naive idea is that internally we skip
sending changes from this table for "FOR ALL TABLES" publication, and
we shouldn't allow creating publication for this table. OTOH, if we
allow the user to create and specify this table, we can ask her to
specify with EXCLUDE syntax in publication. This needs more thoughts.

[1]: /messages/by-id/752672.1699474336@sss.pgh.pa.us
[2]: /messages/by-id/CACJufxH_OJpVra=0c4ow8fbxHj7heMcVaTNEPa5vAurSeNA-6Q@mail.gmail.com
[3]: /messages/by-id/28c420cf-f25d-44f1-89fd-04ef0b2dd3db@dunslane.net
[4]: /messages/by-id/CADrsxdYG++K=iKjRm35u03q-Nb0tQPJaqjxnA2mGt5O=Dht7sw@mail.gmail.com
[5]: /messages/by-id/CANhcyEW+uJB_bvQLEaZCgoRTc1=i+QnrPPHxZ2=0SBSCyj9pkg@mail.gmail.com

--
With Regards,
Amit Kapila.

On Wed, 13 Aug 2025 at 11:09, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Fri, Aug 8, 2025 at 10:01 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Fri, Aug 8, 2025 at 8:58 AM shveta malik <shveta.malik@gmail.com>

wrote:

On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbalaut@gmail.com>

wrote:

So logically for PostgreSQL its an
user table but yeah this is created and managed by the extension.

Any idea if the user can alter/drop or perform any DML on it? I could
not find any details on this part.

In my experience, for such extension managed tables where we want them
to behave like catalog, generally users are just granted with SELECT
permission. So although it is not a catalog but for accessibility
wise for non admin users it is like a catalog. IMHO, even if we
choose to create a user table for conflict log history we can also
control the permissions similarly. What's your opinion on this?

Yes, I think it is important to control permissions on this table even
if it is a user table. How about giving SELECT, DELETE, TRUNCATE
permissions to subscription owner assuming we create one such table
per subscription?

It should be a user table due to following reasons (a) It is an ever
growing table by definition and we need some level of user control to
manage it (like remove the old data); (b) We may want some sort of
partitioning streategy to manage it, even though, we decide to do it
ourselves now but in future, we should allow user to also specify it;
(c) We may also want user to specify what exact information she wants
to get stored considering in future we want resolutions to also be
stored in it. See a somewhat similar proposal to store errors during
copy by Tom [1]; (d) In a near-by thread, we are discussing storing
errors during copy in user table [2] and we have some similarity with
that proposal as well.

If we agree on this then the next thing to consider is whether we
allow users to create such a table or do it ourselves. In the long
term, we may want both but for simplicity, we can auto-create
ourselves during CREATE SUBSCRIPTION with some option. BTW, if we
decide to let user create it then we can consider the idea of TYPED
tables as discussed in emails [3][4].

Having it be a user table, and specifying the table per subscription sounds
good. This is very similar to how the load error tables for CloudBerry
behave, for instance. To have both options for table creation, CREATE ...
IF NOT EXISTS semantics work well - if the option on CREATE SUBSCRIPTION
specifies an existing table of the right type use it, or create one with
the name supplied. This would also give the user control over whether to
have one table per subscription, one central table or anything in between.
Rather than constraining permissions on the table, the CREATE SUBSCRIPTION
command could create a dependency relationship between the table and the
subscription.This would prevent removal of the table, even by a superuser.

For user tables, we need to consider how to avoid replicating these
tables for publications that use FOR ALL TABLES specifier. One idea is
to use EXCLUDE table functionality as being discussed in thread [5]
but that would also be a bit tricky especially if we decide to create
such a table automatically. One naive idea is that internally we skip
sending changes from this table for "FOR ALL TABLES" publication, and
we shouldn't allow creating publication for this table. OTOH, if we
allow the user to create and specify this table, we can ask her to
specify with EXCLUDE syntax in publication. This needs more thoughts.

If a dependency relationship is established between the error table and the
subscription, could this be used as a basis for filtering the error tables
from FOR ALL TABLES subscriptions?

Regards

Alastair

On Thu, Aug 14, 2025 at 4:26 PM Alastair Turner <minion@decodable.me> wrote:

On Wed, 13 Aug 2025 at 11:09, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Fri, Aug 8, 2025 at 10:01 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Fri, Aug 8, 2025 at 8:58 AM shveta malik <shveta.malik@gmail.com> wrote:

On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

So logically for PostgreSQL its an
user table but yeah this is created and managed by the extension.

Any idea if the user can alter/drop or perform any DML on it? I could
not find any details on this part.

In my experience, for such extension managed tables where we want them
to behave like catalog, generally users are just granted with SELECT
permission. So although it is not a catalog but for accessibility
wise for non admin users it is like a catalog. IMHO, even if we
choose to create a user table for conflict log history we can also
control the permissions similarly. What's your opinion on this?

Yes, I think it is important to control permissions on this table even
if it is a user table. How about giving SELECT, DELETE, TRUNCATE
permissions to subscription owner assuming we create one such table
per subscription?

It should be a user table due to following reasons (a) It is an ever
growing table by definition and we need some level of user control to
manage it (like remove the old data); (b) We may want some sort of
partitioning streategy to manage it, even though, we decide to do it
ourselves now but in future, we should allow user to also specify it;
(c) We may also want user to specify what exact information she wants
to get stored considering in future we want resolutions to also be
stored in it. See a somewhat similar proposal to store errors during
copy by Tom [1]; (d) In a near-by thread, we are discussing storing
errors during copy in user table [2] and we have some similarity with
that proposal as well.

If we agree on this then the next thing to consider is whether we
allow users to create such a table or do it ourselves. In the long
term, we may want both but for simplicity, we can auto-create
ourselves during CREATE SUBSCRIPTION with some option. BTW, if we
decide to let user create it then we can consider the idea of TYPED
tables as discussed in emails [3][4].

Having it be a user table, and specifying the table per subscription sounds good. This is very similar to how the load error tables for CloudBerry behave, for instance. To have both options for table creation, CREATE ... IF NOT EXISTS semantics work well - if the option on CREATE SUBSCRIPTION specifies an existing table of the right type use it, or create one with the name supplied. This would also give the user control over whether to have one table per subscription, one central table or anything in between.

Sounds reasonable. I think the first version we can let such a table
be created automatically with some option(s) with subscription. Then,
in subsequent versions, we can extend the functionality to allow
existing tables.

Rather than constraining permissions on the table, the CREATE SUBSCRIPTION command could create a dependency relationship between the table and the subscription.This would prevent removal of the table, even by a superuser.

Okay, that makes sense. But, we still probably want to disallow users
from inserting or updating rows in the conflict table.

For user tables, we need to consider how to avoid replicating these
tables for publications that use FOR ALL TABLES specifier. One idea is
to use EXCLUDE table functionality as being discussed in thread [5]
but that would also be a bit tricky especially if we decide to create
such a table automatically. One naive idea is that internally we skip
sending changes from this table for "FOR ALL TABLES" publication, and
we shouldn't allow creating publication for this table. OTOH, if we
allow the user to create and specify this table, we can ask her to
specify with EXCLUDE syntax in publication. This needs more thoughts.

If a dependency relationship is established between the error table and the subscription, could this be used as a basis for filtering the error tables from FOR ALL TABLES subscriptions?

Yeah, that is worth considering.

--
With Regards,
Amit Kapila.

On Wed, Aug 13, 2025 at 3:39 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Fri, Aug 8, 2025 at 10:01 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Fri, Aug 8, 2025 at 8:58 AM shveta malik <shveta.malik@gmail.com> wrote:

On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

So logically for PostgreSQL its an
user table but yeah this is created and managed by the extension.

Any idea if the user can alter/drop or perform any DML on it? I could
not find any details on this part.

In my experience, for such extension managed tables where we want them
to behave like catalog, generally users are just granted with SELECT
permission. So although it is not a catalog but for accessibility
wise for non admin users it is like a catalog. IMHO, even if we
choose to create a user table for conflict log history we can also
control the permissions similarly. What's your opinion on this?

Yes, I think it is important to control permissions on this table even
if it is a user table. How about giving SELECT, DELETE, TRUNCATE
permissions to subscription owner assuming we create one such table
per subscription?

Right, we need to control the permission. I am not sure whether we
want a per subscription table or a common one. Earlier I was thinking
of a single table, but I think per subscription is not a bad idea
especially for managing the permissions. And there can not be a
really huge number of subscriptions that we need to worry about
creating many conflict log history tables and that too we will only
create such tables when users pass that subscription option.

It should be a user table due to following reasons (a) It is an ever
growing table by definition and we need some level of user control to
manage it (like remove the old data); (b) We may want some sort of
partitioning streategy to manage it, even though, we decide to do it
ourselves now but in future, we should allow user to also specify it;

Maybe we can partition by range on date (when entry is inserted) .
That way it would be easy to get rid of older partitions for users.

(c) We may also want user to specify what exact information she wants
to get stored considering in future we want resolutions to also be
stored in it. See a somewhat similar proposal to store errors during
copy by Tom [1]; (d) In a near-by thread, we are discussing storing
errors during copy in user table [2] and we have some similarity with
that proposal as well.

Right, we may consider that as well.

If we agree on this then the next thing to consider is whether we
allow users to create such a table or do it ourselves. In the long
term, we may want both but for simplicity, we can auto-create
ourselves during CREATE SUBSCRIPTION with some option. BTW, if we
decide to let user create it then we can consider the idea of TYPED
tables as discussed in emails [3][4].

Yeah that's an interesting option.

For user tables, we need to consider how to avoid replicating these
tables for publications that use FOR ALL TABLES specifier. One idea is
to use EXCLUDE table functionality as being discussed in thread [5]
but that would also be a bit tricky especially if we decide to create
such a table automatically. One naive idea is that internally we skip
sending changes from this table for "FOR ALL TABLES" publication, and
we shouldn't allow creating publication for this table. OTOH, if we
allow the user to create and specify this table, we can ask her to
specify with EXCLUDE syntax in publication. This needs more thoughts.

Yes this needs more thought, I will think more on this point and respond.

Yet another question is about table names, whether we keep some
standard name like conflict_log_history_$subid or let users pass the
name.

--
Regards,
Dilip Kumar
Google

On Fri, Aug 15, 2025 at 2:31 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

Yet another question is about table names, whether we keep some
standard name like conflict_log_history_$subid or let users pass the
name.

It would be good if we can let the user specify the table_name and if
she didn't specify then use an internally generated name. I think it
will be somewhat similar to slot_name. However, in this case, there is
one challenge which is how can we decide whether the schema of the
user provided table_name is correct or not? Do we compare it with the
standard schema we are planning to use?

One idea to keep things simple for the first version is that we allow
users to specify the table_name for storing conflicts but the table
should be created internally and if the same name table already
exists, we can give an ERROR. Then we can later extend the
functionality to even allow storing conflicts in pre-created tables
with more checks about its schema.

--
With Regards,
Amit Kapila.

On Mon, Aug 18, 2025 at 12:25 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Fri, Aug 15, 2025 at 2:31 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

Yet another question is about table names, whether we keep some
standard name like conflict_log_history_$subid or let users pass the
name.

It would be good if we can let the user specify the table_name and if
she didn't specify then use an internally generated name. I think it
will be somewhat similar to slot_name. However, in this case, there is
one challenge which is how can we decide whether the schema of the
user provided table_name is correct or not? Do we compare it with the
standard schema we are planning to use?

Ideally we can do that, if you see in this thread [1]/messages/by-id/752672.1699474336@sss.pgh.pa.us there is a patch
[2]: /messages/by-id/attachment/152792/v8-0001-Add-a-new-COPY-option-SAVE_ERROR.patch
exist it creates it on its own. And it seems fine to me.

One idea to keep things simple for the first version is that we allow
users to specify the table_name for storing conflicts but the table
should be created internally and if the same name table already
exists, we can give an ERROR. Then we can later extend the
functionality to even allow storing conflicts in pre-created tables
with more checks about its schema.

That's fair too. I am wondering what namespace we should create this
user table in. If we are creating internally, I assume the user should
provide a schema qualified name right?

[1]: /messages/by-id/752672.1699474336@sss.pgh.pa.us
[2]: /messages/by-id/attachment/152792/v8-0001-Add-a-new-COPY-option-SAVE_ERROR.patch

--
Regards,
Dilip Kumar
Google

On Wed, Aug 20, 2025 at 11:47 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Mon, Aug 18, 2025 at 12:25 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

One idea to keep things simple for the first version is that we allow
users to specify the table_name for storing conflicts but the table
should be created internally and if the same name table already
exists, we can give an ERROR. Then we can later extend the
functionality to even allow storing conflicts in pre-created tables
with more checks about its schema.

That's fair too. I am wondering what namespace we should create this
user table in. If we are creating internally, I assume the user should
provide a schema qualified name right?

Yeah, but if not provided then we should create it based on
search_path similar to what we do when user created the table from
psql.

--
With Regards,
Amit Kapila.

On Wed, Aug 20, 2025 at 5:46 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Wed, Aug 20, 2025 at 11:47 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Mon, Aug 18, 2025 at 12:25 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

One idea to keep things simple for the first version is that we allow
users to specify the table_name for storing conflicts but the table
should be created internally and if the same name table already
exists, we can give an ERROR. Then we can later extend the
functionality to even allow storing conflicts in pre-created tables
with more checks about its schema.

That's fair too. I am wondering what namespace we should create this
user table in. If we are creating internally, I assume the user should
provide a schema qualified name right?

Yeah, but if not provided then we should create it based on
search_path similar to what we do when user created the table from
psql.

Yeah that makes sense.

--
Regards,
Dilip Kumar
Google

On Thu, Aug 21, 2025 at 9:17 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Wed, Aug 20, 2025 at 5:46 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Wed, Aug 20, 2025 at 11:47 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Mon, Aug 18, 2025 at 12:25 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

One idea to keep things simple for the first version is that we allow
users to specify the table_name for storing conflicts but the table
should be created internally and if the same name table already
exists, we can give an ERROR. Then we can later extend the
functionality to even allow storing conflicts in pre-created tables
with more checks about its schema.

That's fair too. I am wondering what namespace we should create this
user table in. If we are creating internally, I assume the user should
provide a schema qualified name right?

Yeah, but if not provided then we should create it based on
search_path similar to what we do when user created the table from
psql.

While working on the patch, I see there are some open questions

1. We decided to pass the conflict history table name during
subscription creation. And it makes sense to create this table when
the CREATE SUBSCRIPTION command is executed. A potential concern is
that the subscription owner will also own this table, having full
control over it, including the ability to drop or alter its schema.
This might not be an issue. If an INSERT into the conflict table
fails, we can check the table's existence and schema. If they are not
as expected, the conflict log history option can be disabled and
re-enabled later via ALTER SUBSCRIPTION.

2. A further challenge is how to exclude these tables from publishing
changes. If we support a subscription-level log history table and the
user publishes ALL TABLES, the output plugin uses
is_publishable_relation() to check if a table is publishable. However,
applying the same logic here would require checking each subscription
on the node to see if the table is designated as a conflict log
history table for any subscription, which could be costly.

3. And one last thing is about should we consider dropping this table
when we drop the subscription, I think this makes sense as we are
internally creating it while creating the subscription.

--
Regards,
Dilip Kumar
Google

Hi Dilip

Thanks for working on this, I think it will make conflict detection a lot
more useful.

On Sat, 6 Sept 2025, 10:38 Dilip Kumar, <dilipbalaut@gmail.com> wrote:

While working on the patch, I see there are some open questions

1. We decided to pass the conflict history table name during
subscription creation. And it makes sense to create this table when
the CREATE SUBSCRIPTION command is executed. A potential concern is
that the subscription owner will also own this table, having full
control over it, including the ability to drop or alter its schema.

...

Typed tables and the dependency framework can address this concern. The
schema of a typed table cannot be changed. If the subscription is marked as
a dependency of the log table, the table cannot be dropped while the
subscription exists.

2. A further challenge is how to exclude these tables from publishing
changes. If we support a subscription-level log history table and the
user publishes ALL TABLES, the output plugin uses
is_publishable_relation() to check if a table is publishable. However,
applying the same logic here would require checking each subscription
on the node to see if the table is designated as a conflict log
history table for any subscription, which could be costly.

Checking the type of a table and/or whether a subscription object depends
on it in a certain way would be a far less costly operation to add to
is_publishable_relation()

3. And one last thing is about should we consider dropping this table
when we drop the subscription, I think this makes sense as we are
internally creating it while creating the subscription.

Having to clean up the log table explicitly is likely to annoy users far
less than having the conflict data destroyed as a side effect of another
operation. I would strongly suggest leaving the table in place when the
subscription is dropped.

Regards
Alastair

On Sun, Sep 7, 2025 at 1:42 PM Alastair Turner <minion@decodable.me> wrote:

Hi Dilip

Thanks for working on this, I think it will make conflict detection a lot more useful.

Thanks for the suggestions, please find my reply inline.

On Sat, 6 Sept 2025, 10:38 Dilip Kumar, <dilipbalaut@gmail.com> wrote:

While working on the patch, I see there are some open questions

1. We decided to pass the conflict history table name during
subscription creation. And it makes sense to create this table when
the CREATE SUBSCRIPTION command is executed. A potential concern is
that the subscription owner will also own this table, having full
control over it, including the ability to drop or alter its schema.

Typed tables and the dependency framework can address this concern. The schema of a typed table cannot be changed. If the subscription is marked as a dependency of the log table, the table cannot be dropped while the subscription exists.

Yeah type table can be useful here, but only concern is when do we
create this type. One option is whenever we can create a catalog
relation say "conflict_log_history" that will create a type and then
for each subscription if we need to create the conflict history table
we can create it as "conflict_log_history" type, but this might not be
a best option as we are creating catalog just for using this type.
Second option is to create a type while creating a table itself but
then again the problem remains the same as subscription owners get
control over altering the schema of the type itself. So the goal is
we want this type to be created such that it can not be altered so
IMHO option1 is more suitable i.e. creating conflict_log_history as
catalog and per subscription table can be created as this type.

2. A further challenge is how to exclude these tables from publishing
changes. If we support a subscription-level log history table and the
user publishes ALL TABLES, the output plugin uses
is_publishable_relation() to check if a table is publishable. However,
applying the same logic here would require checking each subscription
on the node to see if the table is designated as a conflict log
history table for any subscription, which could be costly.

Checking the type of a table and/or whether a subscription object depends on it in a certain way would be a far less costly operation to add to is_publishable_relation()

+1

3. And one last thing is about should we consider dropping this table
when we drop the subscription, I think this makes sense as we are
internally creating it while creating the subscription.

Having to clean up the log table explicitly is likely to annoy users far less than having the conflict data destroyed as a side effect of another operation. I would strongly suggest leaving the table in place when the subscription is dropped.

Thanks for the input, I would like to hear opinions from others as
well here. I agree that implicitly getting rid of the conflict
history might be problematic but we also need to consider that we are
considering dropping this when the whole subscription is dropped. Not
sure even after subscription drop users will be interested in conflict
history, if yes then they need to be aware of preserving that isn't
it.

--
Regards,
Dilip Kumar
Google

On Mon, Sep 8, 2025 at 12:01 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Sun, Sep 7, 2025 at 1:42 PM Alastair Turner <minion@decodable.me> wrote:

Hi Dilip

Thanks for working on this, I think it will make conflict detection a lot more useful.

Thanks for the suggestions, please find my reply inline.

On Sat, 6 Sept 2025, 10:38 Dilip Kumar, <dilipbalaut@gmail.com> wrote:

While working on the patch, I see there are some open questions

1. We decided to pass the conflict history table name during
subscription creation. And it makes sense to create this table when
the CREATE SUBSCRIPTION command is executed. A potential concern is
that the subscription owner will also own this table, having full
control over it, including the ability to drop or alter its schema.

Typed tables and the dependency framework can address this concern. The schema of a typed table cannot be changed. If the subscription is marked as a dependency of the log table, the table cannot be dropped while the subscription exists.

Yeah type table can be useful here, but only concern is when do we
create this type.

How about having this as a built-in type?

One option is whenever we can create a catalog
relation say "conflict_log_history" that will create a type and then
for each subscription if we need to create the conflict history table
we can create it as "conflict_log_history" type, but this might not be
a best option as we are creating catalog just for using this type.
Second option is to create a type while creating a table itself but
then again the problem remains the same as subscription owners get
control over altering the schema of the type itself. So the goal is
we want this type to be created such that it can not be altered so
IMHO option1 is more suitable i.e. creating conflict_log_history as
catalog and per subscription table can be created as this type.

I think having it as a catalog table has drawbacks like who will clean
this ever growing table. The one thing is not clear from Alastair's
response is that he said to make subscription as a dependency of
table, if we do so, then won't it be difficult to even drop
subscription and also doesn't that sound reverse of what we want.

2. A further challenge is how to exclude these tables from publishing
changes. If we support a subscription-level log history table and the
user publishes ALL TABLES, the output plugin uses
is_publishable_relation() to check if a table is publishable. However,
applying the same logic here would require checking each subscription
on the node to see if the table is designated as a conflict log
history table for any subscription, which could be costly.

Checking the type of a table and/or whether a subscription object depends on it in a certain way would be a far less costly operation to add to is_publishable_relation()

+1

3. And one last thing is about should we consider dropping this table
when we drop the subscription, I think this makes sense as we are
internally creating it while creating the subscription.

Having to clean up the log table explicitly is likely to annoy users far less than having the conflict data destroyed as a side effect of another operation. I would strongly suggest leaving the table in place when the subscription is dropped.

Thanks for the input, I would like to hear opinions from others as
well here.

But OTOH, there could be users who want such a table to be dropped.
One possibility is that if we user provided us a pre-created table
then we leave it to user to remove the table, otherwise, we can remove
with drop subscription. BTW, did we decide that we want a
conflict-table-per-subscription or one table for all subscriptions, if
later, then I guess the problem would be that it has to be a shared
table across databases.

--
With Regards,
Amit Kapila.

On Wed, Sep 10, 2025 at 3:25 PM Amit Kapila <amit.kapila16@gmail.com> wrote:

On Mon, Sep 8, 2025 at 12:01 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Sun, Sep 7, 2025 at 1:42 PM Alastair Turner <minion@decodable.me> wrote:

Hi Dilip

Thanks for working on this, I think it will make conflict detection a lot more useful.

Thanks for the suggestions, please find my reply inline.

On Sat, 6 Sept 2025, 10:38 Dilip Kumar, <dilipbalaut@gmail.com> wrote:

While working on the patch, I see there are some open questions

1. We decided to pass the conflict history table name during
subscription creation. And it makes sense to create this table when
the CREATE SUBSCRIPTION command is executed. A potential concern is
that the subscription owner will also own this table, having full
control over it, including the ability to drop or alter its schema.

Typed tables and the dependency framework can address this concern. The schema of a typed table cannot be changed. If the subscription is marked as a dependency of the log table, the table cannot be dropped while the subscription exists.

Yeah type table can be useful here, but only concern is when do we
create this type.

How about having this as a built-in type?

Here we will have to create a built-in type of type table which is I
think typcategory => 'C' and if we create this type it should be
supplied with the "typrelid" that means there should be a backing
catalog table. At least thats what I think.

One option is whenever we can create a catalog
relation say "conflict_log_history" that will create a type and then
for each subscription if we need to create the conflict history table
we can create it as "conflict_log_history" type, but this might not be
a best option as we are creating catalog just for using this type.
Second option is to create a type while creating a table itself but
then again the problem remains the same as subscription owners get
control over altering the schema of the type itself. So the goal is
we want this type to be created such that it can not be altered so
IMHO option1 is more suitable i.e. creating conflict_log_history as
catalog and per subscription table can be created as this type.

I think having it as a catalog table has drawbacks like who will clean
this ever growing table.

No, I didn't mean an ever growing catalog table, I was giving an
option to create a catalog table just to create a built-in type and
then we will create an actual log history table of this built-in type
for each subscription while creating the subscription. So this
catalog table will be there but nothing will be inserted to this table
and whenever the user supplies a conflict log history table name while
creating a subscription that time we will create an actual table and
the type of the table will be as the catalog table type. I agree
creating a catalog table for this purpose might not be worth it, but I
am not yet able to figure out how to create a built-in type of type
table without creating the actual table.

The one thing is not clear from Alastair's

response is that he said to make subscription as a dependency of
table, if we do so, then won't it be difficult to even drop
subscription and also doesn't that sound reverse of what we want.

I assume he means subscription will be dependent on the log table,
that means we can not drop the log table as subscription is dependent
on this table.

2. A further challenge is how to exclude these tables from publishing
changes. If we support a subscription-level log history table and the
user publishes ALL TABLES, the output plugin uses
is_publishable_relation() to check if a table is publishable. However,
applying the same logic here would require checking each subscription
on the node to see if the table is designated as a conflict log
history table for any subscription, which could be costly.

Checking the type of a table and/or whether a subscription object depends on it in a certain way would be a far less costly operation to add to is_publishable_relation()

+1

3. And one last thing is about should we consider dropping this table
when we drop the subscription, I think this makes sense as we are
internally creating it while creating the subscription.

Having to clean up the log table explicitly is likely to annoy users far less than having the conflict data destroyed as a side effect of another operation. I would strongly suggest leaving the table in place when the subscription is dropped.

Thanks for the input, I would like to hear opinions from others as
well here.

But OTOH, there could be users who want such a table to be dropped.
One possibility is that if we user provided us a pre-created table
then we leave it to user to remove the table, otherwise, we can remove
with drop subscription.

Thanks make sense.

BTW, did we decide that we want a

conflict-table-per-subscription or one table for all subscriptions, if
later, then I guess the problem would be that it has to be a shared
table across databases.

Right and I don't think there is an option to create a user defined
shared table. And I don't think there is any issue creating per
subscription conflict log history table, except that the subscription
owner should have permission to create the table in the database while
creating the subscription, but I think this is expected, either user
can get the sufficient privilege or disable the option for conflict
log history table.

--
Regards,
Dilip Kumar
Google