pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMake Trigger run after completion of ENTIRE transaction
2+ messages / 2 participants
[nested] [flat]
* Make Trigger run after completion of ENTIRE transaction
@ 2006-09-19 19:00 Henry Ortega <juandelacruz@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Henry Ortega @ 2006-09-19 19:00 UTC (permalink / raw)
To: pgsql-sql
This maybe more of a theoretical question, can you actually make a Trigger
run
after completion of the entire transaction?
Here's what I have:
LOG
user | startdate | enddate
enddate is getting updated by a trigger (on insert or update).
I have the following transaction:
BEGIN;
Insert into LOG(user,startdate) values('jdoe','2006-08-13');
Insert into LOG(user,startdate) values('jdoe','2006-08-14'); Insert into
LOG(user,startdate) values('jdoe','2006-08-15'); Insert into
LOG(user,startdate) values('jdoe','2006-08-16'); Insert into
LOG(user,startdate) values('jdoe','2006-08-17');
........... *another 20-30 more inserts..........*
COMMIT;
The trigger actually runs on each Insert and therefore slows down the
Insert quite a bit.
My question is, can you tell the trigger to run after Commit?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Make Trigger run after completion of ENTIRE transaction
@ 2006-09-20 09:24 Markus Schaber <schabi@logix-tt.com>
parent: Henry Ortega <juandelacruz@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Markus Schaber @ 2006-09-20 09:24 UTC (permalink / raw)
To: Henry Ortega <juandelacruz@gmail.com>; +Cc: pgsql-sql
Hi, Henry,
Henry Ortega wrote:
> This maybe more of a theoretical question, can you actually make a
> Trigger run
> after completion of the entire transaction?
[...]
> ........... another 20-30 more inserts..........
Which frontend do yu use? Maybe COPY is better than insert.
> The trigger actually runs on each Insert and therefore slows down the
> Insert quite a bit.
This sounds like the trigger itsself could be optimized a bit, or does
some work that is not best suited to be in a trigger.
> My question is, can you tell the trigger to run after Commit?
I assume the trigger has to do some integrity checking work?
Could you reformulate it as foreign key or check constraints? (you may
even call a complex plpgsql function from the check constraint). Most
constraint checks can be deferred to the end of the transaction, see the
docs for more details.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-09-20 09:24 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-19 19:00 Make Trigger run after completion of ENTIRE transaction Henry Ortega <juandelacruz@gmail.com>
2006-09-20 09:24 ` Markus Schaber <schabi@logix-tt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox