I'm trying to find a soultion to automatically execute something (preferrably a function or at least some pure sql statements) at the beginning and at the end of a user session. As an example, imagine just storing of all login and logout timestamps (though the real task is a bit more complicated than that)
I would not like it to be somehow explicitely client-side initiated (like e.g. trivially calling some certain do_at_sess_begin(), do_at_sess_end()) because first, a malicious client could then mess it up easily, and furthermore, in the case that the session somehow ended abnormally (due to say temporary network fault) client-side finalizer function would not be called anyway.
My first try was to create an event trigger for sql drop events, then create a temporary table and watch for its drop at the end of a session. However, it seems drop events are just not fired for temporary tables (although documentation does not state this anywhere, as far as I can see, maybe I'm wrong about that)
It would seem like one of the simplest things ever, but I'm now totally stuck out of ideas.
As a partial solution, handling just session _end_ would already be OK.
Any thoughts?
This cannot be solved without patching PostgreSQL source code :( . There are not good hooks for custom extension. Patch is relative simple, but I cannot to publish it.
You have to modify main loop in src/backend/tcop/postgres.c file. Login point can be immediately before loop. Logout point can be joined to 'X',EOF message.
Attention - there are some corner case, what are necessary to solve - mainly how to handle exceptions in login/logout procedures.
Regards
Pavel
p.s. I understand so this hack is necessary for porting older application from other databases, but I am not sure if it is a good idea to use it. Not all tasks can be solved well in database.
