
Hi,

I'm quite shocked, I hope this is dream:

> psql cs
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.5]

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: cs


cs=> select envelope from recipient where envelope=510349;
envelope
--------
   88320
  510349
  510349
  510349
  510349
  510349
  510349
  510349
  510349
  510349
  510349
  510349
(12 rows)

To my understanding the first should have been *never* selected. 

I had a strange problem tonight, where the backends stopped working
saying something like this

UPDATE waiting
INSERT waiting

dead locks? how can these happen? killed some backends, and restarted
the server. Seems part of the db are corrupted now. Back to 6.4.2?

Dirk

Tue,  4 May 1999 17:30:06 +0200 (CEST)
Dirk Lutzebaeck <lutzeb@aeccom.com>
major flaw in 6.5beta1??? (UPDATE/INSERT waiting)
Dirk Lutzebaeck <lutzeb@aeccom.com> writes:
> cs=> select envelope from recipient where envelope=510349;
> [ returns a tuple that obviously fails the WHERE condition ]

Yipes.  Do you have an index on the envelope field, and if so is
it being used for this query?  (Use EXPLAIN to check.)  My guess
is that the index is corrupted.  Dropping and recreating the index
would probably set things right.

Of course the real issue is how it got corrupted.  Hiroshi found
an important bug in btree a few days ago, and there is a discussion
going on right now about lock-manager bugs that might possibly allow
multiple backends to corrupt data that they're concurrently updating.
But I have no idea if either of those explains your problem.

			regards, tom lane

Tue, 04 May 1999 12:20:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] major flaw in 6.5beta1??? (UPDATE/INSERT waiting) 
Tom Lane writes:
 > Dirk Lutzebaeck <lutzeb@aeccom.com> writes:
 > > cs=> select envelope from recipient where envelope=510349;
 > > [ returns a tuple that obviously fails the WHERE condition ]
 > 
 > Yipes.  Do you have an index on the envelope field, and if so is
 > it being used for this query?  (Use EXPLAIN to check.)  My guess
 > is that the index is corrupted.  Dropping and recreating the index
 > would probably set things right.

Yes, thanks, recreating the index cures the problem.

 > Of course the real issue is how it got corrupted.  Hiroshi found
 > an important bug in btree a few days ago, and there is a discussion
 > going on right now about lock-manager bugs that might possibly allow
 > multiple backends to corrupt data that they're concurrently updating.
 > But I have no idea if either of those explains your problem.

Does this mean they can deadlock themselves?  Is this also true for
6.4.2? I probably switch back then.

Thanks, Dirk

Wed,  5 May 1999 09:30:35 +0200 (CEST)
Dirk Lutzebaeck <lutzeb@aeccom.com>
Re: [HACKERS] major flaw in 6.5beta1??? (UPDATE/INSERT waiting) 
Dirk Lutzebaeck writes:
 > Tom Lane writes:
 >  > Dirk Lutzebaeck <lutzeb@aeccom.com> writes:
 >  > > cs=> select envelope from recipient where envelope=510349;
 >  > > [ returns a tuple that obviously fails the WHERE condition ]
 >  > 
 >  > Yipes.  Do you have an index on the envelope field, and if so is
 >  > it being used for this query?  (Use EXPLAIN to check.)  My guess
 >  > is that the index is corrupted.  Dropping and recreating the index
 >  > would probably set things right.
 > 
 > Yes, thanks, recreating the index cures the problem.

Here is some more info: the automatic vacuum tonight gave the
following errors:

vacuum analyze;
NOTICE:  Index recipient_oid_index: NUMBER OF INDEX' TUPLES (1474) IS NOT THE SAME AS HEAP' (1473)
NOTICE:  Index recipient_addr_index: NUMBER OF INDEX' TUPLES (1474) IS NOT THE SAME AS HEAP' (1473)
NOTICE:  Index recipient_mem_index: NUMBER OF INDEX' TUPLES (1474) IS NOT THE SAME AS HEAP' (1473)
NOTICE:  Index recipient_env_index: NUMBER OF INDEX' TUPLES (1474) IS NOT THE SAME AS HEAP' (1473)
NOTICE:  Index recipient_oid_index: NUMBER OF INDEX' TUPLES (1474) IS NOT THE SAME AS HEAP' (1473)
NOTICE:  Index recipient_addr_index: NUMBER OF INDEX' TUPLES (1474) IS NOT THE SAME AS HEAP' (1473)
NOTICE:  Index recipient_mem_index: NUMBER OF INDEX' TUPLES (1474) IS NOT THE SAME AS HEAP' (1473)
NOTICE:  Index recipient_env_index: NUMBER OF INDEX' TUPLES (1474) IS NOT THE SAME AS HEAP' (1473)
VACUUM

Wed,  5 May 1999 09:54:26 +0200 (CEST)
Dirk Lutzebaeck <lutzeb@aeccom.com>
Re: [HACKERS] major flaw in 6.5beta1??? (UPDATE/INSERT waiting) 