Re: Triggers on transaction? - Mailing list pgsql-hackers
| From | Benedikt Grundmann |
|---|---|
| Subject | Re: Triggers on transaction? |
| Date | |
| Msg-id | CADbMkNNagpOQ6fLHcABt4j9xG0u6-4GL2zrqVntspvZGGMKZkA@mail.gmail.com Whole thread Raw |
| In response to | Re: Triggers on transaction? (hubert depesz lubaczewski <depesz@depesz.com>) |
| List | pgsql-hackers |
On Wed, May 27, 2015 at 12:07 PM, hubert depesz lubaczewski <depesz@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();
pgsql-hackers by date: