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

Поиск
Список
Период
Сортировка
Искать

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

От:
PG 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.




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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
PG Bug reporting form  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


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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
I wrote:
> PG Bug reporting form  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

From 7e560c03b0304aa85c7411396ed634dc62e2a495 Mon Sep 17 00:00:00 2001
From: Tom Lane 
Date: Tue, 6 Oct 2026 16:01:33 -0400
Subject: [PATCH v1] Remove "array_nulls" GUC parameter.

This backwards-compatibility hack was introduced twenty years ago (in
8.2), and has surely been of no value for at least the last ten years.
However, we shouldn't just leave it sit, because as was just pointed
out it creates a dump/reload hazard: if it's set in the receiving
environment then array contents will not be restored correctly.

Our precedent for retiring some other comparable parameters, such as
standard_conforming_strings, has been to leave the parameter in
existence but force it to a fixed value.  I judge however that
that's not worth the trouble here, because there's no reason to
believe that a non-negligible number of applications are either
setting or reading this value.  Let's just delete it in the master
branch and be done with it.

Bug: #19747
Reported-by: Ke 
Author: Tom Lane 
Discussion: https://postgr.es/m/19747-69b01e7fc190cd58@postgresql.org
---
 doc/src/sgml/array.sgml                       |  5 +---
 doc/src/sgml/config.sgml                      | 26 -------------------
 src/backend/utils/adt/arrayfuncs.c            |  7 +----
 src/backend/utils/misc/guc_parameters.dat     |  7 -----
 src/backend/utils/misc/postgresql.conf.sample |  1 -
 src/include/utils/array.h                     |  5 ----
 6 files changed, 2 insertions(+), 49 deletions(-)

diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml
index ce338c770c9..5b8ef5cabe6 100644
--- a/doc/src/sgml/array.sgml
+++ b/doc/src/sgml/array.sgml
@@ -756,10 +756,7 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
    If the value written for an element is NULL (in any case
    variant), the element is taken to be NULL.  The presence of any quotes
    or backslashes disables this and allows the literal string value
-   NULL to be entered.  Also, for backward compatibility with
-   pre-8.2 versions of PostgreSQL, the  configuration parameter can be turned
-   off to suppress recognition of NULL as a NULL.
+   NULL to be entered.
   
 
   
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index f36fbb60101..e537b3b0043 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -11941,32 +11941,6 @@ dynamic_library_path = '/usr/local/lib/postgresql:$libdir'
 
      
 
-     
-      array_nulls (boolean)
-      
-       array_nulls configuration parameter
-      
-      
-      
-       
-        This controls whether the array input parser recognizes
-        unquoted NULL as specifying a null array element.
-        By default, this is on, allowing array values containing
-        null values to be entered.  However, PostgreSQL versions
-        before 8.2 did not support null values in arrays, and therefore would
-        treat NULL as specifying a normal array element with
-        the string value NULL.  For backward compatibility with
-        applications that require the old behavior, this variable can be
-        turned off.
-       
-
-       
-        Note that it is possible to create array values containing null values
-        even when this variable is off.
-       
-      
-     
-
      
       backslash_quote (enum)
       stringsbackslash quotes
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index ef66182b047..f1f6336e5c8 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -38,11 +38,6 @@
 #include "utils/typcache.h"
 
 
-/*
- * GUC parameter
- */
-bool		Array_nulls = true;
-
 /*
  * Local definitions
  */
@@ -925,7 +920,7 @@ unquoted_element:
 					elembuf->len = dstlen;
 					*srcptr = p;
 					/* Check if it's unquoted "NULL" */
-					if (Array_nulls && !has_escapes &&
+					if (!has_escapes &&
 						pg_strcasecmp(elembuf->data, "NULL") == 0)
 						return ATOK_ELEM_NULL;
 					else
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index c57441f7d98..380679a76c0 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -105,13 +105,6 @@
   max => 'INT_MAX / 2',
 },
 
-{ name => 'array_nulls', type => 'bool', context => 'PGC_USERSET', group => 'COMPAT_OPTIONS_PREVIOUS',
-  short_desc => 'Enables input of NULL elements in arrays.',
-  long_desc => 'When turned on, unquoted NULL in an array input value means a null value; otherwise it is taken literally.',
-  variable => 'Array_nulls',
-  boot_val => 'true',
-},
-
 { name => 'authentication_timeout', type => 'int', context => 'PGC_SIGHUP', group => 'CONN_AUTH_AUTH',
   short_desc => 'Sets the maximum allowed time to complete client authentication.',
   flags => 'GUC_UNIT_S',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e759f06b50f..01f98de8a9f 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -892,7 +892,6 @@
 
 # - Previous PostgreSQL Versions -
 
-#array_nulls = on
 #backslash_quote = safe_encoding        # on, off, or safe_encoding
 #lo_compat_privileges = off
 #quote_all_identifiers = off
diff --git a/src/include/utils/array.h b/src/include/utils/array.h
index 88e4f4d70d8..e7ee184034e 100644
--- a/src/include/utils/array.h
+++ b/src/include/utils/array.h
@@ -343,11 +343,6 @@ typedef struct ArrayIteratorData *ArrayIterator;
 	 (a)->xpn.lbound : ARR_LBOUND((ArrayType *) (a)))
 
 
-/*
- * GUC parameter
- */
-extern PGDLLIMPORT bool Array_nulls;
-
 /*
  * prototypes for functions defined in arrayfuncs.c
  */
-- 
2.52.0

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

От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
On Tue, 2026-10-06 at 16:06 -0400, Tom Lane wrote:
> I wrote:
> > PG Bug reporting form  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


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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Laurenz Albe  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


FAQ