proposal : backend startup hook / after logon trigger
Hi,
I occasionally need to perform some action whenever a user connects, and
there's nothing like an "AFTER LOGON" trigger (available in some other
databases).
Is there any particular reason why there's not a "backend start hook",
executed right after a backend is initialized? I've tried a very simple
PoC (basically just a new hook definition, called from PostgresMain(),
see the after-logon-hook.diff (and a simple module that uses it is in
logon.c).
This obviously is not a complete patch or something, but is there a good
reason why this is a stupid idea?
Obviously this is a bit low-level approach, as most of the time the
triggers are implemented in a PL. But who says you can't call a PL from
the C module ...
Tomas
Attachments:
after-logon-hook.difftext/plain; name=after-logon-hook.diffDownload
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
new file mode 100644
index 976a832..35f1926
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
*************** int max_stack_depth = 100;
*** 104,110 ****
/* wait N seconds to allow attach from a debugger */
int PostAuthDelay = 0;
!
/* ----------------
* private variables
--- 104,110 ----
/* wait N seconds to allow attach from a debugger */
int PostAuthDelay = 0;
! backend_startup_hook_type backend_startup_hook = NULL;
/* ----------------
* private variables
*************** PostgresMain(int argc, char *argv[], con
*** 3750,3755 ****
--- 3750,3769 ----
if (!ignore_till_sync)
send_ready_for_query = true; /* initially, or after error */
+ if (backend_startup_hook != NULL)
+ {
+ PG_TRY();
+ {
+ backend_startup_hook(MyProcPid, MyDatabaseId, dbname, username);
+ }
+ PG_CATCH();
+ {
+ elog(FATAL, "Error calling after-logon trigger");
+ proc_exit(0);
+ }
+ PG_END_TRY();
+ }
+
/*
* Non-error queries loop here.
*/
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
new file mode 100644
index 9d19417..3971268
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
*************** extern bool is_authenticated_user_replic
*** 388,391 ****
--- 388,396 ----
extern bool BackupInProgress(void);
extern void CancelBackup(void);
+ typedef void (*backend_startup_hook_type) (pid_t pid, Oid databaseOid,
+ const char * dbname, const char * username);
+
+ extern backend_startup_hook_type backend_startup_hook;
+
#endif /* MISCADMIN_H */
On 10-11-2011 21:12, Tomas Vondra wrote:
I occasionally need to perform some action whenever a user connects, and
there's nothing like an "AFTER LOGON" trigger (available in some other
databases).
Are you proposing an on-logon hook or an on-connect trigger? It is two
separate things. The former can't solve some tasks (e.g. execute whatever pl
code) and the latter can't be implemented with a simple hook (you will have to
propose a syntax and offer some machinery to execute the pl code).
Of course, if you want to propose any of these ideas, keep in mind that a
symmetric functionality (e.g. on-logoff hook or on-disconnect trigger) should
be implemented too.
--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
On 11 Listopad 2011, 3:23, Euler Taveira de Oliveira wrote:
On 10-11-2011 21:12, Tomas Vondra wrote:
I occasionally need to perform some action whenever a user connects, and
there's nothing like an "AFTER LOGON" trigger (available in some other
databases).Are you proposing an on-logon hook or an on-connect trigger? It is two
separate things. The former can't solve some tasks (e.g. execute whatever
pl
code) and the latter can't be implemented with a simple hook (you will
have to
propose a syntax and offer some machinery to execute the pl code).
I'm proposing a backend startup hook.
I agree that it's a bit low-level tool, but it's not exactly true you
can't call a PL function from the hook - you can look it up and call
through fmgr. You'd have to hard code the name somewhere or something, but
it's possible. And you can call C functions from a PL, so in the end the
possibilities are about the same, except that a proper trigger solution
requires a lot of plumbing (catalogue, syntax, ...).
That might happen in the future, but I'm not proposing that now.
Of course, if you want to propose any of these ideas, keep in mind that a
symmetric functionality (e.g. on-logoff hook or on-disconnect trigger)
should
be implemented too.
Well, there's an on_proc_exit() callback, which IMHO serves as a backend
shutdown hook.
Tomas
2011/11/10 Tomas Vondra <tv@fuzzy.cz>:
Is there any particular reason why there's not a "backend start hook",
executed right after a backend is initialized? I've tried a very simple
PoC (basically just a new hook definition, called from PostgresMain(),
see the after-logon-hook.diff (and a simple module that uses it is in
logon.c).This obviously is not a complete patch or something, but is there a good
reason why this is a stupid idea?
[ catching up on some old email ]
I've thought of this before, but I'm not exactly clear on what the use
cases are.
The particular place where you've put this hook doesn't look right to
me. I think it would get re-executed after each ERROR.
Also, if you're going to insist that the triggers be written in C
(blech!) then there's not much point in including a TRY/CATCH block
here. Let the user do that themselves if they are so inclined; it's
not free.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 25 Listopad 2011, 2:44, Robert Haas wrote:
2011/11/10 Tomas Vondra <tv@fuzzy.cz>:
Is there any particular reason why there's not a "backend start hook",
executed right after a backend is initialized? I've tried a very simple
PoC (basically just a new hook definition, called from PostgresMain(),
see the after-logon-hook.diff (and a simple module that uses it is in
logon.c).This obviously is not a complete patch or something, but is there a good
reason why this is a stupid idea?[ catching up on some old email ]
I've thought of this before, but I'm not exactly clear on what the use
cases are.
You mean for a startup hook or after logon trigger?
The startup hook is useful for initializing an extension written in C,
when the extension was loaded from postgresql.conf. If you need to perform
the initialization for each db separately (so that you can decide whether
to apply the extension to the user/database), you need to do that after
the backend starts.
The logon trigger is useful e.g. for a VPD (Virtual Private Database) the
way Oracle does it. A logon trigger is the natural place where to
initialize the application context etc.
I'm not saying there are no workarounds (different, a bit awkward
solutions) for both cases.
The particular place where you've put this hook doesn't look right to
me. I think it would get re-executed after each ERROR.
Yes, I've noticed that too. It should be probably moved out of the
infinite loop.
Also, if you're going to insist that the triggers be written in C
(blech!) then there's not much point in including a TRY/CATCH block
here. Let the user do that themselves if they are so inclined; it's
not free.
I don't insist on writing all logon triggers in C - I see those two
solutions (hook vs. trigger) rather separate, although there are tasks
that may be solved using any of them. My current need nicely matches the
startup hook, that's why I proposed only this.
The logon trigger would require much more work (catalogue, syntax, ...).
Tomas
"Tomas Vondra" <tv@fuzzy.cz> writes:
On 25 Listopad 2011, 2:44, Robert Haas wrote:
I've thought of this before, but I'm not exactly clear on what the use
cases are.
The startup hook is useful for initializing an extension written in C,
when the extension was loaded from postgresql.conf. If you need to perform
the initialization for each db separately (so that you can decide whether
to apply the extension to the user/database), you need to do that after
the backend starts.
If you need that, just load the extension with local_preload_libraries.
regards, tom lane
Dne 25.11.2011 17:48, Tom Lane napsal(a):
"Tomas Vondra" <tv@fuzzy.cz> writes:
On 25 Listopad 2011, 2:44, Robert Haas wrote:
I've thought of this before, but I'm not exactly clear on what the use
cases are.The startup hook is useful for initializing an extension written in C,
when the extension was loaded from postgresql.conf. If you need to perform
the initialization for each db separately (so that you can decide whether
to apply the extension to the user/database), you need to do that after
the backend starts.If you need that, just load the extension with local_preload_libraries.
I can't do that. I have an extension that needs to be loaded from
shared_preload_libraries (because it needs to ask for space in shared
memory segment), but I need to perform some additional initialization
for each backend.
Right now I solve that with an 'initialized' variable that's set to
false, and I have to check that before each action (and perform the init
if it's false). An after-startup hook would be much cleaner.
Tomas