BUG #5900: Coredump on executing query - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5900
Logged by:          Sergey Aleynikov
Email address:      sergey.aleynikov@gmail.com
PostgreSQL version: 8.4.1
Operating system:   FreeBSD 7.3-STABLE amd64
Description:        Coredump on executing query
Details:

I've a setup with 'auto_explain' enabled:

shared_preload_libraries = 'auto_explain'
custom_variable_classes = 'auto_explain'
auto_explain.log_min_duration = '3s'
auto_explain.log_nested_statements = true

Yesterday i've got a non-repeatable database server crash with following
messages in server log:

<start of normal query explain output>
Feb 24 17:44:25 sigeon postgres[91789]: [5-28]
         ->  Index Scan using ind_log_1573_reversed on logs_1573 logs
(cost=0.00..
Feb 24 17:44:25 sigeon postgres[91789]: [5-29]
               Index Cond: (opcode = 2302)
Feb 24 17:44:25 sigeon postgres[91789]: [5-30]                            ->
 Index Scan using "ind_users_modiifers_u+mod" on users_modifiers
(cost=0.00..4.15
Feb 24 17:44:25 sigeon postgres[91789]: [5-31]
   Index Cond: ((users_modifiers.uid = public.logs.uid) AND
(users_modifiers.modifi
Feb 24 17:44:25 sigeon postgres[91789]: [5-32]                      ->
Index Scan using pkey_usersinfo on users_info  (cost=0.00..4.20 rows=1
width=42)
Feb 24 17:44:25 sigeon postgres[91789]: [5-33]
Index Cond: (users_info.uid = users_modifiers.uid)
Feb 24 17:44:25 sigeon postgres[91789]: [5-34]
Filter: ((users_info.regdate >= $1) AND (users_info.regdate < $2))
Feb 24 17:44:25 sigeon postgres[91789]: [5-35] CONTEXT:  PL/pgSQL function
"get_register_leveled_stats" line 3 at RETURN QUERY
Feb 24 17:44:25 sigeon postgres[91789]: [5-36] STATEMENT:  SELECT * FROM
get_register_leveled_stats('02/24/11 00:00:00','02/25/11 00:00:00');
Feb 24 17:44:28 sigeon postgres[1166]: [5-1] LOG:  server process (PID
91789) was terminated by signal 11: Segmentation fault
Feb 24 17:44:28 sigeon postgres[1166]: [6-1] LOG:  terminating any other
active server processes
Feb 24 17:44:28 sigeon postgres[92550]: [7-1] FATAL:  the database system is
in recovery mode
Feb 24 17:44:28 sigeon postgres[1166]: [7-1] LOG:  archiver process (PID
1171) exited with exit code 1

Backtrace is:

(gdb) bt
#0  0x000000000060bf79 in quote_identifier ()
#1  0x00000000004ed45c in explain_outNode ()
#2  0x00000000004ee253 in ExplainPrintPlan ()
#3  0x000000080120112a in explain_ExecutorEnd () from
/usr/local/pgsql/lib/auto_explain.so
#4  0x00000000004fa3cf in PortalCleanup ()
#5  0x000000000067389a in PortalDrop ()
#6  0x00000000005bf2e9 in exec_simple_query ()
#7  0x00000000005bffd7 in PostgresMain ()
#8  0x0000000000599287 in ServerLoop ()
#9  0x0000000000599f7e in PostmasterMain ()
#10 0x000000000054ce64 in main ()

Since this is non-repeatable crash (this is common statistical query, run
tens times a day), i can't make a debug build of PG to show more info.
Executed query (get_register_leveled_stats) was following:

CREATE OR REPLACE FUNCTION get_register_leveled_stats(_from timestamp
without time zone, _to timestamp without time zone) RETURNS setof
f_grls_result AS
$BODY$
begin

return query
    select z.*, z.a + z.b + z.c + z.d + z.e from (
        select sum(other)::integer as a, sum(vk)::integer as b,
sum(vk_ref)::integer as c, sum(mailru)::integer as d, sum(od)::integer as e,
value::integer
            from (
                select     uid,
                    case when eid is null and mlid is null then 1 else 0 end as other,
                    case when eid is not null and net = 0 and referral is null then 1 else
0 end as vk,
                    case when eid is not null and net = 0  and referral is not null then 1
else 0 end as vk_ref,
                    case when eid is not null and net = 12  then 1 else 0 end as od,
                    case when mlid is not null then 1 else 0 end as mailru
                        from users_info
                    where regdate >= _from and regdate < _to
            ) z
            inner join users_modifiers on (users_modifiers.uid = z.uid)
            where     modifiyer = 808
                and origin = 239
            group by value
            order by value asc
    ) z
;

end
$BODY$
  LANGUAGE 'plpgsql' stable;