From: | Luiz Damim <luizvd(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Default value of serial fields changes after restore |
Date: | 2012-07-19 16:37:16 |
Message-ID: | CAK_xzjxX91qFiOKduWU33DUvHgi+RVK_ZVemMAhEvUxLayizsQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2012-07-19 17:26:07 | Re: Trouble with NEW |
Previous Message | Younus | 2012-07-19 16:30:06 | Re: How to stop a query |