Обсуждение: Unwanted effect of search_path on default-value entries in case ofserial 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" == Holger Jakobs <holger@jakobs.com> writes:

 Holger> Hello everybody,

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

 [...]

 Holger> -- Displaying the info about default values from system tables shows a
 Holger> -- difference. ONLY for a3 the sequence name is qualified.
 Holger> hj=# SELECT d.adsrc AS default_value

You should never look at the adsrc column for any reason, it's garbage.
(And it's being removed in pg12, not before time.)

What adsrc stores is the actual text of the original default expression.
The most obvious way that this is garbage is because it doesn't track
renaming of objects: if you rename a sequence, function, or whatever
that was mentioned in a default, then pg will automatically pick up the
change (because adbin, which is what actually gets evaluated, only
stores the OIDs and not names), but adsrc will reflect the old name.

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

 Holger> But in our case, a program reading information from the system
 Holger> tables compares the schemas of two databases.

Then the problem is that your program is looking at the garbage in
adsrc, rather than doing what it should do which is to use pg_get_expr
to deparse adbin back to an expression.

-- 
Andrew (irc:RhodiumToad)