Thread: Triggers on transaction?
I found myself in need of triggers that are run only once per transaction, rather than per row or statement within the transaction. Meaning it will always be deferred and never called twice for the same transaction.
Perhaps in 9.6 this part of syntax for trigger creation can be changed from:[ FOR [ EACH ] { ROW | STATEMENT } ]to:[ FOR [ EACH ] { ROW | STATEMENT | TRANSACTION } ]I looked though the code get an idea of how difficult it would be to implement and I think I might be able to do it, at least for functions without parameters. My idea for functions with parameters is to have them constrained to each one only accepting arrays of the type of element that is passed. Meaning if the column type is integer[], the function must take integer[][] in that parameter. From then on it's a matter of choice whether they'll be passed at original order or reverse and whether the trigger execution will be moved back or not.Should I bother trying or will such a feature not be accepted into a final release, even if it's working?
On 5/27/15 12:39 PM, Jordan Gigov wrote: > I found myself in need of triggers that are run only once per transaction, > rather than per row or statement within the transaction. Meaning it will > always be deferred and never called twice for the same transaction. What's the use case? .m
<div dir="ltr">Updating a materialized view in my case. It should only update when 2-3 of our 30+ tables get new data, whichfor those is kind of rare. Not having such a trigger means I will have to call it in each usage in the code and hopefuture maintainers don't forget it. This is why I postponed migrating the one search query where materialized views wouldbe useful, because it's heavy.<br /></div><div class="gmail_extra"><br /><div class="gmail_quote">2015-05-27 13:42 GMT+03:00Marko Tiikkaja <span dir="ltr"><<a href="mailto:marko@joh.to" target="_blank">marko@joh.to</a>></span>:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px#ccc solid;padding-left:1ex"><span class="">On 5/27/15 12:39 PM, Jordan Gigov wrote:<br /><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> I found myself inneed of triggers that are run only once per transaction,<br /> rather than per row or statement within the transaction.Meaning it will<br /> always be deferred and never called twice for the same transaction.<br /></blockquote><br/></span> What's the use case?<span class="HOEnZb"><font color="#888888"><br /><br /><br /> .m<br /></font></span></blockquote></div><br/></div>
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
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();
On 27 May 2015 at 11:55, Jordan Gigov <coladict@gmail.com> 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.
I don't understand why maintaining rows once per transaction saves any effort/provides any convenience.
Would the TransactionTrigger get access to changed rows? Can't you just use deferred triggers?
I'm not clear exactly when such a trigger should run. If the trigger issues more SQL, which also has triggers... do we run the PreCommit trigger twice? Or just accept that we wanted it to run just prior to commit but it kinda didn't?
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
På onsdag 27. mai 2015 kl. 12:42:29, skrev Marko Tiikkaja <marko@joh.to>:
On 5/27/15 12:39 PM, Jordan Gigov wrote:
> I found myself in need of triggers that are run only once per transaction,
> rather than per row or statement within the transaction. Meaning it will
> always be deferred and never called twice for the same transaction.
What's the use case?
I've often needed this for stuff like:
UPDATE some_table SET col = 'foo' where id = 1;
UPDATE some_table SET col = 'bar' where id = 1;
I want the "on-tx"-trigger to only run once, and on the last update of "col" so that it sees 'bar'.
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
> From: Andreas Joseph Krogh <andreas@visena.com>
>To: pgsql-hackers@postgresql.org
>Sent: Wednesday, 27 May 2015, 13:55
>Subject: Re: [HACKERS] Triggers on transaction?
>
>
>På onsdag 27. mai 2015 kl. 12:42:29, skrev Marko Tiikkaja <marko@joh.to>:
>On 5/27/15 12:39 PM, Jordan Gigov wrote:
>>> I found myself in need of triggers that are run only once per transaction,
>>> rather than per row or statement within the transaction. Meaning it will
>>> always be deferred and never called twice for the same transaction.
>>
>>What's the use case?
>
>
>I've often needed this for stuff like:
>
>UPDATE some_table SET col = 'foo' where id = 1;
>UPDATE some_table SET col = 'bar' where id = 1;
>
>I want the "on-tx"-trigger to only run once, and on the last update of "col" so that it sees 'bar'.
>
I often have similar requirements, and usually end up having to track what's already been updated with a trigger or
similarthen finally do the work in a deferred trigger. I'd have thought something more like FOR EACH { ROW | STATEMENT
}PER TRANSACTION would be more fitting.
Simon Riggs <simon@2ndQuadrant.com> writes:
> Can't you just use deferred triggers?
That seems like the real $64 question.
> I'm not clear exactly when such a trigger should run. If the trigger issues
> more SQL, which also has triggers... do we run the PreCommit trigger twice?
> Or just accept that we wanted it to run just prior to commit but it kinda
> didn't?
Yeah. By the time you get rid of the logical inconsistencies in the
concept of a trigger that runs "at commit", it seems like you end up with
something not so different from the existing deferred-trigger facility.
regards, tom lane
On 05/27/2015 09:40 AM, Glyn Astill wrote:
>> From: Andreas Joseph Krogh <andreas@visena.com>
>> To: pgsql-hackers@postgresql.org
>> Sent: Wednesday, 27 May 2015, 13:55
>> Subject: Re: [HACKERS] Triggers on transaction?
>>
>>
>> På onsdag 27. mai 2015 kl. 12:42:29, skrev Marko Tiikkaja <marko@joh.to>:
>> On 5/27/15 12:39 PM, Jordan Gigov wrote:
>>>> I found myself in need of triggers that are run only once per transaction,
>>>> rather than per row or statement within the transaction. Meaning it will
>>>> always be deferred and never called twice for the same transaction.
>>> What's the use case?
>>
>> I've often needed this for stuff like:
>>
>> UPDATE some_table SET col = 'foo' where id = 1;
>> UPDATE some_table SET col = 'bar' where id = 1;
>>
>> I want the "on-tx"-trigger to only run once, and on the last update of "col" so that it sees 'bar'.
>>
>
>
> I often have similar requirements, and usually end up having to track what's already been updated with a trigger or
similarthen finally do the work in a deferred trigger. I'd have thought something more like FOR EACH { ROW | STATEMENT
}PER TRANSACTION would be more fitting.
>
>
It's come up before. You can read some history here:
<http://postgresql.nabble.com/pre-commit-triggers-td5778575.html>
It seems clear there is some sort of unmet need here.
cheers
andrew