[BUGS] Segfaults and assertion failures with not too extraordinary views and queries

Wed, 14 Feb 2007 14:19:44 -0800

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly






















					Reply via email to