Re: Much Ado About COUNT(*)

Mark Cave-Ayland wrote:
>  
> 
> 
>>-----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 :( 

Well, a statement-level trigger would be called once per statement, 
which can be much less than per row.

--  Richard Huxton  Archonet Ltd