Re: Much Ado About COUNT(*)

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