Re: BUG #3883: Autovacuum deadlock with truncate?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3883: Autovacuum deadlock with truncate?
Date: 2008-01-21 20:33:39
Message-ID: 11741.1200947619@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:
> 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.

> 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.

Yeah, you've got this:

> -- 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;

which means that at the instant you try to do the TRUNCATE, the
FOR-loop's cursor still has the table open. We really should error
out on that (in fact, I'm a bit surprised it doesn't crash). So
instead you should do something like

EXECUTE 'SELECT 1 FROM ' || p_table.tablename || ' LIMIT 1'
INTO x;
IF x IS NOT NULL THEN
EXECUTE 'TRUNCATE ' || p_table.tablename;
END IF;

I think we need to make some fixes here, but the fixes would mainly
consist of complaining about the first approach ;-). The second one
is a much safer way to do it.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Steven Flatt 2008-01-21 20:42:26 Re: BUG #3883: Autovacuum deadlock with truncate?
Previous Message David Fetter 2008-01-21 20:24:15 Re: BUG #3891: Multiple UPDATE doesn't handle UNIQUE constraint correctly