Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xDh9U-00000000ef1-0K6T for pgsql-bugs@arkaria.postgresql.org; Mon, 05 Oct 2026 11:46:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xDh8T-00000009rvq-0Mxt for pgsql-bugs@arkaria.postgresql.org; Mon, 05 Oct 2026 11:45:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xDEcu-000000043BR-4B6K for pgsql-bugs@lists.postgresql.org; Sun, 04 Oct 2026 05:19:08 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xDEcs-00000000JQ9-1vq3 for pgsql-bugs@lists.postgresql.org; Sun, 04 Oct 2026 05:19:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=B5SRD89z8iWHxFa0MNV8PqarYP+fE/DV5uvWTNff1yg=; b=DONlBRbCIM2vHAmA/8i/oZp4l2 OheitBPoHEWPOl1+5aVsbs48lZBSeSNPSBLn9ex4ll19wOyZ46Hi9Wlli1Tov7tPKClSjg/4L5u1n XbZqevp6Tyj1b9C/kdn1pBv/re9mLYIcIZC3S5VAIaaUiFWCj89MGl+t5Z1AidxB1IWJ3VLSH6WnJ Gy06WBL0+y9D2bxvf5v2kB++2uLrZwwip1Nv3UTsk8xzGC808yo2VU02PW4w7O6RknBOy2tEXNJwk ZTQHLSTmRUKY5FphQxrTHtc5W2m5/Yw0Z0dhYImi55uOnQceW79JWNYSaHb19XcL+v0tMERYBuMIN qagzR6xw==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xDEcr-00000000uid-0bl2 for pgsql-bugs@lists.postgresql.org; Sun, 04 Oct 2026 05:19:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xDEcq-00000002S8M-00xw for pgsql-bugs@lists.postgresql.org; Sun, 04 Oct 2026 05:19:04 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19747: pg_dump does not pin array_nulls, so restore mangles NULL array elements To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: kehan5800@gmail.com Reply-To: kehan5800@gmail.com, pgsql-bugs@lists.postgresql.org Date: Sun, 04 Oct 2026 05:19:03 +0000 Message-ID: <19747-69b01e7fc190cd58@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19747 Logged by: Ke Email address: kehan5800@gmail.com PostgreSQL version: 18.6 Operating system: Ubuntu 22.04.2 x86_64 Description: =20 pg_dump writes a null array element as the unquoted word NULL. Whether the array input function reads that as a null depends on array_nulls, which is PGC_USERSET and can be persisted with ALTER DATABASE/ROLE ... SET. The dump's preamble (_doSetFixedOutputState(), src/bin/pg_dump/ pg_backup_archiver.c:3431) pins the other input-side settings (client_encoding, standard_conforming_strings, check_function_bodies, xmloption, row_security, ...) but not array_nulls, so the restoring session inherits the target database's value. Reproduction: createdb src psql -d src -c "CREATE TABLE t_txt (id int PRIMARY KEY, v text[]); INSERT INTO t_txt VALUES (1, ARRAY['x', NULL, 'y']); CREATE TABLE t_int (id int PRIMARY KEY, v int[]); INSERT INTO t_int VALUES (1, ARRAY[1, NULL, 3]);" createdb dst psql -d postgres -c "ALTER DATABASE dst SET array_nulls =3D off" pg_dump -d src -f all.sql psql -d dst -f all.sql ERROR: invalid input syntax for type integer: "NULL" CONTEXT: COPY t_int, line 1, column v: "{1,NULL,3}" and with only the text[] table dumped (pg_dump -t t_txt) there is no error at all: source : {x,NULL,y} v[2] IS NULL -> true restored : {x,"NULL",y} v[2] IS NULL -> false, v[2] =3D 'NULL' psql exits 0 and row counts match; only comparing the array values shows the change. text[], citext[], domains over text and composites containing them change silently; int[] and other element types that cannot parse "NULL" fail the COPY. Expected: a dump restores to the same data regardless of the target database's array_nulls, as it already does for DateStyle, IntervalStyle, standard_conforming_strings, xmloption, etc. Actual: null elements become the four-character string "NULL" (text-like element types) or the table fails to load (other element types). The same omission exists in the two other places that copy pg_dump's list of pinned settings: - contrib/postgres_fdw/connection.c:839-845 (configure_remote_session), whose comment says "This logic should match what pg_dump does": it sets datestyle, intervalstyle and extra_float_digits on the remote session, not array_nulls. With array_nulls =3D off on the remote database, an INSERT of ARRAY['x', NULL, 'y'] through a foreign table stores {x,"NULL",y} remotely. In the other direction, with array_nulls =3D off= in the local database, a remote row {a,NULL,b} reads back through the foreign table as {a,"NULL",b} (v[2] IS NULL false locally, true on the remote). - src/backend/replication/libpqwalreceiver/libpqwalreceiver.c:199 passes "-c datestyle=3DISO -c intervalstyle=3Dpostgres -c extra_float_digits= =3D3" to the publisher; the apply worker parses incoming text values under the subscriber database's settings. With array_nulls =3D off on the subscriber database, publisher {a,NULL,b} arrives as {a,"NULL",b} with nothing logged; for int[] the apply worker errors with "invalid input syntax for type integer" and is restarted repeatedly. All three were reproduced on master by the attached script. Suggested fix: pg_backup_archiver.c, _doSetFixedOutputState(): ahprintf(AH, "SET xmloption =3D content;\n"); + ahprintf(AH, "SET array_nulls =3D on;\n"); array_nulls =3D on is the default and what pg_dump assumed when writing t= he data, so existing dump semantics do not change. postgres_fdw: add "SET array_nulls =3D on" in configure_remote_session() for the write direction; for the read direction the local input conversion (make_tuple_from_result_row) would need the same setting forced locally, e.g. via the GUC nest level that set_transmission_modes() already uses. logical replication: force array_nulls =3D on in the apply worker (the parsing side), not in the publisher connection options.