Trigger to run @ connection time?

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!

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

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

"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

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

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

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

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

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

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!

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.

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!

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

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

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

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

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

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

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