Re: 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

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(at)fuzzy(dot)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(at)fuzzy(dot)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(at)fuzzy(dot)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(at)fuzzy(dot)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