how to write/setup a C trigger function in a background worker

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Bill Moran <wmoran@potentialtech.com>

--
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 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers