Обсуждение: select results on pg_class incomplete

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.

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

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

Thanks for the enlightenment. A pity. I suppose, there is no working 
around this?

Am 14.03.2024 um 18:01 schrieb Adrian Klaver:
> 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
>>
>>
> 

On Fri, 15 Mar 2024 at 07:13, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Thu, Mar 14, 2024, 11:08 Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
>>
>> Thanks for the enlightenment. A pity. I suppose, there is no working
>> around this?
>
> Write a script to do the query in a loop on all databases - that catalog is global.

Yeah, maybe dblink and a LATERAL join might be an easy way. Something like:

create extension dblink;
select d.datname,c.relname from pg_database d, lateral (select * from
dblink('dbname='||d.datname,$$select relname from pg_class where
relname = 'pg_class';$$) c(relname text)) c
(relname) where d.datallowconn;
  datname   | relname
------------+----------
 postgres   | pg_class
 template1  | pg_class
 regression | pg_class
(3 rows)

David

Am 14.03.2024 um 21:03 schrieb David Rowley:
> Yeah, maybe dblink and a LATERAL join might be an easy way. Something like:
> 
> create extension dblink;
> select d.datname,c.relname from pg_database d, lateral (select * from
> dblink('dbname='||d.datname,$$select relname from pg_class where
> relname = 'pg_class';$$) c(relname text)) c
> (relname) where d.datallowconn;
>    datname   | relname
> ------------+----------
>   postgres   | pg_class
>   template1  | pg_class
>   regression | pg_class
> (3 rows)

Thanks for the ideas. As I would want to keep it in the database, dblink 
would be the way to go. Maybe, I will create a prodedure that creates a 
view in the monitor schema accessing the respective databases with union 
all to concatenate the data.

> You solve a problem that no one has. Data belonging together may still be divided into schemas in a database. Thus,
themetadata is also reported and archived individually per database.
 

I am not sure, we are taking about the same problem, but would be 
surprised to be the only one having experienced filling disks. Maybe, I 
am just that old already that disk space has become so cheep, the 
problem does not exist any longer.

With respect to metadata and databases: The point is not that I cannot 
see the tables in another schema (I believe, did not check yet), but in 
other databases. While this actually does not matter much, I still hold 
it true that a disk getting filled up does not care in which database or 
schema a explosively growing table resides. So, if I have a disk getting 
filled up, I would like to get easily information on the problematic 
structures in one go. With PostgreSQL this does not seem to be possible 
out of the box. I now can query each database separately, or I can 
create auxiliary structures like dblink and views to accommodate for a 
"single" query solution. My two dimes.

> On Mar 15, 2024, at 03:30, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
> Thanks for the ideas. As I would want to keep it in the database, dblink would be the way to go. Maybe, I will create
aprodedure that creates a view in the monitor schema accessing the respective databases with union all to concatenate
thedata. 

You could also create a PostgreSQL foreign server for each of the other databases, which would let you issue a query to
UNIONtogether the results of a query on all of the catalogs.  This would require creating a foreign table for pg_class
inthe other databases. 

> You could also create a PostgreSQL foreign server for each of the other databases, which would let you issue a query
toUNION together the results of a query on all of the catalogs.  This would require creating a foreign table for
pg_classin the other databases.
 

Thanks. So many possibilities. I am more familiar with dblinks than 
foreign tables. I will read into it.

> https://wiki.postgresql.org/wiki/Monitoring 

Thanks for the URL. I am not too keen to re-invent the wheel. Although 
it teaches me on PostgreSQL.

On 3/15/24 03:42, Thiemo Kellner wrote:
>> You solve a problem that no one has. Data belonging together may still 
>> be divided into schemas in a database. Thus, the metadata is also 
>> reported and archived individually per database.
> 
> I am not sure, we are taking about the same problem, but would be 
> surprised to be the only one having experienced filling disks. Maybe, I 
> am just that old already that disk space has become so cheep, the 
> problem does not exist any longer.
> 
> With respect to metadata and databases: The point is not that I cannot 
> see the tables in another schema (I believe, did not check yet), but in 
> other databases. While this actually does not matter much, I still hold 

That is backwards, schemas are namespaces within a database you can see 
their contents from the local(database) system catalogs.

> it true that a disk getting filled up does not care in which database or 
> schema a explosively growing table resides. So, if I have a disk getting 
> filled up, I would like to get easily information on the problematic 
> structures in one go. With PostgreSQL this does not seem to be possible 
> out of the box. I now can query each database separately, or I can 
> create auxiliary structures like dblink and views to accommodate for a 
> "single" query solution. My two dimes.
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com