Re: Proposal: Conflict log history table for Logical Replication

On Tue, Sep 22, 2026 at 10:56 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
>
> Hi,
>
> While reviewing the v74 patches for tuple-size limitations, I found
> two cases where the column/tuple size can still exceed the 1GB limit
> and cause a crash.
>
> Case-A: RI-Full on Pub can still lead to error on Sub
> ----------------------------------------------------------------------
> This is rare case, as it requires:
> - pub has RI-Full
> - sub has PK on a text(varlen) column
> - sub table has no pre-existing row to see delete_mising
>
> -- [PUB]
> CREATE TABLE big (a int, b text NOT NULL);
> ALTER TABLE big REPLICA IDENTITY FULL; -- all columns
> INSERT INTO big VALUES (1, repeat(chr(1), 200000000)); -- 200MB, b
> is NOT indexed here
>
> -- [SUB] -- PK on b: the subscriber's RI, unindexed on the publisher
> CREATE TABLE big (a int, b text NOT NULL, PRIMARY KEY (b));
>
> -- [SUB] -- pick up the table WITHOUT copying the row, so it exists
> only on the publisher
> ALTER SUBSCRIPTION s1 REFRESH PUBLICATION WITH (copy_data = false);
> -- copy_data = false matters: if the row were copied, the
> subscriber's own btree would reject it due to index size bound.
>
> -- [PUB] -- sends the full old tuple, including the 200MB b
> DELETE FROM big WHERE a = 1;
>
> On Sub:
> ERROR: string buffer exceeds maximum allowed length (1073741823 bytes)
> DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by
> 32 more bytes.
> ~~~~
>
> Case-B: User-defined types
> --------------------------------------
> Test case for the user-defined type, where even a 8-byte per-column
> cap would not catch it -
>
> -- BOTH nodes
> CREATE TYPE color AS ENUM ('red', 'green');
>
> CREATE FUNCTION color_to_json(color) RETURNS json AS $$
> SELECT ('"' || repeat('x', 600000000) || '"')::json
> $$ LANGUAGE sql IMMUTABLE;
>
> CREATE CAST (color AS json) WITH FUNCTION color_to_json(color);
>
> CREATE TABLE ct (a int, b color NOT NULL, c color NOT NULL, PRIMARY KEY (b, c));
>
> -- Cheap local check first — no replication:
> INSERT INTO ct VALUES (1, 'red', 'green');
> SELECT pg_column_size(b), pg_column_size(c) FROM ct; -- 4 and 4
> SELECT length(row_to_json(t)::text) FROM ct t;
> -- expect: ERROR: string buffer exceeds maximum allowed length
> (1073741823 bytes)
>
> -- [PUB] note: relreplident is 'd', NOT full -- the PK is the replica identity
> INSERT INTO ct VALUES (1, 'red', 'green');
> -- wait for catchup
> -- [SUB]
> DELETE FROM ct;
> -- [PUB]
> DELETE FROM ct WHERE a = 1;
>
> On Sub:
> ERROR: string buffer exceeds maximum allowed length (1073741823 bytes)
> ~~~
>
> A solution approach for both of these issues:
> ---------------------------------------------------------
> Case-B: when a column's type has a user-defined cast to json,
> row_to_json() uses that cast instead of the type's normal output
> function. The cast can return anything of any size no matter how small
> the stored value is, so a 4-byte key can produce more than 1GB of json
> and break apply. It applies inside arrays and composites too, because
> row_to_json() walks into each element.
>
> The LOG case never had this problem, because it prints each key column
> with the type's output function and a cast cannot replace that. So we
> do the same: render each value with its output function and build the
> json object ourselves. No cast is then reachable at any nesting depth.
>
> The cost is that values become json strings instead of numbers,
> booleans or nested objects. With a int PRIMARY KEY, {"a":1} becomes
> {"a":"1"}, and a jsonb key becomes a quoted string.
> This does not reduce what can be queried. "->>" still returns a value
> that can be cast back to the column’s type, so
> (replica_identity->>'a')::int can retrieve the key and be used to find
> the row. Only querying inside a container key requires one extra cast,
> e.g. (replica_identity->>'doc')::jsonb->>'x'.
>
> Case A: With case B handled, adding a per-column size cap before
> rendering becomes straightforward. 1kB looks like the right size.
> Roughly, the rendered value per key column is
> escape_json(typoutput(value)) plus small overhead, and:
> - escape_json expands at most 6x, since every byte below 0x20 becomes \u00XX
> - INDEX_MAX_KEYS bounds the column count at 32
> - the cap bounds each varlena input at 1kB
>
> Among built-in types only numeric produces output vastly larger than
> its storage: length(1e131071::numeric::text) is 131,072, so 10 bytes
> becomes 131kB. Repeating that inside a container (array, jsonb or
> multirange) gives at most about 11,000x per stored byte. See the case
> at [1].
>
> So the worst case at 1kB is 1kB * 11,000 * 32 = ~360MB, a 3x margin
> below the 1GB limit. 2kB gives ~720MB, only 1.5x, which seems thin.
> 4kB exceeds 1GB outright. Hence 1kB.
>
> Fixed-length columns are not size-checked, as their output is bounded
> by construction and adds only a few MB across 32 columns.

I agree with the solutions in attached patches. I don't think we have
any better way to deal with these cases rigth now. But let's see what
others have to say on this.

> What remains:
> Together these cover every built-in type, scalar and container alike.
> The one case neither handles is a user-defined type whose own output
> function renders far more than its input; no cap on the input can
> detect that. Such a function has to be written in C, since a SQL
> function cannot return cstring, so it sits at the same level as
> replacing a built-in output function. IMO, that seems acceptable to
> leave for now.

I agree.

> Thoughts?
>
> Attached are the updated patches:
> v75-001: Merged v74 patches 001 and 002 into a single patch.
> v75-002: Includes the discussed fix, along with TAP tests covering
> both cases. Also updated the documentation with an example.
>
> [1] https://www.postgresql.org/message-id/CABdArM7SWb7Fu%2Bz75mqUuD846CdfHYi%3D7U1sKhY%2BFMh6j4ae5A%40mail.gmail.com
>
> --
> Thanks,
> Nisha