Обсуждение: Updating row with updating function, bug or feature?
Hello List,
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.
Is this a bug, a feature or something else entirely?
Please CC replies to me as well, as I am not on the list.
The following script illustrates the problem:
== SCRIPT ==
BEGIN;
CREATE TABLE test
(
id INTEGER PRIMARY KEY,
locked BOOLEAN DEFAULT FALSE,
accessed TIMESTAMP WITH TIME ZONE
);
CREATE OR REPLACE FUNCTION lock(INTEGER) RETURNS BOOLEAN AS
$$
BEGIN
UPDATE test SET locked=TRUE WHERE
id = $1 AND NOT locked;
RAISE NOTICE 'lock: % -> %', $1, FOUND;
RETURN FOUND;
END;
$$
LANGUAGE plpgsql VOLATILE;
INSERT INTO test (id) VALUES(1);
INSERT INTO test (id) VALUES(2);
SELECT 'accessed is not set';
UPDATE test SET accessed=now() WHERE id=1 AND CASE WHEN id=1 THEN
lock(1) ELSE FALSE END;
SELECT * FROM test;
SELECT 'accessed is set';
SELECT lock(2);
UPDATE test SET accessed=now() WHERE id=2 AND locked;
SELECT * FROM test;
ROLLBACK;
== END SCRIPT ==
== OUTPUT ==
CREATE TABLE
CREATE FUNCTION
INSERT 0 1
INSERT 0 1
?column?
---------------------
accessed is not set
(1 row)
psql:bugfeat.sql:26: NOTICE: lock: 1 -> t
UPDATE 0
id | locked | accessed
----+--------+----------
2 | f |
1 | t |
(2 rows)
?column?
-----------------
accessed is set
(1 row)
psql:bugfeat.sql:31: NOTICE: lock: 2 -> t
lock
------
t
(1 row)
UPDATE 1
id | locked | accessed
----+--------+-------------------------------
1 | t |
2 | t | 2009-09-30 15:27:20.497355+02
(2 rows)
ROLLBACK
== END OUTPUT ==
Thanks & Regards,
Thomas
Thomas Jacob <jacob@internet24.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
On Wed, 2009-09-30 at 10:17 -0400, Tom Lane wrote:
> Thomas Jacob <jacob@internet24.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
Hi all, I use PostgresSQL 8.3 through JDBC Recently one transaction has failed with the following error message: Detail: Process 10660 waits for AccessShareLock on relation 36036 of database 34187; blocked by process 2212. Process 2212 waits for AccessExclusiveLock on relation 36044 of database 34187; blocked by process 10660. I'm trying to understand why I can have this kind or error (it is probably some programming mistake) but reading the PostgresSQL manual I cannot find any trace of AccessExclusiveLock , while I have found explanation of what AccessShareLock is. First question: is there a problem in the documentation or in PostgresSQL error messages ? Another question. The message above was reported to explain why a query sent to the db server has failed: am I right saying that, looking at the above error message, Process 10660 was the one executing the query that has failed and Process 2212 was executing something else and kept going its way ? Final question: is there a way to know what query a Process has executed ? I'm thinking of some logging configuration for PostgresSQL. TIA to all of you. -- ================================================== dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it ==================================================
Ivano Luberti <luberti@archicoop.it> writes: > I'm trying to understand why I can have this kind or error (it is > probably some programming mistake) but reading the PostgresSQL manual I > cannot find any trace of AccessExclusiveLock , while I have found > explanation of what AccessShareLock is. > First question: is there a problem in the documentation or in > PostgresSQL error messages ? Not sure where you are looking, but they are all explained at http://www.postgresql.org/docs/8.3/static/explicit-locking.html#LOCKING-TABLES > Another question. The message above was reported to explain why a query > sent to the db server has failed: am I right saying that, looking at the > above error message, Process 10660 was the one executing the query that > has failed and Process 2212 was executing something else and kept going > its way ? I don't recall whether there's any particular guarantee about which process in the Detail message is the one that gets the error. But since these are asking for two different lock levels it shouldn't be that hard to figure out which is which. Also, the failing query really should have been included as another field of the error report. If you're using client code that prints the detail field and not the query field, you might want to revisit that decision. > Final question: is there a way to know what query a Process has executed > ? I'm thinking of some logging configuration for PostgresSQL. http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html regards, tom lane
Tom, thanks for your answer: the reason I failed to find AccessExclusiveLock is beacuse that string of character is never written in the manual while AccessShareLock is written as it is written above in the manual in the section about index lockin. Not knowing the manual in detail I didn't know there is this section 13.3.1. About which process has failed you say: > I don't recall whether there's any particular guarantee about which > process in the Detail message is the one that gets the error. But > since these are asking for two different lock levels it shouldn't be > that hard to figure out which is which. Also, the failing query really > should have been included as another field of the error report. If > you're using client code that prints the detail field and not the query > field, you might want to revisit that decision. > > My problem is I know what query has failed , but I don't know the other one that caused the deadlock condition. A few second later the same query run by another process (procpid 11704) failed again conflicting again with the process with procpid 2212. Since processes represents the connections open in a small pool that uses jdbc driver, either that was a long query that locked out the failed queries or the 2212 was reused by another application process and coincidentally caused another deadlock. In fact another thing I was asking myself is if exists a way from my java application to know which java thread is using a given postgresSQL process. Because the cause of the deadlock is clearly I have concurrent thread that can generate conflicts on the db. But I think this is more a JDBC list question. Thanks again. -- ================================================== dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it ==================================================
Ivano Luberti <luberti@archicoop.it> writes:
> My problem is I know what query has failed , but I don't know the other
> one that caused the deadlock condition.
Ah. Is it practical for you to upgrade to PG 8.4? IIRC the deadlock
reporting code got improved in 8.4 to log all the queries involved.
regards, tom lane
I don't know: I'm not subscribed to the anno9unce list so I was not aware 8.4 has now a production release. In the past upgrading from 8.2 to 8.3 solved a big issues but at the time the application is not in production. So we are going to evaluate this option. Anyway after reading the manual in the section you pointed out I have been able to identify the other process involved in deadlock: we are going to analyze when the two threads can conflict. I suspect we will have to use the list again... Regards Tom Lane ha scritto: > Ivano Luberti <luberti@archicoop.it> writes: > >> My problem is I know what query has failed , but I don't know the other >> one that caused the deadlock condition. >> > > Ah. Is it practical for you to upgrade to PG 8.4? IIRC the deadlock > reporting code got improved in 8.4 to log all the queries involved. > > regards, tom lane > > -- ================================================== dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it ==================================================