I ran the src/test/regressplans.sh script, which runs the regression tests
under exclusion of various join and scan types.  Without merge joins (-fm)
I get an assertion failure in opr_sanity.

The query is:

  SELECT p1.oid, p1.aggname
  FROM pg_aggregate as p1
  WHERE p1.aggfinalfn = 0 AND p1.aggfinaltype != p1.aggtranstype;

(The plan for this query is a seq scan on pg_aggregate.)

The backtrace is:

#0  0x4012b131 in __kill () from /lib/libc.so.6
#1  0x4012aead in raise (sig=6) at ../sysdeps/posix/raise.c:27
#2  0x4012c534 in abort () at ../sysdeps/generic/abort.c:88
#3  0x8149b98 in ExceptionalCondition (
    conditionName=0x81988a0 "!(((file) > 0 && (file) < (int) SizeVfdCache
&& VfdCache[file].fileName != ((void *)0)))", exceptionP=0x81b93c8,
detail=0x0,
    fileName=0x8198787 "fd.c", lineNumber=851) at assert.c:70
#4  0x8105e6e in FileSeek (file=33, offset=0, whence=2) at fd.c:851
#5  0x810e692 in _mdnblocks (file=33, blcksz=8192) at md.c:1095
#6  0x810de9b in mdnblocks (reln=0x403a35f4) at md.c:667
#7  0x810ec80 in smgrnblocks (which=0, reln=0x403a35f4) at smgr.c:441
#8  0x8103303 in RelationGetNumberOfBlocks (relation=0x403a35f4)
    at xlog_bufmgr.c:1161
#9  0x8072b04 in initscan (scan=0x822af94, relation=0x403a35f4, atend=0,
    nkeys=0, key=0x0) at heapam.c:128
#10 0x8073fa0 in heap_beginscan (relation=0x403a35f4, atend=0,
    snapshot=0x822b438, nkeys=0, key=0x0) at heapam.c:811
#11 0x80c69e4 in ExecBeginScan (relation=0x403a35f4, nkeys=0, skeys=0x0,
    isindex=0, dir=ForwardScanDirection, snapshot=0x822b438) at
execAmi.c:156
#12 0x80c6986 in ExecOpenScanR (relOid=16960, nkeys=0, skeys=0x0,
    isindex=0 '\000', dir=ForwardScanDirection, snapshot=0x822b438,
    returnRelation=0xbffff074, returnScanDesc=0xbffff078) at execAmi.c:104
#13 0x80d098c in InitScanRelation (node=0x822ae60, estate=0x822aeec,
    scanstate=0x822b084) at nodeSeqscan.c:172
#14 0x80d0a62 in ExecInitSeqScan (node=0x822ae60, estate=0x822aeec,
parent=0x0)
    at nodeSeqscan.c:242
#15 0x80c917f in ExecInitNode (node=0x822ae60, estate=0x822aeec,
parent=0x0)
    at execProcnode.c:152
#16 0x80c7be9 in InitPlan (operation=CMD_SELECT, parseTree=0x823b108,
    plan=0x822ae60, estate=0x822aeec) at execMain.c:621
#17 0x80c765b in ExecutorStart (queryDesc=0x822b41c, estate=0x822aeec)
    at execMain.c:135
#18 0x8111439 in ProcessQuery (parsetree=0x823b108, plan=0x822ae60,
    dest=Remote) at pquery.c:263
#19 0x810ffea in pg_exec_query_string (
    query_string=0x823a548 "SELECT p1.oid, p1.aggname\nFROM pg_aggregate
as p1\nWHERE p1.aggfinalfn = 0 AND p1.aggfinaltype != p1.aggtranstype;",
dest=Remote,
    parse_context=0x81f13b0) at postgres.c:818
<snipped>

This failure is completely reproduceable by running

src/test/regress$ PGOPTIONS=-fm ./pg_regress opr_sanity

The problem also happens with the setting '-fn -fm', but *not* with the
setting '-fm -fh'.  (Adding or removing -fs or -fi doesn't affect the
outcome.)


The only other two failures are the join test when both merge and hash
joins are disabled and alter_table without index scans.  Both seem
harmless; see attached diffs.

The former is related to outer joins apparently not working with nest
loops.  The latter is a missing ORDER BY, which I'm inclined to fix.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/
Wed, 22 Nov 2000 17:44:43 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
regressplans failures
Peter Eisentraut <peter_e@gmx.net> writes:
> #3  0x8149b98 in ExceptionalCondition (
>     conditionName=0x81988a0 "!(((file) > 0 && (file) < (int) SizeVfdCache
> && VfdCache[file].fileName != ((void *)0)))", exceptionP=0x81b93c8,
> detail=0x0,
>     fileName=0x8198787 "fd.c", lineNumber=851) at assert.c:70
> #4  0x8105e6e in FileSeek (file=33, offset=0, whence=2) at fd.c:851

I'm guessing this is a variant of the problem Philip Warner reported
yesterday.  Probably WAL-related.  Vadim?

> The only other two failures are the join test when both merge and hash
> joins are disabled and alter_table without index scans.  Both seem
> harmless; see attached diffs.
> The former is related to outer joins apparently not working with nest
> loops.  The latter is a missing ORDER BY, which I'm inclined to fix.

FULL JOIN currently is only implementable by mergejoin (if you can
figure out how to do it with a nest or hash join, I'm all ears...).
I guess it's a bug that the planner honors enable_mergejoin = OFF
even when given a FULL JOIN query.  (At least the failure detection
code works, though ;-).)  I'll see what I can do about that.

I'd be inclined *not* to add ORDER BYs just to make regressplans produce
zero diffs in all cases.  The presence of an ORDER BY may cause the
planner to prefer presorted-output plans, thus defeating the purpose
of testing all plan types...

			regards, tom lane

Wed, 22 Nov 2000 12:12:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: regressplans failures 