Re: Trigger to run @ connection time?

Kynn Jones wrote:
> On Tue, Mar 11, 2008 at 5:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "Kynn Jones" <kynnjo(at)gmail(dot)com> writes:
>>> If one can set up this insert operation so that it happens automatically
>>> whenever a new connection is made, I'd like to learn how it's done.
>> For manual psql sessions, you can put some setup commands in ~/.psqlrc.
>> In any other context I'm afraid you're stuck with modifying your client
>> application code.
>>
>> 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?
>
>
> I guess I don't know just *how broken* a trigger can be :-) ! I guess what
> you're saying is that a trigger can be *so badly broken* that, even if
> executed in response to a regular INSERT/UPDATE/DELETE event, it would
> disable the database to the point that the only recourse would be to kill
> the connection and open a new one. Such a trigger, if it were associated
> with an CONNECT event, would render the database inaccessible. It follows
> from Murphy's law that triggers that are this broken are certainly
> possible...

I've been interested in an ON CONNECT trigger, too.

My suggestion regarding the scary problem noted above is that there
would have to be a configuration setting in postgresql.conf to enable or
disable the trigger so that if a broken trigger killed the data base,
you could recover by modifying the configuration file so as to disable
the trigger and then successfully restart the data base.

The problem with the suggested work-around implementation of modifying
the client application code is that the (pseudo-)trigger is only fired
if the data base is accessed by means of that specifically-rigged-up
application. It would not fire if someone went in via a utility like
pgAdmin III, or psql, for example. And since a really useful data base
is likely to have multiple applications running against it anyway, they
would all have to consistently duplicate the pseudo-trigger code.

-- BMT