Thread: psql 7.3.1: signal 11 + replace_vars_with_subplan_refs: variable not in subplan target list
psql 7.3.1: signal 11 + replace_vars_with_subplan_refs: variable not in subplan target list
From
Stefanos Harhalakis
Date:
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
Re: psql 7.3.1: signal 11 + replace_vars_with_subplan_refs: variable not in subplan target list
From
Tom Lane
Date:
Stefanos Harhalakis <v13@it.teithe.gr> writes:
> I'm stuck here with a query that kills postgresql backend.
Hoo, that was a fun one. Try this patch ...
regards, tom lane
*** src/backend/optimizer/plan/initsplan.c.orig Wed Sep 4 16:31:21 2002
--- src/backend/optimizer/plan/initsplan.c Thu Feb 6 18:39:07 2003
***************
*** 443,449 ****
else
{
/* this relid is for a true baserel */
! newrelids = lappendi(newrelids, lfirsti(relid));
}
}
relids = newrelids;
--- 443,450 ----
else
{
/* this relid is for a true baserel */
! if (!intMember(lfirsti(relid), newrelids))
! newrelids = lappendi(newrelids, lfirsti(relid));
}
}
relids = newrelids;
Re: psql 7.3.1: signal 11 + replace_vars_with_subplan_refs: variable not in subplan target list
From
Stefanos Harhalakis
Date:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Friday 07 February 2003 02:03, Tom Lane wrote: > Stefanos Harhalakis <v13@it.teithe.gr> writes: > > I'm stuck here with a query that kills postgresql backend. > > Hoo, that was a fun one. Try this patch ... First I tried psql 7.3.2 but it was segfaulting too.. Then I applied this patch to 7.3.1 and 7.3.2 and it fixed both versions. Thanks a lot! > regards, tom lane <<V13>> - --=20 Key fingerprint: 7448 3D26 EA5D 275C 749A B597 3576 0073 6AD2 9335 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.0 (GNU/Linux) iD8DBQE+RCSLNXYAc2rSkzURAhuVAJsFULwnN6QI2xyrlNLOTO5CMX5jsACgsZPD cLWQaX74d2WWGHcQ9XcPJ5Q=3D =3DlGJ4 -----END PGP SIGNATURE-----