Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 4D25D1337B53 for ; Sat, 27 Nov 2010 11:08:20 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 34716-06 for ; Sat, 27 Nov 2010 15:08:14 +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 C300C1337B9A for ; Sat, 27 Nov 2010 11:08:11 -0400 (AST) Received: by ewy5 with SMTP id 5so1339124ewy.19 for ; Sat, 27 Nov 2010 07:08:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:references:in-reply-to :content-type; bh=E/6k9uQxxEZuEZeqHBQryC3mP6ziWBzhulW0Gjsbc5o=; b=pX89uWQJEUW38HQ5eMlZJw448Fhm4bWIancVmHLeBq/lFuO28BpQYzhpE0BqUoS+FZ F5McNPoD/+V4LzJ/Vj1DumboOk2qLonywoHE3is/ZFrZuOZOPvOCMXm4ABJHdRZzjqXr OblW2gQ0s1R+mG8M4Cfdk3AXIpa2Ec2VFJ/+w= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type; b=DWSLXZdT4UFanM8TESWdr955HLsjSmmG0w55A33ntC4Kk9gaorp1NoNh5QUjtVkiP/ DBof64XIImFZqkaVJnhinaHIWusko/zW5COiOLe56ilJ81fSHqcf5FiQGuGBA75BnCrC gfJbAJFVAjChZgHrZNQLDEFJDVh+HdMYIsxQg= Received: by 10.213.105.76 with SMTP id s12mr4961645ebo.2.1290870489638; Sat, 27 Nov 2010 07:08:09 -0800 (PST) Received: from [178.49.3.188] (l49-3-188.cn.ru [178.49.3.188]) by mx.google.com with ESMTPS id b52sm2946776eei.1.2010.11.27.07.08.06 (version=TLSv1/SSLv3 cipher=RC4-MD5); Sat, 27 Nov 2010 07:08:07 -0800 (PST) Message-ID: <4CF11EF7.4020605@gmail.com> Date: Sat, 27 Nov 2010 21:08:39 +0600 From: "Mikhail V. Puzanov" User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.15) Gecko/20101030 Icedove/3.0.10 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: atomic multi-threaded upsert References: <4CED72BB.3070905@gmail.com> <21822.1290633670@sss.pgh.pa.us> In-Reply-To: Content-Type: multipart/alternative; boundary="------------030601090805030301030100" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/78 X-Sequence-Number: 34816 This is a multi-part message in MIME format. --------------030601090805030301030100 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On 25.11.2010 12:51, MIkhail Puzanov wrote: > > > 2010/11/25 Tom Lane > > > "Mikhail V. Puzanov" > writes: > > -- increment and get the counter if exists > > UPDATE sequences SET counter = counter + 1 > > WHERE section = 'testSection' AND name = 'testKey' > > RETURNING counter; > > Seems that what you have here is a bad manual implementation of a > sequence. Why don't you use a real sequence object and nextval()? > > > > Well, if I use the real sequence, I'll need unpredictable number of them > and will have to store mapping (section, name)->sequenceName, and > also will have to create those sequences dynamically the similar way. > Finally the problem solved with application level locks (that works for only one application server, but suits our needs for now). This also works http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE but we need some more or less cross-DBMS approach. --------------030601090805030301030100 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On 25.11.2010 12:51, MIkhail Puzanov wrote:


2010/11/25 Tom Lane <tgl@sss.pgh.pa.us>
"Mikhail V. Puzanov" <misha.puzanov@gmail.com> writes:
>    -- increment and get the counter if exists
>   UPDATE sequences SET counter = counter + 1
>   WHERE section = 'testSection' AND name = 'testKey'
>   RETURNING counter;

Seems that what you have here is a bad manual implementation of a
sequence.  Why don't you use a real sequence object and nextval()?



Well, if I use the real sequence, I'll need unpredictable number of them
and will have to store mapping (section, name)->sequenceName, and
also will have to create those sequences dynamically the similar way.


Finally the problem solved with application level locks (that works for only
one application server, but suits our needs for now).

This also works http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE but we need some more or less cross-DBMS approach.

--------------030601090805030301030100--