psql 7.3.1: signal 11 + replace_vars_with_subplan_refs: variable not in subplan target list

Поиск
Список
Период
Сортировка
От Stefanos Harhalakis
Тема psql 7.3.1: signal 11 + replace_vars_with_subplan_refs: variable not in subplan target list
Дата
Msg-id 200302022247.04882.v13@it.teithe.gr
обсуждение исходный текст
Ответы Re: psql 7.3.1: signal 11 + replace_vars_with_subplan_refs: variable not in subplan target list  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
I'm stuck here with a query that kills postgresql backend.

I've installed postgresql 7.3.1 on two machines..
one with gcc 3.2, glibc 2.3.1 source based (a)
and the other with gcc 2.95.3, glibc 2.2.3 slackware 8 (b)
both are PIII with kernel 2.4.20.
(a) has ext3 and (b) has xfs for the database storage...

tested both with altered and original postgresql.conf...

I've the same program installed on both machines using the same database...
both fail when i'm executing a specific query...

Those are the errors (I've removed a lot from the queries to isolate the
problem):

This fails:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND v.sw_ip IS
NULL AND ( h.mac = v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) );
ERROR:  replace_vars_with_subplan_refs: variable not in subplan target list

but this works:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac =
v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) );

this fails too:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac =
v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) AND ( ( h.sw_ip IS NULL AND
v.sw_ip IS NULL ) )  );
ERROR:  replace_vars_with_subplan_refs: variable not in subplan target list

and this causes a segmentation fault:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac =
v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) AND ( h.sw_ip = v.sw_ip OR (
h.sw_ip IS NULL AND v.sw_ip IS NULL ) )  );
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

To reproduce it:
createdb test
createlang plpgsql test
psql -f create.sql test

and then run this:
select * from view_all v where NOT EXISTS ( SELECT * FROM history h WHERE (
h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac = v.mac OR (
h.mac IS NULL AND v.mac IS NULL ) ) AND ( h.sw_ip = v.sw_ip OR ( h.sw_ip IS
NULL AND v.sw_ip IS NULL ) )  );

<<V13>>

--
Key fingerprint: 7448 3D26 EA5D 275C 749A  B597 3576 0073 6AD2 9335

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Bug #893: Trigger causes database to crash
Следующее
От: "Insyde"
Дата:
Сообщение: Re: Problem when adding an existing primary key