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
