Re: background triggers? - Mailing list pgsql-general

On Thu, 2006-05-25 at 11:29 +0200, Thomas Hallgren wrote:
> Rafal Pietrak wrote:
> > consuming housekeeping. Like a cleanup - always succeeds, even if
> > sometimes is not really necesary (like in case of main rolling-back).
> >
> A somewhat limited use-case to form generic database functionality on, wouldn't you say?

OK. I admit.

...but may be ... :) OK. just a final comment and I'm done.

> > And that's exacly why I thing that it should be 'released to run' by
> > RDBMS *after* the main COMMITS (or ROLLES-BACK). It should be run on
> > COMMITED (visible to the world) changes, not on session trancients.
> >
> Right, so it's not a trigger. It's another session (another transaction) that reacts on a
> notification that is sent only if the first transaction succeeds. This is exactly what
> notify/listen is for.

Yes.

And no.

It is a trigger. But 'the other kind of' trigger.

<political-section-possible-source-of-fame-and-war-sorry>

The thing is. That I've seen *very* inefficent database application,
mainly because that was 'the easiest way to go'.

One of programmer's main virtue is lazyness. Of which I myself am proud
of :)

Thinking along the lines: "OK, we have this agregate tables, but we
don't need them 100% acurate, so let's not trigger syncing them with
transaction log on every INSERT" takes effort, and more often then not,
the implementation goes along as: "We have those aggregate tables - we
must keep those in-sync with main log, so we trigger an UPDATE on every
INSERT.... forking a housekeeper process to receive NOTIFY .... naaa....
I don't think so, may be next release". But once the application is in
production, we don't redesign when database load comes to the point
where performence suffer. The redesign is too risky.

My point is, that having a tool like COMMIT within a trigger function,
may result in a better application created easier right from the
beginning.

We don't see programmers wide use of LISTEN/NOTIFY, as I believe is
'just that little over the acceptable complaxity border'. The's why the
request may sound like 'for rare cases/limited use'.

The programmers' environment should 'gravitate' us to create good
software. The gravity is those little thing we find handy - triggers are
handy.... regretably lead to inefficiency.

</political-section>

I mean. Workaround exists but they are just workarounds nonetheless.

Then again. I just wanted to back-up the request which I've seen valid
and help explaining it. Obviously I wouldn't like to endlessly discuss
it's pros and cons. I think, the idea is well stated now, and if someone
is in the mood of implementing it (now, or in some unforseen future) -
hi/she has all the (end-user) explanations in the archieve.

-R