Alban Hertroys wrote:
On Mar 11, 2008, at 10:28 PM, Tom Lane wrote:
An ON CONNECT trigger enforced by the database seems a bit scary to me.
If it's broken, how you gonna get into the DB to fix it?
regards, tom lane
If creating the trigger wouldn't be possible from within the database
that it's defined for (which would be strange anyway as far as I'm
concerned, since you are already connected at that point and thus missed
an opportunity to fire that trigger) this shouldn't be a problem.
To put that into an SQL statement, something like:
#template1 $ CREATE TRIGGER my_database_connect_trg BEFORE CONNECT ON
my_database EXECUTE PROCEDURE my_database_setup()
Although of course that begs the question where that procedure would be
stored; Rather not in template1, I suppose! This points to another
problem with ON CONNECT triggers, you'll likely need to be connected to
reach the stored procedure that the trigger calls! A nice chicken and
egg problem, with some scope issues on the horizon...
I envision this not so much as a BEFORE connect trigger, but
rather as an event that happens after the point of the user being
successfully authenticated, but before executing any user
application commands -- in fact before even starting to listen
for any incoming application commands.
A particular implementation I see this useful for, to give some
context to thinking about this, is to update a user password
expiration date (to, say, CURRENT_DATE + 30) at each login. This
would then allow the creation of a system that lets unused
accounts expire but automatically maintains the validity of
actively used accounts, for example. I can think of other uses, too.
I currently achieve this functionality with an event triggered in
an end-user application, but I'd really like it to happen in the
data base so that every application that access this data base
doesn't have to recreate that particular functionality -- and
also so as to eliminate to problem of the functionality not being
implemented by other applications outside our control that access
the data base.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
