Optimizer problem with subselect.c?

Hi all.

Interesting result when migrating from 7.2.? to 7.4.1, the dump and import went fine, no errors or whistles, in fact all my tests went through okay but a few types of queries which once worked, now don't, and in an odd fashion.

When I checked out the mailing lists and changelogs (in cvs) it seems there is some gotchas in the subselect code in the optimizer/plan section, so I have a hunch it's there somewhere.

select count(id) from connection_info where now() - opentime <= '1 day';
ERROR: variable not found in subplan target lists

This particular error seems to have some fame attached to it...

Anyway, here's the vitals, including our layout:

View "connection_info"
Column | Type | Modifiers
------------------------+--------------------------+-----------
location_appearance_id | integer |
status | integer |
termination_reason | integer |
inputoctets | integer |
outputoctets | integer |
opentime | timestamp with time zone |
termtime | timestamp with time zone |
end_time | timestamp with time zone |
lastpackettime | timestamp with time zone |
id | integer |
session_status | integer |
connection_status | integer |
location_id | integer |
client_ip | inet |
client_mac | macaddr |
original_url | text |
sshash | character varying(64) |
login_id | integer |
organization_id | integer |
connection_timeout | interval |
roaming_username | text |
actual_used | double precision |
current_used | double precision |
idle | double precision |
View definition: SELECT "session".location_appearance_id, "session".status, "session".termination_reason, "session".inputoctets, "session".outputoctets, "session".opentime, "session".termtime, "session".end_time, "session".lastpackettime, "session".id, "session".status AS session_status, "session".status AS connection_status, location_appearance.location_id, location_appearance.client_ip, location_appearance.client_mac, location_appearance.original_url, site_session.sshash, login_site_session.login_id, login.organization_id, login.connection_timeout, roaming_site_session.username AS roaming_username, date_part('epoch'::text, ("session".lastpackettime - "session".opentime)) AS actual_used, date_part('epoch'::text, (now() - "session".opentime)) AS current_used, date_part('epoch'::text, (now() - "session".lastpackettime)) AS idle FROM (((((("session" JOIN location_appearance ON (("session".location_appearance_id = location_appearance.id))) JOIN location_appearance_site_sessio ON
((location_appearance.id = location_appearance_site_sessio.location_appearance_id))) JOIN site_session ON (((location_appearance_site_sessio.site_session_sshash)::text = (site_session.sshash)::text))) LEFT JOIN login_site_session ON (((login_site_session.site_session_sshash)::text = (site_session.sshash)::text))) LEFT JOIN login ON ((login_site_session.login_id = login.id))) LEFT JOIN roaming_site_session ON (((roaming_site_session.site_session_sshash)::text = (site_session.sshash)::text))) WHERE (site_session.status <> 3);

-----------------

Table "session"
Column | Type | Modifiers
------------------------+--------------------------+-------------------------------------------------------
location_appearance_id | integer | not null
status | integer | default 0
termination_reason | integer |
inputoctets | integer | default 0
outputoctets | integer | default 0
opentime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
termtime | timestamp with time zone |
end_time | timestamp with time zone |
lastpackettime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
id | integer | not null default nextval('"connection_id_seq"'::text)
Indexes: connection_id_idx,
session_laid_idx,
session_status_idx
Triggers: RI_ConstraintTrigger_776160,
RI_ConstraintTrigger_776163,
RI_ConstraintTrigger_776397,
RI_ConstraintTrigger_776400

-------------------

Table "location_appearance"
Column | Type | Modifiers
--------------+--------------------------+----------------------------------------------------------------
id | integer | not null default nextval('"location_appearance_id_seq"'::text)
location_id | integer | not null
client_ip | inet | not null
client_mac | macaddr |
original_url | text |
create_time | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes: location_appearance_id_idx
Primary key: location_appearance_pk
Triggers: RI_ConstraintTrigger_776097,
RI_ConstraintTrigger_776101,
RI_ConstraintTrigger_776102,
RI_ConstraintTrigger_776164,
RI_ConstraintTrigger_776165,
RI_ConstraintTrigger_776334,
RI_ConstraintTrigger_776338,
RI_ConstraintTrigger_776339,
RI_ConstraintTrigger_776401,
RI_ConstraintTrigger_776402

-------------------

