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