Re: select results on pg_class incomplete

On 3/14/24 09:41, Thiemo Kellner wrote:
> Hi
> 
> I am trying to access PostgreSQL meta data, possibly in a vane attempt 
> to get size data.
> 
> I use DbVis with a connection as shown in https://ibb.co/2SDzhXt . I try 
> to get information on a regular table "umsaetze". When doing the DbVis 
> object I can see them - https://ibb.co/WxMnY2c . If I execute following 
> SQL query in DbVis's SQL Commander, the result set is empty - 
> https://ibb.co/GngdWLH .
> 
> select *
>    from PG_CLASS
>   where RELNAME = 'umsaetze';
> 
> I noticed that the sessions producing the different results are not the 
> same - https://ibb.co/wdKcCFc , but seem to connect to different 
> databases. The "missing" table is indeed in the budget database.
> 
> The connection user is, apart from being member of pg_monitor vanilla - 
> https://ibb.co/DGs6sQz and https://ibb.co/8xzHrvP .
> 
> It seems, that in pg_class only is, with respect to custom databases, 
> listed what is in the database one connects to - https://ibb.co/dbbJVbJ.

As listed on the tin:

https://www.postgresql.org/docs/current/catalogs-overview.html

"Most system catalogs are copied from the template database during 
database creation and are thereafter database-specific. A few catalogs 
are physically shared across all databases in a cluster; these are noted 
in the descriptions of the individual catalogs."

pg_class is not one of the global tables.

> 
> template1=> select count(*)
>    from PG_CLASS
>   where RELNAME = 'umsaetze';
>   count
> -------
>       0
> (1 row)
> 
> template1=> \q
> 
> C:\Users\thiemo\AppData\Roaming\MOBAXT~1\home>psql -h hp-slimline-260 -p 
> 5436 -U monitor budget
> psql (11.2, server 16.1 (Debian 16.1-1.pgdg110+1))
> WARNING: psql major version 11, server major version 16.
>           Some psql features might not work.
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, 
> bits: 256, compression: off)
> Type "help" for help.
> 
> budget=> select count(*)
>    from PG_CLASS
>   where RELNAME = 'umsaetze';
>   count
> -------
>       2
> (1 row)
> 
> budget=> \q
> 
> 
> Is there a possibility to make the user monitor see all the objects of 
> the cluster? Background is that I was hoping to create a query to spit 
> out the size of tables in the cluster.
> 
> Kind regards
> 
> Thiemo
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com