Re: Regarding extension

On Fri, 4 Oct 2019 at 13:18, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Natarajan R <nataraj3098(at)gmail(dot)com> writes:
> > Me: Thanks Tomas, But this is for that particular database only, I want
> > to get the *list of database Id's* on which my extension is installed
> > during *PG_INIT* itself...
>
> You can't. In the first place, that information simply isn't obtainable,
> because a session running within one database doesn't have access to the
> catalogs of other databases in the cluster. (You could perhaps imagine
> firing up connections to other DBs a la dblink/postgres_fdw, but that will
> fail because you won't necessarily have permissions to connect to every
> database.) In the second place, it's a pretty terrible design to be
> attempting any sort of database access within _PG_init, because that
> precludes loading that module outside a transaction; for example you
> will not be able to preload it via shared_preload_libraries or allied
> features.
>

Absolutely agreed. Having done this myself, it's much, much harder than
you'd expect and not something I suggest anyone try unless it's absolutely
necessary.

It'd be an absolute dream if extensions could create their own shared
catalogs; that'd make life *so* much easier. But I seem to recall looking
at that and nope-ing right out. That was a while ago so I should probably
revisit it.

Anyhow: BDR and pglogical are extensions that do need to concern itself
with what's in various databases, so this is an issue I've worked with day
to day for some time.

BDR1 used a custom security label and the pg_shseclabel catalog to mark
databases that were BDR-enabled. It launched a worker that connected to
database InvalidOid, so it could read only the global shared catalogs, then
it scanned them to find out which DBs to launch individual workers for.
This interacted poorly with pg_dump/pg_restore and proved fragile, so I
don't recommend it.

pglogical instead launches a static bgworker with no DB connections. On
startup or when it gets a suitable message over its extension shmem
segment + a latch set, it launches new workers for each DB. Each worker
inspects the DB to check for the presence of the pglogical extension and
exits if it isn't found.

All in all, it's pretty clumsy, though it works very well.

We have to do our own process management and registration. Workarounds must
be put in place for processes failing to launch then a new process taking
their shmem slot and various other things. pglogical lands up having to
duplicate quite a bit of the bgw and postmaster management infrastructure
because it's not extensible and it has some serious deficiencies in
error/crash handling.

(We also have our own dependency management, lock management, shared cache
invalidations, syscache/catcache-like mechanism, and other areas where we'd
rather extend Pg's infrastructure but can't. Being able to create our own
dependency types, custom lock types/methods, custom syscaches we could get
invalidations for, etc would just be amazing. But each would likely be a
major effort to get into core, if we could get it accepted at all given the
"in core users" argument, and we'd have to keep the old method around
anyway...)

--
Craig Ringer http://www.2ndQuadrant.com/
2ndQuadrant - PostgreSQL Solutions for the Enterprise