Thomas Jacob <jacob@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
