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?

Answer 1

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.