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

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

David J.

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, 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
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 a prodedure that creates a view in the monitor schema accessing the respective databases with union all to concatenate the data.

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

On Fri, Mar 15, 2024 at 6:43 AM Thiemo Kellner <thiemo@gelassene-pferde.biz>
wrote:

I am not sure, we are taking about the same problem, but would be
surprised to be the only one having experienced filling disks.

...

So, if I have a disk getting filled up, I would like to get easily
information on the problematic
structures in one go.

This is a common problem, and one that has been solved before. There are
many monitoring solutions out there that can help you with this. For an
incomplete list, see:

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

If you want to roll your own, the other thread is full of good information
on that.

Cheers,
Greg

You could also create a PostgreSQL foreign server for each of the other databases, which would let you issue a query to UNION together the results of a query on all of the catalogs. This would require creating a foreign table for pg_class in 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