Re: Trigger to run @ connection time?

On Tue, Mar 11, 2008 at 10: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?

Well, I would benefit from ON CONNECT trigger, I must admit.

An application which uses tsearch2 ('SELECT set_curdict() / set_curcfg()' being
called upon session start). That is fine and that works.

Now, using statement pooling solution like pgbouncer is great benefit for this
specific application. There is one little problem however -- one can never be
sure when session is started. As a countermeasure there is a need to call
set_curdict() in every transaction (which is very fast), but one needs
to remember
to call that set_curdict() every time.

ON CONNECT trigger would solve that neatly!

Wouldn't be enough to disallow ON COMMIT triggers for SUPERUSERs?
And a BIG FAT WARNING in documentation to wrap the trigger with
BEGIN ... EXCEPTION WHEN OTHERS RAISE NOTICE ... END, and have
a second user handy with proper permissions?

Dawid
--
Solving [site load issues] with [more database replication] is a lot
like solving your own personal
problems with heroin - at first it sorta works, but after a while
things just get out of hand.
- Fred B. Schneider, PhD