pg_dump not appending sequence to default values

From: Andy Shellam <andy-lists(at)networkmail(dot)eu>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: pg_dump not appending sequence to default values
Date: 2009-06-11 18:42:42
Message-ID: 4A315022.8070308@networkmail.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I've come across an issue with pg_dump from 8.3.7 (running on Windows.)
I'm using pg_dump to dump the schema only of the database for a system
I'm currently displaying.

The other day I had to re-create the database using the latest dump, and
for a lot of the tables I now get the error "relation xxx does not
exist" when adding a record, and I've found out it's because the
nextval() default value isn't correctly re-created with the schema name.

Here's an example - the table "tax" in the "product" schema has a
default value for the primary key field of
"nextval('product.tax_id'::regclass)"

When I pg_dump the schema, the resulting SQL is:

...
CREATE SCHEMA product;
...
SET search_path = product, pg_catalog;
...
CREATE SEQUENCE tax_id
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
...
CREATE TABLE tax (
id smallint DEFAULT nextval('tax_id'::regclass) NOT NULL
);

Notice how "product.tax_id" has now become just "tax_id" so when I now
insert a record into that table, it complains "relation 'tax_id' does
not exist" and I have to manually edit it.

Is this a known issue? I know a work-around is to include every schema
name in the user's search path, but it's still annoying it's not
re-created as it was originally.

Thanks,
Andy

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2009-06-11 19:16:53 Re: pg_dump not appending sequence to default values
Previous Message Emanuel Calvo Franco 2009-06-11 15:01:10 Re: Sobre Actualizacion