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

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.