Обсуждение: how to write/setup a C trigger function in a background worker
I would like to execute a trigger function (written in C) in one of my background workers. Didn't figure out how to do that not even if it's possible. Currently my trigger function is called, but in the "server-process" connected to the client who does the "insert into ..." . Any hint/help is welcome. Jacques K.
On Wed, Aug 19, 2015 at 05:37:31PM +0200, jacques klein wrote: > I would like to execute a trigger function (written in C) in one of my > background workers. > > Didn't figure out how to do that not even if it's possible. You can write your trigger function in such a way as not to do the usual check for trigger context, but it might be better to write two functions, one with the trigger stuff in it, the other, which it calls, for whatever action you actually want to trigger, and call that second in your background worker. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Well, sorry David, I don't understand what you mean, let me explain what I want to do: in short, IPC between "background workers". I am trying to transform my app. from a multi-threaded C SQL-client into some "background workers", execution speed beeing the goal (avoid network io). Worker start/stopping is nicely solved by server start/stop, but I have also to do some messaging/notifying between my worker processes, and would like to use a Postgres based solution instead of the usual unix or network ipc, or course by avoiding polling (tables acting as message queues). On Wed, 2015-08-19 at 10:01 -0700, David Fetter wrote: > On Wed, Aug 19, 2015 at 05:37:31PM +0200, jacques klein wrote: > > I would like to execute a trigger function (written in C) in one of my > > background workers. > > > > Didn't figure out how to do that not even if it's possible. > > You can write your trigger function in such a way as not to do the > usual check for trigger context, but it might be better to write two > functions, one with the trigger stuff in it, the other, which it > calls, for whatever action you actually want to trigger, and call that > second in your background worker. > > Cheers, > David.
On Wed, 19 Aug 2015 19:45:47 +0200 jacques klein <jacques.klei@googlemail.com> wrote: > Well, sorry David, I don't understand what you mean, > > let me explain what I want to do: in short, IPC between "background > workers". > > I am trying to transform my app. from a multi-threaded C SQL-client into > some "background workers", execution speed beeing the goal (avoid > network io). > Worker start/stopping is nicely solved by server start/stop, but I have > also to do some messaging/notifying between my worker processes, and > would like to use a Postgres based solution instead of the usual unix or > network ipc, or course by avoiding polling (tables acting as message > queues). I think what David is saying, and what I would suggest, is the following: * It's not possible to have a trigger execute in the background * Create a background job that runs perpetually and listens for notification that it has work to do (i.e. it sleeps untilnotified) * Notify the job from the trigger > On Wed, 2015-08-19 at 10:01 -0700, David Fetter wrote: > > On Wed, Aug 19, 2015 at 05:37:31PM +0200, jacques klein wrote: > > > I would like to execute a trigger function (written in C) in one of my > > > background workers. > > > > > > Didn't figure out how to do that not even if it's possible. > > > > You can write your trigger function in such a way as not to do the > > usual check for trigger context, but it might be better to write two > > functions, one with the trigger stuff in it, the other, which it > > calls, for whatever action you actually want to trigger, and call that > > second in your background worker. > > > > Cheers, > > David. > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bill Moran <wmoran@potentialtech.com>
Bill Moran wrote: > On Wed, 19 Aug 2015 19:45:47 +0200 > jacques klein <jacques.klei@googlemail.com> wrote: > > > Well, sorry David, I don't understand what you mean, > > > > let me explain what I want to do: in short, IPC between "background > > workers". > > > > I am trying to transform my app. from a multi-threaded C SQL-client into > > some "background workers", execution speed beeing the goal (avoid > > network io). > > Worker start/stopping is nicely solved by server start/stop, but I have > > also to do some messaging/notifying between my worker processes, and > > would like to use a Postgres based solution instead of the usual unix or > > network ipc, or course by avoiding polling (tables acting as message > > queues). > > I think what David is saying, and what I would suggest, is the > following: > > * It's not possible to have a trigger execute in the background > * Create a background job that runs perpetually and listens for > notification that it has work to do (i.e. it sleeps until notified) > * Notify the job from the trigger You could use shm_mq for IPC between backend (trigger) and bgworker, but what should the backend do when the bgworker is not running for whatever reason? One option is to curl up and die of course (i.e. abort the transaction that fires the action). Also consider what happens if your backend sends the notify and commits, and before the bgworker does its stuff the system crashes. You will be happy to have things queued in a database table ... -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Ok, think I got it, I can use LISTEN and NOTIFY to do my IPC stuf, will just have to see if it's possible to listen in a worker, with a permanent server connection, I guess. (as I remember, I already did a "listener" 10 years ago in a C client app.) Thanks, Jaquest K. On Wed, 2015-08-19 at 16:54 -0400, Bill Moran wrote: > On Wed, 19 Aug 2015 19:45:47 +0200 > jacques klein <jacques.klei@googlemail.com> wrote: > > > Well, sorry David, I don't understand what you mean, > > > > let me explain what I want to do: in short, IPC between "background > > workers". > > > > I am trying to transform my app. from a multi-threaded C SQL-client into > > some "background workers", execution speed beeing the goal (avoid > > network io). > > Worker start/stopping is nicely solved by server start/stop, but I have > > also to do some messaging/notifying between my worker processes, and > > would like to use a Postgres based solution instead of the usual unix or > > network ipc, or course by avoiding polling (tables acting as message > > queues). > > I think what David is saying, and what I would suggest, is the > following: > > * It's not possible to have a trigger execute in the background > * Create a background job that runs perpetually and listens for > notification that it has work to do (i.e. it sleeps until notified) > * Notify the job from the trigger > > > On Wed, 2015-08-19 at 10:01 -0700, David Fetter wrote: > > > On Wed, Aug 19, 2015 at 05:37:31PM +0200, jacques klein wrote: > > > > I would like to execute a trigger function (written in C) in one of my > > > > background workers. > > > > > > > > Didn't figure out how to do that not even if it's possible. > > > > > > You can write your trigger function in such a way as not to do the > > > usual check for trigger context, but it might be better to write two > > > functions, one with the trigger stuff in it, the other, which it > > > calls, for whatever action you actually want to trigger, and call that > > > second in your background worker. > > > > > > Cheers, > > > David. > > > > > > > > > > -- > > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers > >