Re: Oracle vs. PostgreSQL - a comment

Hi Stefan,

> On 01. Jun, 2020, at 00:35, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
> Paul Förster <paul(dot)foerster(at)gmail(dot)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 database inside the cluster is faster. Yet, it's an inconvenience. Also, if I had to create a new database, I'd also have to 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. I know 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 would be 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 because it 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