[PostgreSQL-Hackers] Lock table in non-volatile functions

[PostgreSQL-Hackers] Lock table in non-volatile functions

	Tom Lane
	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

Search Discussions

Discussion Posts

Gaetano Mendola: 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?

Follow ups

Gaetano Mendola: 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$#
Tom Lane: Hm, that's a bug --- SPI_cursor_open is failing to check for a read-only query. 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

Related Discussions

REVIEW: PL/Python table functions
[PATCH 10/16] Skip dropped attributes when converting tuples to Python objects.
Why percent_rank is so slower than rank?
pl/python table functions
Window functions seem to inhibit push-down of quals into views
Planner deficiencies with views that use windowing functions
inlining SQL functions
Limitations on trigger functions
proposal: table functions and plpgsql
How to handle error message in PG_CATCH

Discussion Navigation

view	thread \| post
posts	‹ prev \| 2 of 4 \| next ›

Discussion Overview

group	pgsql-hackers @
categories	postgresql
posted	Mar 16, '07 at 8:54a
active	Mar 17, '07 at 2:17a
posts	4
users	2
website	postgresql.org...
irc	#postgresql

font	variable	Hotkey: f
user style	avatars	Hotkey: a

[PostgreSQL-Hackers] Lock table in non-volatile functions

Search Discussions

Discussion Posts

Related Discussions

2 users in discussion