"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''
> 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'
IF x IS NOT NULL THEN
EXECUTE 'TRUNCATE ' || p_table.tablename;
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
pgsql-bugs by date
|Next:||From: Steven Flatt||Date: 2008-01-21 20:42:26|
|Subject: Re: BUG #3883: Autovacuum deadlock with truncate?|
|Previous:||From: David Fetter||Date: 2008-01-21 20:24:15|
|Subject: Re: BUG #3891: Multiple UPDATE doesn't handle UNIQUEconstraint correctly|