Re: Proposal: Conflict log history table for Logical Replication - Mailing list pgsql-hackers

Hi,

On Tue, Aug 5, 2025 at 5:24 AM 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 overall idea. Having an option to separate out the
conflicts helps analyze the data correctness issues and understand the
behavior of conflicts.

Parsing server logs file for analysis and debugging is a typical
requirement differently met with tools like log_fdw or capture server
logs in CSV format for parsing or do text search and analyze etc.

> 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.

How good is storing conflicts on the table? Is it okay to generate WAL
traffic? Is it okay to physically replicate this log table to all
replicas? Is it okay to logically replicate this log table to all
subscribers and logical decoding clients? How does this table get
truncated? If truncation gets delayed, won't it unnecessarily fill up
storage?

> 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.

-1 for the system table for sure.

> 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 am currently working on a POC patch for the same, but will post that
> once we have some thoughts on design choices.

How about streaming the conflicts in fixed format to a separate log
file other than regular postgres server log file?  All the
rules/settings that apply to regular postgres server log files also
apply for conflicts server log files (rotation, GUCs, format
CSV/JSON/TEXT etc.). This way there's no additional WAL, and we don't
have to worry about drop/alter, truncate, delete, update/insert,
permission model, physical replication, logical replication, storage
space etc.

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com