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
hi, Vadim,
(sorry, Vadim, I used "reply" to "vadim@krs.ru", but I mean
pgsql-general@postgreSQL.org )
I use postgresql as the backend of a website using cgi(in perl/DBI).
so,
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 -:)
A) I can not be happy ;-(
But if example above is case for you then:
1. all applications should use BEGIN/END;
B) what does that mean for perl? or just limited to PL/pgSQL--
then, isn't BEGIN/END the common syntax in PL/pgSQL?
C) can you give me an outline of PL/pgSQL for 2. and 3. ?
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.
D) I'm not sure yet ALL those can be handled by PS/pgSQL within the
postgresql? I do not need to handle all of them in perl through
dbi? If I do, how? and, can I just disable the concurrent
multiversion in V6.5?
Thanks!!!
Kai
_______________________________________________________________
Get Free Email and Do More On The Web. Visit http://www.msn.com
Import Notes
Resolved by subject fallback
amy cheng wrote:
1. all applications should use BEGIN/END;
B) what does that mean for perl? or just limited to PL/pgSQL--
then, isn't BEGIN/END the common syntax in PL/pgSQL?C) can you give me an outline of PL/pgSQL for 2. and 3. ?
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.D) I'm not sure yet ALL those can be handled by PS/pgSQL within the
postgresql? I do not need to handle all of them in perl through
dbi? If I do, how? and, can I just disable the concurrent
LOCKs can't be handled by PL/pgSQL - you have to execute
LOCKs from your application _before_ update statements.
Turn autocommit off to execute LOCKs and update statements
in one transaction (dbi will use BEGIN/END arround
your statements).
multiversion in V6.5?
No way.
Vadim