1

Is it normal for a function to acquire a database-level lock that prevents auto vacuuming and index creation on totally unrelated tables?

I've got a long-running stored procedure that reads from one table and writes SUM()/AVG() data to another table. During the execution of the script (which can take almost an hour on large systems), all auto vacuuming stops and attempts to do things like creating indexes (CREATE UNIQUE INDEX CONCURRENTLY) on unrelated tables to block. As soon as the stored procedure completes, the blocked processes complete.

Thinking it was something within the stored procedure that was incorrectly locking some resource, I re-wrote it be literally just a sleep:

CREATE OR REPLACE FUNCTION summarize_day(p_agg_date date)
RETURNS int AS
$$
BEGIN
  SELECT pg_sleep(120);
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Even this stored do nothing procedure prevented auto vacuuming and other database operations.

Is there any way to code it to NOT lock the database for other operations while this long running procedure runs?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
KenHuffman
  • 106
  • 1
  • 4

2 Answers2

1

VACUUM is prevented from marking dead tuples by long running transactions. The system keeps track of the oldest transaction ID (xid) that still might be visible in any snapshot (even with no competing locks involved.) Tuples with a later xid cannot be removed until blocking transaction(s) are finished. Long running transactions can stall VACUUM in this respect.

Related:

However, I can't think of a way how CREATE UNIQUE INDEX on an unrelated table should be directly affected. There might be other transactions stalled by locks of the long running transaction, which in turn hold locks blocking CREATE UNIQUE INDEX. Locks of long running transactions can mushroom this way. So keep your transactions brief under concurrent write load.

Related:

Further reading:

"When autovacuum does not vacuum" (blog post by Thomas Vondra)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

The person answering the question edited my original question quite a bit and answered a very generic question, not the specific one I was asking.

The original question was whether a PostgreSQL stored procedure could support small transactions instead of one long running one. Starting with version 11, it can:

Transaction Management

KenHuffman
  • 106
  • 1
  • 4
  • You should have mentioned that you are running Postgres 11 and you should have included an example of a stored procedure, not a function if that is the context of your question (I'm sure Erwin would not have replaced "procedure" with "function" then) –  Nov 27 '18 at 15:59
  • In the original question I mentioned postgresql and that it was a stored procedure. That text was edited out. (I did not mention version 11 specifically because I wasn't aware at the time that that version offered the fix I was looking for.) In the future, I'll be sure to only ask questions I already know the answer to. – KenHuffman Nov 28 '18 at 17:15