Thread: pgadmin Bogus Varno: 3

I am using Pgadmin Version 1.2.0 (Nov 29 2004) as shipped with Postgres
8.0.0 rc4 running on Windows 2000 Desktop. I have two versions of Postgres
running on Windows 2000 server. One version is Postgres 7.4.2 on cygwin,
the other is Postgres 8.0.0.rc4 native windows.

I have a large database on Postgres v7 that is working well with all
versions of pgadmin.

I have backed up the Postgres 7 database data schema as plain text and
restored it on the Postgres 8 server. There were no restore errors. However
when I try to view the database using Pgadmin I get error Bogus Varno: 3.
Visually the Pgadmin display tree is showing everything down to the views,
but no views are being shown. The views can be seen in psql although I
haven't tried to look at all 55 views. Some views have rules. I tried the
same using various combinations of data schema and data compressed and
plain. They all give the same results.

In the Pgadmin log (set to log debug) the last few lines with the error are
...
...
2005-01-11 09:57:43 INFO   : Creating a pgType object
2005-01-11 09:57:43 INFO   : Destroying pgSet object
2005-01-11 09:57:43 INFO   : Creating a pgCollection object
2005-01-11 09:57:43 QUERY  : Set query (localhost:5433): 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::oidORDER BY relname
2005-01-11 09:57:43 ERROR  : ERROR:  bogus varno: 3

2005-01-11 09:57:45 STATUS : Retrieving Schema details... (4.33 secs)

I am not sure what other information would  be useful . Any help would be
most welcome.

Laurie Burrow





:.________________
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.

laurie.burrow@powerconv.alstom.com wrote:
> I am using Pgadmin Version 1.2.0 (Nov 29 2004) as shipped with Postgres


> 2005-01-11 09:57:43 QUERY  : Set query (localhost:5433): 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
> 2005-01-11 09:57:43 ERROR  : ERROR:  bogus varno: 3

That's a new one!
Please run that offending query interactively (you'll hopefully receive 
that error then too), and try to change it (e.g. restrict to a single 
view's oid) to find out what's happening. We might have to redirect you 
to pgsql-bugs, after we know more exactly the reason.

Try pg_get_viewdef(c.oid, false), to check if view reformatting code is 
the sinner.

Regards,
Andreas

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.oidand
 
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::oidORDER 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::oidORDER 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::oidORDER BY relnameoffset 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::oidORDER BY relnameoffset 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.

Please stay on the list!


laurie.burrow@powerconv.alstom.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.
> 
> 
> 

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 ASSELECT 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.

laurie.burrow@powerconv.alstom.com wrote:

> AFAICT the function is objecting to the scalar select.

The original query definition causing the Pgadmin problem was:

CREATE OR REPLACE VIEW full_product_view ASSELECT 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;

  Rewriting the query to include the table qualification on the scalar  select cures the problem with
pg_get_viewdef(c.oid,true).
 

CREATE OR REPLACE VIEW full_product_view ASSELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_,
( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text          FROM rspper         WHERE rspper.rspperid
=prd.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;
  I don't know if this behaviour is expected.
  Regards  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.

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