Обсуждение: v6.5 foreign key trigger reposted with details

hi,

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

Yes, seems that this is an old old question, answered long time ago.
However, the key is the v6.5! Here is a quote from the RELEASE note:

       Keep the above in mind if you are using contrib/refint.*
       triggers for referential integrity. Additional technics
       are required now.

I'm learning PL/pgSQL now, no way to understand/implement the suggestion
made in the RELEASE, so, help me, please. I'm not the
dummest, I'm quite sure this question will be asked again. So,
Please help US. I put the whole paragraph of the RELEASE NOTE at
the end of the mail for your convevience.

P.S.:
I posted this at novice list. I got an anwser suggesting me to read
the README of ...contrib/spi and have a look on refint* . I read
them again, it sounds it is OLDER than the V6.5 RELEASE NOTE. I browsed the
whole mailing listing (after all the FAQ and DOC ! -- I did my homework, so,
PLEASE HELP!):

Thanks in
advance!!!!

amy

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

Note: Note that if you run a transaction in SERIALIZABLE mode then you must
execute the LOCK commands above before execution of any DML statement
(SELECT/INSERT/DELETE/UPDATE/FETCH/COPY_TO) in the transaction.


These inconveniences will disappear in the future when the ability to read
dirty (uncommitted) data (regardless of isolation level) and true
referential integrity will be implemented.
#####################################end of exerpt





_______________________________________________________________
Get Free Email and Do More On The Web. Visit http://www.msn.com

> 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