Thread: Trigger to run @ connection time?
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
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:For manual psql sessions, you can put some setup commands in ~/.psqlrc.
> 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.
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 donefor 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
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
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