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

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.

In general, having side-effects in a function invoked in WHERE
is a dangerous and unwise practice anyhow, IMNSHO. You have
very little control over when or even whether the side effects
will happen.

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.

regards, tom lane