Is a PostgreSQL function marked as STABLE allowed to acquire locks?

Question

The PostgreSQL documentation has this to say on the subject of marking functions as STABLE:

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

I have a function (call it F) that needs to lock a table IN EXCLUSIVE MODE before starting, to guarantee no new rows are inserted between the time the function is called and the end of the transaction. F doesn't make any direct changes to the database. It would be safe for the planner to elide multiple calls to F with the same arguments within the same transaction, as long as it is called once.

As an example, let's use

CREATE FUNCTION F(x INTEGER) RETURNS INTEGER AS $$
BEGIN
    LOCK TABLE foobar IN EXCLUSIVE MODE;
    RETURN (SELECT COUNT(*) FROM foobar WHERE id = x);
END;
$$ LANGUAGE 'plpgsql';

My question is, does that lock acquisition disqualify the function from being marked STABLE? That is, is acquiring a lock considered "modifying the database" in this context?

Edited to add: What about using an advisory lock? Does that change the answer?

Laurenz Albe · Accepted Answer · 2018-04-22 18:49:40Z

3

As you can easily test, that won't work. You'll get this error:

ERROR:  LOCK TABLE is not allowed in a non-volatile function

But that is no problem. If you want your function to see a stable snapshot of the table, just start a transaction with

START TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;

Then the table won't change for you.

answered Apr 22, 2018 at 18:49

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

the paul · Accepted Answer · 2018-04-22 18:37:50Z

1

Well, that was a simple answer. I tried it, and PG was decidedly unhappy.

ERROR:  LOCK TABLE is not allowed in a non-volatile function
CONTEXT:  SQL statement "LOCK TABLE foobar IN EXCLUSIVE MODE"

So the answer is, yes, using LOCK TABLE definitely disqualifies a function from being marked STABLE.

Using an advisory lock, however, does not (at least, I do not get an error when I try it).

answered Apr 22, 2018 at 18:37

the paul

9,1991 gold badge40 silver badges56 bronze badges

Collectives™ on Stack Overflow

Is a PostgreSQL function marked as STABLE allowed to acquire locks?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related