pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmail alert
16+ messages / 10 participants
[nested] [flat]
* mail alert
@ 2009-08-11 13:01 Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-11 22:35 ` Re: mail alert Denis BUCHER <dbucherml@hsolutions.ch>
2009-08-12 04:01 ` Re: mail alert ramasubramanian <ramasubramanian.g@renaissance-it.com>
2009-08-13 12:08 ` Re: mail alert Jasen Betts <jasen@xnet.co.nz>
0 siblings, 3 replies; 16+ messages in thread
From: Jan Verheyden @ 2009-08-11 13:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
@ 2009-08-11 22:35 ` Denis BUCHER <dbucherml@hsolutions.ch>
2009-08-12 01:38 ` Re: mail alert Rob Sargent <robjsargent@gmail.com>
2 siblings, 1 reply; 16+ messages in thread
From: Denis BUCHER @ 2009-08-11 22:35 UTC (permalink / raw)
To: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-11 22:35 ` Re: mail alert Denis BUCHER <dbucherml@hsolutions.ch>
@ 2009-08-12 01:38 ` Rob Sargent <robjsargent@gmail.com>
2009-08-12 13:27 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
0 siblings, 1 reply; 16+ messages in thread
From: Rob Sargent @ 2009-08-12 01:38 UTC (permalink / raw)
To: Denis BUCHER <dbucherml@hsolutions.ch>; +Cc: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-11 22:35 ` Re: mail alert Denis BUCHER <dbucherml@hsolutions.ch>
2009-08-12 01:38 ` Re: mail alert Rob Sargent <robjsargent@gmail.com>
@ 2009-08-12 13:27 ` Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 14:01 ` Re: mail alert Rob Sargent <robjsargent@gmail.com>
2009-08-13 23:30 ` Re: mail alert Adrian Klaver <aklaver@comcast.net>
0 siblings, 2 replies; 16+ messages in thread
From: Jan Verheyden @ 2009-08-12 13:27 UTC (permalink / raw)
To: 'Rob Sargent' <robjsargent@gmail.com>; 'Denis BUCHER' <dbucherml@hsolutions.ch>; +Cc: pgsql-sql
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: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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-11 22:35 ` Re: mail alert Denis BUCHER <dbucherml@hsolutions.ch>
2009-08-12 01:38 ` Re: mail alert Rob Sargent <robjsargent@gmail.com>
2009-08-12 13:27 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
@ 2009-08-12 14:01 ` Rob Sargent <robjsargent@gmail.com>
2009-08-12 14:44 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
1 sibling, 1 reply; 16+ messages in thread
From: Rob Sargent @ 2009-08-12 14:01 UTC (permalink / raw)
To: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>; +Cc: Denis BUCHER <dbucherml@hsolutions.ch>; pgsql-sql
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: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
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-11 22:35 ` Re: mail alert Denis BUCHER <dbucherml@hsolutions.ch>
2009-08-12 01:38 ` Re: mail alert Rob Sargent <robjsargent@gmail.com>
2009-08-12 13:27 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 14:01 ` Re: mail alert Rob Sargent <robjsargent@gmail.com>
@ 2009-08-12 14:44 ` Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
0 siblings, 0 replies; 16+ messages in thread
From: Jan Verheyden @ 2009-08-12 14:44 UTC (permalink / raw)
To: 'Rob Sargent' <robjsargent@gmail.com>; +Cc: 'Denis BUCHER' <dbucherml@hsolutions.ch>; pgsql-sql
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 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: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
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-11 22:35 ` Re: mail alert Denis BUCHER <dbucherml@hsolutions.ch>
2009-08-12 01:38 ` Re: mail alert Rob Sargent <robjsargent@gmail.com>
2009-08-12 13:27 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
@ 2009-08-13 23:30 ` Adrian Klaver <aklaver@comcast.net>
1 sibling, 0 replies; 16+ messages in thread
From: Adrian Klaver @ 2009-08-13 23:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
@ 2009-08-12 04:01 ` ramasubramanian <ramasubramanian.g@renaissance-it.com>
2009-08-12 06:15 ` Re: mail alert Shoaib Mir <shoaibmir@gmail.com>
2009-08-12 07:29 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2 siblings, 2 replies; 16+ messages in thread
From: ramasubramanian @ 2009-08-12 04:01 UTC (permalink / raw)
To: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 04:01 ` Re: mail alert ramasubramanian <ramasubramanian.g@renaissance-it.com>
@ 2009-08-12 06:15 ` Shoaib Mir <shoaibmir@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Shoaib Mir @ 2009-08-12 06:15 UTC (permalink / raw)
To: ramasubramanian <ramasubramanian.g@renaissance-it.com>; +Cc: Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>; pgsql-sql
> ----- Original Message -----
> *From:* Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
> *To:* 'pgsql-sql@postgresql.org' <%27pgsql-sql@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/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 04:01 ` Re: mail alert ramasubramanian <ramasubramanian.g@renaissance-it.com>
@ 2009-08-12 07:29 ` Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-13 12:16 ` Re: mail alert Jasen Betts <jasen@xnet.co.nz>
1 sibling, 1 reply; 16+ messages in thread
From: Jan Verheyden @ 2009-08-12 07:29 UTC (permalink / raw)
To: 'ramasubramanian' <ramasubramanian.g@renaissance-it.com>; pgsql-sql
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<mailto:jan.verheyden@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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 04:01 ` Re: mail alert ramasubramanian <ramasubramanian.g@renaissance-it.com>
2009-08-12 07:29 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
@ 2009-08-13 12:16 ` Jasen Betts <jasen@xnet.co.nz>
2009-08-13 21:54 ` Re: mail alert Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 16+ messages in thread
From: Jasen Betts @ 2009-08-13 12:16 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 04:01 ` Re: mail alert ramasubramanian <ramasubramanian.g@renaissance-it.com>
2009-08-12 07:29 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-13 12:16 ` Re: mail alert Jasen Betts <jasen@xnet.co.nz>
@ 2009-08-13 21:54 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-08-13 22:24 ` Re: mail alert Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 1 reply; 16+ messages in thread
From: Alvaro Herrera @ 2009-08-13 21:54 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 04:01 ` Re: mail alert ramasubramanian <ramasubramanian.g@renaissance-it.com>
2009-08-12 07:29 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-13 12:16 ` Re: mail alert Jasen Betts <jasen@xnet.co.nz>
2009-08-13 21:54 ` Re: mail alert Alvaro Herrera <alvherre@commandprompt.com>
@ 2009-08-13 22:24 ` Tim Landscheidt <tim@tim-landscheidt.de>
2009-08-13 22:36 ` Re: mail alert Alvaro Herrera <alvherre@commandprompt.com>
2009-08-14 15:18 ` Re: mail alert Christopher Browne <cbbrowne@ca.afilias.info>
0 siblings, 2 replies; 16+ messages in thread
From: Tim Landscheidt @ 2009-08-13 22:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 04:01 ` Re: mail alert ramasubramanian <ramasubramanian.g@renaissance-it.com>
2009-08-12 07:29 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-13 12:16 ` Re: mail alert Jasen Betts <jasen@xnet.co.nz>
2009-08-13 21:54 ` Re: mail alert Alvaro Herrera <alvherre@commandprompt.com>
2009-08-13 22:24 ` Re: mail alert Tim Landscheidt <tim@tim-landscheidt.de>
@ 2009-08-13 22:36 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 16+ messages in thread
From: Alvaro Herrera @ 2009-08-13 22:36 UTC (permalink / raw)
To: Tim Landscheidt <tim@tim-landscheidt.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 04:01 ` Re: mail alert ramasubramanian <ramasubramanian.g@renaissance-it.com>
2009-08-12 07:29 ` Re: mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-13 12:16 ` Re: mail alert Jasen Betts <jasen@xnet.co.nz>
2009-08-13 21:54 ` Re: mail alert Alvaro Herrera <alvherre@commandprompt.com>
2009-08-13 22:24 ` Re: mail alert Tim Landscheidt <tim@tim-landscheidt.de>
@ 2009-08-14 15:18 ` Christopher Browne <cbbrowne@ca.afilias.info>
1 sibling, 0 replies; 16+ messages in thread
From: Christopher Browne @ 2009-08-14 15:18 UTC (permalink / raw)
To: pgsql-sql
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 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"
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: mail alert
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
@ 2009-08-13 12:08 ` Jasen Betts <jasen@xnet.co.nz>
2 siblings, 0 replies; 16+ messages in thread
From: Jasen Betts @ 2009-08-13 12:08 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2009-08-14 15:18 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-11 13:01 mail alert Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-11 22:35 ` Denis BUCHER <dbucherml@hsolutions.ch>
2009-08-12 01:38 ` Rob Sargent <robjsargent@gmail.com>
2009-08-12 13:27 ` Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-12 14:01 ` Rob Sargent <robjsargent@gmail.com>
2009-08-12 14:44 ` Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-13 23:30 ` Adrian Klaver <aklaver@comcast.net>
2009-08-12 04:01 ` ramasubramanian <ramasubramanian.g@renaissance-it.com>
2009-08-12 06:15 ` Shoaib Mir <shoaibmir@gmail.com>
2009-08-12 07:29 ` Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-13 12:16 ` Jasen Betts <jasen@xnet.co.nz>
2009-08-13 21:54 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-08-13 22:24 ` Tim Landscheidt <tim@tim-landscheidt.de>
2009-08-13 22:36 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-08-14 15:18 ` Christopher Browne <cbbrowne@ca.afilias.info>
2009-08-13 12:08 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox