PostgreSQL Bugs - BUG #2106: EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault

Author

BUG #2106: EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault

2005-12-10, 1:24 pm

The following bug has been logged online:

Bug reference: 2106
Logged by:
Email address: bugrep@oldanygroup.cz
PostgreSQL version: 8.1.0, 8.2devel
Operating system: CentOS 4.2 with 2.6.13.4-SMP kernel
Description: EXPLAIN ANALYZE with SELECT query causes a single
backend server process to segfault
Details:

Hello,

while testing the 8.1.0 version of PostgreSQL (we've been using version
7.4.8 for a long time), the EXPLAIN ANALYZE causes a single backend server
process to segfault after issuing the following query:

SELECT obch_vyrobek.id_vyrobku AS id_vyrobku,
obch_vyrobek.id_vyrobce AS id_vyrobce,
obch_vyrobce.nazev AS nazev_vyrobce,
CASE obch_vyrobek.nazev ILIKE '%aloe%' WHEN true THEN 1 ELSE 0 END AS
match_nazev_vyrobku
FROM obch_vyrobek LEFT JOIN obch_vyrobce USING (id_vyrobce)
WHERE (obch_vyrobek.aktivni AND ((obch_vyrobek.sukl IS NULL AND
obch_vyrobek.v_katalogu) OR (obch_vyrobek.sukl IS NOT NULL) OR
(obch_vyrobek. odhadovana_dostupnos
t IS NOT NULL))) AND (obch_vyrobek.nazev
ILIKE
'%aloe%' OR obch_vyrobek.popis ILIKE '%aloe%' OR
coalesce(obch_vyrobe
k. popis_od_vyrobce,obc
h_vyrobek.popis) ILIKE '%aloe%'
OR
obch_vyrobce.nazev ILIKE '%aloe%')
ORDER BY otc DESC, typ DESC, odhadovana_dostupnos
t DESC, obr_m DESC,
match_nazev_vyrobku DESC, obch_vyrobek.nazev;

The same happens with version 8.2devel. There are no segfaults with such
EXPLAIN ANALYZE followed by abovementioned query when using version 7.4.8,
even 8.0.4 is ok.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2005-12-10, 1:24 pm

"" <bugrep@oldanygroup.cz> writes:
> Description: EXPLAIN ANALYZE with SELECT query causes a single
> backend server process to segfault

Could you provide a self-contained test case, please? Guessing at your
table schemas does not seem a productive use of time. See
http://www.postgresql.org/docs/8.1/...-reporting.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Michael Fuhr

2005-12-10, 1:24 pm

On Sat, Dec 10, 2005 at 12:57:13PM -0500, Tom Lane wrote:
> "" <bugrep@oldanygroup.cz> writes:
>
> Could you provide a self-contained test case, please?

Here's a test case:

CREATE TABLE foo (x integer);

EXPLAIN
SELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y
FROM foo
ORDER BY y;

I get a segfault in 8.1.1 with EXPLAIN but not for the query alone.
Here's the stack trace:

#0 0x081bfdcf in get_rule_expr (node=0x83ac460, context=0xbfbfd4b0, showimplicit=80 'P') at pg_list.h:82
#1 0x081c0e73 in deparse_expression_p
retty (expr=0x83ac460, dpcontext=0x83ac7b0,
forceprefix=0 '\0', showimplicit=1 '\001', prettyFlags=32, startIndent=32)
at ruleutils.c:1388
#2 0x080ef4a5 in explain_outNode (str=0x83ac648, plan=0x83ac388, planstate=0x83ad1f0,
outer_plan=0x0, indent=0, es=0x83ac638) at explain.c:1160
#3 0x080ef90d in ExplainOnePlan (queryDesc=0x83ac5f0
, stmt=0x835b708, tstate=0x8305b00) at explain.c:282
#4 0x080efca5 in ExplainOneQuery (query=0x83c7580, stmt=0x835b708, tstate=0x8305b00) at explain.c:214
#5 0x080efdcd in ExplainQuery (stmt=0x835b708, dest=0x83c7530) at explain.c:121
#6 0x0818cb2f in PortalRunUtility (portal=0x83ca018, query=0x835b228, dest=0x83c7530, completionTag=0x0) at pquery.c:987
#7 0x0818ce16 in PortalRun (portal=0x83ca018, count=2147483647, dest=0x835b748, altdest=0x835b748, completionTag=0xbfbf
d7b0 "") at pg_list.h:81
#8 0x08189127 in exec_simple_query (query_string=0x835b
018 "EXPLAIN\nSELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y\nFROM foo\nORDER BY y;") at postgres.c:1002
#9 0x0818b693 in PostgresMain (argc=4, argv=0x82e4d98, username=0x82e4d78 "mfuhr") at postgres.c:3168
#10 0x081684e6 in ServerLoop () at postmaster.c:2853
#11 0x08169a01 in PostmasterMain (argc=3, argv=0xbfbfecb8) at postmaster.c:943
#12 0x08133612 in main (argc=3, argv=0xbfbfecb8) at main.c:256

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2005-12-10, 1:24 pm

Michael Fuhr <mike@fuhr.org> writes:
> On Sat, Dec 10, 2005 at 12:57:13PM -0500, Tom Lane wrote:
[color=darkred]
> Here's a test case:

Thanks, I'll fix this ... but I'm not certain it is the same problem the
OP is seeing. The OR join clauses he's got look related to other bugs
we've found in 8.1.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2005-12-10, 8:24 pm

Michael Fuhr <mike@fuhr.org> writes:
> CREATE TABLE foo (x integer);

> EXPLAIN
> SELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y
> FROM foo
> ORDER BY y;

Fixed, but this is a bit too late for 8.1.1. Meanwhile the easy way to
avoid the bug is to write the CASE in a less obtuse form, like
CASE WHEN x = 1 THEN 1 ELSE 0 END
or
CASE x WHEN 1 THEN 1 ELSE 0 END

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links