From: Jan de Visser <jdevisser@digitalfairway.com>
To: Brendan Duddridge <brendan@clickspace.com>
Subject: Re: App very unresponsive while performing simple update
Date: Wed, 31 May 2006 13:43:40 -0400
User-Agent: KMail/1.9.1
Cc: pgsql-performance@postgresql.org
References: <961F6F02-4B00-494C-B071-6DF76A305C14@clickspace.com>
	<200605310834.44935.jdevisser@digitalfairway.com>
	<5C36D400-CDCE-41D1-AD5F-EBF03E5F9656@clickspace.com>
In-Reply-To: <5C36D400-CDCE-41D1-AD5F-EBF03E5F9656@clickspace.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200605311343.40925.jdevisser@digitalfairway.com>

On Wednesday 31 May 2006 13:34, Brendan Duddridge wrote:
> Hi Jan,
>
> That sounds like a great idea! How would you control the update to
> occur only every 10,000 transactions?
>
> Is there a trigger setting for that somewhere?

I was thinking something like

IF count(*) % 10000 =3D 0 then
  ... do stuff ...
end if

Problem may be that that may be a bit expensive; maybe better to have a=20
sequence and use the sequence value.=20

Or something like that.

Also, maybe you should do the actual update of click_count not in the trigg=
er=20
itself, but have the trigger do a NOTIFY and have another process do a=20
LISTEN. Depends how long the update takes.

jan

>
> Thanks,
>
> ____________________________________________________________________
> Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan@clickspace.com
>
> ClickSpace Interactive Inc.
> Suite L100, 239 - 10th Ave. SE
> Calgary, AB  T2G 0V9
>
> http://www.clickspace.com
>
> On May 31, 2006, at 6:34 AM, Jan de Visser wrote:
> > On Wednesday 31 May 2006 02:29, Brendan Duddridge wrote:
> >> We'll probably have to write a process to update the click_count from
> >> querying our product_click_history table.
> >
> > How about an insert trigger on product_click_history which updates
> > click_count
> > every say 10000 transactions or so?
> >
> > jan
> >
> > --
> > --------------------------------------------------------------
> > Jan de Visser                     jdevisser@digitalfairway.com
> >
> >                 Baruk Khazad! Khazad ai-menu!
> > --------------------------------------------------------------
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq

=2D-=20
=2D-------------------------------------------------------------
Jan de Visser =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 jdevisser@digitalfair=
way.com

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Baruk Khazad! Khazad ai-menu!
=2D-------------------------------------------------------------