Table locking in a plpgsql function

Question

Let's say I've written plpgsql function that does the following:

CREATE OR REPLACE FUNCTION foobar (_foo_data_id bigint)
RETURNS bigint AS $$
BEGIN

    DROP TABLE IF EXISTS tmp_foobar;

    CREATE TEMP TABLE tmp_foobar AS
    SELECT *
    FROM foo_table ft
    WHERE ft.foo_data_id = _foo_data_id;

    -- more SELECT queries on unrelated tables

    -- a final SELECT query that invokes tmp_foobar

END;

First question:

If I simultaneously invoked this function twice, is it possible for the second invocation of foobar() to drop the tmp_foobar table while the first invocation of foobar() is still running?

I understand that SELECT statements create an ACCESS SHARE lock, but will that lock persist until the SELECT statement completes or until the implied COMMIT at the end of the function?

Second question:

If the latter is true, will the second invocation of foobar() indefinitely re-try DROP TABLE IF EXISTS tmp_foobar; until the lock is dropped or will it fail at some point?

I wouldn't use a temp table for this to begin with. I wouldn't be surprised if a single (large) SELECT was faster. Are you coming from a SQL Server background? — , Feb 08 '13 at 19:45
@a_horse_with_no_name My question is a small-scale example and isn't representative of our real code. The reason why we're using temp tables is beyond the scope of this question. — Elliot B., Feb 08 '13 at 20:38

score 3 · Accepted Answer · answered Feb 08 '13 at 19:38

If you simultaneously invoke a function twice, it means you're using two separate sessions to do so. Temporary tables are not shared between sessions, so the second session would not "see" tmp_foobar from the first session, and there would be no interaction. See http://www.postgresql.org/docs/9.2/static/sql-createtable.html#AEN70605 ("Temporary tables").

Locks persist until the end of the transaction (regardless of how you acquire them; exception are advisory locks, but that's not what you're doing.)

The second question does not need an answer, because the premise is false.

One more thing. It might be useful to create indexes on that temporary table of yours, and ANALYZE it; that might cause the final query to be faster.

Table locking in a plpgsql function

1 Answers1