Subject: V4.0 problems

From: David Feldcamp <[email-masked]>
Date: Thu, 16 Jul 92 13:08:43 -0700

I have loaded and installed V4.0 on a Sun IPX, SunOS 4.1.2 and am experiencing two 
	serious problems:

[1]  Generally worse performance than with V3.1 for my application (Postgres via libpq 
	used as a backend for a software engineering GUI written in C).
	I would not be surprised if performance did not improve significantly, but 
	I would like to know if there is anything fixable that might be causing
	the slowdown.

[2]  A fatal backend crash on a relatively simple query which works fine in V3.1.  The
	problem seems to be a pointer being NULL when it shouldn't.  Here is
	the output from the backend execution under dbx:

Running: postgres transputer 
	---debug info---
	Quiet =        f
	Noversion =    f
	override  =    f
	stable    =    f
	parallel  =    f
	timings   =    f
	bufsize   =    64
	query echo =   f
	DatabaseName = [transputer]
	----------------

	**** Transaction System Active ****
	InitPostgres()..

POSTGRES backend interactive interface
$Revision: 1.144 $ $Date: 1992/06/28 05:43:01 $
	StartTransactionCommand() at Thu Jul 16 09:46:16 1992

> retrieve unique (b.edge) from a, b in graph where (a.node = "Process 0") and (a.mid = 1) and (a.edge = b.edge) and (a.node != b.node) and (views.pid = 1149) and (b.mid = views.mid) sort by edge
	init_planner()..
	ProcessQuery() at Thu Jul 16 09:46:21 1992

blank
	 1: edge	(typeid = 25, len = -1, byval = f)
	----
signal SEGV (no mapping at the fault address) in ExecEvalVar at line 342 in file "obj.sparc/executor/ex_qual.c"
  342       tuple_type = 	    ExecSlotDescriptor((Pointer)slot);
(dbx) w
unrecognized command/syntax "w"
(Type 'help' for help)
(dbx) where
ExecEvalVar(variable = 0x252dc0, econtext = 0x2867b0, isNull = 0xf7ffd247 ""), line 342 in "obj.sparc/executor/ex_qual.c"
ExecHashGetBucket(hashtable = 0x2873c8, econtext = 0x2867b0, hashkey = 0x252dc0), line 655 in "obj.sparc/executor/n_hash.c"
ExecHashTableInsert(hashtable = 0x2873c8, econtext = 0x2867b0, hashkey = 0x252dc0, batches = (nil)), line 539 in "obj.sparc/executor/n_hash.c"
ExecHash(node = 0x283880), line 138 in "obj.sparc/executor/n_hash.c"
ExecProcNode(node = 0x283880), line 411 in "obj.sparc/executor/ex_procnode.c"
ExecHashJoin(node = 0x2838e8), line 147 in "obj.sparc/executor/n_hashjoin.c"
ExecProcNode(node = 0x2838e8), line 415 in "obj.sparc/executor/ex_procnode.c"
ExecSort(node = 0x2844c8), line 180 in "obj.sparc/executor/n_sort.c"
ExecProcNode(node = 0x2844c8), line 394 in "obj.sparc/executor/ex_procnode.c"
ExecUnique(node = 0x284528), line 126 in "obj.sparc/executor/n_unique.c"
ExecProcNode(node = 0x284528), line 398 in "obj.sparc/executor/ex_procnode.c"
ExecutePlan(estate = 0x2847f0, plan = 0x284528, parseTree = 0x258bb8, operation = 278, numberTuples = 0, direction = 1, printfunc = &debugtup() at 0x6554), line 636 in "obj.sparc/executor/ex_main.c"
ExecMain(queryDesc = 0x2845f8, estate = 0x2847f0, feature = 0x284a88), line 184 in "obj.sparc/executor/ex_main.c"
ProcessQueryDesc(queryDesc = 0x2845f8), line 450 in "obj.sparc/tcop/pquery.c"
ProcessQuery(parsertree = 0x258bb8, plan = 0x284528, argv = (nil), typev = (nil), nargs = 0, dest = Debug), line 498 in "obj.sparc/tcop/pquery.c"
pg_eval_dest(query_string = 0xf7ffdaec "retrieve unique (b.edge) from a, b in graph where (a.node = "Process 0") and (a.mid = 1) and (a.edge = b.edge) and (a.node != b.node) and (views.pid = 1149) and (b.mid = views.mid) sort by edge", argv = (nil), typev = (nil), nargs = 0, dest = Debug), line 787 in "obj.sparc/tcop/postgres.c"
pg_eval(query_string = 0xf7ffdaec "retrieve unique (b.edge) from a, b in graph where (a.node = "Process 0") and (a.mid = 1) and (a.edge = b.edge) and (a.node != b.node) and (views.pid = 1149) and (b.mid = views.mid) sort by edge", argv = (nil), typev = (nil), nargs = 0), line 635 in "obj.sparc/tcop/postgres.c"
PostgresMain(argc = 2, argv = 0xf7fffbd4), line 1429 in "obj.sparc/tcop/postgres.c"
main(argc = 2, argv = 0xf7fffbd4), line 26 in "obj.sparc/main/main.c"
(dbx) print slot
`ex_qual`ExecEvalVar`slot = (nil)

Tables involved:

create graph (mid=int4, node=text, port=text, edge=text)\g

create node (mid=int4, name=text, ptype=text, kind=int2, weight=int2, cconfig=text, vid=int4, x=int4, y=int4)\g

create edge (mid=int4, name=text, weight=int2, width=int2, color=int2)\g

create views (pid=int4, mid=int4, iid=int4, vid=int4, 
				pmid=int4, piid=int4, pvid=int4, 
				name=text, kind=int4, x=int4, y=int4, 
				color=int2, bg_color=int2, def_nc=int2, 
				def_sgc=int2, def_ec=int2)\g

---

From: postarch (Postgres Mailing Archive)
Date: Thu, 23 Jul 92 14:51:19 PDT

you write:

> [2]  A fatal backend crash on a relatively simple query which works fine in V
 +3.1.  The
> 	the output from the backend execution under dbx:
 [rest deleted]

The problem resulted from a bug in the planner in dealing with joins on text
or char16 attributes.  Due to the severity of this and a bug introduced to
the rewrite rule system we are going to cut a 4.0.1 bug fix release.  In the
coming week we will try to fix to all of the bugs already reported and release
shortly after that.  In the interim there is a relatively easy way to get
around the join bug.  It involves changing the postmaster to exec postgres
with an additional option that turns off expensive function optimization.
You have to do it on two lines.  Here is a diff:

816c816
<     execl(execFile, execFile, "-p", debugBuf, portBuf, database, NULL);
---
>     execl(execFile, execFile, "-p", "-xoff", debugBuf, portBuf, database, NULL);
820c820
<       execl(execFile, execFile, "-p", debugBuf, portBuf, args, database, NULL);
---
>       execl(execFile, execFile, "-p", "-xoff", debugBuf, portBuf, args, database, NULL);

You need to add the -xoff string to the arguments of execl().  The postmaster
can be found in $POSTGRESHOME/src/support/postmaster.c

After making the change you should run:

	Make postmaster install

Jeff Meredith
[email-masked]