Re: pgadmin Bogus Varno: 3 - Mailing list pgadmin-support
From | laurie.burrow@powerconv.alstom.com |
---|---|
Subject | Re: pgadmin Bogus Varno: 3 |
Date | |
Msg-id | OFBE2BF2DD.490B85D9-ON80256F87.0038A620-80256F87.0038DFBC@transport.alstom.com Whole thread Raw |
In response to | pgadmin Bogus Varno: 3 (laurie.burrow@powerconv.alstom.com) |
List | pgadmin-support |
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.
pgadmin-support by date: