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

Search Discussions

  • Tom Lane at Sep 30, 2009 at 2:17 pm

    Thomas Jacob 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
  • Thomas Jacob at Sep 30, 2009 at 2:54 pm

    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
  • Ivano Luberti at Sep 30, 2009 at 3:35 pm
    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
    ==================================================
  • Tom Lane at Sep 30, 2009 at 3:45 pm

    Ivano Luberti 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
  • Ivano Luberti at Sep 30, 2009 at 4:07 pm
    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
    ==================================================
  • Tom Lane at Sep 30, 2009 at 4:24 pm

    Ivano Luberti 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
  • Ivano Luberti at Sep 30, 2009 at 4:31 pm
    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
    ==================================================

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedSep 30, '09 at 1:47p
activeSep 30, '09 at 4:31p
posts8
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase