Re: Vacuum Question

Tom Lane wrote:
>
> Ed Loehr <eloehr(at)austin(dot)rr(dot)com> writes:
> >> The short answer is that a table being vacuumed is locked against any
> >> other access, read or write; but only that one table. You might as
> >> well leave the rest of the database open for use.
>
> > I take it you don't give much credence to the previous reports that
> > concurrently writing during vacuuming caused corruption?
>
> No, not really, for the simple reason that there aren't going to *be*
> any concurrent writes (or reads either) during a vacuum ... unless our
> table locking system fails, in which case we've got lots worse problems
> than vacuum.

On my way to some performance testing I ran into this repeatable crash
scenario. Just wondering if anyone else can reproduce the same or
explain what is happening. Could very well be something peculiar/buggy
about the bash/perl commands...

First, start up this rather obfuscated perl/bash command which just
inserts random records forever...

% createdb pdb; psql -d pdb -c "create table foo(id serial, t timestamp
not null default current_timestamp, f float, s varchar, i integer, l
int8);"; perl -e 'for (my $j = 0; $j < 10000000; $j++ ) { print "--
Insert #$j\n"; $f = rand(2000000); $i = int(rand(2000000)) % 16000; $l =
int(rand(2000000000)); $s = chr(65 + int(rand(20))) x int(rand(128));
$delim = chr(39); print "insert into foo (f,s,i,l) values ($f,
$delim${s}$delim, $i, $l);\n"; }' | psql -d pdb

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:

NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend died
abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am going to
terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
connection to server was lost

Regards,
Ed Loehr