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- verify that 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.
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
