DEFERABLE vs. NOT DEFERABLE constraints

Hello,

I will be switching from PostgreSQL 7.4 to 8.2 operationally. So far, the transition goes smoothly but one thing is giving me headache when trying to make some pieces of code work on my newer version of PSQL, especially the code of one very simple function.

Basically, the function inserts a row in a table (in the database I’m testing with) that has a reference (foreign key set) to another table. Under 7.4, I have no problem inserting the rows in the two tables even when inserting in the table which column is being referenced first. On 8.2 however, I get an error message “constraint violation”.

I know that the later version does things well/better but I don’t get it - the 2 documentations (7.4 & 8.2) say the same thing about DEFERABLE and NOT DEFERABLE on a table’s constraint. Also, I’m pretty sure that all the settings in Postgres.conf are the same on the 2 databases but I might be missing something…

Was there a bug in 7.4 that was allowing my old code to work or is there a switch somewhere I’m missing?

Thanks for all your help, regards!

Claude Chaloux

Analyste "Q&A / R&D" Analyst

Above Sécurity Montréal (www.abovesecurity.com)

à 1919 boul. Lionel-Bertrand suite 203, Boisbriand, Qc, J7H 1N8         

450.430.8166 ext: 2115

claude.chaloux@abovesecurite.com