Re: Do I need serializable for this query? - Mailing list pgsql-general

William Garrison wrote:
> I have a table that keeps running totals.  It is possible that this
> would get called twice simultaneously for the same UserID.  Do I need to
> put this in a serializable transaction?  Since this gets called often,
> could it be a performance problem if I make it serializable?
>
> CREATE FUNCTION UpdateTotals(IN UserID int,
>                              IN AddToCount int,
>                              IN AddToSize bigint)
> RETURNS void AS $$
> BEGIN
>     UPDATE
>         Totals
>     SET
>         TotalSize = TotalSize + $2,
>         TotalCount = TotalCount + $3
>     WHERE
>         UserID = $1;
>     END IF;
> END
> $$ LANGUAGE 'plpgsql' VOLATILE;

Where is that stray "END IF;" comming from?
Anyway, this should be safe, and work even more
reliably in read-committed mode than in serializable
mode. In serializeable mode, if the Total of the same
user is updated by two transactions simultanously,
you'll get a SerializationError. Read-Committed mode
OTOH will make sure that it uses the latest version of
the tuple for calculating the new values.

Usually, you need serializable mode if you do updates
based on the results of previous selects.

greetings, Florian Pflug