Re: BUG #3883: Autovacuum deadlock with truncate?

On Jan 21, 2008 1:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Well, there's our problem: for some reason PID 7908 has this buffer
> pinned, which is blocking the vacuum. That seems pretty darn odd for
> a process that is about to (try to) truncate the table. The only way
> I can imagine is that the function has an open cursor scanning the table
> ... which is a case that we probably ought to error out on, though I
> think TRUNCATE is not checking for it now. Or you've managed to tickle
> some previously-unknown bug that leaks buffer pins.
>
> Could we see that whole function? Also, what has been done previously
> in the transaction that's calling it?
>

Here's the function. I've removed some of our schema-specific details but
the important stuff is there. The part about "checking if a table has any
rows" before truncating it was recently added to speed up our tests -- and
this no doubt introduced the problem. Before we were unconditionally
truncating a few thousand tables, most of which were empty, which took close
to 5 minutes. By checking first and only truncating tables with data,
execution time was reduced to <10 seconds. If possible, I'd still like a
way to check the tables but avoid the "hanging" problem.

By the way, nothing else is done in this transaction before calling the
function.

CREATE OR REPLACE FUNCTION fn_clean_tables()
RETURNS VOID
AS '
DECLARE
p_table RECORD;
r RECORD;

-- A list of the dimension tables which are cleaned.
tables varchar[] := ''{
-- table names removed
}'';

-- A list of the sequences on dimension tables which
-- need to be reset.
sequences varchar[] := ''{
-- sequence names removed
}'';

-- A list of the sequences on partitioned tables which need to
-- be reset.
p_sequences varchar[] := ''{
-- sequence names removed
}'';

i int;
BEGIN
-- Clean all partitioned tables, those which match a regex.
FOR p_table IN SELECT tablename FROM pg_tables
WHERE tablename ~ ''regex''
LOOP
-- Check if table has any rows.
FOR r IN EXECUTE
''SELECT 1 FROM '' || p_table.tablename ||
'' LIMIT 1''
LOOP
EXECUTE ''TRUNCATE '' || p_table.tablename;
END LOOP;
END LOOP;

-- Reset all sequences of cleaned tables to start at 1.
FOR i IN array_lower(p_sequences, 1) ..
array_upper(p_sequences, 1)
LOOP
EXECUTE ''SELECT setval('''''' || p_sequences[i] ||
'''''', 1, false)'';
END LOOP;

-- Clean all dimension tables.
-- The order in which the tables are cleaned is important.
-- Note that we cannot simply truncate the tables due to the
-- foreign key relationships between tables.
FOR i IN array_lower(tables, 1) .. array_upper(tables, 1)
LOOP
-- Check if table has any rows.
FOR r IN EXECUTE
''SELECT 1 FROM '' || tables[i] ||
'' LIMIT 1''
LOOP
EXECUTE ''DELETE FROM '' || tables[i];
END LOOP;
END LOOP;

-- Reset all sequences of cleaned tables to start at 1.
FOR i IN array_lower(sequences, 1) ..
array_upper(sequences, 1)
LOOP
EXECUTE ''SELECT setval('''''' || sequences[i] ||
'''''', 1, false)'';
END LOOP;
END;'
LANGUAGE PLpgSQL
VOLATILE;
COMMENT ON FUNCTION fn_clean_tables()
IS 'Cleans tables and resets sequences';
Steve