Thread: Default value of serial fields changes after restore

Today I found a strange behavior after restoring a PostgreSQL database: the schema of all serialfields default values are trimmed out.

For example:

CREATE TABLE testschema.testtable
(  id serial,   name character varying(255),   CONSTRAINT pk_testtable PRIMARY KEY (id)
) 
WITH ( OIDS = FALSE
)
;


SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value
FROM pg_attribute AS a
JOIN pg_class AS c ON a.attrelid = c.oid
JOIN pg_namespace AS n ON c.relnamespace = n.oid
LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum
WHERE a.attnum > 0  AND n.nspname = 'testschema'  AND c.relname = 'testtable' 

The id's default_value is nextval('testschema.testtable_id_seq'::regclass).

After restore, default_value changes to nextval('testtable_id_seq'::regclass) and INSERT's start to fail as the sequence can´t be found on it's schema.

Backup
$ pg_dump -F c -Z 9 -b -h localhost -U postgres -f backup dbname

Restore
$ pg_restore -U postgres -h localhost -l backup > backup.list
$ pg_restore -U postgres -h localhost --disable-triggers -O -d dbname -S postgres -Fc -L backup.list backup

Is this some backup/restore problem? What am I doing wrong? 

BTW, PostgreSQL version is 9.1.3 x64 running on Windows 7 (dev machine), but can be reproduced on Linux x64 too.

Luiz Damim <luizvd@gmail.com> writes:
> Today I found a strange behavior after restoring a PostgreSQL database: the
> schema of all serialfields default values are trimmed out.

I don't think anything's being "trimmed out".  It's the normal behavior
of regclass literals to not print the schema if the table (or sequence
in this case) is visible in the current search_path.

> After restore, default_value changes to
> nextval('testtable_id_seq'::regclass) and INSERT's start to fail as the
> sequence can´t be found on it's schema.

This claim is utter nonsense.  If you are having a problem it's not due
to the way regclass literals print.  Please show a complete example of
something failing.

            regards, tom lane

On Thu, Jul 19, 2012 at 1:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Luiz Damim <luizvd@gmail.com> writes:
>> After restore, default_value changes to
>> nextval('testtable_id_seq'::regclass) and INSERT's start to fail as the
>> sequence can´t be found on it's schema.
>
> This claim is utter nonsense.  If you are having a problem it's not due
> to the way regclass literals print.  Please show a complete example of
> something failing.

Is it possible the OP has an "alter user set search_path=..." in their
original db that's not getting backed up here because they're not
backing up globals?  Seems a more reasonable explanation to me.

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Thu, Jul 19, 2012 at 1:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This claim is utter nonsense.  If you are having a problem it's not due
>> to the way regclass literals print.  Please show a complete example of
>> something failing.

> Is it possible the OP has an "alter user set search_path=..." in their
> original db that's not getting backed up here because they're not
> backing up globals?  Seems a more reasonable explanation to me.

That might explain why the literals look different than they used to.
But I still say that if he's getting errors on INSERT, they're coming
from some other cause.  A regclass literal is just an OID under the hood
--- it cannot result in a lookup error, because the lookup's already
been done.

            regards, tom lane