Re: pgadmin Bogus Varno: 3

Please stay on the list!

laurie(dot)burrow(at)powerconv(dot)alstom(dot)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.
>
>
>