Optimizer problem with subselect.c? - Mailing list pgsql-bugs
| From | Daniel O'Neill |
|---|---|
| Subject | Optimizer problem with subselect.c? |
| Date | |
| Msg-id | 20040226164252.6c0f68d1.daniel@fatport.com Whole thread Raw |
| Responses |
Re: Optimizer problem with subselect.c?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-bugs |
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
allmy 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
theoptimizer/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))
AScurrent_used, date_part('epoch'::text, (now() - "session".lastpackettime)) AS idle FROM (((((("session" JOIN
location_appearanceON (("session".location_appearance_id = location_appearance.id))) JOIN
location_appearance_site_sessioON
((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_sessionON (((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
pardonthe obfuscation of the database, it's the softwares' fault!
Thanks,
--Daniel F. O'Neill
fatport.com
pgsql-bugs by date: