Re: pgadmin Bogus Varno: 3

laurie.burrow@powerconv.alstom.com wrote:
> pgadmin@pse-consulting.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