Re: [HACKERS] Lock table in non-volatile functions
Gaetano Mendola [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Lock table in non-volatile functions
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] 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 - 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Lock table in non-volatile functions
Gaetano Mendola [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
