From: Mario Splivalo <mario.splivalo@mobart.hr>
To: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: SELECT FOR UPDATE performance is bad
Date: Wed, 19 Apr 2006 10:20:54 +0200
Message-ID: <1145434854.4458.12.camel@localhost.localdomain> (raw)
In-Reply-To: <op.s77qveghcigqcu@apollo13>
References: <1145365720.18469.35.camel@localhost.localdomain>
<13037.1145370658@sss.pgh.pa.us>
<1145371662.31555.10.camel@localhost.localdomain>
<13609.1145374386@sss.pgh.pa.us>
<op.s77qveghcigqcu@apollo13>
On Tue, 2006-04-18 at 19:00 +0200, PFC wrote:
> Suppose you have a table codes :
> (
> game_id INT,
> code TEXT,
> used BOOL NOT NULL DEFAULT 'f',
> prize ...
> ...
> PRIMARY KEY (game_id, code)
> )
>
> Just UPDATE codes SET used='t' WHERE used='f' AND game_id=... AND code=...
>
> Then check the rowcount : if one row was updated, the code was not used
> yet. If no row was updated, the code either did not exist, or was already
> used.
>
> Another option : create a table used_codes like this :
>
> (
> game_id INT,
> code TEXT,
> ...
> PRIMARY KEY (game_id, code)
> )
>
> Then, when trying to use a code, INSERT into this table. If you get a
> constraint violation on the uniqueness of the primary key, your code has
> already been used.
>
> Both solutions have a big advantage : they don't require messing with
> locks and are extremely simple. The one with UPDATE is IMHO better,
> because it doesn't abort the current transaction (although you could use a
> savepoint in the INSERT case to intercept the error).
>
>
This works perfectly, but sometimes the game has no codes, and I still
need to know exactley who came first, who was second, and so on... So a
locking table as Tom suggested is, I guess, a perfect solution for my
situation...
Mario
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mario.splivalo@mobart.hr
Subject: Re: SELECT FOR UPDATE performance is bad
In-Reply-To: <1145434854.4458.12.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox