Re: Vacuum Question

Ed Loehr <eloehr(at)austin(dot)rr(dot)com> writes:
>> Then, start this one in another bash window/terminal/whatever...
>>
>> % while test 1; do echo -n "`date`: "; psql -d pdb -c "vacuum analyze;
>> select count(*) from foo;"; sleep 3; done
>>
>> This seems to consistently crash after the first vacuum with the
>> following message:

This is a known gotcha that's got nothing to do with any sort of
concurrency. You can't safely send a VACUUM followed by anything
else in a single query string. The problem is that VACUUM forces a
transaction commit, which releases all transiently allocated memory
in its backend ... including the already-parsed querytrees for the
rest of the query string. Oops. (cf. comment near line 560 in
src/backend/tcop/postgres.c)

You won't see the problem if you enter "vacuum analyze; select ..."
interactively or as a script in psql, because it chops up the
commands into separate query submittals. But apparently psql
doesn't chop up a -c string. Non-psql frontends can expose the bug
as well.

It's possible that this will get cleaned up as a byproduct of the
proposed rework of transaction-local memory contexts. But it's
not a real high-priority problem, at least not IMHO. For now,
the answer is "if it hurts, don't do it ;-)"

regards, tom lane