Обсуждение: Regarding extension
Hi team,
I am creating sample extension in postgres, For that "During PG_INIT, i want to get the list of database Id's in which my extension is installed". Is there a way to get this?
and also another one.
How to have trigger for create extension?
Thanks in advance.
Em qui, 3 de out de 2019 às 02:24, Natarajan R <nataraj3098@gmail.com> escreveu: > > I am creating sample extension in postgres, For that "During PG_INIT, i want to get the list of database Id's in whichmy extension is installed". Is there a way to get this? > I'm not sure what you mean by "ld". However, if you want to know an extension is installed in a specific database, you should be logged in it. That's because extension catalog is not global. > How to have trigger for create extension? > Event triggers. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Thanks for your response Euler.
1)
"id" i meant by database id
I make my question simple, " during pg_init i want to get databaseid's in which my extension is installed... "
1. by using pg_database and pg_extension catalogs
2. if there any other way, kindly suggest me.
2)
I have one sql file which will be loaded during create extension, in that file only i have code for event trigger for create extension on ddl_command_end event....
My question is "When giving create extension, sql file will be loaded at that time only, if that is the case, this event trigger will be invoked or not? "
Thanks...
On Thu, 3 Oct 2019 at 19:31, Euler Taveira <euler@timbira.com.br> wrote:
Em qui, 3 de out de 2019 às 02:24, Natarajan R <nataraj3098@gmail.com> escreveu:
>
> I am creating sample extension in postgres, For that "During PG_INIT, i want to get the list of database Id's in which my extension is installed". Is there a way to get this?
>
I'm not sure what you mean by "ld". However, if you want to know an
extension is installed in a specific database, you should be logged in
it. That's because extension catalog is not global.
> How to have trigger for create extension?
>
Event triggers.
--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
On Thu, Oct 03, 2019 at 07:51:04PM +0530, Natarajan R wrote: >Thanks for your response Euler. > >1) >"id" i meant by database id > >I make my question simple, " during pg_init i want to get databaseid's in >which my extension is installed... " >1. by using pg_database and pg_extension catalogs >2. if there any other way, kindly suggest me. > Well, there's also MyDatabaseId variable, which tells you the OID of the current database. So you can use that, from the C code. In SQL, you can simply run "SELECT current_database()" or something like that. > >2) >I have one sql file which will be loaded during create extension, in that >file only i have code for event trigger for create extension on >ddl_command_end event.... >My question is "When giving create extension, sql file will be loaded at >that time only, if that is the case, this event trigger will be invoked or >not? " > I'm not sure I understand the question. Are you asking if the event trigger will be invoked to notify you about creation of the extension containing it? I'm pretty sure that won't happen - it will be executed only for future CREATE EXTENSION commands. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Thu, 3 Oct 2019 at 20:54, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
On Thu, Oct 03, 2019 at 07:51:04PM +0530, Natarajan R wrote:
>Thanks for your response Euler.
>
>1)
>"id" i meant by database id
>
>I make my question simple, " during pg_init i want to get databaseid's in
>which my extension is installed... "
>1. by using pg_database and pg_extension catalogs
>2. if there any other way, kindly suggest me.
>
Well, there's also MyDatabaseId variable, which tells you the OID of the
current database. So you can use that, from the C code. In SQL, you can
simply run "SELECT current_database()" or something like that.
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...
>
>2)
>I have one sql file which will be loaded during create extension, in that
>file only i have code for event trigger for create extension on
>ddl_command_end event....
>My question is "When giving create extension, sql file will be loaded at
>that time only, if that is the case, this event trigger will be invoked or
>not? "
>
I'm not sure I understand the question. Are you asking if the event
trigger will be invoked to notify you about creation of the extension
containing it? I'm pretty sure that won't happen - it will be executed
only for future CREATE EXTENSION commands.
Me: Thanks Tomas, Yaah, what you said above is the way it should perform, but this trigger has been invoked in postgres 10.0 but not in postgres 10.4.. So, i am asking any GUC or anything need to be enabled to invoke this type of event triggers in 10.4 version tooo..
Natarajan R <nataraj3098@gmail.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.
We should back up about three steps and ask why you think you need
to do this. Generally speaking, database code shouldn't be concerning
itself with what is happening in other databases.
regards, tom lane
On Fri, 4 Oct 2019 at 13:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Natarajan R <nataraj3098@gmail.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...)