Segfaults and assertion failures with not too extraordinary views and queries

I have been attempting to migrate my application from 8.1 to 8.2.3.
In doing so, I found some queries would always cause the postgres
backend to die with a segfault. I was advised to rebuild with --
enable-debug --enable-cassert, and so I did. The same query would now
cause an assertion failure instead of segfaulting. I reduced a dump
of my database as much as possible and arrived at this:


--------------------------------------------

SET client_min_messages = warning;


CREATE SCHEMA private;


CREATE TABLE private.orderitem (
     objectid integer
);


CREATE FUNCTION public.i_have_global_priv() RETURNS boolean
     AS $$
     select true;
$$
     LANGUAGE sql STABLE SECURITY DEFINER;       -- removing security
definer avoids the problem


CREATE TABLE private.orderitemproduct (
     objectid integer NOT NULL
);


CREATE VIEW public.orderitemproduct AS
     SELECT orderitemproduct.objectid FROM private.orderitemproduct
WHERE i_have_global_priv();  -- not calling i_have_global_priv avoids
the problem


CREATE VIEW public.orderitem_with_prices AS
     SELECT 1 FROM private.orderitem LEFT JOIN orderitemproduct USING
(objectid);


-- removing either pkey avoids the problem

ALTER TABLE ONLY private.orderitem
     ADD CONSTRAINT orderitem_pkey PRIMARY KEY (objectid);

ALTER TABLE ONLY private.orderitemproduct
     ADD CONSTRAINT orderitemproduct_pkey PRIMARY KEY (objectid);


SELECT * FROM orderitem_with_prices; -- succeeds
SELECT * FROM orderitem_with_prices limit 1; -- fails, output below

--------------------------------------------

Apparently, even asking "EXPLAIN SELECT * FROM orderitem_with_prices
limit 1;" causes the same problem. The server log will contain this:

TRAP: FailedAssertion("!(!(eflags & 0x0008))", File: "nodeResult.c",
Line: 183)
LOG:  server process (PID 12838) was terminated by signal 6
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2007-02-14 17:00:32 EST
LOG:  checkpoint record is at 0/4AA38710
LOG:  redo record is at 0/4AA38710; undo record is at 0/0; shutdown
FALSE
LOG:  next transaction ID: 0/12285; next OID: 2457841
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system was not properly shut down; automatic recovery
in progress
LOG:  redo starts at 0/4AA38758
LOG:  unexpected pageaddr 0/43A54000 in log file 0, segment 74,
offset 10829824
LOG:  redo done at 0/4AA53B24
LOG:  database system is ready


gcc provides the wisdom:


Program received signal SIGABRT, Aborted.
0x9004796c in kill ()
(gdb) bt
#0  0x9004796c in kill ()
#1  0x9012dc14 in abort ()
#2  0x00206fa0 in ExceptionalCondition (conditionName=0x2 <Address
0x2 out of bounds>, errorType=0x25 <Address 0x25 out of bounds>,
fileName=0x8 <Address 0x8 out of bounds>, lineNumber=80) at assert.c:51
#3  0x000f8c18 in ExecInitResult (node=0xe48740, estate=0x204901c,
eflags=8) at nodeResult.c:183
#4  0x000e60f8 in ExecInitNode (node=0xe48740, estate=0x204901c,
eflags=8) at execProcnode.c:141
#5  0x000f7aa0 in ExecInitMergeJoin (node=0xe4885c, estate=0x204901c,
eflags=0) at nodeMergejoin.c:1539
#6  0x000e61c8 in ExecInitNode (node=0xe4885c, estate=0x204901c,
eflags=0) at execProcnode.c:212
#7  0x000fa64c in ExecInitLimit (node=0xe488e8, estate=0x204901c,
eflags=0) at nodeLimit.c:337
#8  0x000e6258 in ExecInitNode (node=0xe488e8, estate=0x204901c,
eflags=0) at execProcnode.c:260
#9  0x000e5274 in ExecutorStart (queryDesc=0x2045c48, eflags=0) at
execMain.c:628
#10 0x001863b8 in PortalStart (portal=0x204701c, params=0x0,
snapshot=0x0) at pquery.c:426
#11 0x00182388 in exec_simple_query (query_string=0x202da1c "select *
from orderitem_with_prices limit 1;") at postgres.c:902
#12 0x00183c98 in PostgresMain (argc=4, argv=0x2011790,
username=0x20118a8 "postgres") at postgres.c:3424
#13 0x00154880 in ServerLoop () at postmaster.c:2931
#14 0x00155d38 in PostmasterMain (argc=3, argv=0x1900750) at
postmaster.c:963
#15 0x001094fc in main (argc=3, argv=0x1900780) at main.c:188


pg_config tells me that i run:


BINDIR = /usr/local/pgsql/bin
DOCDIR = /usr/local/pgsql/doc
INCLUDEDIR = /usr/local/pgsql/include
PKGINCLUDEDIR = /usr/local/pgsql/include
INCLUDEDIR-SERVER = /usr/local/pgsql/include/server
LIBDIR = /usr/local/pgsql/lib
PKGLIBDIR = /usr/local/pgsql/lib
LOCALEDIR =
MANDIR = /usr/local/pgsql/man
SHAREDIR = /usr/local/pgsql/share
SYSCONFDIR = /usr/local/pgsql/etc
PGXS = /usr/local/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-python' '--with-openssl' '--enable-debug' '--
enable-cassert'
CC = gcc -no-cpp-precomp
CPPFLAGS =
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -
Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g
CFLAGS_SL =
LDFLAGS =
LDFLAGS_SL =
LIBS = -lpgport -lssl -lcrypto -lz -lreadline -lm
VERSION = PostgreSQL 8.2.3


uname adds:


Darwin Coding-Mac.local 8.8.0 Darwin Kernel Version 8.8.0: Fri Sep  8
17:18:57 PDT 2006; root:xnu-792.12.6.obj~1/RELEASE_PPC Power
Macintosh powerpc