AA van Raalte wrote:
> 
> >>
> >> Summary: Indexes are broken
> >
> >Could you post me your data to give me way to reproduce bug locally ?
> >
> >Vadim
> 
> OK. There are three scripts attatched, s1 generates the data, s2 creates the
> indexes, s3 performs a query on the data.
> If you perform the following query:
> 
> cat s1 s3 | psql db
> 
> the last query returns a count of 8.
> If you use indexes by performing the following, identical query:
> 
> cat s1 s2 s3 | psql db
> 
> the last query returns a count of 4!.

Well, fortunately, btree are not broken and two old bugs fixed. I suppose
that your server compiled without CASSERT. Having CASSERT I got core from
the next s3' query when tested using indices:

update temp_bankbaln                                                           
        set prev_baln = b.baln                                                 
        from temp_bankbaln b                                                   
        where temp_bankbaln.prev_date = b.date and                             
                temp_bankbaln.accno = b.accno;                                 

EXPLAIN:

Merge Join  (cost=53.85 size=24556 width=62)
  ->  Index Scan on b  (cost=19.40 size=228 width=24)
  ->  Index Scan on temp_bankbaln  (cost=19.40 size=228 width=38)

Without index baln_i4 on temp_bankbaln(accno):

Merge Join  (cost=71.18 size=24556 width=60)
  ->  Index Scan on b  (cost=18.40 size=228 width=24)
  ->  Seq Scan  (cost=18.40 size=0 width=0)
        ->  Sort  (cost=10.52 size=0 width=0)
              ->  Seq Scan on temp_bankbaln  (cost=10.52 size=228 width=36)

- no probs.

First bug was in execScan.c when "bad" tuple table slot was returned in
the end of index' scan and the second was in nodeIndexscan.c where 
"tooo" general and dummy IndexScanMarkPosition()/ExecIndexRestrPos() 
were used instead of index_markpos()/index_restrpos().

Note, that ONLY MergeJoin was affected by these two bugs because
this join method is only one using ExecMarkPos() and ExecRestrPos()
(and for inner child plan onle).
BTW, SELECT with second plan above is 13 times faster than with 
the first one! Sorting by using indices is not always fastest way...

Thanks Alvin for given simple way to reproduce bug!

BTW, elog(FATAL) from vacuum and btree were reported by 2-3
another ppl - shouldn't we ask users to re-compile server with
CASSERT in all "FATAL" cases ? 
CASSERT assisted me very much to find real sources of this problem
and recently fixed vacuum bug.

Vadim
P.S. Unfortunately, I still didn't add permissions check to subselect
code and didn't implement ReScan of MergeJoin node - hope to do this
tomorrow... Bye.

Thu, 26 Feb 1998 18:55:14 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
MergeJoin and Mark/Restr scan positions (Re: [PORTS] Port Bug Report)
> BTW, elog(FATAL) from vacuum and btree were reported by 2-3
> another ppl - shouldn't we ask users to re-compile server with
> CASSERT in all "FATAL" cases ? 
> CASSERT assisted me very much to find real sources of this problem
> and recently fixed vacuum bug.

I am a little confused on when we should have them use this.  In most
cases, turning on CASSERT doesn't help.  Should we suggest it in certain
cases as we see it applies, or is there a rule we can give them to tell
them when to try it?


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 26 Feb 1998 09:44:34 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] MergeJoin and Mark/Restr scan positions (Re: [PORTS]
	Port Bug Report)
Bruce Momjian wrote:
> 
> > BTW, elog(FATAL) from vacuum and btree were reported by 2-3
> > another ppl - shouldn't we ask users to re-compile server with
> > CASSERT in all "FATAL" cases ?
> > CASSERT assisted me very much to find real sources of this problem
> > and recently fixed vacuum bug.
> 
> I am a little confused on when we should have them use this.  In most
> cases, turning on CASSERT doesn't help.  Should we suggest it in certain
> cases as we see it applies, or is there a rule we can give them to tell
> them when to try it?

For me, CASSERT is last chance to put me in right direction when
I can't reproduce reported bug (user can't provide test data etc).
But you're right - we shouldn't suggest anything generaly.

BTW, Alvin got ASSERTion for real data! We'll try to fix this...

Vadim

Fri, 27 Feb 1998 10:05:12 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] MergeJoin and Mark/Restr scan positions (Re: [PORTS]
	Port Bug Report)