> -----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



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Thu, Jan 20, 2005 at 01:33:10PM -0000, Mark Cave-Ayland wrote:

> 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.

I don't see how it is any slower than the approach of inserting one
entry per row in the narrow table the OP wanted to use. And it will be
faster for the scans.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Officer Krupke, what are we to do?
Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend