Re: Simplify code building the LR conflict messages

On Sat, Nov 29, 2025 at 11:51 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> After a little bit of thought, here's a sketch of a straw-man idea.
>
> 1. The longstanding output for unique constraint violations is like
>
> ERROR: duplicate key value violates unique constraint "foo_f1_f2_key"
> DETAIL: Key (f1, f2)=(1, 2) already exists.
>
> We could do worse than to use exactly that output (perhaps even
> sharing code) and add errcontext like
>
> CONTEXT: replicated INSERT of row with replica identity (f1, f2)=(1, 2) in table "foo"
>
> We should drop the full-row output for the reason I gave previously,
> and I do not see the value of the XID printout either.
>

The reason for displaying in this style is that, in conflicts, users
may want to define their custom resolution strategies based on
conflict_type. Sometimes they need to resolve conflicts manually as
well. To make an informed decision on which version of the row is
"correct," the human reviewer needs full context.

Example: Imagine a table storing employee data.
Node A updates John Smith’s salary from $100k to $110k.
Node B simultaneously updates John Smith’s job title from "Senior Dev"
to "Lead Dev".
If the conflict report only showed:
Conflict on PK 123. Column 'Salary' differs. Column 'Job Title' differs.

The reviewer doesn't have enough information. However, if the report
shows the full rows:

Node A Version: ID:123, Name: John Smith, Salary: $110k, Title: Senior
Dev, Dept: Engineering, LastUpdatedBy: PayrollSys
Node B Version: ID:123, Name: John Smith, Salary: $100k, Title: Lead
Dev, Dept: Engineering, LastUpdatedBy: HR_Manager

Seeing the full row allows the reviewer to see who made the change and
what else is in the row. They might decide that the HR Manager's
promotion (Title change) should take precedence, or they might realize
they need to merge the two changes manually because both are valid.
Without the full row data (like LastUpdatedBy or Dept), this decision
is impossible.

Another case where we need row data is when a value in Column A might
only be valid based on the value in Column B within the same row. For
example, CHECK Constraints: Imagine a constraint where IF status =
'Active' THEN termination_date MUST BE NULL. If a conflict arises
involving these columns, you need to see both columns simultaneously
to understand why a specific resolution might violate database rules.

We do display the entire row as DETAIL for CHECK constraints even without apply.
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);

postgres=# insert into products values (1, 'pen', 10, 20);
ERROR: new row for relation "products" violates check constraint
"products_check"
DETAIL: Failing row contains (1, pen, 10, 20).

Also, as per the docs [1], "The large column values are truncated to
64 bytes." while displaying conflict information which is same as what
we do while displaying the row during CHECK constraint violation.

Additionally, we checked some other systems where they display the
entire row information [2] (See, "The format of a uniqueness conflict
record is").

Currently, we display the information for multiple_unique_conflicts,
when track_commit_timestamp is on as follows:
ERROR: conflict detected on relation "public.conf_tab":
conflict=multiple_unique_conflicts
DETAIL: Key already exists in unique index "conf_tab_pkey", modified
locally in transaction 764 at 2025-12-01 08:59:50.789608+05:30.
Key (a)=(2); existing local row (2, 2, 2); remote row (2, 3, 4).
Key already exists in unique index "conf_tab_b_key", modified
locally in transaction 764 at 2025-12-01 08:59:50.789608+05:30.
Key (b)=(3); existing local row (3, 3, 3); remote row (2, 3, 4).
Key already exists in unique index "conf_tab_c_key", modified
locally in transaction 764 at 2025-12-01 08:59:50.789608+05:30.
Key (c)=(4); existing local row (4, 4, 4); remote row (2, 3, 4).
CONTEXT: processing remote data for replication origin "pg_16394"
during message type "INSERT" for replication target relation
"public.conf_tab" in transaction 759, finished at 0/017A7380

The idea to split it as per your suggestion, and assuming we agree
that additional row details are required for conflict/resolution based
on my above explanation.

LOG: conflict (multiple_unique_conflicts) detected on relation
"public.conf_tab"
DETAIL: Key already exists in unique index "conf_tab_pkey", modified
locally in transaction 764 at 2025-12-01 08:59:50.789608+05:30.
Key (a)=(2); existing local row (2, 2, 2); remote row (2, 3, 4).
LOG: conflict (multiple_unique_conflicts) detected on relation
"public.conf_tab"
DETAIL: Key already exists in unique index "conf_tab_b_key", modified
locally in transaction 764 at 2025-12-01 08:59:50.789608+05:30.
Key (b)=(3); existing local row (3, 3, 3); remote row (2, 3, 4).
…
ERROR: stopping replication because of previously-logged errors
CONTEXT: processing remote data for replication origin "pg_16394"
during message type "INSERT" for replication target relation
"public.conf_tab" in transaction 759, finished at 0/017A7380

OR

DETAIL: key (a)=(2) already exists in unique index "conf_tab_pkey",
modified locally in transaction 764 at 2025-12-01
08:59:50.789608+05:30
existing local row (2, 2, 2)
remote row (2, 3, 4)

This is based on the below errdetail, we use somewhere else in the code.

Example:
ERROR: cannot drop table parent because other objects depend on it
DETAIL: constraint child1_parent_id_fkey on table child1 depends on
table parent
constraint child2_parent_id_fkey on table child2 depends on table parent
view parent_view depends on table parent
view parent_view2 depends on table parent
HINT: Use DROP ... CASCADE to drop the dependent objects too.

Steps:
CREATE TABLE parent ( id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE child1 ( id SERIAL PRIMARY KEY, parent_id INT
REFERENCES parent(id));
CREATE TABLE child2 ( id SERIAL PRIMARY KEY, parent_id INT
REFERENCES parent(id));
CREATE VIEW parent_view AS SELECT * FROM parent;

CREATE VIEW parent_view2 AS SELECT id, name FROM parent;
CREATE SEQUENCE parent_seq OWNED BY parent.id;
DROP TABLE parent;

>
> 2. For no-matching-row in UPDATE or DELETE, perhaps
>
> ERROR: row to be updated[deleted] does not exist
> CONTEXT: replicated UPDATE[DELETE] of row with replica identity (f1, f2)=(1, 2) in table "foo"
>
> 3. I don't understand what delete_origin_differs means or why
> it's an error condition, so I won't dare to propose new text
> for that. But the new text should make the reason clear,
> and I think the same errcontext still works.
>

You can see the information on delete_origin_differs and other
conflicts in the docs [3]. We can discuss/decide on the message of
other conflict types, once we decide multiple_unique_conflicts.

[1] - https://www.postgresql.org/docs/devel/logical-replication-conflicts.html
[2] - https://docs.oracle.com/database/timesten-18.1/TTREP/conflict.htm#TTREP634
[3] - https://www.postgresql.org/docs/devel/logical-replication-conflicts.html

--
With Regards,
Amit Kapila.