Thread: NOTIFY/LISTEN on server, asynchronous processing
Hi all, I'm writing seeking help for making asynchronous & decoupled processes run on a
Postgres server.
Here's my current hairbraned workingis:
1. Create an table "async_process" and attach a trigger after.
2. Establish a dblink to localhost.
3. dblink_send_query("update async_process set counter = counter + 1;") from other sprocs
3. Designated processing hanging off this "async_process" table now runs.
All I'm doing is using a table, to create a trigger, that can be run asynchronously.
There's at least two things gross about this strategy:
1. A "async_process" table exists only because I need a trigger that can be updated at will.
2. Having to dblink to oneself to run a query from inside the database asynchronously.
Postgres has a capability for doing async work: NOTIFY/LISTEN. I'd like to verify first,
LISTEN is only for clients, correct? There's no way I can define something resident on
postgres itself that will LISTEN, that can be targetted by notifications?
Does anyone have suggestions for decoupling work done on a server, for breaking up a task
into multiple asychronous pieces? I believe I've described 1. a viable if ugly means of
doing so, and 2. limitations in the primary asynchronous toolsuite of Postgres, and am
looking for ways to make more progress.
Regards,
-rektide
If I'm understanding you correctly, you want a job queue. This involves polling and retrieving jobs to work on them. The polling can be assisted by listen/notify so workers don'tpoll unnecessarily. The retrieving has to be done intelligently to avoid concurrency issues. Kiriakos Georgiou http://mockbites.com/about/email On Dec 11, 2012, at 2:29 PM, rektide <rektide@voodoowarez.com> wrote: > Hi all, I'm writing seeking help for making asynchronous & decoupled processes run on a > Postgres server. > > Here's my current hairbraned workingis: > 1. Create an table "async_process" and attach a trigger after. > 2. Establish a dblink to localhost. > 3. dblink_send_query("update async_process set counter = counter + 1;") from other sprocs > 3. Designated processing hanging off this "async_process" table now runs. > > All I'm doing is using a table, to create a trigger, that can be run asynchronously. > > There's at least two things gross about this strategy: > 1. A "async_process" table exists only because I need a trigger that can be updated at will. > 2. Having to dblink to oneself to run a query from inside the database asynchronously. > > Postgres has a capability for doing async work: NOTIFY/LISTEN. I'd like to verify first, > LISTEN is only for clients, correct? There's no way I can define something resident on > postgres itself that will LISTEN, that can be targetted by notifications? > > Does anyone have suggestions for decoupling work done on a server, for breaking up a task > into multiple asychronous pieces? I believe I've described 1. a viable if ugly means of > doing so, and 2. limitations in the primary asynchronous toolsuite of Postgres, and am > looking for ways to make more progress. > > Regards, > -rektide > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Hi, On Tue, Dec 11, 2012 at 11:29 AM, rektide <rektide@voodoowarez.com> wrote: > Does anyone have suggestions for decoupling work done on a server, for breaking up a task > into multiple asychronous pieces? I believe I've described 1. a viable if ugly means of > doing so, and 2. limitations in the primary asynchronous toolsuite of Postgres, and am > looking for ways to make more progress. Use PgQ (http://wiki.postgresql.org/wiki/PGQ_Tutorial) for this purpose. It is simple to implement solution and it will allow you to preserve your queries between server restarts. -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
Hiya! PgQ looks like cool technology, but it still violates the #1 thing I'm looking for, which is a postgres-only solution. I don't see any reason why I'd want an external daemon ticking the system over, and I'm hoping perhaps there's some way to coax Postgres itself into doing this asychronous job processing, akin to the dblink technique mentioned twice today, but not ugly. NOTIFY/LISTEN again comes spiritually close, but is still restrained to only being a useful construct for database clients; there's no resident processes that can react to database events, and that's kind of a shame IMO. -rektide On Tue, Dec 11, 2012 at 12:58:21PM -0800, Sergey Konoplev wrote: > Hi, > > On Tue, Dec 11, 2012 at 11:29 AM, rektide <rektide@voodoowarez.com> wrote: > > Does anyone have suggestions for decoupling work done on a server, for breaking up a task > > into multiple asychronous pieces? I believe I've described 1. a viable if ugly means of > > doing so, and 2. limitations in the primary asynchronous toolsuite of Postgres, and am > > looking for ways to make more progress. > > Use PgQ (http://wiki.postgresql.org/wiki/PGQ_Tutorial) for this > purpose. It is simple to implement solution and it will allow you to > preserve your queries between server restarts. > > -- > Sergey Konoplev > Database and Software Architect > http://www.linkedin.com/in/grayhemp > > Phones: > USA +1 415 867 9984 > Russia, Moscow +7 901 903 0499 > Russia, Krasnodar +7 988 888 1979 > > Skype: gray-hemp > Jabber: gray.ru@gmail.com
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
body p { margin-bottom: 0cm; margin-top: 0pt; }
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" bgcolor="#FFFFFF"
text="#000000">
I have experience with LIsten/Notify
and as you mention the only problem is that I need a server side
client that calls the listen and then calls a db function on the
notify.
One thought I had to do this completely in the database is to
right notify code in plpython similar to
<meta http-equiv="content-type" content="text/html;
charset=ISO-8859-1">
<a
href="http://postgresql.1045698.n5.nabble.com/LISTEN-NOTIFY-and-python-td1878518.html">http://postgresql.1045698.n5.nabble.com/LISTEN-NOTIFY-and-python-td1878518.html
Then I could call the function on db startup and have it call the
appropriate function whenever notify is called. It would probably
be easier to write the function in C, but I don't have experience
in copiling a C function that will run in the database.
This would be a completely postgresql solution.
Sim
On 12/11/2012 09:29 PM, rektide wrote:
<blockquote cite="mid:20121211192938.GU23164@eldergods.com"
type="cite">
Hi all, I'm writing seeking help for making asynchronous & decoupled processes run on a
Postgres server.
Here's my current hairbraned workingis:
1. Create an table "async_process" and attach a trigger after.
2. Establish a dblink to localhost.
3. dblink_send_query("update async_process set counter = counter + 1;") from other sprocs
3. Designated processing hanging off this "async_process" table now runs.
All I'm doing is using a table, to create a trigger, that can be run asynchronously.
There's at least two things gross about this strategy:
1. A "async_process" table exists only because I need a trigger that can be updated at will.
2. Having to dblink to oneself to run a query from inside the database asynchronously.
Postgres has a capability for doing async work: NOTIFY/LISTEN. I'd like to verify first,
LISTEN is only for clients, correct? There's no way I can define something resident on
postgres itself that will LISTEN, that can be targetted by notifications?
Does anyone have suggestions for decoupling work done on a server, for breaking up a task
into multiple asychronous pieces? I believe I've described 1. a viable if ugly means of
doing so, and 2. limitations in the primary asynchronous toolsuite of Postgres, and am
looking for ways to make more progress.
Regards,
-rektide
On Tue, Dec 11, 2012 at 1:48 PM, rektide <rektide@voodoowarez.com> wrote: > PgQ looks like cool technology, but it still violates the #1 thing I'm looking for, which is > a postgres-only solution. I don't see any reason why I'd want an external daemon ticking > the system over, and I'm hoping perhaps there's some way to coax Postgres itself into doing > this asychronous job processing, akin to the dblink technique mentioned twice today, but not > ugly What is wrong with external solutions for you? > NOTIFY/LISTEN again comes spiritually close, but is still restrained to only being a useful > construct for database clients; there's no resident processes that can react to database > events, and that's kind of a shame IMO. Also do not forget that if nobody LISTENs you will loose such events. > > -rektide > > On Tue, Dec 11, 2012 at 12:58:21PM -0800, Sergey Konoplev wrote: >> Hi, >> >> On Tue, Dec 11, 2012 at 11:29 AM, rektide <rektide@voodoowarez.com> wrote: >> > Does anyone have suggestions for decoupling work done on a server, for breaking up a task >> > into multiple asychronous pieces? I believe I've described 1. a viable if ugly means of >> > doing so, and 2. limitations in the primary asynchronous toolsuite of Postgres, and am >> > looking for ways to make more progress. >> >> Use PgQ (http://wiki.postgresql.org/wiki/PGQ_Tutorial) for this >> purpose. It is simple to implement solution and it will allow you to >> preserve your queries between server restarts. >> >> -- >> Sergey Konoplev >> Database and Software Architect >> http://www.linkedin.com/in/grayhemp >> >> Phones: >> USA +1 415 867 9984 >> Russia, Moscow +7 901 903 0499 >> Russia, Krasnodar +7 988 888 1979 >> >> Skype: gray-hemp >> Jabber: gray.ru@gmail.com -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray.ru@gmail.com