Skip site navigation (1) Skip section navigation (2)

Segfaults and assertion failures with not too extraordinary views and queries

From: Phil Frost <phil(at)macprofessionals(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Segfaults and assertion failures with not too extraordinary views and queries
Date: 2007-02-14 22:18:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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 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  

-- 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  
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  
#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  
#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
MANDIR = /usr/local/pgsql/man
SHAREDIR = /usr/local/pgsql/share
SYSCONFDIR = /usr/local/pgsql/etc
PGXS = /usr/local/pgsql/lib/pgxs/src/makefiles/
CONFIGURE = '--with-python' '--with-openssl' '--enable-debug' '-- 
CC = gcc -no-cpp-precomp
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline - 
Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g
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


pgsql-bugs by date

Next:From: Tom LaneDate: 2007-02-14 22:49:16
Subject: Re: Segfaults and assertion failures with not too extraordinary views and queries
Previous:From: Bruce MomjianDate: 2007-02-14 18:57:18
Subject: Re: BUG #2997: parse error: setObject("2004-August-15",java.sql.Types.DATE)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group