Re: pgadmin Bogus Varno: 3

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?

Thanks again for the help
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.