Re: Trigger to run @ connection time?

On Friday 14 March 2008 11:36, Marko Kreen wrote:
> On 3/14/08, Erik Jones  wrote:
> >  On Mar 14, 2008, at 7:17 AM, Marko Kreen wrote:
> >  > To put it to core Postgres, it needs to be conceptually sane
> >  > first, without needing ugly workarounds to avoid it bringing
> >  > whole db down.
> >  >
> >  > I can see ATM only few ways:
> >  >
> >  > - Applies only to non-superusers.
> >  >
> >  > - Error from CONNECT trigger does not affect superuser.
> >  >
> >  > - Applies to database + role.  Role could be also group of users.
> >  >
> >  > So you always have way do fix things, without hexediting in data
> >  > dir...
> >
> > Another option:
> >
> >  Does not fire at all in single-user mode.  This would be covered by
> >  "Applies to non-superusers" if that were there but, by itself, the
> >  triggers would still fire for normal superuser connections.
>
> Seems bit too hard - you may other db-s that work fine,
> why should those suffer?
>

there are other failure scenario's for a single db that require single user 
mode (think corrupted indexes), so I'm not sure that is too high a price to 
be paid, though a less barriar would be better.

If we decide that an on connect trigger involves the combination of a database 
and a role, you generally can escape from the failure scenario by having 
either a different role, or a different database with the ability to 
do "alter database disable on connect triggers". whether this is a direct 
alter database, or set at the GUC level, either makes it pretty hard to lock 
yourself out completly, and single user mode can be the fall back for that if 
needed. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL