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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
