Errata: the July post on escape_string_warning said to leave that parameter on. It was removed in PostgreSQL 19, by a commit that predates the post by almost six months. The audit at the end of this post covers it.
This series covered array_nulls in April, sorted the applications that still need it into extinct, museum pieces, and things running at an institution that does not know what it has, and told you to leave it alone. On October 7, Tom Lane deleted it. The series had gotten as far as the letter O. I would like to think the two events are unrelated.
The commit is on master only, and master is 20devel. PostgreSQL 19 (in beta as I write this) still has array_nulls; the first release without it will be PostgreSQL 20. Tom also wrote the commit that added the parameter, on November 17, 2005, so if anyone gets to do this, he does.
One SET too few
Three days earlier, BUG #19747 pointed out something that has been true since 8.2. pg_dump writes a null array element as the unquoted word NULL. Every dump opens by pinning the settings that affect how its contents will be read (client_encoding, standard_conforming_strings, and several more), and array_nulls was never on that list. Restore into a database where someone has turned it off, and the restore reads those nulls as text. On 18.6:
1 $ createdb prod 2 $ psql -q -d prod -c "CREATE TABLE t (id int, v text[]); INSERT INTO t VALUES (1, ARRAY['x', NULL, 'y'])" 3 $ createdb staging 4 $ psql -d postgres -c "ALTER DATABASE staging SET array_nulls = off" 5 ALTER DATABASE 6 $ pg_dump -d prod | psql -q -d staging > /dev/null; echo $? 7 0 8 $ psql -d prod -c "SELECT v, v[2] IS NULL AS is_null FROM t" 9 v | is_null 10 ------------+--------- 11 {x,NULL,y} | t 12 (1 row) 13 14 $ psql -d staging -c "SELECT v, v[2] IS NULL AS is_null FROM t" 15 v | is_null 16 --------------+--------- 17 {x,"NULL",y} | f 18 (1 row)
No error, exit status 0, same row count, and every null element in every text[] column is now a four-character string. An int[] column at least produces an error, since "NULL" is not an integer, although without ON_ERROR_STOP the restore carries on and leaves that table empty. The report shows the same substitution happening through postgres_fdw and logical replication; both reproduced on 18.6 on the first try.
The report’s suggested fix began with one more SET in the dump preamble. Tom’s answer was that a parameter pg_dump sets is a parameter nobody can ever remove. The project has been here before. During PostgreSQL 12 development, default_with_oids was deleted along with WITH OIDS itself; old dump files containing SET default_with_oids = false; stopped restoring cleanly, Tom complained, and eight weeks later the parameter was back. It is still there. It accepts off.
That is the usual retirement plan for a compatibility parameter: keep the name, accept one value. ssl_renegotiation_limit has taken nothing but 0 since 9.5, and as of 19, standard_conforming_strings takes nothing but on. (Tom’s commit for that one cites the default_with_oids episode as its precedent, and notes that dump scripts will go on setting it.) array_nulls skips that stage. The commit message’s reason is that there is no evidence a meaningful number of applications set or read it, and Laurenz Albe, reviewing, said he had never seen it used in the field. It also helps that no dump preamble has ever mentioned it, which makes the bug and the exit the same omission. Tom’s own summary, from the thread: “I’d rather just nuke it from orbit.”
How rare is this?
It depends on what you count. I diffed the GUC table in the source at every major release from 8.0 to this week’s master. It grew from 153 entries to 439, and along the way 49 names went missing, a little over two per release. Most were renamed (wal_keep_segments) or went down with the machinery they configured (max_fsm_pages, checkpoint_segments, old_snapshot_threshold). When the thing a parameter tunes no longer exists, nobody mourns the parameter.
The parameters that exist purely for backward compatibility are a smaller club. The “Previous PostgreSQL Versions” category has existed since 7.4. Thirteen parameters have lived in it, and six have been deleted outright: add_missing_from and regex_flavor in 9.0, sql_inheritance in 10, operator_precedence_warning in 14, escape_string_warning in 19, and now array_nulls in 20. Six in twenty-three years, two of them in consecutive releases. By this project’s standards, that is a purge. (Neutered parameters do get deleted eventually. autocommit accepted only on from 7.4 through 9.4 before it went, which gives default_with_oids something to look forward to.)
What deletion costs
A neutered parameter still accepts its one remaining value, so a configuration that spells out the default keeps working. A deleted parameter accepts nothing. On a build of today’s master:
SET array_nulls = onfails withunrecognized configuration parameter "array_nulls", and so does any connection that passes it inoptionsorPGOPTIONS.array_nulls = oninpostgresql.confprevents the server from starting. (On a reload, it gets every change in the file rejected.)- An
ALTER DATABASE ... SET, anALTER ROLE ... SET, or a function-levelSETofarray_nullsin the old cluster makespg_upgradefail partway through, afterpg_upgrade --checkhas reported the clusters compatible.
The value does not matter; on breaks all three exactly as off does. You also do not have to wait for 20 to watch it happen. Going from 18.6 to 19 beta 4, a stored escape_string_warning setting (either value) or a stored standard_conforming_strings = off fails pg_upgrade the same way, clean --check and all. (A year is a long time, and a pg_upgrade check for array_nulls may yet appear. I would not plan around it.)
So “leave it alone” needs an amendment: you have to confirm that everyone else left it alone too.
1 -- Once per cluster: database- and role-level settings. 2 SELECT coalesce(d.datname, '(all)') AS database, 3 coalesce(r.rolname, '(all)') AS role, 4 s.cfg 5 FROM pg_db_role_setting AS x 6 CROSS JOIN LATERAL unnest(x.setconfig) AS s(cfg) 7 LEFT JOIN pg_database AS d ON d.oid = x.setdatabase 8 LEFT JOIN pg_roles AS r ON r.oid = x.setrole 9 WHERE s.cfg ~ '^(array_nulls|escape_string_warning|standard_conforming_strings)='; 10 11 -- Once per database: function-level SET clauses. 12 SELECT p.oid::regprocedure AS routine, s.cfg 13 FROM pg_proc AS p 14 CROSS JOIN LATERAL unnest(p.proconfig) AS s(cfg) 15 WHERE s.cfg ~ '^(array_nulls|escape_string_warning|standard_conforming_strings)='; 16 17 -- Configuration files (superuser only). 18 SELECT sourcefile, sourceline, name, setting 19 FROM pg_file_settings 20 WHERE lower(name) IN ('array_nulls', 'escape_string_warning', 'standard_conforming_strings');
For standard_conforming_strings, only off is a problem. None of these queries can see a -c on the postmaster command line, an options entry in a client’s connection string, or a SET issued by application code or inside a function body; those take grep.
A row that says array_nulls=off means you are that institution, and that database has the restore problem right now; the fix was to delete the parameter in 20, and nothing was changed in the back branches. Find out what depends on it, then ALTER DATABASE ... RESET array_nulls. A row that says on means someone was being thorough. Remove it anyway. And if your configuration management writes out every parameter with its default, take this one out of the template before PostgreSQL 20 declines to start over it.