"Steven Flatt" <steven.flatt@gmail.com> writes:
> 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.
> 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
