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