Re: background triggers? - Mailing list pgsql-general

Dawid Kuroczko wrote:
> On 5/25/06, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
>> > 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'.
>
> Oh, I think Florian meant that it is strange that your application is not
> interested in the trigger's output.  Of course one might want to add
> a notify-about-a-trigger-failure-by-email feature to circumvent that,
> but I won't be going so far off.
Ouch. ;-)

> What is here, is that with your approach, you fire a trigger and forget
> about it.  It either commits some time later, or does not, and you
> don't know it.  You don't know it, because your application went on,
> did other things, and has no way of knowing what's with the commit.
>
> Well, you can speculate, that you will notice that no work is being
> done.  But why?  Maybe the trigger is inefficient and isss soo sloooow,
> iittt taaaakessss aaaaaggeeees tooo cooompleeete.  Or maybe
> it ROLLBACKed, effectively removing all evidence of the work done.
> With this approach, you don't know it -- and this is what probably
> struck Florian's "strange for RDBMS" feeling.
This is exactly what I meant. Since there is always a possibillity for
failure (out of space, maschine crash, ...), all guarantees a RDBMS
gives you are of the form "If I responded with OK to command ... _then_
you can assume .....".

Doing things asynchronously defeats this for two reasons. First, nobody
will be able to check "if it responded OK". Second, since your proposed
background trigger would _need_ to fire only after the transaction
comitted (Which snapshot should it see otherwise? And when would its own
changes be visible?). But what if the trigger fails then? I can't cause
the transaction that caused it's run to rollback now, because the
database already committed that transaction.

>> 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.
>
> Don't get me wrong, but a word "bizzarre" is more suitable than
> "novelty".  The background processing is there since very long
> time -- why do you think LISTEN/NOTIFY was implemented? :)
>
>> But I'd like to express my believe, that having such tool within the
>> server can help build better database applications.
IMHO it won't. The _very_ reason why people use database systems is
because they provide strong guarantees about isolation and durability
of transactions. _Additionally_ the provide convenient searching, and
indexing, and stuff like that. But, at least from my point of view, the
part really hard to get right is the correct behavior of transactions.

I know that people "grew up" with mysql, especially mysql before at
least version 4, think differently. For them, a database is merely a
convenient replacement for flat files, providing a nice language (sql)
to specify complicated algorithms which would be tendious to hand-code.
If this is your interpretation of what a database does, then I can see
why this "background trigger" feature sounds so intriguing.

But if look
at things from the "transactional" point of view, then the feature
doesn't sound intriguing any more, because it just doesn't work together
well with transactions, and would have very strange semantics when
compared to other feature of the database.

>> 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.
I'd suggest that you try to find a way to do what you want _outside_ of
the backend. I'd actually love to see a more generic pgAgent (see my
other mail) that is distributed in contrib, or even installed by default.

You could use such a "postgres-aware" cron to schedule vacuum runs,
periodic cleanup jobs, or start some procedure on some notification.
_But_ it wouldn't be part of the backend. It would be a separate
process, connecting like any other process.

> Actually, I like the idea of "ON COMMIT" trigger (though without the
> "DETACHED" part), but this is another story...
Me too, although 2PC-Transactions would actually need to call it on
prepare, not on commit, so "on commit" would be a bad name.

greetings, Florian Pflug