X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 652A411F65FF for ; Wed, 19 Apr 2006 05:20:59 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.12 X-Spam-Level: X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id ck1Xxz+jB8Ow for ; Wed, 19 Apr 2006 05:20:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pjetlic.mobart.hr (pjetlic.mobart.hr [80.80.51.10]) by postgresql.org (Postfix) with ESMTP id 4A67411F60B4 for ; Wed, 19 Apr 2006 05:20:56 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by pjetlic.mobart.hr (Postfix) with ESMTP id 07A6817A67 for ; Wed, 19 Apr 2006 10:20:56 +0200 (CEST) Received: from pjetlic.mobart.hr ([127.0.0.1]) by localhost (pjetlic [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 02792-04-6 for ; Wed, 19 Apr 2006 10:20:55 +0200 (CEST) Received: from mike.mobart.hr (mike.mobart.hr [192.168.10.94]) (using SSLv3 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by pjetlic.mobart.hr (Postfix) with ESMTP id E252517A3C for ; Wed, 19 Apr 2006 10:20:55 +0200 (CEST) Subject: Re: SELECT FOR UPDATE performance is bad From: Mario Splivalo Reply-To: mario.splivalo@mobart.hr To: Pgsql performance In-Reply-To: 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> Content-Type: text/plain Organization: Mob-Art Date: Wed, 19 Apr 2006 10:20:54 +0200 Message-Id: <1145434854.4458.12.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by OpenSource AV+AS Mail Server X-Archive-Number: 200604/423 X-Sequence-Number: 18507 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."