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