Thread: 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
Attachment
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, Suporte24x7 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