Re: pgadmin Bogus Varno: 3
Re: pgadmin Bogus Varno: 3
Please stay on the list!
laurie(dot)burrow(at)powerconv(dot)alstom(dot)com wrote:
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
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 ....).
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.
Home |
Main Index |
Thread Index
