BUG #19747: pg_dump does not pin array_nulls, so restore mangles NULL array elements

Started by PG Bug reporting formabout 2 months ago5 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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:

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 = 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] = '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 = 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 = 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=ISO -c intervalstyle=postgres -c extra_float_digits=3" to
the publisher; the apply worker parses incoming text values under the
subscriber database's settings. With array_nulls = 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 = content;\n");
+ ahprintf(AH, "SET array_nulls = on;\n");

array_nulls = on is the default and what pg_dump assumed when writing the
data, so existing dump semantics do not change.

postgres_fdw: add "SET array_nulls = 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 = on in the apply worker (the
parsing side), not in the publisher connection options.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: PG Bug reporting form (#1)
Re: BUG #19747: pg_dump does not pin array_nulls, so restore mangles NULL array elements

PG Bug reporting form <noreply@postgresql.org> writes:

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.

Meh. TBH, that GUC was past its shelf life ten years ago. What
I'd rather do about this report is just summarily remove the GUC.
If we make pg_dump issue a SET for it then we'll never be able
to remove it.

We could alternatively do what we've done with some other GUCs
whose time has passed: force them to have constant values and
accept SET commands only when the value matches. But I doubt
that this one ever got widespread enough use to justify doing that
much work. I'd rather just nuke it from orbit.

regards, tom lane

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: PG Bug reporting form (#1)
Re: BUG #19747: pg_dump does not pin array_nulls, so restore mangles NULL array elements

I wrote:

PG Bug reporting form <noreply@postgresql.org> writes:

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.

Meh. TBH, that GUC was past its shelf life ten years ago. What
I'd rather do about this report is just summarily remove the GUC.
If we make pg_dump issue a SET for it then we'll never be able
to remove it.

Concretely, about like this. (BTW, another argument for doing
this rather than fixing pg_dump is that there are a ton of other
applications that will likely misbehave if array_nulls is turned
on underneath them.)

regards, tom lane

Attachments:

t254060_2
v1-0001-Remove-array_nulls-GUC-parameter.patchtext/x-diff; charset=us-ascii; name=v1-0001-Remove-array_nulls-GUC-parameter.patchDownload+2-50
#4Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Tom Lane (#3)
Re: BUG #19747: pg_dump does not pin array_nulls, so restore mangles NULL array elements

On Tue, 2026-10-06 at 16:06 -0400, Tom Lane wrote:

I wrote:

PG Bug reporting form <noreply@postgresql.org> writes:

[misbehavior with array_nulls on]

Meh. TBH, that GUC was past its shelf life ten years ago. What
I'd rather do about this report is just summarily remove the GUC.
If we make pg_dump issue a SET for it then we'll never be able
to remove it.

Concretely, about like this. (BTW, another argument for doing
this rather than fixing pg_dump is that there are a ton of other
applications that will likely misbehave if array_nulls is turned
on underneath them.)

+1

I have never seen this used in the field. One less potential headache!

Yours,
Laurenz Albe

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Laurenz Albe (#4)
Re: BUG #19747: pg_dump does not pin array_nulls, so restore mangles NULL array elements

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Tue, 2026-10-06 at 16:06 -0400, Tom Lane wrote:

Concretely, about like this. (BTW, another argument for doing
this rather than fixing pg_dump is that there are a ton of other
applications that will likely misbehave if array_nulls is turned
on underneath them.)

+1
I have never seen this used in the field. One less potential headache!

Pushed, thanks for looking at it!

regards, tom lane