Hallo to everybody.
I have found out that statements in a function beheave differently than
"root" statements due to the fact that statements inside a function do
not update the db snapshot.
I already read the discussions in the archieve before 7.3 release, but i
still disagree strongly with this behaviour!
As a very simple example, if i select for update a row in a function, it
correctly waits for a commit of other transactions that lock that row,
but after this the row is still the same than before !! So at least
there is an inconsistence (i could not have been locked, since i
continue on seeing the same thing than before the changes).
But things get really bad in the following case. Suppose i want to write
a function that checks (before doing some manipulation on it) if a row
with a given primary key exists. If not it insterts it. Now, there is no
way to write such a function in a safe way, since even if before
inserting the row it locks the whole table in exclusive mode, another
concurrent call to the same function would: 1- wait on the select for update untill the first call has committed 2-
verifythat the row does not exists (since irt still sees the
previous snapshot) 3- insert it causing an error because it tries to insert a duplicate
primary key
All this without considering the really non-intuitive fact that if i try
to simulate step by step the same thing on two psql terminals (giving
the commands one by one, i mean), it beheaves differently (it works fine
without any error)!!!
I checked out if there is some discussion on this for the 7.4, but could
not find anything, so i ask directly: is there some reconsideration on
this issue? (if it affects too much performance, it could be an option).
Personally i would strongly suggest it, and i really apologize on not
having (yet) the time to really dive into postgresql sources to
contribute myself on this point instead of asking like this.
Thanks to everybody (especially to all developpers since this is really
the only point i don't like in postgres!)
Max.