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