Backend crashes on DDL statements longer than ~1100 bytes (self-compiled 2.1.8, PG 17.10)

[gzuco]

Summary

On a self-compiled pgactive 2.1.8 node, any DDL statement whose text exceeds roughly 1100 bytes crashes the backend. The connection is closed (server closed the connection unexpectedly), the postmaster survives, and nothing is logged by the server.

The content of the statement is irrelevant — only its length matters.

Reproduction

On a database with the pgactive extension installed, as a superuser:

-- passes
CREATE OR REPLACE FUNCTION public.t() RETURNS int LANGUAGE plpgsql AS $f$
BEGIN
-- <1000 'x' characters>
  RETURN 1;
END; $f$;

-- crashes the backend
CREATE OR REPLACE FUNCTION public.t() RETURNS int LANGUAGE plpgsql AS $f$
BEGIN
-- <1100 'x' characters>
  RETURN 1;
END; $f$;

The function body is meaningless on purpose: a comment is enough to trigger it. Measured threshold: 1080 bytes passes, 1100 bytes crashes (total statement length). The boundary is not sharp, which is what one would expect from a memory overflow.

What it is not

Verified by elimination:

Hypothesis	Result
Specific SQL construct (CTEs, NOT EXISTS, LEFT JOIN, SELECT *, function calls)	ruled out — each passes on its own
CREATE OR REPLACE of an existing object	ruled out — crashes with a brand-new name too
plpgsql body validation	ruled out — SET check_function_bodies = off changes nothing
Client library	ruled out — same crash from psql and from psycopg2
Language	happens with both LANGUAGE sql and LANGUAGE plpgsql
Schema qualification of referenced tables	irrelevant

The only pgactive event trigger present is pgactive_truncate_trigger_add (evtenabled = 'A'). Reading src/pgactive_ddlrep_truncate.c, that handler returns early for anything that is not CREATE TABLE, so the crash likely happens elsewhere in the DDL path.

Environment

	Affected node	Unaffected
pgactive	2.1.8	2.1.8 (RDS)
PostgreSQL	17.10	17.10
OS / build	Debian bookworm, built from source (./configure && make, no extra flags), postgres:17.10-bookworm image	Amazon RDS binary

Same extension version and same PostgreSQL version on both sides — only the build differs. A third database without pgactive accepts the identical DDL without problems.

pgactive.skip_ddl_replication = on on the affected node.

Impact

Any schema migration containing a moderately large DDL statement — a function with a non-trivial body, a wide CREATE TABLE, a complex view — fails on the node. Because the failure surfaces as a dropped connection with no server-side log entry, the length limit is very hard to guess: it took us several hours and a bisection on statement size to find it.

Transactions roll back cleanly, so no corruption was observed.

Notes

Happy to provide more detail, run additional tests on the affected node, or try a build with different compiler flags if that would help narrow it down.

[gzuco]

This is not limited to DDL: large read-only statements crash the backend too

While investigating #327 on the same self-compiled node (PostgreSQL 17.11 on Debian, pgactive 2.1.8 built from source), I hit this bug twice with plain SELECTs against system catalogs — no DDL involved, no write of any kind. The session was even running with default_transaction_read_only = on.

Case 1 — pg_dump cannot be used against this node at all.

$ pg_dump --schema-only --no-owner -t public.product_feature "$DSN"
pg_dump: error: query failed: server closed the connection unexpectedly

The failing statement is pg_dump's own catalog query for table attributes, which is several kilobytes long — well above the ~1100 byte threshold reported here. This makes pg_dump, pg_dumpall and anything built on them unusable against an affected node, which is a rather sharp practical consequence.

Case 2 — a ~2.5 KB catalog query of my own, a single SELECT with two CTEs over pg_index/pg_attribute/pg_collation: same result, server closed the connection unexpectedly.

It is not the network. Case 2 was run over a Tailscale tunnel with TCP keepalives explicitly enabled (keepalives=1 keepalives_idle=15 keepalives_interval=5 keepalives_count=6), which is the configuration we adopted precisely to rule out tunnel-induced disconnects. Splitting the same work into several small queries over the same connection works fine, every time.

Blast radius. Checked immediately after each crash:

pg_postmaster_start_time()  unchanged (still the previous day)
pg_replication_slots        active = true, wal_status = reserved
pgactive_nodes              both nodes 'r'

so only the backend dies — the postmaster does not restart and the cluster is not recycled. Consistent with what is already described in this issue for DDL.

Practical workaround for anyone hitting this: keep diagnostic statements short as well, extract raw rows with small queries and do the aggregation client-side. That is how we ended up producing our schema report.

Happy to provide the exact failing statements if a reproducer outside pg_dump is useful.

[gzuco]

Retraction: this is not a pgactive bug. It was a path MTU black hole.

I opened this issue and I was wrong — including the comment I posted earlier today extending it to read-only statements. Nothing here is caused by pgactive or by PostgreSQL. Sorry for the noise, and thanks to anyone who spent time reading it.

Root cause. The node is reached over Tailscale, whose interface MTU is 1280. On this particular path only about 1240 bytes get through, and oversized packets are dropped silently — no ICMP "fragmentation needed", so Path MTU Discovery never learns about it. Any SQL statement above ~1100 bytes sent to the node from outside never reached the server at all.

How I finally saw it — five checks, none of which require touching the database:

1. The same 3000-byte statement succeeds against RDS and fails against the node. RDS is reached through a Tailscale subnet route; the node through tailscale serve --tcp=5432, a userspace TCP proxy. Same mesh, different mechanism.
2. PostgreSQL logs nothing at all in that window, and pg_postmaster_start_time() is unchanged. After a real backend crash the postmaster always restarts the cluster. No backend ever died — I had misread "server closed the connection" as "the server died".
3. It is asymmetric. SELECT length(repeat('x',1000000)) returns fine — the node happily streams 1 MB to me — while I cannot send it 1.2 kB. Only the client→server direction breaks.
4. The threshold moved over time: ~1090 bytes on Aug 10 (path was via a DERP relay), ~1175 bytes on Aug 15 (direct connection). A fixed-size buffer does not drift; encapsulation overhead does.
5. ping -f -l over the tailnet: 1228-byte packets get replies, 1268-byte packets vanish with no response at all.

Confirmation. Lowering the MTU only on the client (netsh interface ipv4 set subinterface "Tailscale" mtu=1200) made statements of 20,000 bytes go through, with no change whatsoever to the server, to pgactive, or to PostgreSQL.

Fix we applied, for anyone landing here with the same symptom: set TS_DEBUG_MTU=1200 on the node rather than on each client. The MSS is advertised by the node's userspace netstack, so lowering it there makes every client send segments the path can carry, with no per-machine configuration. Verified with a client back at the default 1280 MTU.

What made this hard to see, in case it helps someone else: the symptom is indistinguishable from a server-side crash, it is perfectly reproducible, it depends only on statement length and never on content, and it disappears entirely on any other network path — which is exactly what a "self-compiled node only" bug would look like. The three questions that would have settled it on day one: did the postmaster restart? did the server log anything? does the same statement work over a different path?

Please feel free to close this. Happy to leave it open with a corrected title if you think it has value as a troubleshooting reference for other Tailscale/overlay-network users.