Postgres Pro
Downloads
Home   >   mailing lists

Thread: DEFERABLE vs. NOT DEFERABLE constraints

DEFERABLE vs. NOT DEFERABLE constraints

From
"Claude Chaloux"
Date:

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

 

Attachment

Re: DEFERABLE vs. NOT DEFERABLE constraints

From
Stephan Szabo
Date:
On Tue, 24 Jul 2007, Claude Chaloux wrote:

> 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".

In 7.4, a not deferred trigger caused by a statement in a function was
still delayed until the end of the statement that called the function. As
of 8.0, a not deferred trigger is called at the end of the statement in
the function.

I think the beginning of the following thread describes part of the
reasoning:
 http://archives.postgresql.org/pgsql-hackers/2004-09/msg00140.php

Re: DEFERABLE vs. NOT DEFERABLE constraints

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Tue, 24 Jul 2007, Claude Chaloux wrote:
>> 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".

> In 7.4, a not deferred trigger caused by a statement in a function was
> still delayed until the end of the statement that called the function. As
> of 8.0, a not deferred trigger is called at the end of the statement in
> the function.

Note that if the constraint is marked DEFERRABLE INITIALLY DEFERRED,
it should be checked at transaction end in either version.  Are you
sure you really have it set to deferred?

            regards, tom lane