Re: Vacuum Question

Ed Loehr <eloehr@austin.rr.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