Thread: Trigger to run @ connection time?

Trigger to run @ connection time?

From
"Kynn Jones"
Date:

Hi!  I want to set up a trigger (somehow) that, whenever someone connects database my_db, will fire and thereby run a stored PLPERL procedure perl_setup() in the new connection's environment.  (BTW, this procedure adds useful definitions, mostly subs, to Perl's main package.  This needs to be done for each connection, because such modifications of package main do not persist from one session to the next.)

I figured that setting up such a trigger would be possible, based on the naive assumption that all global events of interest (e.g. the opening (or closing) of a connection to a specific database, or the creation of a new database) would correspond to an INSERT, UPDATE, or DELETE event on some system table.

So I tried to find some system table that would get modified whenever a new connection was made, but I was unsuccessful.  The closest I found was the VIEW pg_activity, and the crucial information I need from this view comes from procedures like pg_stat_get_backend_pid().

Is there a bona fide table (not a view!) that I could use to define an "on connect" trigger?  (This table would not only have to undergo some INSERT or UPDATE event at the time of the new connection, but it should also provide enough information to allow my code to determine which database is being connected to.)

If not, is there some other way to set up a trigger that

TIA!

Kynn

Re: Trigger to run @ connection time?

From
Alban Hertroys
Date:
On Mar 10, 2008, at 9:07 PM, Kynn Jones wrote:

> So I tried to find some system table that would get modified
> whenever a new connection was made, but I was unsuccessful.  The
> closest I found was the VIEW pg_activity, and the crucial
> information I need from this view comes from procedures like
> pg_stat_get_backend_pid().

You can't define triggers on system tables.

> Is there a bona fide table (not a view!) that I could use to define
> an "on connect" trigger?  (This table would not only have to
> undergo some INSERT or UPDATE event at the time of the new
> connection, but it should also provide enough information to allow
> my code to determine which database is being connected to.)

I'm afraid not, although there's been mention on this ML of on
connect triggers before. Not sure whether that got solved though...

> If not, is there some other way to set up a trigger that

Closest thing I can think of is defining a table that you insert a
record in as soon as you connect and put a trigger on that.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47d5c63d233091216612506!



Re: Trigger to run @ connection time?

From
"Kynn Jones"
Date:


On Mon, Mar 10, 2008 at 7:47 PM, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:

You can't define triggers on system tables.
 
Oh, well... :-/  Thanks for the reality check!

> If not, is there some other way to set up a trigger that

Oops.  I guess a cut-and-paste error in my original message must have caused some text to be lost.  I meant to write

> If not, is there some other way to set up a trigger that will run whenever a new connection to my_db is 
> established?

One could be forgiven for not being able to figure out what I was asking, but, if, in spite of the huge typo, you were in fact able to guess what I had meant to ask, then I'm confused by your suggestion here:
 
Closest thing I can think of is defining a table that you insert a
record in as soon as you connect and put a trigger on that.

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.  But if not, then I don't see how performing the insert "manually" every time one connects would be any easier than simply executing the perl_setup() procedure directly.

Kynn

Re: Trigger to run @ connection time?

From
"Andrej Ricnik-Bay"
Date:
On 12/03/2008, Kynn Jones <kynnjo@gmail.com> wrote:

> 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.  But if
> not, then I don't see how performing the insert "manually" every time one
> connects would be any easier than simply executing the perl_setup()
> procedure directly.
After having thought about this for a few seconds <BEG> you
could conceivably use some OS/DB integration to achieve this.
Just make sure postmaster writes new connections to a log,
monitor that log from a script, and if it sees a "connect" have
that insert a value into "special table" of yours that then can
do the trigger you looked for?   Of course I may not have quite
understood how that  "this procedure adds useful definitions,
mostly subs, to Perl's main package.  This needs to be done
for each connection" is meant to work.


Cheers,
Andrej



--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

Re: Trigger to run @ connection time?

From
Tom Lane
Date:
"Kynn Jones" <kynnjo@gmail.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?

            regards, tom lane

Re: Trigger to run @ connection time?

From
"Kynn Jones"
Date:


On Tue, Mar 11, 2008 at 5:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Kynn Jones" <kynnjo@gmail.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...

Which is a long-winded way to say that I see your point!

Kynn


Re: Trigger to run @ connection time?

From
btober@ct.metrocast.net
Date:
Kynn Jones wrote:
> On Tue, Mar 11, 2008 at 5:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> "Kynn Jones" <kynnjo@gmail.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

Re: Trigger to run @ connection time?

From
Karsten Hilbert
Date:
On Wed, Mar 12, 2008 at 07:52:29AM -0400, Kynn Jones 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?

