We're running into problem with vacuum analyze. We first noticed that
with Postgres v.7.2.1. Now with v.7.3.2 it appears again.
After nightly maintenance application isn't functioning. It looks that
corruption occurs (at least) on one table.

I was able to narrow down the problem by dropping all but one
column from the table in question. What I have now is a table
with a single varchar column and just over three hundred records on it.
Running vacuum analyze on this table results in some sort of corruption.
I'm running Pg 7.3.2 on Solaris. There's no explicit settings for vacuum
in postgresql.conf

Attached is the dump of this table. The problem can be easily reproduced.
Load the dump into your database, it'll create table "nla"

syncdb=# select count (*) from nla where note_url LIKE 'sync:///FUNCTREE%' ;
 count
-------
   121
(1 row)

syncdb=# vacuum analyze nla;
VACUUM

-- --- now repeat the same query ---
syncdb=# select count (*) from nla where note_url LIKE 'sync:///FUNCTREE%' ;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: WARNING:  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.
Failed.

Now, if I reconnect and delete the nla's record from pg_statistic
I can run the query again.

Problem could be data related, I tried to poke around and update
all records beginning with "sync:///FUNCTREE" to something different,
then problem may go away.

Could someone from the Postgres team comment on this, please.
Mike.
Fri, 23 May 2003 10:33:17 -0400
Michael Brusser <michael@synchronicity.com>
vacuum analyze corrupts database
Michael Brusser <michael@synchronicity.com> writes:
> Attached is the dump of this table. The problem can be easily reproduced.

Not as easily as all that --- your example works fine for me, under
7.2.4, 7.3.3, or CVS tip.  I speculate that there may be some necessary
factor in build options or runtime environment.  How exactly did you
configure Postgres, what compiler did you use, are you using a non-C
locale or non-default multibyte encoding, etc?

It would help to know where the crash is occurring; could you get a
debugger backtrace please?

			regards, tom lane

Fri, 23 May 2003 13:24:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: vacuum analyze corrupts database 
On Fri, 23 May 2003 10:33:17 -0400, Michael Brusser
<michael@synchronicity.com> wrote:
>syncdb=# vacuum analyze nla;
>VACUUM
>
>-- --- now repeat the same query ---
>syncdb=# select count (*) from nla where note_url LIKE 'sync:///FUNCTREE%' ;
>server closed the connection unexpectedly
>        This probably means the server terminated abnormally
>        before or while processing the request.

Works for me:

regression=# VACUUM VERBOSE ANALYSE nla;
INFO:  --Relation public.nla--
INFO:  Pages 3: Changed 0, Empty 0; Tup 323: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Analyzing public.nla
VACUUM
regression=# select count (*) from nla where note_url LIKE
'sync:///FUNCTREE%' ;
 count
-------
   121
(1 row)

regression=# SELECT version();
                            version
----------------------------------------------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
(1 row)

I did the VACUUM - SELECT cycle almost 20 times on my develpment
system and then several times on a second machine, all without any
problem:
                           version
-------------------------------------------------------------
 PostgreSQL 7.3 on i586-pc-linux-gnu, compiled by GCC 2.95.2

Are you able to reproduce the problem on any other computer?

Servus
 Manfred

Fri, 23 May 2003 20:05:06 +0200
Manfred Koizar <mkoi-pg@aon.at>
Re: vacuum analyze corrupts database
> Not as easily as all that --- your example works fine for me, under
> 7.2.4, 7.3.3, or CVS tip.  I speculate that there may be some necessary
> factor in build options or runtime environment.  How exactly did you
> configure Postgres, what compiler did you use, are you using a non-C
> locale or non-default multibyte encoding, etc?
>
> It would help to know where the crash is occurring; could you get a
> debugger backtrace please?
>
> 			regards, tom lane

This is a good news. What you said here made me do something
I should've done already - I loaded this table into another
instance of the database and I could not reproduce the problem!
I try to analyze situation, I think this is what it looks like.
Two databases were built few days apart with no changes to the code,
make files, build options, etc.
vacuum analyze on the same table very consistently creates problem
on one db, and works fine on another.
I use the same instance of postmaster against each db, I guess
whatever makes difference must be somewhere under $PGDATA directory.
The database that has the problem underwent some testing. Some data
was created and deleted while QA guys tested our application.
Test was manual, this makes it difficult to exactly reproduce the case.
I've attached the config options we use and database log.
If you're willing to look farther I could tar/zip the db as well.
but let me know if there's something else I could try first.

What I did this time was
select count (*) from nla where note_url LIKE 'sync:///FUNCTREE%' ;
vacuum analyze nla;
\encoding
select count (*) from nla where note_url LIKE 'sync:///FUNCTREE%' ;

I alo tried to drop and recreate database - same result.

Thank you.
Mike.


------------------------------------------------------
tomkins%  pg_config  --configure
'--srcdir=/home/synch/build/relipg21/src/vendor/postgresql/732'
'--prefix=/home/synch/build/relipg21/obj/SunOS_58_CC_60/postgresql-732/insta
ll'
'--enable-locale'
'--enable-multibyte=UNICODE'
'--with-tcl'
'--without-tk'
'--with-tclconfig=/home/synch/build/relipg21/sbin/SunOS_58_CC_60'
'--with-tkconfig=/home/synch/build/relipg21/sbin/SunOS_58_CC_60'
'--with-includes=/home/synch/build/relipg21/obj/SunOS_58_CC_60/postgresql-73
2/../tcl/include

/home/synch/build/relipg21/obj/SunOS_58_CC_60/postgresql-732/../tk/include'
'--with-libraries=/home/synch/build/relipg21/sbin/SunOS_58_CC_60'
'--without-readline'
'--without-zlib'
'--bindir=/home/synch/build/relipg21/sbin/SunOS_58_CC_60'
'--libdir=/home/synch/build/relipg21/sbin/SunOS_58_CC_60'
'CC=gcc -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
------------------------------------------------------
tomkins% uname -a
SunOS tomkins 5.8 Generic_108528-19 sun4u sparc SUNW,Ultra-80
tomkins% locale
LANG=
LC_CTYPE="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_COLLATE="C"
LC_MONETARY="C"
LC_MESSAGES="C"
LC_ALL=
------------------------------------------------------
Fri, 23 May 2003 21:33:35 -0400
Michael Brusser <michael@synchronicity.com>
Re: vacuum analyze corrupts database
Michael Brusser <michael@synchronicity.com> writes:
> I should've done already - I loaded this table into another
> instance of the database and I could not reproduce the problem!

Hm.  The log looks like the crash is occurring during planning (since
you get a "parse tree" message but no "ProcessQuery").  I'm guessing
that some selectivity-estimation operation that tries to use the
pg_statistic entries generated by VACUUM ANALYZE is failing.  Can't
narrow it down further than that though.  Again, how about a debugger
stack trace?  Also, the pg_stats rows for the table would be interesting.

> If you're willing to look farther I could tar/zip the db as well.

Waste of time, likely, since I don't have a Solaris system to install it
on.

			regards, tom lane

Sat, 24 May 2003 00:52:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: vacuum analyze corrupts database 