Postgres Pro
Downloads
Home   >   mailing lists

Thread: Lock table in non-volatile functions

Lock table in non-volatile functions

From
Gaetano Mendola
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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?




-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFF+lsm7UpzwH2SGd4RAjloAJ4j/AOdJhGMRnvM/TKVpKHPwesAOACeO4mT
OQhSwR1of3xS7HSSvtjGiQc=
=nFFM
-----END PGP SIGNATURE-----

Re: Lock table in non-volatile functions

From
Tom Lane
Date:
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.
        regards, tom lane

Re: Lock table in non-volatile functions

From
Gaetano Mendola
Date:
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.3on 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
---------       0
(1 row)

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

Regards
Gaetano Mendola

Re: Lock table in non-volatile functions

From
Tom Lane
Date:
Gaetano Mendola <mendola@bigfoot.com> 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