Skip site navigation (1) Skip section navigation (2)

'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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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

pgsql-odbc by date

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

pgsql-general by date

Next:From: Sebastian RitterDate: 2010-07-06 10:05:24
Subject: Re: PostgreSQL trigger execution order
Previous:From: Alban HertroysDate: 2010-07-06 09:57:14
Subject: Re: PostgreSQL trigger execution order

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group