Table "site_session"
Column | Type | Modifiers
------------+--------------------------+----------------------------------------------------
sshash | character varying(64) | not null
createtime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
lastupdate | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
status | integer | default 0
Indexes: site_session_date_idx,
site_session_sshash_idx,
site_session_status_idx
Primary key: site_session_pk
Triggers: RI_ConstraintTrigger_776104,
RI_ConstraintTrigger_776105,
RI_ConstraintTrigger_776110,
RI_ConstraintTrigger_776111,
RI_ConstraintTrigger_776113,
RI_ConstraintTrigger_776114,
RI_ConstraintTrigger_776119,
RI_ConstraintTrigger_776120,
RI_ConstraintTrigger_776179,
RI_ConstraintTrigger_776180,
RI_ConstraintTrigger_776185,
RI_ConstraintTrigger_776186,
RI_ConstraintTrigger_776341,
RI_ConstraintTrigger_776342,
RI_ConstraintTrigger_776347,
RI_ConstraintTrigger_776348,
RI_ConstraintTrigger_776350,
RI_ConstraintTrigger_776351,
RI_ConstraintTrigger_776356,
RI_ConstraintTrigger_776357,
RI_ConstraintTrigger_776416,
RI_ConstraintTrigger_776417,
RI_ConstraintTrigger_776422,
RI_ConstraintTrigger_776423

-------------------

Table "location_appearance_site_sessio"
Column | Type | Modifiers
------------------------+-----------------------+-----------
location_appearance_id | integer | not null
site_session_sshash | character varying(64) | not null
Indexes: lass_id_idx,
lass_sshash_idx
Triggers: RI_ConstraintTrigger_776100,
RI_ConstraintTrigger_776103,
RI_ConstraintTrigger_776337,
RI_ConstraintTrigger_776340

------------------

Table "login"
Column | Type | Modifiers
--------------------+--------------------------+-------------------------------------------------------------
id | integer | not null default nextval('"login_id_seq"'::text)
organization_id | integer | not null
email | text | not null
realname | character varying(32) | default ''::character varying
title | character varying(32) | default ''::character varying
question | character varying(32) | default ''::character varying
answer | character varying(32) | default ''::character varying
contact_method | integer | default 0
connection_timeout | interval | default '00:15:00'::interval
auto_connect | boolean | not null default true
createdate | timestamp with time zone | not null default ('now'::text)::timestamp(6) with time zone
Primary key: login_pk
Unique keys: login_id_key
Triggers: RI_ConstraintTrigger_775965,
RI_ConstraintTrigger_775969,
RI_ConstraintTrigger_775970,
RI_ConstraintTrigger_776026,
RI_ConstraintTrigger_776027,
RI_ConstraintTrigger_776035,
RI_ConstraintTrigger_776036,
RI_ConstraintTrigger_776044,
RI_ConstraintTrigger_776045,
RI_ConstraintTrigger_776053,
RI_ConstraintTrigger_776054,
RI_ConstraintTrigger_776056,
RI_ConstraintTrigger_776057,
RI_ConstraintTrigger_776083,
RI_ConstraintTrigger_776084,
RI_ConstraintTrigger_776107,
RI_ConstraintTrigger_776108,
RI_ConstraintTrigger_776205,
RI_ConstraintTrigger_776209,
RI_ConstraintTrigger_776210,
RI_ConstraintTrigger_776263,
RI_ConstraintTrigger_776264,
RI_ConstraintTrigger_776272,
RI_ConstraintTrigger_776273,
RI_ConstraintTrigger_776281,
RI_ConstraintTrigger_776282,
RI_ConstraintTrigger_776290,
RI_ConstraintTrigger_776291,
RI_ConstraintTrigger_776293,
RI_ConstraintTrigger_776294,
RI_ConstraintTrigger_776320,
RI_ConstraintTrigger_776321,
RI_ConstraintTrigger_776344,
RI_ConstraintTrigger_776345

--------------------

Table "roaming_site_session"
Column | Type | Modifiers
---------------------+--------------------------+----------------------------------------------------
username | text | not null
site_session_sshash | character varying(64) | not null
createtime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes: roaming_sshash_idx
Triggers: RI_ConstraintTrigger_776112,
RI_ConstraintTrigger_776349

--------------------

Table "login_site_session"
Column | Type | Modifiers
---------------------+--------------------------+----------------------------------------------------
login_id | integer | not null
site_session_sshash | character varying(64) | not null
createtime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes: login_sshash_idx
Triggers: RI_ConstraintTrigger_776106,
RI_ConstraintTrigger_776109,
RI_ConstraintTrigger_776343,
RI_ConstraintTrigger_776346

-----------------------

And I think that's all of them. Most of the relevant data is in the original view (connection_info). Also, please pardon the obfuscation of the database, it's the softwares' fault!

Thanks,
--Daniel F. O'Neill
fatport.com

"Daniel O'Neill" <daniel(at)fatport(dot)com> writes:
> Anyway, here's the vitals, including our layout:

