On Thu, 2003-09-11 at 11:57, Max Jacob wrote:
> 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- 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!)
>
There is no discussion of it that I am aware of. Most of the push back
against this behavior seems to stem from a concern that changing this
behavior could cause unforeseen problems down the road. Personally I
find this a weak argument since the change would solve a currently known
problem, and doesn't seem to introduce any immediate problems. There
are two proposed changes I am aware of: one would be to have functions
do a new setQuerySnapshot whenever they acquire a lock, the other would
be to add a new keyword to the function semantics to allow some
functions to call setQuerySnapshot just as if all internal sql
statements were running in read committed mode. The first seems like the
easier to implement, though I think some don't feel it is an elegant
solution. The second would be more powerful, but exactly how to
implement it seems mysterious (the one good explanation I heard required
nested transactions). Personally I would love to see some work done on
this, I think it's a major lacking in the postgresql function support.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
