Re: background triggers? - Mailing list pgsql-general

On Thu, 2006-05-25 at 18:49 +0200, Florian G. Pflug wrote:
> Rafal Pietrak wrote:
> > 'technical' need for him/her to create the server side proces *provided*
> > hi/she can setup a job *within* the database server itself, and just go
> > away.
> Well, exactly "not being interested in the outcome" is IMHO the reason
> why your demands clash with archtecture of a RDBMS. Most RDBMS go a long
> way to ensure consistency and safety of your data, once they promised
> (usually by not raising an error at least on "commit") that they stored
> them. This doesn't match very well with asynchronous processes for which
> "nobody cares" (In the sense that there is nobody to reports errors to).

No, no no. This is completly different story.

That would really be very missfortunate if we couldn't relay on RDBMS
'confirmed storage'.

Here I'm just not interested in that procedure outcome: if it eventually
COMMITED or ROLLED-BACK. But obviously I am interested in consistancy of
database, when the detached procedure COMMITS.

I mean. It looks like this is *really* a novelty for RDBMS design - I
feel, that real programmers here (you guys :) are so hard to persuade
its necesary, because it's so far from the 'synchronous nature' of
clasical RDBMS design and triggers in particular.

But I'd like to express my believe, that having such tool within the
server can help build better database applications.

BTW: It's tempting to bring here another example from another world: OS
kernel design (linux in particular) does have a 'sort of triggers' - the
interrupt service subsystem. To have the overal system efficient,
interrupt serive routines are split into 'front-end' and 'back-ends'.
Front-ends execute 'inside' interrupt context - interrupts disabled.
Back-ends execute 'outside' interupt context - interrupts enabled.

The goal here would be to allow for similar optimisation handed over to
database programmers. Some part of trigger must be executed within the
context of an opened transaction; but another part may be executed
without constrains of blocking the caller.

Lots of similarities.

Having an easy to use database backend framework that supports splitting
trigger execution, will allow 'user space' programmers optimise trigger
implementations.

> But what would probably ease your 'etching desire' is if there was a
> general-purpose daemon that could be told the execute a given statement
> either at predefined times, or when a notification arrives, or both.

Yesss. A pg_crontab system table. One, that keeps stored procedures for
scheduled execution within the backend by the backend (like VACUUM)
would be nice. Yes, yes, yes. But that's not exactly the functionality
of 'background triggers'.

> Then you wouldn't need to write the daemon yourself, and it would even
> be "part of the framework" as long as you consider pgadmin to be "part
> of the framework".

I wouldn't.

I would only call it a framework if I can say COMMIT within the trigger
body. Or alternatively, if I can define a trigger, so to say: "FOR EACH
COMMIT" (pls note, that it's a different point in time, then "FOR EACH
STATEMENT") which I could also define as "DETACHED" - launched by the
forked backend.

sory :)

regards,

-R