Re: [GENERAL] v6.5 foreign key trigger reposted with details

> My question is: how to handle v6.5 foreign key trigger.

...

> ###############################exerpt from V6.5 RELEASE NOTE:
> Because readers in 6.5 don't lock data, regardless of transaction isolation
> level, data read by one transaction can be overwritten by another. In other
> words, if a row is returned by SELECT it doesn't mean that this row really
> exists at the time it is returned (i.e. sometime after the statement or
> transaction began) nor that the row is protected from being deleted or
> updated by concurrent transactions before the current transaction does a
> commit or rollback.
>
> To ensure the actual existence of a row and protect it against concurrent
> updates one must use SELECT FOR UPDATE or an appropriate LOCK TABLE
> statement. This should be taken into account when porting applications from
> previous releases of Postgres and other environments.
>
> Keep the above in mind if you are using contrib/refint.* triggers for
> referential integrity. Additional technics are required now. One way is to
> use LOCK parent_table IN SHARE ROW EXCLUSIVE MODE command if a transaction
> is going to update/delete a primary key and use LOCK parent_table IN SHARE
> MODE command if a transaction is going to update/insert a foreign key.

If one transaction deletes primary key P and another transaction
at the same time inserts foreign key F (P == F) then trigger fired
by first transaction will not see F just inserted by second transaction
and trigger fired by second transaction will not see that P is being
deleted. So, both transaction could commit and ref. integrity
would be broken.

Note that if in any time only one application will update
primary/foreign tables then you can forget all above, don't
worry about RELEASE NOTES and be happy -:)
But if example above is case for you then:

1. all applications should use BEGIN/END;
2. in first transaction (deleting/updating something in primary table)
   you have to execute LOCK _primary_table_ IN SHARE ROW EXCLUSIVE MODE
   _before_ execution of any update/delete statement for the
   primary table;
3. in second transaction (inserting/updating something in foreign
   table) you have to execute LOCK _primary_table_ IN SHARE MODE
   _before_ execution of any insert/update statement for the
   foreign table.

Vadim