From: | "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3883: Autovacuum deadlock with truncate? |
Date: | 2008-01-21 19:51:16 |
Message-ID: | 357fa7590801211151h2942eecva02db265f25570b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2008-01-21 20:24:15 | Re: BUG #3891: Multiple UPDATE doesn't handle UNIQUE constraint correctly |
Previous Message | Tom Lane | 2008-01-21 18:24:39 | Re: BUG #3883: Autovacuum deadlock with truncate? |