Re: pgadmin Bogus Varno: 3

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.