Re: [SQL] mail alert
t...@tim-landscheidt.de (Tim Landscheidt) writes: Alvaro Herrera alvhe...@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 where there's a lot of work to do (flooded queue?) so that processing takes longer than the polling interval, leading to 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 processing new 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
On 2009-08-12, Jan Verheyden jan.verhey...@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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
Jasen Betts wrote: On 2009-08-12, Jan Verheyden jan.verhey...@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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
Alvaro Herrera alvhe...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
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 akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
- Original Message - *From:* Jan Verheyden jan.verhey...@uz.kuleuven.ac.be *To:* 'pgsql-sql@postgresql.org' %27pgsql-...@postgresql.org%27 *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. 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/
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 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!! Jan -Original Message- From: Rob Sargent [mailto:robjsarg...@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 m...@work.com /tmp/alert_data_file -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
It's on Windows From: ramasubramanian [mailto:ramasubramania...@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 Verheydenmailto:jan.verhey...@uz.kuleuven.ac.be To: 'pgsql-sql@postgresql.org'mailto:'pgsql-sql@postgresql.org' 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
Re: [SQL] mail alert
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!! Jan -Original Message- From: Rob Sargent [mailto:robjsarg...@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 m...@work.com /tmp/alert_data_file -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
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:robjsarg...@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 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!! Jan -Original Message- From: Rob Sargent [mailto:robjsarg...@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 m...@work.com /tmp/alert_data_file -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[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
Re: [SQL] mail alert
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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 m...@work.com /tmp/alert_data_file -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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 To: 'pgsql-sql@postgresql.org' 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
