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

BUG #5900: Coredump on executing query

From: "Sergey Aleynikov" <sergey(dot)aleynikov(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5900: Coredump on executing query
Date: 2011-02-25 12:11:08
Message-ID: 201102251211.p1PCB8Vo083286@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5900
Logged by:          Sergey Aleynikov
Email address:      sergey(dot)aleynikov(at)gmail(dot)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;

Responses

pgsql-bugs by date

Next:From: Merlin MoncureDate: 2011-02-25 14:46:29
Subject: Re: Function trunc() behaves in unexpected manner with different data types
Previous:From: Jakub OuhrabkaDate: 2011-02-25 11:20:33
Subject: Corrupted index on 9.0.3 streaming hot standby

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