Обсуждение: 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