A "psql --skip-on-connect-trigger", only available to, say,
superusers ? Or a database flag (like the "accepts
connections" one) editable by superusers when connected to
another database ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Trigger to run @ connection time?

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Hi!  I want to set up a trigger (somehow) that, whenever someone connects
> database my_db, will fire and thereby run a stored PLPERL procedure
> perl_setup() in the new connection's environment.  (BTW, this procedure adds
> useful definitions, mostly subs, to Perl's main package.  This needs to be
> done for each connection, because such modifications of package main do not
> persist from one session to the next.)

I think you are going about this the wrong way. Create a hook in each plperl
func that does the initial setup for you as needed. Simply store a
value in $_SHARED indicating whether it has already run or not for that
session. This also avoid any overhead at all of calling perl_setup() if
the connection in question is never going to use plperl, or even if it is
going to use plperl but does not need perl_setup().

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200803121042
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8


-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkfX6+0ACgkQvJuQZxSWSsiFdQCg4WGmB4+InrL7E+7c8Tq82lFy
TFcAn2lQfSXJwO8LUQ9vZPf9ZStLdVHW
=R5fK
-----END PGP SIGNATURE-----



Re: Trigger to run @ connection time?

From
"Kynn Jones"
Date:


On Tue, Mar 11, 2008 at 4:51 PM, Andrej Ricnik-Bay <andrej.groups@gmail.com> wrote:
On 12/03/2008, Kynn Jones <kynnjo@gmail.com> wrote:
Of course I may not have quite
understood how that  "this procedure adds useful definitions,
mostly subs, to Perl's main package.  This needs to be done
for each connection" is meant to work.

What I mean is illustrated by the following (extremely artificial and clumsy) example:

CREATE OR REPLACE FUNCTION setup_perl () RETURNS void
AS $PERL$

  # globals
  $::TRUE = 1;
  $::FALSE = 0;

  {
    my $leading_ws  = qr/\A\s*/;
    my $trailing_ws = qr/\s*\z/;

    # The next assignment defines the Perl function main::trim();
    # it has almost the same effect as writing
    # sub trim { ... }
    # at the top level scope (in the main package), except that
    # the definition happens at run time rather than at compile
    # time.
    *trim = sub {
      local $_ = shift;
      s/$leading_ws//;
      s/$trailing_ws//;
      return $_;
    };
  }
$PERL$ LANGUAGE plperl IMMUTABLE;

CREATE OR REPLACE FUNCTION is_foo ( TEXT ) RETURNS BOOLEAN
AS $PERL$
  return trim( shift ) eq 'foo' ? $::TRUE : $::FALSE;
$PERL$ LANGUAGE plperl IMMUTABLE;

CREATE OR REPLACE FUNCTION is_bar ( TEXT ) RETURNS BOOLEAN
AS $PERL$
  return trim( shift ) eq 'bar' ? $::TRUE : $::FALSE;
$PERL$ LANGUAGE plperl IMMUTABLE;

Notice that is_foo() and is_bar() both rely on the *perl* function trim.  They also refer to the Perl global variables $::TRUE and $::FALSE.  This technique facilitates the reuse of Perl code in two ways.  First, individual Perl subroutines can be defined once and called from various PLPERL procedures.  Second, it simplifies the cut-and-paste porting of Perl code (which often uses subroutines and global or file-scoped lexical variables) straight into to PLPERL.  (I wrote more about this technique recently, in the post with the subject line "On defining Perl functions within PLPERL code.")

(BTW, notice that, the function trim is actually a closure: it uses a couple of lexical variables, $leading_ws and $trailing_ws, that are defined in the enclosing scope; i.e. these definitions need to happen only once.  Such variables serve the same purpose as that of C static variables.  The ease of defining such closures is an added bonus of this technique.  In this artificial example, of course, this benefit is negligible, but when the computation of such constants is time-consuming, this could be a useful little optimization.)

Now, note that if we try to use is_foo() before invoking perl_setup(), it will fail:

my_db=> select is_foo( '  foo  ' );
ERROR:  error from Perl function: Undefined subroutine &main::trim called at line 2.

my_db=> select setup_perl();
 setup_perl
------------
 
(1 row)

(BTW, is there a way to avoid the useless output above?)

my_db=> select is_foo( '  foo  ' );
 is_foo 
--------
 t
(1 row)

my_db=> select is_bar( '  foo  ' );
 is_bar 
--------
 f
(1 row)


That's why it would be nice to run perl_setup() automatically at the beginning of each session.  Granted, one workaround would be to include the line

  spi_query( 'SELECT setup_perl()' ) unless $::TRUE;

at the top of ever PLPERL function that required the definitions provided by setup_perl().  Something like an ON CONNECT trigger would obviate this small annoyance, but I guess that's not a possibility at the moment.

