Re: Vacuum Question

Ed Loehr wrote:
>
> 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

Sorry...word-wrap killed that one's cut-n-paste-ability...try this if
you're interested:

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 \n";
print "($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