pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcannot EXPLAIN query...
13+ messages / 6 participants
[nested] [flat]
* cannot EXPLAIN query...
@ 2003-02-03 14:02 Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:15 ` Re: cannot EXPLAIN query... Tomasz Myrta <jasiek@klaster.net>
2003-02-03 14:46 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Rajesh Kumar Mallah. @ 2003-02-03 14:02 UTC (permalink / raw)
To: pgsql-sql
Hi,
the query is running fine but i cannot EXPLAIN or (ANALYZE)
it.
I am seeing this message for the first time:
tradein_clients=# SELECT count(*) from shippers1 where city='DELHI';
+-------+
| count |
+-------+
| 2 |
+-------+
(1 row)
tradein_clients=#
tradein_clients=# explain SELECT count(*) from shippers1 where city='DELHI';
ERROR: get_names_for_var: bogus varno 5
tradein_clients=#
i can paste the nasty view definations if nothing is obvious till
now.
regds
MAllah.
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2003-02-03 14:15 ` Tomasz Myrta <jasiek@klaster.net>
1 sibling, 0 replies; 13+ messages in thread
From: Tomasz Myrta @ 2003-02-03 14:15 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
Rajesh Kumar Mallah. wrote:
> Hi,
>
> the query is running fine but i cannot EXPLAIN or (ANALYZE)
> it.
>
> I am seeing this message for the first time:
>
>
> tradein_clients=# SELECT count(*) from shippers1 where city='DELHI';
> +-------+
> | count |
> +-------+
> | 2 |
> +-------+
> (1 row)
>
> tradein_clients=#
> tradein_clients=# explain SELECT count(*) from shippers1 where city='DELHI';
> ERROR: get_names_for_var: bogus varno 5
> tradein_clients=#
>
> i can paste the nasty view definations if nothing is obvious till
> now.
>
>
> regds
> MAllah.
Isn't it a very long view? I found some limitations of explain, but possibly they
had something to cygwin enviroment.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2003-02-03 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:38 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2003-02-03 14:46 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
"Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
> tradein_clients=# explain SELECT count(*) from shippers1 where city='DELHI';
> ERROR: get_names_for_var: bogus varno 5
What version is this? ISTR having fixed some bugs that might cause that.
> i can paste the nasty view definations if nothing is obvious till
> now.
If it's a current release, we need to see *all* the schema definitions
referenced by the query --- views and tables.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:46 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-03 15:38 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:40 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Rajesh Kumar Mallah. @ 2003-02-03 15:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
It is PostgreSQL 7.3.0 on Linux.
Sorry Postgresql has really made my VIEWS ugly.
It wasnt' so when i fed them.
I wish pgsql stores the create view defination some day ,
just like it does for indexes (pg_get_indexdef)
Here is the EXPLAIN ANALYZE output of a query that is working
on the view.
i find that whenever i put any filter expression on the select
for any feild it stops working.
tradein_clients=# explain analyze SELECT * from shippers1 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=31.67..26780.73 rows=2 width=339) (actual time=45.18..6072.38 rows=732 loops=1)
Hash Cond: ("outer".id = "inner".id)
Join Filter: ("inner".source = "outer".source)
-> Subquery Scan b (cost=0.00..26737.99 rows=492 width=307) (actual time=0.14..6015.04 rows=2293 loops=1)
-> Append (cost=0.00..26737.99 rows=492 width=307) (actual time=0.13..6001.13 rows=2293 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..6739.42 rows=249 width=307) (actual time=0.12..2982.18 rows=321 loops=1)
-> Index Scan using eyp_listing_category_id, eyp_listing_category_id, eyp_listing_category_id, eyp_listing_category_id, eyp_listing_category_id on eyp_listing (cost=0.00..6739.42 rows=249 width=307) (actual time=0.11..2979.18 rows=321 loops=1)
Index Cond: ((category_id = 1142) OR (category_id = 1143) OR (category_id = 1145) OR (category_id = 1259) OR (category_id = 1161))
Filter: ((amount > 0) AND (status = 'a'::character varying) AND (email IS NOT NULL))
-> Subquery Scan "*SELECT* 2" (cost=0.00..9288.33 rows=77 width=286) (actual time=0.65..162.03 rows=112 loops=1)
-> Seq Scan on iid_listing (cost=0.00..9288.33 rows=77 width=286) (actual time=0.63..161.02 rows=112 loops=1)
Filter: (((category_id = 1142) OR (category_id = 1143) OR (category_id = 1145) OR (category_id = 1259) OR (category_id = 1161)) AND (amount > 0) AND (status = 'a'::character varying) AND (email IS NOT NULL))
-> Subquery Scan "*SELECT* 3" (cost=10701.96..10710.24 rows=166 width=248) (actual time=2824.89..2851.45 rows=1860 loops=1)
-> Unique (cost=10701.96..10710.24 rows=166 width=248) (actual time=2824.86..2837.43 rows=1860 loops=1)
-> Sort (cost=10701.96..10706.10 rows=1656 width=248) (actual time=2824.85..2831.21 rows=2790 loops=1)
Sort Key: branding_master.company_id
-> Hash Join (cost=339.35..10613.44 rows=1656 width=248) (actual time=33.62..2798.98 rows=2790 loops=1)
Hash Cond: ("outer".company_id = "inner".company_id)
-> Seq Scan on branding_master (cost=0.00..7171.80 rows=36254 width=242) (actual time=0.07..2620.57 rows=38766 loops=1)
Filter: ((old_company_id = 0) AND (status = 'a'::character varying) AND (email IS NOT NULL))
-> Hash (cost=331.00..331.00 rows=3339 width=6) (actual time=32.92..32.92 rows=0 loops=1)
-> Seq Scan on branding_sp_category (cost=0.00..331.00 rows=3339 width=6) (actual time=0.06..26.18 rows=3892 loops=1)
Filter: ((category_id = 1142) OR (category_id = 1143) OR (category_id = 1145) OR (category_id = 1259) OR (category_id = 1161))
-> Hash (cost=29.74..29.74 rows=774 width=15) (actual time=43.78..43.78 rows=0 loops=1)
-> Seq Scan on approved_profiles a (cost=0.00..29.74 rows=774 width=15) (actual time=40.64..42.36 rows=778 loops=1)
Total runtime: 6074.86 msec
(26 rows)
Time: 7080.76 ms
tradein_clients=#
And Following are the VIEWS & Tables:
tradein_clients=# \d shippers1
View "shipping_corner.shippers1"
+----------+-------------------+-----------+
| Column | Type | Modifiers |
+----------+-------------------+-----------+
| co_name | character varying | |
| address | character varying | |
| city | character varying | |
| pin_code | character varying | |
| phone | character varying | |
| fax | character varying | |
| contact | character varying | |
| email | character varying | |
| size | character varying | |
| paid | boolean | |
+----------+-------------------+-----------+
View definition: SELECT b.co_name, b.address, b.city, b.pin_code, b.phone, b.fax, b.contact, b.email, b.size, CASE WHEN (b.size = 'BRANDING'::character varying) THEN false ELSE true END AS paid FROM (ONLY approved_profiles a JOIN ONLY shipping_candidate_profiles b
ON (((a.id = b.id) AND (a.source = b.source))));
tradein_clients=# \d shipping_candidate_profiles
View "shipping_corner.shipping_candidate_profiles"
+-------------+-------------------+-----------+
| Column | Type | Modifiers |
+-------------+-------------------+-----------+
| id | integer | |
| branch | character varying | |
| source | character varying | |
| co_name | character varying | |
| address | character varying | |
| city | character varying | |
| pin_code | character varying | |
| phone | character varying | |
| fax | character varying | |
| contact | character varying | |
| email | character varying | |
| size | character varying | |
| status | boolean | |
| category_id | integer | |
| keywords | text | |
| edition | smallint | |
| sno | integer | |
+-------------+-------------------+-----------+
View definition: ((((SELECT eyp_listing.list_id AS id, eyp_listing.branch, 'EYP'::character varying AS source, eyp_listing.co_name, ((((CASE WHEN (eyp_listing.address1 IS NOT NULL) THEN eyp_listing.address1 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::character varying END || ' '::character varying) || CASE WHEN (eyp_listing.address2 IS NOT NULL) THEN eyp_listing.address2 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::character varying END) || ' '::character varying) || CASE WHEN (eyp_listing.address3 IS NOT NULL) THEN eyp_listing.address3 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::character varying END) AS address, eyp_listing.city, eyp_listing.pin_code, eyp_listing.phone, eyp_listing.fax, eyp_listing.contact, eyp_listing.email, eyp_listing.size, true AS status, eyp_listing.category_id, eyp_listing.keywords, eyp_listing.edition, eyp_listing.sno FROM ONLY public.eyp_listing WHERE ((((((((eyp_listing.category_id = 1142) OR (eyp_listing.category_id = 1143)) OR (eyp_listing.category_id = 1145)) OR (eyp_listing.category_id = 1259)) OR (eyp_listing.category_id = 1161)) AND (eyp_listing.amount > 0)) AND (eyp_listing.status = 'a'::character varying)) AND (eyp_listing.email IS NOT NULL))) UNION ALL (SELECT iid_listing.list_id AS id, iid_listing.branch, 'IID'::character varying AS source, iid_listing.co_name, ((((CASE WHEN (iid_listing.address1 IS NOT NULL) THEN iid_listing.address1 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::character varying END || ' '::character varying) || CASE WHEN (iid_listing.address2 IS NOT NULL) THEN iid_listing.address2 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::character varying END) || ' '::character varying) || CASE WHEN (iid_listing.address3 IS NOT NULL) THEN iid_listing.address3 WHEN ('' IS NOT NULL) THEN ''::character varying ELSE NULL::character varying END) AS address, iid_listing.city, iid_listing.pin_code, iid_listing.phone, iid_listing.fax, iid_listing.contact, iid_listing.email, iid_listing.size, true AS status, iid_listing.category_id, iid_listing.keywords, iid_listing.edition, iid_listing.sno FROM ONLY public.iid_listing WHERE ((((((((iid_listing.category_id = 1142) OR (iid_listing.category_id = 1143)) OR (iid_listing.category_id = 1145)) OR (iid_listing.category_id = 1259)) OR (iid_listing.category_id = 1161)) AND (iid_listing.amount > 0)) AND (iid_listing.status = 'a'::character varying)) AND (iid_listing.email IS NOT NULL))))) UNION ALL (SELECT DISTINCT ON (company_id) company_id AS id, trade_india_branch AS branch, 'BRANDING'::character varying AS source, comp_name AS co_name, office_addr AS address, city, pincode AS pin_code, phone_no AS phone, fax_no AS fax, key_exec AS contact, email, 'BRANDING' AS size, false AS status, category_id, serv_prov AS keywords, branding_master.edition, 0 AS sno FROM (ONLY public.branding_master JOIN ONLY public.branding_sp_category USING (company_id)) WHERE ((((((((category_id = 1142) OR (category_id = 1143)) OR (category_id = 1145)) OR (category_id = 1259)) OR (category_id = 1161)) AND (old_company_id = 0)) AND (status = 'a'::character varying)) AND (email IS NOT NULL)) ORDER BY company_id));
tradein_clients=# \d approved_profiles
Table "shipping_corner.approved_profiles"
+--------+-------------------+-----------+
| Column | Type | Modifiers |
+--------+-------------------+-----------+
| id | integer | not null |
| source | character varying | not null |
+--------+-------------------+-----------+
Indexes: approved_profiles_id_key unique btree (id, source)
tradein_clients=# \d branding_master
Table "public.branding_master"
Column | Type | Modifiers
--------------------+--------------------------+--------------------------------------------------------------------
company_id | integer | not null default nextval('"branding_master_company_id_seq"'::text)
comp_name | character varying(100) |
office_addr | character varying(255) |
phone_no | character varying(100) |
fax_no | character varying(100) |
email | character varying(100) |
website | character varying(100) |
key_exec | character varying(255) |
bran | character varying(100) |
estd | smallint |
staff | integer |
prod_exp | text |
prod_manu | text |
prod_imp | text |
serv_prov | text |
banker_name | character varying(255) |
email_valid | integer |
currency_ann | character varying(4) |
old_company_id | integer | default 0
currency_exp | character varying(4) |
old_formno | integer |
currency_imp | character varying(4) |
imp_exp_code | character varying(100) |
memb_affl | character varying(255) |
std_cert | character varying(255) |
trade_india_branch | character varying(25) |
areacode | integer | default 0
datasource | smallint |
status | character varying(1) | default 'p'
formno | integer |
edition | smallint |
area | character varying(50) |
sno | integer |
city | character varying(100) |
pincode | character varying(20) |
old_edition | bigint |
has_new_form | numeric(15,3) |
annual_turn | numeric(15,2) |
exp_turn | numeric(15,2) |
imp_turn | numeric(15,2) |
generated | timestamp with time zone | default "timestamp"('now'::text)
operator_code | character varying(25) |
fts_index | integer[] |
eyp_group_id | integer | default 0
old_branch | character varying(20) |
imported | boolean | default false
collector | character varying(255) |
for_delete1 | boolean | default false
for_delete2 | boolean | default false
eyp_exported | timestamp with time zone |
last_updated | timestamp with time zone | default now()
eyp_paid | integer |
iid_exported | timestamp with time zone |
iid_paid | integer |
delete3 | character varying(100) |
comp_name_index | txtidx |
Indexes: branding_master_pkey primary key btree (company_id),
branding_master_formno_ed_branc unique btree (formno, edition, trade_india_branch),
branding_master_name_city unique btree (comp_name, city) WHERE (old_company_id = 0),
branding_master_area btree (area),
branding_master_areacode btree (areacode),
branding_master_branch btree (trade_india_branch),
branding_master_comp_name btree (comp_name),
branding_master_comp_name_index gist (comp_name_index),
branding_master_edition btree (edition),
branding_master_email btree (email) WHERE ((old_company_id = 0) AND (email IS NOT NULL)),
branding_master_oldcomapany_id btree (old_company_id) WHERE (old_company_id > 0),
branding_master_pincode btree (pincode),
branding_master_status btree (status),
branding_master_tibranch btree (trade_india_branch),
branding_master_website btree (website)
Check constraints: "no_whites_paceallowed_in_imp" ((length(btrim(prod_imp)) > 1) OR (prod_imp IS NULL))
"no_whites_paceallowed_in_email" ((length(btrim((email)::text)) > 1) OR (email IS NULL))
"no_white_space_allowed_in_serv" ((length(btrim(serv_prov)) > 1) OR (serv_prov IS NULL))
"no_white_space_allowed_in_manu" ((length(btrim(prod_manu)) > 1) OR (prod_manu IS NULL))
"no_white_space_allowed_in_exp" ((length(btrim(prod_exp)) > 1) OR (prod_exp IS NULL))
tradein_clients=#
tradein_clients=# \d eyp_listing
Table "public.eyp_listing"
Column | Type | Modifiers
--------------------+--------------------------+-------------------------------------------------------------
userid | integer |
category | character varying(50) |
category_id | integer | not null
branch | character varying |
sno | integer | default -1
size | character varying(20) |
co_name | character varying(100) | not null
address1 | character varying(200) |
address2 | character varying(200) | default ' '
address3 | character varying(200) | default ' '
city | character varying(100) |
pin_code | character varying(30) |
phone | character varying(100) |
fax | character varying(100) |
email | character varying(100) |
contact | character varying(100) |
website | character varying(100) |
keywords | text |
show_ad | character varying(25) | default 'f'
status | character varying(200) | default 'a'
amount | integer |
group_id | integer | default nextval('"eyp_listing_group_id_seq"'::text)
list_id | integer | not null default nextval('"eyp_listing_list_id_seq"'::text)
catalog_id | integer | default 0
generated | date | default date('now'::text)
edition | smallint |
wrong_last_updated | date |
last_updated | timestamp with time zone | default now()
user_keywords | text |
counter | smallint | default 0
sent_on | timestamp with time zone | default now()
max_emails | smallint |
emails_sent | smallint | default 0
total_emails | smallint | default 0
notification | boolean |
branding_id | integer |
keywordidx | txtidx |
company_id | integer |
website_working | boolean | default 'f'
hide_email | boolean | default 'f'
co_name_index | txtidx |
bankers | character varying(200) |
estd | integer |
staff | integer |
annual_turn_value | numeric |
mobile | character varying(50) |
reminder_cnt | smallint | default 0
expires_on | date |
Indexes: eyp_listing_pkey primary key btree (list_id),
eyp_listing_br_cid_cat_id unique btree (company_id, category_id) WHERE (size = 'BRANDING'::character varying),
a_gist_key gist (keywordidx),
eyp_listing_amt btree (amount),
eyp_listing_branch btree (branch) WHERE (amount > 0),
eyp_listing_category_id btree (category_id),
eyp_listing_co_name btree (co_name),
eyp_listing_co_name_index gist (co_name_index),
eyp_listing_company_id btree (company_id),
eyp_listing_email btree (email),
eyp_listing_group_id btree (group_id),
eyp_listing_size btree (size),
eyp_listing_sno_branch btree (branch, sno),
eyp_listing_userid btree (userid)
Check constraints: "branding_check" CASE WHEN (size = 'BRANDING'::character varying) THEN ((company_id IS NOT NULL) AND (company_id > 0)) ELSE (company_id IS NULL) END
Foreign Key constraints: referrer_branches_master FOREIGN KEY (branch) REFERENCES tradeindia_branches(branch) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: RI_ConstraintTrigger_29292778,
RI_ConstraintTrigger_29292779,
co_name_index_update,
last_updated,
set_category,
set_max_emails
tradein_clients=#
tradein_clients=# \d eyp_listing
Table "public.eyp_listing"
Column | Type | Modifiers
--------------------+--------------------------+-------------------------------------------------------------
userid | integer |
category | character varying(50) |
category_id | integer | not null
branch | character varying |
sno | integer | default -1
size | character varying(20) |
co_name | character varying(100) | not null
address1 | character varying(200) |
address2 | character varying(200) | default ' '
address3 | character varying(200) | default ' '
city | character varying(100) |
pin_code | character varying(30) |
phone | character varying(100) |
fax | character varying(100) |
email | character varying(100) |
contact | character varying(100) |
website | character varying(100) |
keywords | text |
show_ad | character varying(25) | default 'f'
status | character varying(200) | default 'a'
amount | integer |
group_id | integer | default nextval('"eyp_listing_group_id_seq"'::text)
list_id | integer | not null default nextval('"eyp_listing_list_id_seq"'::text)
catalog_id | integer | default 0
generated | date | default date('now'::text)
edition | smallint |
wrong_last_updated | date |
last_updated | timestamp with time zone | default now()
user_keywords | text |
counter | smallint | default 0
sent_on | timestamp with time zone | default now()
max_emails | smallint |
emails_sent | smallint | default 0
total_emails | smallint | default 0
notification | boolean |
branding_id | integer |
keywordidx | txtidx |
company_id | integer |
website_working | boolean | default 'f'
hide_email | boolean | default 'f'
co_name_index | txtidx |
bankers | character varying(200) |
estd | integer |
staff | integer |
annual_turn_value | numeric |
mobile | character varying(50) |
reminder_cnt | smallint | default 0
expires_on | date |
Indexes: eyp_listing_pkey primary key btree (list_id),
eyp_listing_br_cid_cat_id unique btree (company_id, category_id) WHERE (size = 'BRANDING'::character varying),
a_gist_key gist (keywordidx),
eyp_listing_amt btree (amount),
eyp_listing_branch btree (branch) WHERE (amount > 0),
eyp_listing_category_id btree (category_id),
eyp_listing_co_name btree (co_name),
eyp_listing_co_name_index gist (co_name_index),
eyp_listing_company_id btree (company_id),
eyp_listing_email btree (email),
eyp_listing_group_id btree (group_id),
eyp_listing_size btree (size),
eyp_listing_sno_branch btree (branch, sno),
eyp_listing_userid btree (userid)
Check constraints: "branding_check" CASE WHEN (size = 'BRANDING'::character varying) THEN ((company_id IS NOT NULL) AND (company_id > 0)) ELSE (company_id IS NULL) END
Foreign Key constraints: referrer_branches_master FOREIGN KEY (branch) REFERENCES tradeindia_branches(branch) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: RI_ConstraintTrigger_29292778,
RI_ConstraintTrigger_29292779,
co_name_index_update,
last_updated,
set_category,
set_max_emails
tradein_clients=#
On Monday 03 February 2003 08:16 pm, Tom Lane wrote:
> "Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
> > tradein_clients=# explain SELECT count(*) from shippers1 where
> > city='DELHI'; ERROR: get_names_for_var: bogus varno 5
>
> What version is this? ISTR having fixed some bugs that might cause that.
>
> > i can paste the nasty view definations if nothing is obvious till
> > now.
>
> If it's a current release, we need to see *all* the schema definitions
> referenced by the query --- views and tables.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
--------------------------------------------
Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:46 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:38 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2003-02-03 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:49 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2003-02-03 15:40 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
"Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
> It is PostgreSQL 7.3.0 on Linux.
Try 7.3.1 then. I think this is this problem:
2002-12-06 14:28 tgl
* src/backend/commands/explain.c (REL7_3_STABLE): Explain's code
for showing quals of SubqueryScan nodes has been broken all along;
not noticed till now. It's a scan not an upper qual ...
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:46 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:38 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:40 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-03 15:49 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:45 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:47 ` Re: cannot EXPLAIN query... Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Rajesh Kumar Mallah. @ 2003-02-03 15:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thank you . But i have a problem ,
I think if i do that i will hve to immediately upgrade
all the 7.3.0 clients in other machines to 7.3.1 rite?
regds
Mallah.
On Monday 03 February 2003 09:10 pm, Tom Lane wrote:
> "Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
> > It is PostgreSQL 7.3.0 on Linux.
>
> Try 7.3.1 then. I think this is this problem:
>
> 2002-12-06 14:28 tgl
>
> * src/backend/commands/explain.c (REL7_3_STABLE): Explain's code
> for showing quals of SubqueryScan nodes has been broken all along;
> not noticed till now. It's a scan not an upper qual ...
>
> regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:46 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:38 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:40 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:49 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2003-02-03 15:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:54 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 16:32 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
1 sibling, 2 replies; 13+ messages in thread
From: Tom Lane @ 2003-02-03 15:45 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
"Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
> I think if i do that i will hve to immediately upgrade
> all the 7.3.0 clients in other machines to 7.3.1 rite?
No.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:46 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:38 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:40 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:49 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:45 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-03 15:54 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
1 sibling, 0 replies; 13+ messages in thread
From: Rajesh Kumar Mallah. @ 2003-02-03 15:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thanks , if that is so i am upgrading it right away and posting
you the results. Its my live DB server :-)
Regds
mallah.
On Monday 03 February 2003 09:15 pm, Tom Lane wrote:
> "Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
> > I think if i do that i will hve to immediately upgrade
> > all the 7.3.0 clients in other machines to 7.3.1 rite?
>
> No.
>
> regards, tom lane
--
--------------------------------------------
Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:46 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:38 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:40 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:49 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:45 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-03 16:32 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 17:34 ` Re: cannot EXPLAIN query... mallah@trade-india.com
1 sibling, 1 reply; 13+ messages in thread
From: Rajesh Kumar Mallah. @ 2003-02-03 16:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hmmm... upgrade to 7.3.1 was not that smooth..
after upgrade i could not run a single query..
tradein_clients=> SELECT * from hogs;
ERROR: current transaction is aborted, queries ignored until end of transaction block
tradein_clients=>
any other query seems to be giving the same ERROR.
check the message below on psql start (7.3.1) with a 7.3.1 server.
PS: i applied the heir patch though ... ;-) will try again without
that.
[postgres@ns3 postgres]$ psql -Upostgres -h66.234.10.12 tradein_clients
>> ERROR: nodeRead: did not find '}' at end of plan node
Welcome to psql 7.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
tradein_clients=>
regds
mallah.
On Monday 03 February 2003 09:15 pm, Tom Lane wrote:
> "Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
> > I think if i do that i will hve to immediately upgrade
> > all the 7.3.0 clients in other machines to 7.3.1 rite?
>
> No.
>
> regards, tom lane
--
--------------------------------------------
Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:46 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:38 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:40 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:49 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:45 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 16:32 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2003-02-03 17:34 ` mallah@trade-india.com
0 siblings, 0 replies; 13+ messages in thread
From: mallah@trade-india.com @ 2003-02-03 17:34 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: tgl@sss.pgh.pa.us; pgsql-sql
Hi ,
This is to confirm that the EXPLAIN problem
does not occur anymore after successfully upgrading
to 7.3.1 from 7.3.0
Thanks everyone.
Regards
Mallah.
explain select * from shippers1 where city='DELHI';
QUERY
PLAN------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..26461.22 rows=1 width=339)
-> Subquery Scan b (cost=0.00..26448.53 rows=477 width=307)
Filter: (city = 'DELHI'::character varying)
-> Append (cost=0.00..26448.53 rows=477 width=307)
-> Subquery Scan "*SELECT* 1" (cost=0.00..6739.42 rows=249 width=307)
-> Index Scan using eyp_listing_category_id, eyp_listing_category_id,
eyp_listing_category_id, eyp_listing_category_id, eyp_listing_category_id on
eyp_listing (cost=0.00..6739.42 rows=249 width=307) Index Cond: ((category_id = 1142) OR (category_id = 1143) OR
(category_id = 1145) OR (category_id = 1259) OR (category_id = 1161)) Filter: ((amount > 0) AND (status = 'a'::character varying) AND (email
IS NOT NULL)) -> Subquery Scan "*SELECT* 2" (cost=0.00..9288.33 rows=77 width=286)
-> Seq Scan on iid_listing (cost=0.00..9288.33 rows=77 width=286)
Filter: (((category_id = 1142) OR (category_id = 1143) OR (category_id
= 1145) OR (category_id = 1259) OR (category_id = 1161)) AND (amount >
0) AND (status = 'a'::character varying) AND (email IS NOT NULL)) -> Subquery Scan "*SELECT* 3" (cost=10413.26..10420.79 rows=151 width=248)
-> Unique (cost=10413.26..10420.79 rows=151 width=248)
-> Sort (cost=10413.26..10417.02 rows=1506 width=248)
Sort Key: branding_master.company_id
-> Hash Join (cost=339.35..10333.74 rows=1506 width=248)
Hash Cond: ("outer".company_id = "inner".company_id)
-> Seq Scan on branding_master (cost=0.00..7171.80
rows=32985 width=242) Filter: ((old_company_id = 0) AND (status =
'a'::character varying) AND (email IS NOT NULL) AND
(eyp_paid IS NULL) AND (iid_paid IS NULL)) -> Hash (cost=331.00..331.00 rows=3339 width=6)
-> Seq Scan on branding_sp_category
(cost=0.00..331.00 rows=3339 width=6) Filter: ((category_id = 1142) OR (category_id =
1143) OR (category_id = 1145) OR (category_id =
1259) OR (category_id = 1161)) -> Index Scan using approved_profiles_id_key on approved_profiles a (cost=0.00..5.30 rows=1
width=15) Index Cond: ((a.id = "outer".id) AND (a.source = "outer".source))
(24 rows)
tradein_clients=#
>
>
> Hmmm... upgrade to 7.3.1 was not that smooth..
> after upgrade i could not run a single query..
>
> tradein_clients=> SELECT * from hogs;
> ERROR: current transaction is aborted, queries ignored until end of transaction block
> tradein_clients=>
> any other query seems to be giving the same ERROR.
>
> check the message below on psql start (7.3.1) with a 7.3.1 server.
>
> PS: i applied the heir patch though ... ;-) will try again without that.
>
> [postgres@ns3 postgres]$ psql -Upostgres -h66.234.10.12 tradein_clients
>
>>> ERROR: nodeRead: did not find '}' at end of plan node
>
> Welcome to psql 7.3.1, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> tradein_clients=>
>
>
>
>
> regds
> mallah.
>
>
> On Monday 03 February 2003 09:15 pm, Tom Lane wrote:
>> "Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
>> > I think if i do that i will hve to immediately upgrade
>> > all the 7.3.0 clients in other machines to 7.3.1 rite?
>>
>> No.
>>
>> regards, tom lane
>
> --
>
>
> --------------------------------------------
> Regds Mallah
> Rajesh Kumar Mallah,
> Project Manager (Development)
> Infocom Network Limited, New Delhi
> phone: +91(11)26152172 (221) (L) 9811255597 (M)
> Visit http://www.trade-india.com ,
> India's Leading B2B eMarketplace.
>
>
>
> ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and
> unsubscribe commands go to majordomo@postgresql.org
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:46 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:38 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:40 ` Re: cannot EXPLAIN query... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:49 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2003-02-03 15:47 ` Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2003-02-03 15:47 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
No, you can mix them.
---------------------------------------------------------------------------
Rajesh Kumar Mallah. wrote:
>
>
> Thank you . But i have a problem ,
>
> I think if i do that i will hve to immediately upgrade
> all the 7.3.0 clients in other machines to 7.3.1 rite?
>
>
> regds
> Mallah.
>
> On Monday 03 February 2003 09:10 pm, Tom Lane wrote:
> > "Rajesh Kumar Mallah." <mallah@trade-india.com> writes:
> > > It is PostgreSQL 7.3.0 on Linux.
> >
> > Try 7.3.1 then. I think this is this problem:
> >
> > 2002-12-06 14:28 tgl
> >
> > * src/backend/commands/explain.c (REL7_3_STABLE): Explain's code
> > for showing quals of SubqueryScan nodes has been broken all along;
> > not noticed till now. It's a scan not an upper qual ...
> >
> > regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
@ 2003-02-03 15:50 Christoph Haller <ch@rodos.fzk.de>
2003-02-03 15:59 ` Re: cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 1 reply; 13+ messages in thread
From: Christoph Haller @ 2003-02-03 15:50 UTC (permalink / raw)
To: pgsql-sql; +Cc: mallah@trade-india.com
>
> Sorry Postgresql has really made my VIEWS ugly.
> It wasnt' so when i fed them.
>
> I wish pgsql stores the create view defination some day ,
> just like it does for indexes (pg_get_indexdef)
>
Did you ever try
SELECT * FROM pg_views ;
It definitely has all view definitions.
Regards, Christoph
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cannot EXPLAIN query...
2003-02-03 15:50 Re: cannot EXPLAIN query... Christoph Haller <ch@rodos.fzk.de>
@ 2003-02-03 15:59 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 0 replies; 13+ messages in thread
From: Rajesh Kumar Mallah. @ 2003-02-03 15:59 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Monday 03 February 2003 09:20 pm, Christoph Haller wrote:
> > Sorry Postgresql has really made my VIEWS ugly.
> > It wasnt' so when i fed them.
> >
> > I wish pgsql stores the create view defination some day ,
> > just like it does for indexes (pg_get_indexdef)
>
> Did you ever try
>
> SELECT * FROM pg_views ;
i thing when you do a \d <view_name> it uses that only.
in any case i have verified that the content in them are equally
messed up.
regds
mallah.
>
> It definitely has all view definitions.
>
> Regards, Christoph
--
--------------------------------------------
Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-02-03 17:34 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-03 14:02 cannot EXPLAIN query... Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 14:15 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-03 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:38 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:49 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 15:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-03 15:54 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 16:32 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-02-03 17:34 ` mallah@trade-india.com
2003-02-03 15:47 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-02-03 15:50 Re: cannot EXPLAIN query... Christoph Haller <ch@rodos.fzk.de>
2003-02-03 15:59 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox