Обсуждение: pgadmin Bogus Varno: 3
I am using Pgadmin Version 1.2.0 (Nov 29 2004) as shipped with Postgres 8.0.0 rc4 running on Windows 2000 Desktop. I have two versions of Postgres running on Windows 2000 server. One version is Postgres 7.4.2 on cygwin, the other is Postgres 8.0.0.rc4 native windows. I have a large database on Postgres v7 that is working well with all versions of pgadmin. I have backed up the Postgres 7 database data schema as plain text and restored it on the Postgres 8 server. There were no restore errors. However when I try to view the database using Pgadmin I get error Bogus Varno: 3. Visually the Pgadmin display tree is showing everything down to the views, but no views are being shown. The views can be seen in psql although I haven't tried to look at all 55 views. Some views have rules. I tried the same using various combinations of data schema and data compressed and plain. They all give the same results. In the Pgadmin log (set to log debug) the last few lines with the error are ... ... 2005-01-11 09:57:43 INFO : Creating a pgType object 2005-01-11 09:57:43 INFO : Destroying pgSet object 2005-01-11 09:57:43 INFO : Creating a pgCollection object 2005-01-11 09:57:43 QUERY : Set query (localhost:5433): SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0)WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class= c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oidORDER BY relname 2005-01-11 09:57:43 ERROR : ERROR: bogus varno: 3 2005-01-11 09:57:45 STATUS : Retrieving Schema details... (4.33 secs) I am not sure what other information would be useful . Any help would be most welcome. Laurie Burrow :.________________ CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium.
laurie.burrow@powerconv.alstom.com wrote: > I am using Pgadmin Version 1.2.0 (Nov 29 2004) as shipped with Postgres > 2005-01-11 09:57:43 QUERY : Set query (localhost:5433): SELECT c.oid, > c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, > pg_get_viewdef(c.oid, true) AS definition > FROM pg_class c > LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and > des.objsubid=0) > WHERE ((c.relhasrules AND (EXISTS ( > SELECT r.rulename FROM pg_rewrite r > WHERE ((r.ev_class = c.oid) > AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind > = 'v'::char)) > AND relnamespace = 2200::oid > ORDER BY relname > 2005-01-11 09:57:43 ERROR : ERROR: bogus varno: 3 That's a new one! Please run that offending query interactively (you'll hopefully receive that error then too), and try to change it (e.g. restrict to a single view's oid) to find out what's happening. We might have to redirect you to pgsql-bugs, after we know more exactly the reason. Try pg_get_viewdef(c.oid, false), to check if view reformatting code is the sinner. Regards, Andreas
Andreas, Here are the results of my tests Test 1 ----------- This query run interactively gives ERROR: bogus varno: 3 SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oidand des.objsubid=0)WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class= c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oidORDER BY relname Test 2 ----------- This query with the field pg_get_viewdef(c.oid, true) AS definition removed run interactively works and returns a list of 55 view OIDs which is correct in my case. SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0)WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class= c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oidORDER BY relname Test 3 --------- This query which runs the function using one of the OID's as an example returns the definition without error select pg_get_viewdef(17391, true) AS definition Test 4 ---------- Altering the original query to return only one (the same) OID fails with Error Bogus Varno: 3, as in, SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS definition FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0)WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class= c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oidORDER BY relnameoffset 0 limit 1 Test 5 ---------- Hardwiring pg_get_viewdef(c.oid, true) works SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, pg_get_viewdef(17391, true) AS definition FROM pg_class c LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0)WHERE ((c.relhasrules AND (EXISTS ( SELECT r.rulename FROM pg_rewrite r WHERE ((r.ev_class= c.oid) AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char)) AND relnamespace = 2200::oidORDER BY relnameoffset 0 limit 1 Other Points ------------------ Changing pg_get_viewdef(c.oid, true) to pg_get_viewdef(c.oid, false) has no effect. Other databases that I have transferred to Postgres 8 don't exhibit this problem. I have checked the operation of all the views in the Postgres 8 database that exhibits the problem and they all seem to work fine so I don't believe that the problem is a corrupted view or some such. The main obvious feature of the database exhibiting the problem is that it is much bigger and more complex than any other database I have ported from Postgres 7 to Postgres 8 but I guess this may be irelevent. I hope I have covered all useful points Thanks for the support Laurie :.________________ CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium.
Please stay on the list! laurie.burrow@powerconv.alstom.com wrote: > Andreas, > > Here are the results of my tests > > Test 1 > ----------- > This query run interactively gives ERROR: bogus varno: 3 > > SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, > c.relacl, description, pg_get_viewdef(c.oid, true) AS definition > FROM pg_class c > LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and > des.objsubid=0) > WHERE ((c.relhasrules AND (EXISTS ( > SELECT r.rulename FROM pg_rewrite r > WHERE ((r.ev_class = c.oid) > AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind > = 'v'::char)) > AND relnamespace = 2200::oid > ORDER BY relname > > Test 2 > ----------- > This query with the field pg_get_viewdef(c.oid, true) AS definition > removed run interactively works and returns a list of 55 view OIDs which is > correct in my case. > > SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, > c.relacl, description > FROM pg_class c > LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and > des.objsubid=0) > WHERE ((c.relhasrules AND (EXISTS ( > SELECT r.rulename FROM pg_rewrite r > WHERE ((r.ev_class = c.oid) > AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind > = 'v'::char)) > AND relnamespace = 2200::oid > ORDER BY relname > > Test 3 > --------- > This query which runs the function using one of the OID's as an example > returns the definition without error > > select pg_get_viewdef(17391, true) AS definition > > Test 4 > ---------- > Altering the original query to return only one (the same) OID fails with > Error Bogus Varno: 3, as in, > > SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) > AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS > definition > FROM pg_class c > LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and > des.objsubid=0) > WHERE ((c.relhasrules AND (EXISTS ( > SELECT r.rulename FROM pg_rewrite r > WHERE ((r.ev_class = c.oid) > AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind > = 'v'::char)) > AND relnamespace = 2200::oid > ORDER BY relname > offset 0 limit 1 Check this query again without OFFSET 0 LIMIT 1. Use .... WHERE c.OID = 17391 instead. I suspect that this will work, and you'll have to try which oid is the offending one (binary search using WHERE c.OID BETWEEN ....). > > Test 5 > ---------- > Hardwiring pg_get_viewdef(c.oid, true) works > > SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) > AS viewowner, c.relacl, description, pg_get_viewdef(17391, true) AS > definition > FROM pg_class c > LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and > des.objsubid=0) > WHERE ((c.relhasrules AND (EXISTS ( > SELECT r.rulename FROM pg_rewrite r > WHERE ((r.ev_class = c.oid) > AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind > = 'v'::char)) > AND relnamespace = 2200::oid > ORDER BY relname > offset 0 limit 1 > > Other Points > ------------------ > Changing pg_get_viewdef(c.oid, true) to pg_get_viewdef(c.oid, false) has no > effect. > > Other databases that I have transferred to Postgres 8 don't exhibit this > problem. > > I have checked the operation of all the views in the Postgres 8 database > that exhibits the problem and they all seem to work fine so I don't believe > that the problem is a corrupted view or some such. The main obvious feature > of the database exhibiting the problem is that it is much bigger and more > complex than any other database I have ported from Postgres 7 to Postgres 8 > but I guess this may be irelevent. > > I hope I have covered all useful points > Thanks for the support > Laurie > > > > > > > :.________________ > CONFIDENTIALITY : This e-mail and any attachments are confidential and > may be privileged. If you are not a named recipient, please notify the > sender immediately and do not disclose the contents to another person, use > it for any purpose or store or copy the information in any medium. > > >
pgadmin@pse-consulting.de wrote >> SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) >> AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS >> definition >> FROM pg_class c >> LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and >> des.objsubid=0) >> WHERE ((c.relhasrules AND (EXISTS ( >> SELECT r.rulename FROM pg_rewrite r >> WHERE ((r.ev_class = c.oid) >> AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind >> = 'v'::char)) >> AND relnamespace = 2200::oid >> ORDER BY relname >> offset 0 limit 1 > >Check this query again without OFFSET 0 LIMIT 1. >Use .... WHERE c.OID = 17391 instead. >I suspect that this will work, and you'll have to try which oid is the >offending one (binary search using WHERE c.OID BETWEEN ....). This did identify one view that caused the problem. When this view is dropped pgadmin works fine. The view definition (as generated by Pgadmin 1.2.0 from Postgres 7.4.2) is CREATE OR REPLACE VIEW full_product_view ASSELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_, ( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text FROM rspper WHERE rspper.rspperid =slimprdmgrrspperid) AS _prd_slimprdmgrrspperid_d, prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_, actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS _slimprdnmgnnmeid_gennme_catnmeclssid_ FROM prd JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid; This SQL creates the view ok on both Postgres 7 and Postgres 8 in that it does not return an error and the resulting view seems to operate correctly on both versions of Postgres. Pgadmin 1.2.0 works fine with the Postgres 7 but fails with Postgres 8.0.0.rc4. The cause appears to be the call to pg_get_viewdef(c.oid, false) which works correctly on Postgres 7 but generates the error Bogus Varno: 3 in Postgres 8. AFAICT the function is objecting to the scalar select. Is this one for a pgsql-bugs or pgadmin-support? Thanks again for the help Laurie :.________________ CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium.
laurie.burrow@powerconv.alstom.com wrote: > AFAICT the function is objecting to the scalar select. The original query definition causing the Pgadmin problem was: CREATE OR REPLACE VIEW full_product_view ASSELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_, ( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text FROM rspper WHERE rspper.rspperid =slimprdmgrrspperid) AS _prd_slimprdmgrrspperid_d, prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_, actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS _slimprdnmgnnmeid_gennme_catnmeclssid_ FROM prd JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid; Rewriting the query to include the table qualification on the scalar select cures the problem with pg_get_viewdef(c.oid,true). CREATE OR REPLACE VIEW full_product_view ASSELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_, ( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text FROM rspper WHERE rspper.rspperid =prd.slimprdmgrrspperid) AS _prd_slimprdmgrrspperid_d, prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_, actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS _slimprdnmgnnmeid_gennme_catnmeclssid_ FROM prd JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid; I don't know if this behaviour is expected. Regards Laurie :.________________ CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium.
laurie.burrow@powerconv.alstom.com wrote: > pgadmin@pse-consulting.de wrote > > >>>SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) >>>AS viewowner, c.relacl, description, pg_get_viewdef(c.oid, true) AS >>>definition >>> FROM pg_class c >>> LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and >>>des.objsubid=0) >>> WHERE ((c.relhasrules AND (EXISTS ( >>> SELECT r.rulename FROM pg_rewrite r >>> WHERE ((r.ev_class = c.oid) >>> AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind >>>= 'v'::char)) >>> AND relnamespace = 2200::oid >>> ORDER BY relname >>> offset 0 limit 1 >> >>Check this query again without OFFSET 0 LIMIT 1. >>Use .... WHERE c.OID = 17391 instead. >>I suspect that this will work, and you'll have to try which oid is the >>offending one (binary search using WHERE c.OID BETWEEN ....). > > > This did identify one view that caused the problem. When this view is > dropped pgadmin works fine. > > The view definition (as generated by Pgadmin 1.2.0 from Postgres 7.4.2) is > > CREATE OR REPLACE VIEW full_product_view AS > SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_, > ( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text > FROM rspper > WHERE rspper.rspperid = slimprdmgrrspperid) AS > _prd_slimprdmgrrspperid_d, > prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_, > actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS > _slimprdnmgnnmeid_gennme_catnmeclssid_ > FROM prd > JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid; > > This SQL creates the view ok on both Postgres 7 and Postgres 8 in that it > does not return an error and the resulting view seems to operate correctly > on both versions of Postgres. Pgadmin 1.2.0 works fine with the Postgres 7 > but fails with Postgres 8.0.0.rc4. > > The cause appears to be the call to pg_get_viewdef(c.oid, false) which > works correctly on Postgres 7 but generates the error Bogus Varno: 3 in > Postgres 8. AFAICT the function is objecting to the scalar select. > > Is this one for a pgsql-bugs or pgadmin-support? This is one for pgsql-bugs. Apparently you can pinpoint the problem, please post a summary of it immediately to pgsql-bugs so it gets reviewed; maybe it can be fixed for pgsql8.0-gold. Regards, Andreas