Unwanted effect of search_path on default-value entries in case of serial columns

Hello everybody,

Recently we encountered a funny behaviour of PostgreSQL regarding the
default-value entry resulting from columns with the pseudo type
"serial". Actually, the string saved as the name of the sequence depends
on the search_path set when creating the table.

For PostgreSQL itself, it doesn't matter whether the name of the
sequence is qualified with the schema name or not. When using \d in
psql, it is displayed qualified if the table currently is not in the
search_path, unqualified otherwise.

When dumping the table with pg_dump, the sequence name is always saved
qualified, so dumping and restoring will add the schema name.

Here is (commented) code to reproduce it. The behaviour has been the
same since at least 9.6 and including 11.1.

hj=# create schema s1;
CREATE SCHEMA
hj=# set search_path to s1;
SET
-- Two tables are created in a schema contained in the search_path.
Whether the table name
-- is qualified or not makes no difference.
hj=# create table a1 (id serial primary key);
CREATE TABLE
hj=# create table s1.a2 (id serial primary key);
CREATE TABLE

-- One table is created in a schema NOT contained in the search_path.
hj=# set search_path to public;
SET
hj=# create table s1.a3 (id serial primary key);
CREATE TABLE

-- Displaying the table info while the tables are NOT in the search_path
-- shows the sequence names QUALIFIED for all tables.
hj=# \d s1.a1
                                   Tabelle »s1.a1«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? |           
Vorgabewert           
--------+---------+--------------+---------------+-----------------------------------
 id     | integer |              | not null      |
nextval('s1.a1_id_seq'::regclass)
Indexe:
    "a1_pkey" PRIMARY KEY, btree (id)

hj=# \d s1.a2
                                   Tabelle »s1.a2«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? |           
Vorgabewert           
--------+---------+--------------+---------------+-----------------------------------
 id     | integer |              | not null      |
nextval('s1.a2_id_seq'::regclass)
Indexe:
    "a2_pkey" PRIMARY KEY, btree (id)

hj=# \d s1.a3
                                   Tabelle »s1.a3«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? |           
Vorgabewert           
--------+---------+--------------+---------------+-----------------------------------
 id     | integer |              | not null      |
nextval('s1.a3_id_seq'::regclass)
Indexe:
    "a3_pkey" PRIMARY KEY, btree (id)

-- Displaying the table info while the tables are INCLUDED in the
search_path
-- shows the sequence names UNQUALIFIED for all tables.
hj=# set search_path to s1;
SET
hj=# \d a1
                                 Tabelle »s1.a1«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? |         
Vorgabewert          
--------+---------+--------------+---------------+--------------------------------
 id     | integer |              | not null      |
nextval('a1_id_seq'::regclass)
Indexe:
    "a1_pkey" PRIMARY KEY, btree (id)

hj=# \d a2
                                 Tabelle »s1.a2«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? |         
Vorgabewert          
--------+---------+--------------+---------------+--------------------------------
 id     | integer |              | not null      |
nextval('a2_id_seq'::regclass)
Indexe:
    "a2_pkey" PRIMARY KEY, btree (id)

hj=# \d a3
                                 Tabelle »s1.a3«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? |         
Vorgabewert          
--------+---------+--------------+---------------+--------------------------------
 id     | integer |              | not null      |
nextval('a3_id_seq'::regclass)
Indexe:
    "a3_pkey" PRIMARY KEY, btree (id)

-- Displaying the info about default values from system tables shows a
-- difference. ONLY for a3 the sequence name is qualified.
hj=# SELECT d.adsrc AS default_value
hj-# FROM   pg_catalog.pg_attribute a
hj-# LEFT   JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)
hj-#                                      = (d.adrelid,  d.adnum)
hj-# WHERE  a.attrelid = 's1.a1'::regclass
hj-# AND    a.attname = 'id';
         default_value         
--------------------------------
 nextval('a1_id_seq'::regclass)
(1 Zeile)

hj=#
hj=# SELECT d.adsrc AS default_value
hj-# FROM   pg_catalog.pg_attribute a
hj-# LEFT   JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)
hj-#                                      = (d.adrelid,  d.adnum)
hj-# WHERE  a.attrelid = 's1.a2'::regclass
hj-# AND    a.attname = 'id';
         default_value         
--------------------------------
 nextval('a2_id_seq'::regclass)
(1 Zeile)

hj=#
hj=# SELECT d.adsrc AS default_value
hj-# FROM   pg_catalog.pg_attribute a
hj-# LEFT   JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)
hj-#                                      = (d.adrelid,  d.adnum)
hj-# WHERE  a.attrelid = 's1.a3'::regclass
hj-# AND    a.attname = 'id';
           default_value          
-----------------------------------
 nextval('s1.a3_id_seq'::regclass)
(1 Zeile)

Why does this impose a problem? For ordinary applications, it actually
doesn't.

But in our case, a program reading information from the system tables
compares the schemas of two databases. If they were created using the
exactly identical SQL statements (with all table names qualified, so the
search_path is never of importance), but with differently set
search_path values, they will report a difference in the schema.

This difference magically disappears after a dump and reload, because
after this, all sequence names are qualified.

So I would ask to save all sequence names in all cases schema-qualified,
independent of the search_path settings.

Regards,

Holger Jakobs

--

Holger Jakobs, Bergisch Gladbach
instant messaging: xmpp:holger(at)jakobs(dot)com
+49 178 9759012 <tel:+491789759012> oder +49 2202 817157
<tel:+492202817157>