Re: Vacuum Question - Mailing list pgsql-general

Ed Loehr wrote:
>
> Tom Lane wrote:
> >
> > Ed Loehr <eloehr@austin.rr.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