Hi Stefan,
> On 01. Jun, 2020, at 00:35, Stefan Keller <sfkeller@gmail.com> wrote:
> Paul Förster <paul.foerster@gmail.com> wrote:
>> Also, I like the idea of global container/cluster-wide views such as CDB_TABLES, etc.,
>> a thing which I definitely and seriously miss about PostgreSQL.
>
> Can you specify little more: What's the use case for this (assuming
> you know dblink and postgres_fdw)?
you don't expect me to create a dblink to each and every database inside each database cluster? Reconnecting to another
databaseinside the cluster is faster. Yet, it's an inconvenience. Also, if I had to create a new database, I'd also
haveto setup a dblink to it. Why?
I don't know much about FDW. Our developers (increasingly) use Flyway to distribute their data models across platforms.
Iknow that FDWs are used in some cases but I don't know much about them (yet).
The use case would be to locate whatever a user/developer is referring to, something like this (assuing such a view
wouldbe named pg_global_tables):
postgres# select dbname, schema, owner, tablename from pg_global_tables;
postgres=# select * from pg_global_tables;
dbname | schema | owner | tablename
--------+----------------+---------+-----------
db01 | schema_test | test | testtab
db02 | schema_test_13 | test_13 | testtab
(2 rows)
Many times, a user calls and is in his context and you don't even know what database he's on. So you can do a quick
search.Interviewing him on the phone and making him tell you from the start a) takes time and b) drives him nuts
becauseit throws him out of his context. But I still need to know which database he's on and which schema he uses.
Same goes for all other object types, such as views, etc.
It's all there in pg_tables and information_schema (what a name...) but it's only inside each database and not
globally.
Cheers,
Paul