Thread: major flaw in 6.5beta1??? (UPDATE/INSERT waiting)

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 queryYou are
currentlyconnected 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

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

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
envelopefield, 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
isa discussion> going on right now about lock-manager bugs that might possibly allow> multiple backends to corrupt data
thatthey'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

Dirk Lutzebaeck writes:> Tom Lane writes:>  > Dirk Lutzebaeck <lutzeb@aeccom.com> writes:>  > > cs=> select envelope
fromrecipient 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