Search Postgresql Archives

Re: Trigger to run @ connection time?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux