Re: background triggers? - Mailing list pgsql-general

I'd like to propose a 'syntax/semantics' of such trigger:

Triggers normally execute inside of a transaction.

A COMMIT within a trigger could mean: "do a fork: fork-1) return to the
main and schedule COMMIT there, fork-2) continue in bacground".

From the perspective of my earlier applications, it would be desired to
have an argument for such COMMIT - a label, which is a *global* database
object (may be just a semaphore), and is:
1) Assuming semaphore implementation - semaphore should be tested and
fail if already asserted, NOT tesed an block. FORK should be initiated
only if semaphore test succeeds.
2) the execution of procedure within fork-2 (meaning, after semaphore
assertion succeeds) should be posponed until caller actually COMMITS.
3) On EXIT, fork-2 deasserts semaphore.
4) in the simplest case, the semaphore can be defined on the trigger
function name itself, and consequently, the 'label' for the COMMIT
wouldn't be necesary?


-R

On Thu, 2006-05-25 at 08:50 +0200, Sim Zacks wrote:
> The question is not how it is implemented, the question is what does the
> end user have to do to accomplish his requirements. If I have to write
> my own daemon, then I have to debug it and maintain it, write a script
> so it automatically turns on when the machine is rebooted. If I have
> multiple environments, for development, testing and production, I will
> need to modify it to handle this. If I move the database to a new server
> this script has to be in the action items to be moved along with its
> accompanying start-up scripts. If I decide to recover a backup to test
> something (something I do on an occasional basis), I will need to
> consider my daemon as well. It is basically an outside process that has
> a lot of overhead (not necessarily computing overhead).
>
> If the database supported background triggers, it might be implemented
> by a daemon or by the Listen/Notify framework, but I really couldn't
> care less. It is part of the database. I only need to write the function
>   and the trigger code with an option to run this in the background. No
> matter what I do now, or how many environments I have, the database
> handles it all. If I backup my database and install it on another
> server, my process is still intact.
>
> The reason why this should be part of the database engine and not
> another client application is because what you want is a trigger. All
> you want is when a table is updated that another function should run,
> you don't need any outside processing. The Listen/Notify framework is
> needed for a case where you would like non-database actions to take
> place. Your client application can then go and do what it needs to and
> then comes back and can tell the database that it is done.
>
> Chris Browne wrote:
>
> > Well, you *clearly* need to have some sort of "daemon" running in
> > order to do this.
> >
> > I expect it will in effect be a LISTEN process that waits for clients
> > to submit NOTIFY requests.
> >
> > Even if you don't actually choose to use NOTIFY/LISTEN, per se, you'll
> > doubtless wind up creating an ad hoc, informally-specified
> > implementation of part of it...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org