Обсуждение: Statement Level Deferred Triggers

Hi all,

I would like to hear some opinions before starting to take a stab at 
implementing $subject.
My current use case is updating materialized views at the end of the 
transaction so that they appear consistent to the outside.
Updating them on every row changed is far too expensive - so every change is 
logged and depending on the size of the changeset the views are recomputed 
completely or incrementally.
Currently this is hacked up by using deferred constraint triggers - which are 
row level only... (i.e. the first run trigger deletes the whole changelog so 
that all following triggers have nearly nothing to do).
This is neither nice from an architectural point nor from an performance 
angle.

I am sure most of you can think of other use cases.

Opinions? 

Andres

On Fri, Oct 23, 2009 at 7:29 PM, Andres Freund <andres@anarazel.de> wrote:
> Hi all,
>
> I would like to hear some opinions before starting to take a stab at
> implementing $subject.
> My current use case is updating materialized views at the end of the
> transaction so that they appear consistent to the outside.
> Updating them on every row changed is far too expensive - so every change is
> logged and depending on the size of the changeset the views are recomputed
> completely or incrementally.
> Currently this is hacked up by using deferred constraint triggers - which are
> row level only... (i.e. the first run trigger deletes the whole changelog so
> that all following triggers have nearly nothing to do).
> This is neither nice from an architectural point nor from an performance
> angle.
>
> I am sure most of you can think of other use cases.
>
> Opinions?

This same use case has arisen for me in the past, with slightly
different details.

...Robert

Andres Freund <andres@anarazel.de> wrote:

> I would like to hear some opinions before starting to take a stab at 
> implementing $subject.

+1 to support it.

I'm using deferred trigger to emulate on-commit trigger,
but official support is infinitely better.
   UPDATE pg_catalog.pg_trigger      SET tgdeferrable = true, tginitdeferred = true    WHERE tgrelid =
'atcommit_tbl'::regclass     AND tgname = 'atcommit';
 

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