Re: transaction locking

On Wed, 17 Sep 2003, tom baker wrote:

> i am (probably) shooting myself in the foot, but here goes the question.
>
> inside of a begin transaction/commit block, i am attempting to insert a record
> into a parts table, and then insert a record that references the part into an
> application table. and the transaction fails with this error message:
>
> ERROR: app_part_ins_trig referential integrity violation - key referenced from
> application not found in parts
>
> i understand that since the record was inserted into the parts table *after*
> the BEGIN TRANSACTION statement, the insert into the application table cannot
> see that a record exists until a commit.

Assuming that they are both in the same transaction, the second insert
should be able to see the results of the first insert, can you send an
example sequence of inserts as well? (Also see the note below)

> and the constraint that is causing problems is:
> CREATE CONSTRAINT TRIGGER app_part_ins_trig AFTER INSERT OR UPDATE ON
> application FROM parts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
> EXECUTE PROCEDURE "RI_FKey_check_ins" ('app_part_ins_trig', 'application',
> 'parts', 'UNSPECIFIED', 'make', 'make', 'amc_part_no', 'amc_part_no');
>
> $sql = "SET CONSTRAINTS ALL DEFERRED" ;

I'm not sure if you know, but this is not going to deferr the constraint
above because it was created with NOT DEFERRABLE.