Re: proposal : backend startup hook / after logon trigger

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