On Wed, May 27, 2015 at 12:07 PM, hubert depesz lubaczewski <
dep...@depesz.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();
>
