Re: Spec discussion: Generalized Data Queue / Modification Trigger

Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> (1) The ability to send asynchronous (or synchronous?) notifications, on
> a per-row basis, whenever data is modified *only after commit*. This
> has been generally described as "on-commit triggers", but could actually
> take a variety of forms.

> The question is, can we satisfy (1) with the new LISTEN/NOTIFY, or is it
> inadequate? I'll also point out that currently DEFFERABLE
> CONSTRAINT/TRIGGERS can be perverted to work like an after-commit trigger.

I think Modification Trigger is also used by not only clustering middlewares
but also materialized view and VACUUM FULL CONCURRENTLY, where we can replay
the modifications using the queued items. So, we could separate the trigger
parts into (1.1) store modifications into persistent storage and (1.2) send
the items to another server. IMHO, "on-commit" part is not so important.

We can implement 1.1 with existing triggers like Slony-I and PgQ,
but there is a problem in trigger-based approach that the trigger
needs to be called at the last of a trigger chain. PostgreSQL calls
the trigger chain in alphabetical order, but it we cannot forbid users
to add triggers with bottom names (ex. 'zzz_trigger').

We can develop 1.2 on the top of 1.1. If we try to improve performance
of the queue storage, it would be implemented with global temp tables
for which we don't write any WALs.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center