Re: background triggers?

On Thu, 2006-05-25 at 10:21 +0200, Dawid Kuroczko wrote:
> On 5/25/06, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote:
> > 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".
>
> I don't think fork(2)ing a running backed is a good idea, probably it would
> end up with major data corruption. You want to call fork(2) in your
> application. Something like: "if (fork()==0) { reestablish connection, issue
> trigger-code on the database } else { gracefully return };"

I'm not that fluent in postgresql backend programming. But further on,
you write a suggestion of a trigger skeleton, which should be feasable -
so if such trigger (user level function) is feasable, may be it could be
implemented by backend engine, too.... as if it were a syntax shortcut
(using wining trigger COMMIT keyword) into the trigger implementation
you suggest at the end of your response below?

> Hmm, I've got a feeling its something like "I don't feel like coding it in
> application, so it would be better if community changed the backend
> to do it". :) However what you propose i 1,2,3,4 points is somewhat

:) YES!!!

> similar to already existing 2PC (2-phase commit), which PostgreSQL
> implements. Probably not what you want, but should be valuable to
> know, I guess.

May be. Currnetly I wouldn't know - I never used 2PC. When I've learned
about 2PC, I though 2PC is for occasions when application can figure out
how to re-run a transaction in some other way when 'primary' way fails
(and rolls-back). The alternative way might re-use parcial work acheved
by the original path, up to the checkpoint.

I never thought this can be used for a *disconnedted*, forked additional
instance of a back-end process.

I might have been wrong - I must get back to books.

But the goal here is not to re-run a transaction some other way, but to
cut the transaction short, and do tasks which don't need transaction
braces, outside of a transaction.

To run part of the trigger ourside of a transaction. Thusly make the
transaction commit sooner.

I don't think 2PC gives any help here.

> If your trigger would call COMMIT, the referential integrity triggers would

I didn't really ment 'semantics of COMMIT'. I just ment using COMMIT
keyword, as it normally *may*never* be used within the trigger; and
initiate/implement semantics, which 'detaches' the remaining processing
implemented/encoded as contained within that trigger funciton, from the
original transaction execution.

> So... let's assume the "commit" whould not actually commit, but rather
> start another backend and do the work [1]. The problem is that newly
> started backed would not see the work until the old backend actually
> COMMIT;

Yes. And in fact, it should be kept blocked until that moment.

> [1]: If you really insist on doing it this way, of course you may! Here is
> a fishing rod:
> write a trigger in PL/perlU, which will fork(); The newly started child will
> use DBI to connect to database, and issue your query, and then call
> exit(0) to be sure you don't return to backend. You might want to call
> exec() with a pre-prepared script doing above work.
>
> From the perspective of the "main" backend, the trigger will call fork(),
> and finish. And your application will commit.
>
> That's everything you need to do it the way you want it. Have fun!

If that works, may be it could be implemented within the database
backend? And accesable to client programming by means of COMMIT keyword
(or to be more generic: COMMIT macro, provided also for other language
bindings) within the trigger function body?

-R