Re: Triggers on transaction?

On Wed, May 27, 2015 at 12:07 PM, hubert depesz lubaczewski <
depesz(at)depesz(dot)com> wrote:

> On Wed, May 27, 2015 at 01:55:24PM +0300, Jordan Gigov wrote:
> > Updating a materialized view in my case. It should only update when 2-3
> of
> > our 30+ tables get new data, which for those is kind of rare. Not having
> > such a trigger means I will have to call it in each usage in the code and
> > hope future maintainers don't forget it. This is why I postponed
> migrating
> > the one search query where materialized views would be useful, because
> it's
> > heavy.
>
> Add daemon which runs "LISTEN", and triggers on the underlying tables
> that do NOTIFY.
> When daemon picks something from LISTEN (which happens only after
> commit) - it can do anything that needs to be done, and it will not
> block the transaction, which is an added benefit.
>
> depesz
>
>
You can also emulate once per transaction on commit triggers by a
combination of an
after trigger and a temporary table.

-- Deferred constraint triggers run when the transaction are commited

-- BUT they can only be declared AFTER ROW ! And having many of them

-- can be quiet expensive.

-- Normal triggers are pretty cheap but cannot be deferred

-- So we indirect via an additional table so that per one transaction

-- we issue only one run of the trigger

create unlogged table schedule_work_at_end_of_transaction(b bool);

create function work_at_end_of_transaction() returns trigger language
plpgsql as $$
begin

delete from schedule_work_at_end_of_transaction;

raise notice 'trigger!';

return NEW;

end ;

$$ ;

create constraint trigger work_at_end_of_transaction after insert

on schedule_work_at_end_of_transaction

initially deferred for each row

execute procedure work_at_end_of_transaction();

create table test(t text);

create function schedule_if_necessary() returns trigger language plpgsql as
$$
begin

if not exists (select null from schedule_work_at_end_of_transaction limit
1) then
insert into schedule_work_at_end_of_transaction values(true);

end if;

return NEW;

end;

$$ ;

begin;

do $$

begin

for i in 1..1000 loop

insert into test select s :: text from generate_series(1, 1000) s;

end loop;

end ;

$$ ;

commit;

truncate test;

create trigger schedule_if_necessary after insert or update on test

for each statement

execute procedure schedule_if_necessary();

begin;

do $$

begin

for i in 1..1000 loop

insert into test select s :: text from generate_series(1, 1000) s;

end loop;

end ;

$$ ;

commit;

drop table test;

drop table schedule_work_at_end_of_transaction;

drop function schedule_if_necessary();

drop function work_at_end_of_transaction();

>