Re: Much Ado About COUNT(*)

> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
> Sent: 20 January 2005 12:45
> To: D'Arcy J.M. Cain
> Cc: Mark Cave-Ayland; jdavis-pgsql@empires.org;
> alvherre@dcc.uchile.cl; 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" <m.cave-ayland@webbased.co.uk> 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