Could I trouble you to provide those table and view definitions as an
SQL script? (pg_dump -s will help you.) I'm too short of time to
manually convert your \d listings into something executable.

regards, tom lane

On Fri, 27 Feb 2004 00:57:36 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Daniel O'Neill" <daniel(at)fatport(dot)com> writes:
> > Anyway, here's the vitals, including our layout:
>
> Could I trouble you to provide those table and view definitions as an
> SQL script? (pg_dump -s will help you.) I'm too short of time to
> manually convert your \d listings into something executable.
>
> regards, tom lane
>

Hmm, the design isn't very 'pick aparty', would it be kosher of me to send you the full schema to work with? I can email it directly.

Thanks,
--Daniel

"Daniel O'Neill" <daniel(at)fatport(dot)com> writes:
> Hmm, the design isn't very 'pick aparty', would it be kosher of me to send you the full schema to work with? I can email it directly.

Sure, a pg_dump -s dump would be fine. If it's large you can just send
to me off-list.

It's possible that this is the same bug I just fixed in connection with
Damon Hart's bug report, but I'm not convinced of that; I'd still like
to reproduce your case here.

regards, tom lane

"Daniel O'Neill" <daniel(at)fatport(dot)com> writes:
> [ complex test case ]

Got it. The patch against 7.4.* is attached and will appear in 7.4.2.

regards, tom lane

Index: createplan.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/plan/createplan.c,v
retrieving revision 1.157.2.2
diff -c -r1.157.2.2 createplan.c
*** createplan.c 18 Jan 2004 00:31:53 -0000 1.157.2.2
--- createplan.c 29 Feb 2004 17:29:48 -0000
***************
*** 101,107 ****
static Sort *make_sort(Query *root, List *tlist, Plan *lefttree, int numCols,
AttrNumber *sortColIdx, Oid *sortOperators);
static Sort *make_sort_from_pathkeys(Query *root, Plan *lefttree,
! Relids relids, List *pathkeys);


/*
--- 101,107 ----
static Sort *make_sort(Query *root, List *tlist, Plan *lefttree, int numCols,
AttrNumber *sortColIdx, Oid *sortOperators);
static Sort *make_sort_from_pathkeys(Query *root, Plan *lefttree,
! List *pathkeys);


/*
***************
*** 1015,1021 ****
outer_plan = (Plan *)
make_sort_from_pathkeys(root,
outer_plan,
- best_path->jpath.outerjoinpath->parent->relids,
best_path->outersortkeys);
}

--- 1015,1020 ----
***************
*** 1025,1031 ****
inner_plan = (Plan *)
make_sort_from_pathkeys(root,
inner_plan,
- best_path->jpath.innerjoinpath->parent->relids,
best_path->innersortkeys);
}

--- 1024,1029 ----
***************
*** 1793,1799 ****
* Create sort plan to sort according to given pathkeys
*
* 'lefttree' is the node which yields input tuples
- * 'relids' is the set of relids represented by the input node
* 'pathkeys' is the list of pathkeys by which the result is to be sorted
*
* We must convert the pathkey information into arrays of sort key column
--- 1791,1796 ----
***************
*** 1806,1813 ****
* adding a Result node just to do the projection.
*/
static Sort *
! make_sort_from_pathkeys(Query *root, Plan *lefttree,
! Relids relids, List *pathkeys)
{
List *tlist = lefttree->targetlist;
List *sort_tlist;
--- 1803,1809 ----
* adding a Result node just to do the projection.
*/
static Sort *
! make_sort_from_pathkeys(Query *root, Plan *lefttree, List *pathkeys)
{
List *tlist = lefttree->targetlist;
List *sort_tlist;
***************
*** 1852,1863 ****
}
if (!resdom)
{
! /* No matching Var; look for an expression */
foreach(j, keysublist)
{
pathkey = lfirst(j);
! if (bms_is_subset(pull_varnos(pathkey->key), relids))
! break;
}
if (!j)
elog(ERROR, "could not find pathkey item to sort");
--- 1848,1869 ----
}
if (!resdom)
{
! /* No matching Var; look for a computable expression */
foreach(j, keysublist)
{
+ List *exprvars;
+ List *k;
+
pathkey = lfirst(j);
! exprvars = pull_var_clause(pathkey->key, false);
! foreach(k, exprvars)
! {
! if (!tlist_member(lfirst(k), tlist))
! break;
! }
! freeList(exprvars);
! if (!k)
! break; /* found usable expression */
}
if (!j)
elog(ERROR, "could not find pathkey item to sort");