'default nextval()' loses schema-qualification in dump ?

From: Arnaud Lesauvage <arnaud(dot)listes(at)codata(dot)eu>
To: pgsql-odbc(at)postgresql(dot)org, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: 'default nextval()' loses schema-qualification in dump ?
Date: 2010-07-06 10:02:30
Message-ID: 4C32FF36.8030604@codata.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-odbc

Hi lists !

We ran into a problem after restoring a database dump.
Postgesql version is 8.4.3 on Win32.

The tables are linked with psqlODBC (v8.03.0400) and have SERIAL primary
keys (that's why I cross-posted to psql-odbc).
Before the restore, insertion in MSAccess was fine. After the restore,
insertions failed with a 'currval(<sequence>) not set' error.

After some research, we found in psqlODBC's log that before the restore
psqlODBC was getting the sequence's nextval with a schema qualified
call, and after the restore the call was not schema qualified.
I checked in pg_attrdef before and after the dump/restore, and indeed
the "default nextval()" on this problematic table loses it's schema
qualification in the process.

Why this doesn't matter in psql, I don't know (the schema in question is
not in the search_path), but this does break psqlODBC's handling of
"auto numbering" columns.

I tried a simple dump of the table structure, and indeed the restore
sets the search_path first and then creates the table without schema
qualification, neither for the table nore for the sequence.

Is this by design ? How can I work around this ?
I am not sure this is really normal, since the restored database's
strucure is not matching perfectly the original one's.

Thanks a lot for your thoughts and help on this matter.

Regards,
Arnaud Lesauvage

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sebastian Ritter 2010-07-06 10:05:24 Re: PostgreSQL trigger execution order
Previous Message Alban Hertroys 2010-07-06 09:57:14 Re: PostgreSQL trigger execution order

Browse pgsql-odbc by date

  From Date Subject
Next Message Tom Lane 2010-07-06 14:22:14 Re: 'default nextval()' loses schema-qualification in dump ?
Previous Message Jose Torres 2010-07-02 21:43:31 Concern about psqlodbc driver when migrating to PostgreSql 8.4