Thread: mail alert
<div class="Section1"><p class="MsoNormal">Hi All,<p class="MsoNormal"> <p class="MsoNormal">I was looking in what way it’spossible to alert via mail when some conditions are true in a database.<p class="MsoNormal"> <p class="MsoNormal">Thanksin advance!<p class="MsoNormal"> <p class="MsoNormal">Jan</div>
Hello, Jan Verheyden a écrit : > I was looking in what way it’s possible to alert via mail when some > conditions are true in a database. a) If the alert is not "very urgent" i.e. you can alter some minutes later I would do it like this : 1. Create a function that returns what you need, most importantly if the conditions are met 2. Create a script that does something like "SELECT * FROM function()..." b) If the email should be sent immediately, you could create a perl function in the database, launched by a TRIGGER and launching an "external" script... Therefore, first you have to know the requirements... Denis
Denis BUCHER wrote: > Hello, > > Jan Verheyden a écrit : > >> I was looking in what way it’s possible to alert via mail when some >> conditions are true in a database. >> > > a) If the alert is not "very urgent" i.e. you can alter some minutes > later I would do it like this : > > 1. Create a function that returns what you need, most importantly if the > conditions are met > > 2. Create a script that does something like "SELECT * FROM function()..." > > b) If the email should be sent immediately, you could create a perl > function in the database, launched by a TRIGGER and launching an > "external" script... > > Therefore, first you have to know the requirements... > > Denis > > see "check_postgres" for nagios-style monitoring see \o /tmp/alert_data_file and \! mailx -s "alert" me@work.com /tmp/alert_data_file
Hi,
Can you just tell me whether your database is place on which server(linux/or windows or..)?
----- Original Message -----From: Jan VerheydenSent: Tuesday, August 11, 2009 6:31 PMSubject: [SQL] mail alertHi All,
I was looking in what way its possible to alert via mail when some conditions are true in a database.
Thanks in advance!
Jan
----- Original Message -----From: Jan VerheydenSent: Tuesday, August 11, 2009 6:31 PMSubject: [SQL] mail alertHi All,
I was looking in what way it’s possible to alert via mail when some conditions are true in a database.
I guess you might be able to do that by using PLPython, PLPerl or PLTcl and using the standard functions for doing emails in there.
--
Shoaib Mir
http://shoaibmir.wordpress.com/
It looks to me like you want Dennis's #2. Lauch a mailer script from a trigger function. (Function can be any language you're familiar with including pgsql if you wish to take advantage of "\! your-mail-script" Jan Verheyden wrote: > Hi, > > I got a bit stuck... > I was looking for a solution for option a) > > Maybe I'll first explain the situation a bit more: > > I have one database for patient registration > Another one for image storage > And a third one for book keeping > A patient should be registered first before the images are stored, so if there is a new line in the second database withan id which does not exist yet, it has to be notified in the book keeping database. > > Now the questions: > 1) Can I do this with the inner join (tables subject_id from DB1, pat_id from DB2), there it is two different databases > 2) Once it is notified in the book keeping that is not registered yet, is it best to poll on this column to send awarning, or use a trigger?? > > Thanks!! > > > Jan > -----Original Message----- > From: Rob Sargent [mailto:robjsargent@gmail.com] > Sent: Wednesday, August 12, 2009 3:38 AM > To: Denis BUCHER > Cc: Jan Verheyden; 'pgsql-sql@postgresql.org' > Subject: Re: [SQL] mail alert > > Denis BUCHER wrote: > >> Hello, >> >> Jan Verheyden a écrit : >> >> >>> I was looking in what way it’s possible to alert via mail when some >>> conditions are true in a database. >>> >>> >> a) If the alert is not "very urgent" i.e. you can alter some minutes >> later I would do it like this : >> >> 1. Create a function that returns what you need, most importantly if the >> conditions are met >> >> 2. Create a script that does something like "SELECT * FROM function()..." >> >> b) If the email should be sent immediately, you could create a perl >> function in the database, launched by a TRIGGER and launching an >> "external" script... >> >> Therefore, first you have to know the requirements... >> >> Denis >> >> >> > see "check_postgres" for nagios-style monitoring > see \o /tmp/alert_data_file > and \! mailx -s "alert" me@work.com /tmp/alert_data_file > >
It’s on Windows
From: ramasubramanian [mailto:ramasubramanian.g@renaissance-it.com]
Sent: Wednesday, August 12, 2009 6:01 AM
To: Jan Verheyden; pgsql-sql@postgresql.org
Subject: Re: [SQL] mail alert
Hi,
Can you just tell me whether your database is place on which server(linux/or windows or..)?
----- Original Message -----
From: Jan Verheyden
Sent: Tuesday, August 11, 2009 6:31 PM
Subject: [SQL] mail alert
Hi All,
I was looking in what way it’s possible to alert via mail when some conditions are true in a database.
Thanks in advance!
Jan
Hi, I got a bit stuck... I was looking for a solution for option a) Maybe I'll first explain the situation a bit more: I have one database for patient registration Another one for image storage And a third one for book keeping A patient should be registered first before the images are stored, so if there is a new line in the second database withan id which does not exist yet, it has to be notified in the book keeping database. Now the questions:1) Can I do this with the inner join (tables subject_id from DB1, pat_id from DB2), there it is two differentdatabases 2) Once it is notified in the book keeping that is not registered yet, is it best to poll on this columnto send a warning, or use a trigger?? Thanks!! Jan -----Original Message----- From: Rob Sargent [mailto:robjsargent@gmail.com] Sent: Wednesday, August 12, 2009 3:38 AM To: Denis BUCHER Cc: Jan Verheyden; 'pgsql-sql@postgresql.org' Subject: Re: [SQL] mail alert Denis BUCHER wrote: > Hello, > > Jan Verheyden a écrit : > >> I was looking in what way it’s possible to alert via mail when some >> conditions are true in a database. >> > > a) If the alert is not "very urgent" i.e. you can alter some minutes > later I would do it like this : > > 1. Create a function that returns what you need, most importantly if the > conditions are met > > 2. Create a script that does something like "SELECT * FROM function()..." > > b) If the email should be sent immediately, you could create a perl > function in the database, launched by a TRIGGER and launching an > "external" script... > > Therefore, first you have to know the requirements... > > Denis > > see "check_postgres" for nagios-style monitoring see \o /tmp/alert_data_file and \! mailx -s "alert" me@work.com /tmp/alert_data_file
Hi, Is it possible to use pgsql for creating a mailer script?? Can you help me as well with my first question? (below) Thanks a lot! Jan -----Original Message----- From: Rob Sargent [mailto:robjsargent@gmail.com] Sent: Wednesday, August 12, 2009 4:01 PM To: Jan Verheyden Cc: 'Denis BUCHER'; 'pgsql-sql@postgresql.org' Subject: Re: [SQL] mail alert It looks to me like you want Dennis's #2. Lauch a mailer script from a trigger function. (Function can be any language you're familiar with including pgsql if you wish to take advantage of "\! your-mail-script" Jan Verheyden wrote: > Hi, > > I got a bit stuck... > I was looking for a solution for option a) > > Maybe I'll first explain the situation a bit more: > > I have one database for patient registration > Another one for image storage > And a third one for book keeping > A patient should be registered first before the images are stored, so if there is a new line in the second database withan id which does not exist yet, it has to be notified in the book keeping database. > > Now the questions: > 1) Can I do this with the inner join (tables subject_id from DB1, pat_id from DB2), there it is two different databases > 2) Once it is notified in the book keeping that is not registered yet, is it best to poll on this column to send awarning, or use a trigger?? > > Thanks!! > > > Jan > -----Original Message----- > From: Rob Sargent [mailto:robjsargent@gmail.com] > Sent: Wednesday, August 12, 2009 3:38 AM > To: Denis BUCHER > Cc: Jan Verheyden; 'pgsql-sql@postgresql.org' > Subject: Re: [SQL] mail alert > > Denis BUCHER wrote: > >> Hello, >> >> Jan Verheyden a écrit : >> >> >>> I was looking in what way it’s possible to alert via mail when some >>> conditions are true in a database. >>> >>> >> a) If the alert is not "very urgent" i.e. you can alter some minutes >> later I would do it like this : >> >> 1. Create a function that returns what you need, most importantly if the >> conditions are met >> >> 2. Create a script that does something like "SELECT * FROM function()..." >> >> b) If the email should be sent immediately, you could create a perl >> function in the database, launched by a TRIGGER and launching an >> "external" script... >> >> Therefore, first you have to know the requirements... >> >> Denis >> >> >> > see "check_postgres" for nagios-style monitoring > see \o /tmp/alert_data_file > and \! mailx -s "alert" me@work.com /tmp/alert_data_file > >
On 2009-08-11, Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be> wrote: > > Hi All, > > I was looking in what way it's possible to alert via mail when some conditi= > ons are true in a database. > > Thanks in advance! Assuming you mean email, and not ink on paper (hmm, OTOH you could load postcards into a printer....) you could do this using NOTIFY and a listener written in some other langauge, notify is really neat. or possibly invoke mail(1) with a plpythonu or C function, or as superuser you can write a file (using copy ...) and arrange for something else to look, find it, and mail it. arbitrary file contents are possible with copy: COPY (SELECT NULL) TO myfile_name WITH NULL AS myfile_contents;
On 2009-08-12, Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be> wrote: > --_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > It's on Windows > I'd go with notify and a listener written in C using c-client to send emails, but only because I've used those before.
Jasen Betts wrote: > On 2009-08-12, Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be> wrote: > > --_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_ > > Content-Type: text/plain; charset="us-ascii" > > Content-Transfer-Encoding: quoted-printable > > > > It's on Windows > > > > I'd go with notify and a listener written in C using c-client to send > emails, but only because I've used those before. I wouldn't write it in C but rather Perl or Python, but whatever suits your fancy should work (Visual Basic anyone?). The advantages to using a listener program instead of doing it in a trigger or something like that are: - transaction semantics are kept; you don't send an email only to find out your transaction has been rolled back for whatever reason, and then send a second email when the transaction is replayed - you don't block the database system just because your mail server is down - the email can be sent on whatever schedule fits the listener program - the listener client can run elsewhere, not only in the database server - any further external processing can take place at that time, without bothering the database server - other stuff I don't recall ATM -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> wrote: >> > It's on Windows >> I'd go with notify and a listener written in C using c-client to send >> emails, but only because I've used those before. > I wouldn't write it in C but rather Perl or Python, but whatever suits > your fancy should work (Visual Basic anyone?). The advantages to using > a listener program instead of doing it in a trigger or something like > that are: > - transaction semantics are kept; you don't send an email only to find > out your transaction has been rolled back for whatever reason, and then > send a second email when the transaction is replayed > - you don't block the database system just because your mail server is > down > - the email can be sent on whatever schedule fits the listener program > - the listener client can run elsewhere, not only in the database server > - any further external processing can take place at that time, without > bothering the database server > - other stuff I don't recall ATM The main disadvantage in using a listener is that it is your responsibility to make sure that the listener is listening 24/7 - from before the database accepts other connections, through network failures, bugs, etc. - otherwise notifica- tions will be lost. Therefore I find it much more reliable (and easier to program) to copy the relevant data to a table "mailqueue" (or whatever) and then process that queue every other minute. Tim
Tim Landscheidt wrote: > The main disadvantage in using a listener is that it is your > responsibility to make sure that the listener is listening > 24/7 - from before the database accepts other connections, > through network failures, bugs, etc. - otherwise notifica- > tions will be lost. Therefore I find it much more reliable > (and easier to program) to copy the relevant data to a table > "mailqueue" (or whatever) and then process that queue every > other minute. You just have to make sure the listener scans the table for possible events that were missed. Think of notifications as signals to wake up and check for possible work, not data carriers. The mailqueue table should still be there for the data. The only difference between your approach and mine is that you poll every minute instead of sleeping until getting a notification. If your system is going to be receiving notifications fairly frequently, it is probably better to stay with polling. (This is what Skype's replication system does, and Hannu Krossing says "what, are you going to optimize for the time when the server is idle?") -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Wednesday 12 August 2009 6:27:25 am Jan Verheyden wrote: > Hi, > > I got a bit stuck... > I was looking for a solution for option a) > > Maybe I'll first explain the situation a bit more: > > I have one database for patient registration > Another one for image storage > And a third one for book keeping > A patient should be registered first before the images are stored, so if > there is a new line in the second database with an id which does not exist > yet, it has to be notified in the book keeping database. > > Now the questions: > 1) Can I do this with the inner join (tables subject_id from DB1, pat_id > from DB2), there it is two different databases 2) Once it is notified in > the book keeping that is not registered yet, is it best to poll on this > column to send a warning, or use a trigger?? > > Thanks!! > If at all possible, try to move all that information into schema's of one database. As it stands now you have a lot of moving parts to keep track of via external processes. It is possible but you lose transactional support and trust me that turns into a royal pain. -- Adrian Klaver aklaver@comcast.net
tim@tim-landscheidt.de (Tim Landscheidt) writes:
> Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
>>> > It's on Windows
>
>>> I'd go with notify and a listener written in C using c-client to send
>>> emails, but only because I've used those before.
>
>> I wouldn't write it in C but rather Perl or Python, but whatever suits
>> your fancy should work (Visual Basic anyone?). The advantages to using
>> a listener program instead of doing it in a trigger or something like
>> that are:
>
>> - transaction semantics are kept; you don't send an email only to find
>> out your transaction has been rolled back for whatever reason, and then
>> send a second email when the transaction is replayed
>
>> - you don't block the database system just because your mail server is
>> down
>
>> - the email can be sent on whatever schedule fits the listener program
>
>> - the listener client can run elsewhere, not only in the database server
>
>> - any further external processing can take place at that time, without
>> bothering the database server
>
>> - other stuff I don't recall ATM
>
> The main disadvantage in using a listener is that it is your
> responsibility to make sure that the listener is listening
> 24/7 - from before the database accepts other connections,
> through network failures, bugs, etc. - otherwise notifica-
> tions will be lost. Therefore I find it much more reliable
> (and easier to program) to copy the relevant data to a table
> "mailqueue" (or whatever) and then process that queue every
> other minute.
Actually, I don't think there's any real disagreement here...
- The *important* bit is to make sure that the data required to generate the email is queued in the database.
- Whether you poll or use notify/listen is *way* less important.
You could implement the "listener process" a number of ways:
- It could be a "cron" that wakes up every so often to do whatever work is outstanding
- It could be a "polling daemon" that sleeps for a while between iterations.
That seems a little nicer than the "cron" approach in that it eliminates a troublesome scenario, namely the case
wherethere's a lot of work to do (flooded queue?) so that processing takes longer than the polling interval,
leadingto the risk that a second "cron" starts up while the previous one is still working.
- It could be a "listening daemon" that listens for notifications to indicate that work is outstanding
That is a little better than the "polling daemon" in that it doesn't need to wait the full polling period to start
processingnew work.
Any of those three approaches are quite viable, as long as you're
careful to cover scenarios like:- daemon falling over- accidentally starting multiple "queue processors"
--
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
Christopher Browne
"Bother," said Pooh, "Eeyore, ready two photon torpedoes and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"