Kynn

Re: Trigger to run @ connection time?

From
Alban Hertroys
Date:
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...

Regards,
Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47d85f64233091819183316!



Re: Trigger to run @ connection time?

From
Berend Tober
Date:
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.




Re: Trigger to run @ connection time?

From
Alban Hertroys
Date:
On Mar 13, 2008, at 2:00 AM, Berend Tober wrote:

> 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.

It doesn't matter whether you're talking about a BEFORE or an AFTER
trigger, that was just an example to illustrate the problem - which
is the same for both cases.

What I was saying is that if the ON CONNECT trigger is defined in the
database you're connecting to and it contains an error, there's
nothing you can do to fix the error (starting with logging into that
database). Which was what Tom pointed out already.

My idea around this was to define the ON CONNECT trigger outside the
database you're connecting to, so that you'll at least be able to fix
or disable the problematic code. It's a bit like the situation with
PAM authentication to your (UNIX-based) OS - you can define your own
authorisation methods, but if they are broken your only option is to
boot into single-user mode and disable that part of authorisation. In
Postgres template1 is a bit like single-user mode in UNIX.

Another option would be to not fire the trigger if a super user
connects to the database, but that pretty much depends on what the
trigger does, which is unknown by definition.

Thinking this over a bit more, it seems you're not so much looking
for an ON CONNECT trigger, but for an ON AUTHORISE trigger. The
problems remain pretty much the same though.

Regards,
Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47d8d2ed233091559156500!



Re: Trigger to run @ connection time?

From
"Dawid Kuroczko"
Date:
On Tue, Mar 11, 2008 at 10:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kynn Jones" <kynnjo@gmail.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

Re: Trigger to run @ connection time?

From
"Daniel Verite"
Date:
    Tom Lane wrote:

> 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?

At the protocol level, when the opening of a new session is requested,
how about a "skip connect-trigger" run-time parameter? This parameter
would be ignored (or an error being raised) if the connect isn't issued
by the owner of the database or a superuser.
Within client applications, the functionality could then be implemented
at the same level than the other connection parameters, I'm thinking of
a checkbox in a GUI or an additional parameter to the \connect psql
command.

By comparison, Oracle's solution to this is a grantable privilege that
causes their "logon triggers" to ignore any error.

--
 Daniel
 PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

Re: Trigger to run @ connection time?

From
"Marko Kreen"
Date:
On 3/13/08, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On Tue, Mar 11, 2008 at 10:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  > "Kynn Jones" <kynnjo@gmail.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!

Hm.  It seems to make more sense to implement connect-time
hook directly in pgbouncer.

--
marko

Re: Trigger to run @ connection time?

From
"Dawid Kuroczko"
Date:
On Thu, Mar 13, 2008 at 2:18 PM, Marko Kreen <markokr@gmail.com> wrote:
> On 3/13/08, Dawid Kuroczko <qnex42@gmail.com> wrote:
>  >  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!
>
>  Hm.  It seems to make more sense to implement connect-time
>  hook directly in pgbouncer.

Indeed that would solve the issue.  But then again it could be argued that
PL/pgSQL could be implemented outside the backend as well. ;-)

I see it as an addition which does have its applications.

   Regards,
       Dawid

Re: Trigger to run @ connection time?

From
"Marko Kreen"
Date:
On 3/14/08, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On Thu, Mar 13, 2008 at 2:18 PM, Marko Kreen <markokr@gmail.com> wrote:
>  > On 3/13/08, Dawid Kuroczko <qnex42@gmail.com> wrote:
> >  >  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!
>  >
>  >  Hm.  It seems to make more sense to implement connect-time
>  >  hook directly in pgbouncer.
>
> Indeed that would solve the issue.  But then again it could be argued that
>  PL/pgSQL could be implemented outside the backend as well. ;-)

Well, as the transaction-pooling breaks application expectations
and makes impossible for them to do initial setup, the connect
hook could give a way to compensate for that.

>  I see it as an addition which does have its applications.

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...

--
marko

Re: Trigger to run @ connection time?

From
Erik Jones
Date:
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.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




Re: Trigger to run @ connection time?

From
"Marko Kreen"
Date:
On 3/14/08, Erik Jones <erik@myemma.com> 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?

--
marko

Re: Trigger to run @ connection time?

From
Erik Jones
Date:
On Mar 14, 2008, at 10:36 AM, Marko Kreen wrote:

>> 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?

Excellent point.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




Re: Trigger to run @ connection time?

From
Robert Treat
Date:
On Friday 14 March 2008 11:36, Marko Kreen wrote:
> On 3/14/08, Erik Jones <erik@myemma.com> 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