Re: pgadmin Bogus Varno: 3

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

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.