Re: Row locking during UPDATE

I didn't see any response to this.  Sorry if this is already stale
for you.

On Thu, Sep 04, 2003 at 08:21:17AM -0400, David F. Skoll wrote:
> Questions:
>
> 1) Am I on the right track?  Is this how PostgreSQL works?

More or less, yes.  The significant part here is that the postmaster
won't notice that the client is gone until it returns from the work
it was trying to do.  It'll eventually come back, but it'll take some
time.  How low does your contention need to be?

> A statistics update looks like this:
>
> UPDATE low_contention_stats SET value = value + 1
>     WHERE date = current_date AND key = 'KEY'
>     AND randomizer = my_random_val AND not summarized;
> (If the update updates 0 rows, we do an INSERT)
>
> The summary process when we copy to statistics looks like this:

If you're going to summarise anyway, why not just always insert into
a "holding" table, and then periodically (infrequently, though)
select out of there and summarise at that point.  Note that if you do
this very frequently, and you have also to select the summary data,
it won't work (as I have learned from painful experience) because
the holding table will gradually build up a lot of dead tuples.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110