SEO

I would like to write a trigger or function that spawns a forked
process so that the transaction is considered 'complete' to the client,
but continues to perform more work. I've been looking for examples to
steal^H^H^H^H^H learn from but have only found someone asking pretty
much the same question:

http://archives.postgresql.org/pgsql...2/msg01187.php

Has anyone done anything like this?

Cheers,

Chris


--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Christopher Murtagh <christopher.murtagh@mcgill.ca> writes:
> I would like to write a trigger or function that spawns a forked
> process so that the transaction is considered 'complete' to the client,
> but continues to perform more work.

It's not very clear what you are hoping to have the forked process do,
but if it's supposed to be another backend, forget it --- won't work.
See this thread:
http://archives.postgresql.org/pgsql...4/msg00329.php

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Mon, 2005-05-09 at 15:38 -0400, Tom Lane wrote:
> Christopher Murtagh <christopher.murtagh@mcgill.ca> writes:
> > I would like to write a trigger or function that spawns a forked
> > process so that the transaction is considered 'complete' to the client,
> > but continues to perform more work.
>
> It's not very clear what you are hoping to have the forked process do,
> but if it's supposed to be another backend, forget it --- won't work.

No, I don't want the trigger to do any db stuff at all. Basically, I've
got a content management system that is going to be split across a
cluster. Upon an update submission from one of them, I want to replicate
across the others (which can happen in pseudo real time). So, basically
the DB is the master, once it makes an update, it will spawn a process
to the syncronization (all of this code is written). I just don't want
the initial update process to wait for replication to finish (which is
only a second or two under normal load). I could write a daemon that
would sit an listen to these replication requests, but that just seems
to be more complex than I need.

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Christopher Murtagh <christopher.murtagh@mcgill.ca> writes:

> No, I don't want the trigger to do any db stuff at all. Basically, I've
> got a content management system that is going to be split across a
> cluster. Upon an update submission from one of them, I want to replicate
> across the others (which can happen in pseudo real time). So, basically
> the DB is the master, once it makes an update, it will spawn a process
> to the syncronization (all of this code is written). I just don't want
> the initial update process to wait for replication to finish (which is
> only a second or two under normal load). I could write a daemon that
> would sit an listen to these replication requests, but that just seems
> to be more complex than I need.

Why not have a client connection LISTENing and doing the
synchronization, and have the trigger use NOTIFY?

Or, you could have the trigger write to a table, and have another
client periodically scanning the table for new sync events.

Either one of those would be simpler and more robust than fork()ing
inside the backend.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Douglas McNaught <doug@mcnaught.org> writes:
> Why not have a client connection LISTENing and doing the
> synchronization, and have the trigger use NOTIFY?
> Or, you could have the trigger write to a table, and have another
> client periodically scanning the table for new sync events.
> Either one of those would be simpler and more robust than fork()ing
> inside the backend.

.... not to mention it would avoid the risk of propagating
not-yet-committed changes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Mon, 2005-05-09 at 17:01 -0400, Douglas McNaught wrote:
> Why not have a client connection LISTENing and doing the
> synchronization, and have the trigger use NOTIFY?
>
> Or, you could have the trigger write to a table, and have another
> client periodically scanning the table for new sync events.
>
> Either one of those would be simpler and more robust than fork()ing
> inside the backend.

How is writing a daemon simpler than using something that could be done
within Postgres? Forking is something that should be natural to Unix
systems, I shouldn't need to write another application to do this. I
don't see how a daemon would necessarily be more robust either.

Cheers,

Chris


--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Mon, 2005-05-09 at 17:07 -0400, Tom Lane wrote:
> Douglas McNaught <doug@mcnaught.org> writes:
> > Why not have a client connection LISTENing and doing the
> > synchronization, and have the trigger use NOTIFY?
> > Or, you could have the trigger write to a table, and have another
> > client periodically scanning the table for new sync events.
> > Either one of those would be simpler and more robust than fork()ing
> > inside the backend.
>
> ... not to mention it would avoid the risk of propagating
> not-yet-committed changes.

How's that? If I can notify a daemon that the change is committed, then
why couldn't I write a forking plperl function that executes when the
transaction is done? How is one riskier than the other? Is there
something obvious I'm missing here?

Cheers,

Chris


--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Christopher Murtagh <christopher.murtagh@mcgill.ca> writes:

> On Mon, 2005-05-09 at 17:01 -0400, Douglas McNaught wrote:
>> Why not have a client connection LISTENing and doing the
>> synchronization, and have the trigger use NOTIFY?
>>
>> Or, you could have the trigger write to a table, and have another
>> client periodically scanning the table for new sync events.
>>
>> Either one of those would be simpler and more robust than fork()ing
>> inside the backend.
>
> How is writing a daemon simpler than using something that could be done
> within Postgres? Forking is something that should be natural to Unix
> systems, I shouldn't need to write another application to do this. I
> don't see how a daemon would necessarily be more robust either.

Why do random code surgery on the backend, which needs to be utterly
robust and stable, when client-level solutions are just as easy and
much less dangerous?

It's kind of akin to the Linux principle of "don't do in the kernel
what you can do in userspace."

That's my philosophy, anyway.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Christopher Murtagh <christopher.murtagh@mcgill.ca> writes:
> On Mon, 2005-05-09 at 17:07 -0400, Tom Lane wrote:
>> ... not to mention it would avoid the risk of propagating
>> not-yet-committed changes.

> How's that? If I can notify a daemon that the change is committed, then
> why couldn't I write a forking plperl function that executes when the
> transaction is done? How is one riskier than the other? Is there
> something obvious I'm missing here?

Yes: the mechanisms that are being suggested to you already exist.
There is not, AND NEVER WILL BE, any mechanism to invoke random
user-defined functions during the post-commit sequence. That code
sequence cannot afford to do anything that will potentially incur
errors.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Mon, May 09, 2005 at 09:07:40PM -0400, Christopher Murtagh wrote:
> On Mon, 2005-05-09 at 17:01 -0400, Douglas McNaught wrote:
> > Why not have a client connection LISTENing and doing the
> > synchronization, and have the trigger use NOTIFY?
> >
> > Or, you could have the trigger write to a table, and have another
> > client periodically scanning the table for new sync events.
> >
> > Either one of those would be simpler and more robust than fork()ing
> > inside the backend.
>
> How is writing a daemon simpler than using something that could be done
> within Postgres? Forking is something that should be natural to Unix
> systems, I shouldn't need to write another application to do this. I
> don't see how a daemon would necessarily be more robust either.

Well, LISTEN and NOTIFY are built into PostgreSQL
(http://www.postgresql.org/docs/8.0/i...l-notify.html). If the
processes that you're trying to notify of the changes are connected to
the database then this might be the easiest way to do what you're
looking for. Setting up some form of replication, such as Slony, also
comes to mind. But it's impossible to really make a recommendation
without having a better idea of what you're doing.

BTW, my understanding is that it's pretty easy to write a daemon in
perl, and there are examples of how to do this floating around.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend