Re: BUG #3883: Autovacuum deadlock with truncate? - Mailing list pgsql-bugs

On Jan 21, 2008 1:24 PM, Tom Lane <tgl@sss.pgh.pa.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