Please stay on the list!
[EMAIL PROTECTED] 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.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
