pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtriggering an external action
4+ messages / 3 participants
[nested] [flat]
* triggering an external action
@ 2005-05-17 18:01 Jay Parker <jbparker@ualr.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Jay Parker @ 2005-05-17 18:01 UTC (permalink / raw)
To: pgsql-sql
I am trying to find the best way for a database trigger to signal a
client process to take an action.
Specifically, I am working on the classic problem of creating and
modifying system accounts based on the updates to a "person registry"
database.
The basic model I'm working with has triggers on my tables of interest
that stick a person's unique ID into a "todo queue" table whenever
modifications are made. The queue is periodically polled by a script
which processes and deletes each "todo" record. The polling script goes
to sleep for gradually increasing periods of time whenever it polls the
queue and finds it empty.
What I want is a trigger on the "todo" table that will "kick" my
processing script to make it wake up and process the queue immediately.
In an Oracle environment, I think I could use a database pipe to
achieve more or less the behavior I'm looking for, but I can't find
anything that does what I want in pgsql at the database level.
I could write a trigger in C or Perl or something that would do
something at the OS level, like send a signal, but when I go down that
path I start having to deal with unix issues like having to elevate to
root privs to send a signal to a process that isn't running as the pgsql
user, etc. It is doable but gets complex quickly.
Is there anything I'm missing at the database level that would help me
process updates in realtime? (And if not, which of the other mailing
lists would be the most appropriate place for me to discuss the best way
to implement an OS-level solution?)
Thanks,
-jbp
--
Jay Parker - UALR Computing Services - Networks Project Manager
jbparker@ualr.edu - http://www.ualr.edu/jbparker - 501-569-3345
But I have promises to keep, And miles to go before I sleep. -Frost
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: triggering an external action
@ 2005-05-17 18:07 Bricklen Anderson <BAnderson@PresiNET.com>
parent: Jay Parker <jbparker@ualr.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Bricklen Anderson @ 2005-05-17 18:07 UTC (permalink / raw)
To: Jay Parker <jbparker@ualr.edu>; +Cc: pgsql-sql
Jay Parker wrote:
> I am trying to find the best way for a database trigger to signal a
> client process to take an action.
>
> Specifically, I am working on the classic problem of creating and
> modifying system accounts based on the updates to a "person registry"
> database.
>
> The basic model I'm working with has triggers on my tables of interest
> that stick a person's unique ID into a "todo queue" table whenever
> modifications are made. The queue is periodically polled by a script
> which processes and deletes each "todo" record. The polling script goes
> to sleep for gradually increasing periods of time whenever it polls the
> queue and finds it empty.
>
> What I want is a trigger on the "todo" table that will "kick" my
> processing script to make it wake up and process the queue immediately.
> In an Oracle environment, I think I could use a database pipe to
> achieve more or less the behavior I'm looking for, but I can't find
> anything that does what I want in pgsql at the database level.
>
> I could write a trigger in C or Perl or something that would do
> something at the OS level, like send a signal, but when I go down that
> path I start having to deal with unix issues like having to elevate to
> root privs to send a signal to a process that isn't running as the pgsql
> user, etc. It is doable but gets complex quickly.
>
> Is there anything I'm missing at the database level that would help me
> process updates in realtime? (And if not, which of the other mailing
> lists would be the most appropriate place for me to discuss the best way
> to implement an OS-level solution?)
>
> Thanks,
>
> -jbp
>
How about LISTEN and NOTIFY, would they work for this?
--
_______________________________
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: triggering an external action
@ 2005-05-17 18:09 Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 4+ messages in thread
From: Magnus Hagander @ 2005-05-17 18:09 UTC (permalink / raw)
To: Jay Parker <jbparker@ualr.edu>; pgsql-sql
>I am trying to find the best way for a database trigger to signal a
>client process to take an action.
>
>Specifically, I am working on the classic problem of creating and
>modifying system accounts based on the updates to a "person registry"
>database.
>
>The basic model I'm working with has triggers on my tables of interest
>that stick a person's unique ID into a "todo queue" table whenever
>modifications are made. The queue is periodically polled by a script
>which processes and deletes each "todo" record. The polling
>script goes
>to sleep for gradually increasing periods of time whenever it
>polls the
>queue and finds it empty.
>
>What I want is a trigger on the "todo" table that will "kick" my
>processing script to make it wake up and process the queue
>immediately.
> In an Oracle environment, I think I could use a database pipe to
>achieve more or less the behavior I'm looking for, but I can't find
>anything that does what I want in pgsql at the database level.
>
>I could write a trigger in C or Perl or something that would do
>something at the OS level, like send a signal, but when I go down that
>path I start having to deal with unix issues like having to elevate to
>root privs to send a signal to a process that isn't running as
>the pgsql
>user, etc. It is doable but gets complex quickly.
>
>Is there anything I'm missing at the database level that would help me
>process updates in realtime? (And if not, which of the other mailing
>lists would be the most appropriate place for me to discuss
>the best way
>to implement an OS-level solution?)
This sounds like a job for LISTEN/NOTIFY:
http://www.postgresql.org/docs/8.0/static/sql-listen.html
http://www.postgresql.org/docs/8.0/static/sql-notify.html
//Magnus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: triggering an external action
@ 2005-05-17 18:20 Jay Parker <jbparker@ualr.edu>
parent: Bricklen Anderson <BAnderson@PresiNET.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jay Parker @ 2005-05-17 18:20 UTC (permalink / raw)
To: pgsql-sql
On 05/17/2005 01:07 PM, Bricklen Anderson wrote:
> How about LISTEN and NOTIFY, would they work for this?
Yes, that is precisely what I need... and what I have somehow overlooked
during at least a dozen passes through the docs. Sigh.
Thanks for your help,
-jbp
--
Jay Parker - UALR Computing Services - Networks Project Manager
jbparker@ualr.edu - http://www.ualr.edu/jbparker - 501-569-3345
But I have promises to keep, And miles to go before I sleep. -Frost
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-05-17 18:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-17 18:01 triggering an external action Jay Parker <jbparker@ualr.edu>
2005-05-17 18:07 ` Bricklen Anderson <BAnderson@PresiNET.com>
2005-05-17 18:20 ` Jay Parker <jbparker@ualr.edu>
2005-05-17 18:09 Re: triggering an external action Magnus Hagander <mha@sollentuna.net>
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