The following bug has been logged online:
Bug reference: 5049
Logged by: Thomas
Email address: me@alternize.com
PostgreSQL version: 8.4.0
Operating system: Debian lenny
Description: query crashing backend with TRAP: FailedAssertion
Details:
while trying out the queries from
http://radek.cc/2009/09/05/psqlrc-tricks-indexes/, i found the 2nd one
crashing the database backend:
select src_table, dst_table, fk_name, pg_size_pretty(s_size) as s_size,
pg_size_pretty(d_size) as d_size, d from ( select distinct on (1,2,3,4,5)
textin(regclassout(c.conrelid)) as src_table,
textin(regclassout(c.confrelid)) as dst_table, c.conname as fk_name,
pg_relation_size(c.conrelid) as s_size, pg_relation_size(c.confrelid) as
d_size, array_upper(di.indkey::int[], 1) + 1 - array_upper(c.conkey::int[],
1) as d from pg_constraint c left join pg_index di on di.indrelid =
c.conrelid and array_to_string(di.indkey, ' ') ~ ('^' ||
array_to_string(c.conkey, ' ') || '( |$)') join pg_stat_user_tables st on
st.relid = c.conrelid where c.contype = 'f' order by 1,2,3,4,5,6 asc) mfk
where mfk.d is distinct from 0 and mfk.s_size > 1000000 order by mfk.s_size
desc, mfk.d desc;
the postgresql log shows
TRAP: FailedAssertion("!(pathkeys_contained_in(root->distinct_pathkeys,
current_pathkeys))", File: "planner.c", Line: 1527)
just before terminating & restarting all backends. the error happens every
time issuing the query. relevant log extract available on
http://pastebin.com/m1f0dcb85
the conflicting part of the query seem to be "distinct on (1,2,3,4,5)",
removing this does not produce the failed assertion trap.
as there's currently no 8.4.1 backport for lenny available, i'm unable to
test if it also happens in the latest version.
regards,
thomas