On Tue, Sep 22, 2026 at 10:56 PM Nisha Moond <[email protected]> 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
