> -----Original Message----- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: 20 January 2005 12:45 > To: D'Arcy J.M. Cain > Cc: Mark Cave-Ayland; [EMAIL PROTECTED]; > [EMAIL PROTECTED]; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Much Ado About COUNT(*) > > > D'Arcy J.M. Cain wrote: > > On Thu, 20 Jan 2005 10:12:17 -0000 > > "Mark Cave-Ayland" <[EMAIL PROTECTED]> wrote: > > > >>Thanks for the information. I seem to remember something similar to > >>this being discussed last year in a similar thread. My only > real issue > >>I can see with this approach is that the trigger is fired for every > >>row, and it is likely that the database I am planning will > have large > >>inserts of several hundred thousand records. Normally the impact of > >>these is minimised by inserting the entire set in one > transaction. Is > >>there any way that your trigger can be modified to fire once per > >>transaction with the number of modified rows as a parameter? > > > > > > I don't believe that such a facility exists but before > dismissing it > > you should test it out. I think that you will find that disk > > buffering (the system's as well as PostgreSQL's) will effectively > > handle this for you anyway. > > Well, it looks like ROW_COUNT isn't set in a statement-level trigger > function (GET DIAGNOSTICS myvar=ROW_COUNT). Which is a shame, > otherwise > it would be easy to handle. It should be possible to expose this > information though, since it gets reported at the command conclusion.
Hi Richard, This is more the sort of approach I would be looking for. However I think even in a transaction with ROW_COUNT defined, the trigger will still be called once per insert. I think something like this would require a new syntax like below, and some supporting code that would keep track of the tables touched by a transaction :( CREATE TRIGGER tt_test AFTER TRANSACTION ON trigtest FOR EACH TRANSACTION EXECUTE PROCEDURE tt_test_fn(); I am sure that Jeff's approach will work, however it just seems like writing out one table entry per row is going to slow large bulk inserts right down. Kind regards, Mark. ------------------------ WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 791021 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
