Re: BUG #1231: Probelm with transactions in stored code.

On Thu, 2004-08-26 at 04:23, Gaetano Mendola wrote:
> Tom Lane wrote:
>
> > Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> >
> >>I believe it sees the one that was valid in the snapshot as of the
> >>beginning of the function.
> >
> >
> > Actually, the problem is that it can see *both* that row and the updated
> > row; it's a crapshoot which one will be returned by the SELECT INTO.
>
> Confirmed, if the last select is:
>
> select count(*) into a from test where id=1;
>
> this return 2. There is a space for a new bug considering that if the
> table have the unique index on id that select must return 1.
>
> > The reason this can happen is that we're not doing SetQuerySnapshot
> > between commands of a plpgsql function. There is discussion going way
> > way back about whether we shouldn't do so (see the archives). I think
> > the major reason why we have not done it is fear of introducing
> > non-backwards-compatible behavior. Seems like 8.0 is exactly the right
> > version to consider doing that in.
>
> If my 2 cents are valid I agree with you, what I don't totally agree is why
> consider this bug as a *feature* in previous 8.0 version.
>
I don't think this was ever considered a feature (at least I never found
any evidence of that) but more the concern was that it was "expected
behavior" and changing that behavior might toss people into a loop who
were expecting it. I would certainly be in favor of changing it though
since I think it would make our function implementation a lot stronger,
and I don't really see any practical downsides to changing the behavior.
I agree with Tom that 8.0 does seem like the best time to make such a
change and I'd like to see this put up to a vote since I have _never_
seen anyone argue the case that the current functionality is
preferable. I'm sure some will argue that they don't want to make the
change now that we are already into beta, but istm if we can't make
determinations on version number jumps until we're in beta, we can't
also tie peoples hands completely on making changes once we're there.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL