pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: kehan5800@gmail.com
Subject: BUG #19747: pg_dump does not pin array_nulls, so restore mangles NULL array elements
Date: Sun, 04 Oct 2026 05:19:03 +0000
Message-ID: <19747-69b01e7fc190cd58@postgresql.org> (raw)

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.








view thread (5+ messages)  latest in thread

Message-ID: <19747-69b01e7fc190cd58@postgresql.org>
Permalink:  ../19747-69b01e7fc190cd58@postgresql.org/
Also on:    postgresql.org/message-id/19747-69b01e7fc190cd58@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, kehan5800@gmail.com
  Subject: Re: BUG #19747: pg_dump does not pin array_nulls, so restore mangles NULL array elements
  In-Reply-To: <19747-69b01e7fc190cd58@postgresql.org>
  References: <19747-69b01e7fc190cd58@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox