[#1011289] certain queries with FDW cause Postgresql to crash

View Trackers | Bugs | Download .csv | Monitor

Date:
2012-12-11 23:56 Priority:
3

State:
Closed

Submitted by:
Jim Kennedy (jim_kennedy) Assigned to:
Laurenz Albe (albe)

Category:
Runtime Error Group:
None

Resolution:
Could not Reproduce

Summary:
certain queries with FDW cause Postgresql to crash

Detailed description
I have a table in Oracle called subscriber. I have a foreign data table called oracle.subscriber. I have a local (postgresql local) table called common.subscriber. All 3 have the same structure. with the exception of a column in postgresql which is boolean. (In Oracle it could be a 1 or 0 or T or F Y or N etc.) I have an overloaded function in Postgresql called utility.map_to_boolean which takes an argument and returns TRUE, FALSE or null::boolean. If I do: (s2 is a FDW table, s1 is a normal Postgresql table) select count() from common.subscriber s1 where not exists ( select 1 from oracle.subscriber s2 where (s1.subscriber_id=s2.subscriber_code::integer or (s1.subscriber_id is NULL AND s2.subscriber_code is NULL)) AND -- works (s1.subscriber_name=s2.subscriber_name or (s1.subscriber_name is NULL AND s2.subscriber_name is NULL)) AND -- works (s1.subscriber_desc=s2.subscriber_desc or (s1.subscriber_desc is NULL AND s2.subscriber_desc is NULL)) AND -- works (s1.is_disabled=utility.map_to_boolean(s2.is_disabled) or (s1.is_disabled is NULL AND s2.is_disabled is NULL)) ); The Postgresql instance crashes. If I remove that last line and do: select count() from common.subscriber s1 where not exists ( select 1 from oracle.subscriber s2 where (s1.subscriber_id=s2.subscriber_code::integer or (s1.subscriber_id is NULL AND s2.subscriber_code is NULL)) AND -- works (s1.subscriber_name=s2.subscriber_name or (s1.subscriber_name is NULL AND s2.subscriber_name is NULL)) AND -- works (s1.subscriber_desc=s2.subscriber_desc or (s1.subscriber_desc is NULL AND s2.subscriber_desc is NULL)) ); Then the query is fine.

Followup

Message
Date: 2013-01-23 08:37 Sender: Laurenz Albe Is it possible to get more detailed information? If not, I'll have to close the bug as not reproducible. Yours, Laurenz Albe
Date: 2012-12-12 09:13 Sender: Laurenz Albe Which version of oracle_fdw, Oracle and PostgreSQL are you using? Please post at least the table definitions. If there need to be certain data in the tables to provoke the crash, I'd need information about these too. The best thing would be a self-contained test case. Can you produce a core dump and get a stack trace?

Attached Files:

Changes:

Field	Old Value	Date	By
status_id	Open	2013-02-22 15:35	albe
close_date	None	2013-02-22 15:35	albe
Resolution	None	2013-02-22 15:35	albe
assigned_to	none	2012-12-12 09:13	albe