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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
2015-05-27 13:42 GMT+03:00 Marko Tiikkaja <marko@joh.to>:
Show quoted text
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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 postponedmigrating
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();
Show quoted text
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/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
På onsdag 27. mai 2015 kl. 12:42:29, skrev Marko Tiikkaja <marko@joh.to
<mailto: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 similar then 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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 similar then 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers