Re: pgadmin Bogus Varno: 3

laurie(dot)burrow(at)powerconv(dot)alstom(dot)com wrote:
> pgadmin(at)pse-consulting(dot)de wrote
>
>
>>>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 ....).
>
>
> This did identify one view that caused the problem. When this view is
> dropped pgadmin works fine.
>
> The view definition (as generated by Pgadmin 1.2.0 from Postgres 7.4.2) is
>
> CREATE OR REPLACE VIEW full_product_view AS
> SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_,
> ( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text
> FROM rspper
> WHERE rspper.rspperid = slimprdmgrrspperid) AS
> _prd_slimprdmgrrspperid_d,
> prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_,
> actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS
> _slimprdnmgnnmeid_gennme_catnmeclssid_
> FROM prd
> JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid;
>
> This SQL creates the view ok on both Postgres 7 and Postgres 8 in that it
> does not return an error and the resulting view seems to operate correctly
> on both versions of Postgres. Pgadmin 1.2.0 works fine with the Postgres 7
> but fails with Postgres 8.0.0.rc4.
>
> The cause appears to be the call to pg_get_viewdef(c.oid, false) which
> works correctly on Postgres 7 but generates the error Bogus Varno: 3 in
> Postgres 8. AFAICT the function is objecting to the scalar select.
>
> Is this one for a pgsql-bugs or pgadmin-support?

This is one for pgsql-bugs. Apparently you can pinpoint the problem,
please post a summary of it immediately to pgsql-bugs so it gets
reviewed; maybe it can be fixed for pgsql8.0-gold.

Regards,
Andreas