title: Table locking in a plpgsql function
body: <p>Let's say I've written plpgsql function that does the following:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p><strong>First question:</strong></p>
  
  <p>If I simultaneously invoked this function twice, is it possible for the second invocation of <code>foobar()</code> to drop the <code>tmp_foobar</code> table  while the first invocation of <code>foobar()</code> is still running?</p>
  
  <p>I understand that <code>SELECT</code> statements create an <code>ACCESS SHARE</code> lock, but will that lock persist until the <code>SELECT</code> statement completes or until the implied <code>COMMIT</code> at the end of the function?</p>
  
  <p><strong>Second question:</strong></p>
  
  <p>If the latter is true, will the second invocation of <code>foobar()</code> indefinitely re-try <code>DROP TABLE IF EXISTS tmp_foobar;</code> until the lock is dropped or will it fail at some point?</p>