Re: [pgadmin-support] pgadmin Bogus Varno: 3

[Andreas Pflug]

[EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html