Re: Updating row with updating function, bug or feature?

On Wed, 2009-09-30 at 10:17 -0400, Tom Lane wrote:
> Thomas Jacob <jacob(at)internet24(dot)de> writes:
> > I've run into some weirdness in PSQL 8.3.8 (Ubuntu 8.04 LTS x86_64
> > package). When I update a row while using a function result
> > that updates that very same row in the "WHERE" part of the update,
> > the main update no longer takes place, even though the "WHERE"
> > conditions should match. But if I execute
> > the function before the update, and then do the update
> > based on the same logic, I see both changes.
>
> This is expected; it's worked like that since Berkeley days.
> An UPDATE will not touch a row that's already been updated
> within your own transaction since the UPDATE started. This
> is mainly to avoid sorceror's-apprentice syndrome with repeatedly
> updating the same row.

OK , thanks for clearing this up. Out of interest, does some
SQL standard make any clear pronouncements on conforming
behavior in this case?

> In the particular case at hand, you might want to think about
> using SELECT FOR UPDATE locking instead of rolling your own.
> Something like
>
> BEGIN;
> SELECT * FROM tab WHERE id = x FOR UPDATE;
> ... do some work using retrieved values ...
> UPDATE tab SET ... WHERE id = x;
> COMMIT;
>
> has simple and reliable behavior.

I need to lock a row over longer periods, just for
an application, without staying connected to the
database, or indeed for the database system
to still be running. So SELECT FOR UPDATE
isn't enough.

To get the desired functionality,
I simply moved the updates and checks from the
function to the toplevel updates, and then everything
works fine.

Thanks for your quick reply,
Thomas