-----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 :(
Well, a statement-level trigger would be called once per statement, which can be much less than per row.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
