Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 923D3B5DC00
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  1 Aug 2011 05:52:29 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 20500-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  1 Aug 2011 08:52:23 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ew0-f46.google.com (mail-ew0-f46.google.com
	[209.85.215.46])
	by mail.postgresql.org (Postfix) with ESMTP id 1A008B5DBE2
	for <pgsql-performance@postgresql.org>;
	Mon,  1 Aug 2011 05:52:22 -0300 (ADT)
Received: by ewy4 with SMTP id 4so2681088ewy.19
	for <pgsql-performance@postgresql.org>;
	Mon, 01 Aug 2011 01:52:22 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	bh=g9s6PjoqBEStF9CAwHvPPXlamqvcB98Gm7oqz+wyLqs=;
	b=mqcLFu20KCM3+LWrP01cKpxkiHnagbtYIVXFk0e3ChNrjHYHIYVVSGsk4ZwE8S5Kjv
	NCgHURsHh0cj8lRK/5lwog4TETDnvyCwjlfF2s6HnwWTLKi82SLjgWlVPOtAKmg/++Nh
	A5VXEi3luZHPQHBBujZjNkS38P/DzKKSMTrV8=
Received: by 10.204.139.69 with SMTP id d5mr1129676bku.145.1312188741836;
	Mon, 01 Aug 2011 01:52:21 -0700 (PDT)
Received: from [10.64.1.26] ([213.108.72.250])
	by mx.google.com with ESMTPS id t19sm1242601bku.40.2011.08.01.01.52.19
	(version=TLSv1/SSLv3 cipher=OTHER);
	Mon, 01 Aug 2011 01:52:20 -0700 (PDT)
Message-ID: <4E366941.9070507@gmail.com>
Date: Mon, 01 Aug 2011 11:52:17 +0300
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: alan <alan.miller3@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: insert
References: 
 <2e208bc6-1bb8-4170-809f-77ab63a15bf4@l18g2000yql.googlegroups.com>
	<a3899672-2ff6-4174-bc31-07f5c4c2a113@l37g2000yqd.googlegroups.com>
In-Reply-To: 
 <a3899672-2ff6-4174-bc31-07f5c4c2a113@l37g2000yqd.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.799 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=0.1, FREEMAIL_FROM=0.001
X-Spam-Level: 
X-Archive-Number: 201108/3
X-Sequence-Number: 44450

Hello.

Please note that in multitasking environment you may have problems with 
your code. Two connections may check if "a" is available and if not (and 
both got empty "select" result), try to insert. One will succeed, 
another will fail if you have a unique constraint on category name (and 
you'd better have one).

Please note that select for update won't help you much, since this is 
new record you are looking for, and select don't return (and lock) it. I 
am using "lock table <tableName> in SHARE ROW EXCLUSIVE mode" in this case.

But then, if you have multiple lookup dictinaries, you need to ensure 
strict order of locking or you will be getting deadlocks. As for me, I 
did create a special application-side class to retrieve such values. If 
I can't find a value in main connection with simple select, I open new 
connection, perform table lock, check if value is in there. If it is 
not, add the value and commit. This may produce orphaned dictionary 
entries (if dictionary entry is committed an main transaction is rolled 
back), but this is usually OK for dictionaries. At the same time I don't 
introduce hard locks into main transaction and don't have to worry about 
deadlocks.

Best regards, Vitalii Tymchyshyn