Hi all,
I'm observing that is not allowed to LOCK a table in a
STABLE/IMMUTABLE function but at same time is allowed
a SELECT FOR UPDATE.

Is that normal?

Search Discussions

  • Tom Lane at Mar 16, 2007 at 2:55 pm

    Gaetano Mendola writes:
    I'm observing that is not allowed to LOCK a table in a
    STABLE/IMMUTABLE function but at same time is allowed
    a SELECT FOR UPDATE.
    Really? AFAICS, CommandIsReadOnly() will reject SELECT FOR UPDATE too.

    regards, tom lane
  • Gaetano Mendola at Mar 17, 2007 at 1:17 am

    Tom Lane wrote:
    Gaetano Mendola <mendola@bigfoot.com> writes:
    I'm observing that is not allowed to LOCK a table in a
    STABLE/IMMUTABLE function but at same time is allowed
    a SELECT FOR UPDATE.
    Really? AFAICS, CommandIsReadOnly() will reject SELECT FOR UPDATE too.
    kalman=# select version();
    version
    --------------------------------------------------------------------------------------------------------
    PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
    4.1.1 20070105 (Red Hat 4.1.1-51)
    (1 row)

    kalman=# CREATE TABLE test( a integer );
    CREATE TABLE
    kalman=# INSERT INTO test VALUES ( 1 );
    INSERT 0 1
    kalman=# CREATE OR REPLACE FUNCTION sp_test()
    kalman-# RETURNS INTEGER AS $body$
    kalman$# DECLARE
    kalman$# my_integer integer;
    kalman$# my_port_set RECORD;
    kalman$# BEGIN
    kalman$# FOR my_port_set IN
    kalman$# SELECT a
    kalman$# FROM test
    kalman$# FOR UPDATE
    kalman$# LOOP
    kalman$# my_integer = 0;
    kalman$# END LOOP;
    kalman$# RETURN 0;
    kalman$# END;
    kalman$# $body$ language 'plpgsql'
    kalman-# STABLE;
    CREATE FUNCTION
    kalman=# select sp_test();
    sp_test
    ---------

    (1 row)

    BTW why forbid the lock in a non volatile function or (if you fix this)
    the SELECT FOR UPDATE ?

    Regards
    Gaetano Mendola
  • Tom Lane at Mar 17, 2007 at 2:17 am

    Gaetano Mendola writes:
    Tom Lane wrote:
    Really? AFAICS, CommandIsReadOnly() will reject SELECT FOR UPDATE too.
    kalman$# FOR my_port_set IN
    kalman$# SELECT a
    kalman$# FROM test
    kalman$# FOR UPDATE
    kalman$# LOOP
    Hm, that's a bug --- SPI_cursor_open is failing to check for a read-only
    query.
    BTW why forbid the lock in a non volatile function or (if you fix this)
    the SELECT FOR UPDATE ?
    Well, as for the lock, a non-volatile function isn't supposed to have
    any side-effects, and taking a lock is certainly a side-effect no?
    Now I suppose it'll be taking AccessShareLock anyway if it reads any
    tables, so maybe we could negotiate about what sort of locks could be
    allowed; but I'd certainly argue that allowing it to take any kind of
    exclusive lock would be a Bad Idea.

    As for SELECT FOR UPDATE, there's a very good reason for disallowing
    that even without considering what locks it takes. In a READ COMMITTED
    transaction, SELECT FOR UPDATE can return row states that aren't visible
    according to the nominal transaction snapshot, and so it violates the
    promise of stable results.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 16, '07 at 8:54a
activeMar 17, '07 at 2:17a
posts4
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Gaetano Mendola: 2 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2022 Grokbase