Re: pgadmin Bogus Varno: 3 - Mailing list pgadmin-support

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.
> 
> 
>