Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 315D3B5DC33 for ; Tue, 13 Sep 2011 17:23:20 -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 33786-06 for ; Tue, 13 Sep 2011 20:23:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from lo.gmane.org (lo.gmane.org [80.91.229.12]) by mail.postgresql.org (Postfix) with ESMTP id 612A5B5DC2D for ; Tue, 13 Sep 2011 17:23:12 -0300 (ADT) Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1R3ZVd-0005J2-Th for pgsql-performance@postgresql.org; Tue, 13 Sep 2011 22:23:09 +0200 Received: from p5dc4f155.dip.t-dialin.net ([93.196.241.85]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 13 Sep 2011 22:23:09 +0200 Received: from shortcutter by p5dc4f155.dip.t-dialin.net with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 13 Sep 2011 22:23:09 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org From: Robert Klemme Subject: Re: Postgres for a "data warehouse", 5-10 TB Date: Tue, 13 Sep 2011 22:22:47 +0200 Lines: 36 Message-ID: References: <4E6CC458.4010206@squeakycode.net> <4E6E3FE8.5070609@squeakycode.net> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: p5dc4f155.dip.t-dialin.net User-Agent: Mozilla/5.0 (Windows NT 6.0; WOW64; rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2 In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.393 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RP_MATCHES_RCVD=-0.504, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201109/192 X-Sequence-Number: 44968 On 13.09.2011 20:11, Marti Raudsepp wrote: > On Tue, Sep 13, 2011 at 19:34, Robert Klemme wrote: >> I don't think so. You only need to catch the error (see attachment). >> Or does this create a sub transaction? > > Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a > SAVEPOINT it can roll back to in case of an error. Ouch! Learn something new every day. Thanks for the update! http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html Side note: it seems that Oracle handles this differently (i.e. no subtransaction but the INSERT would be rolled back) making the pattern pretty usable for this particular situation. Also, I have never heard that TX ids are such a scarse resource over there. Would anybody think it a good idea to optionally have a BEGIN EXCEPTION block without the current TX semantics? In absence of that what would be a better pattern to do it (other than UPDATE and INSERT if not found)? >> Yes, I mentioned the speed issue. But regardless of the solution for >> MySQL's "INSERT..ON DUPLICATE KEY UPDATE" which Igor mentioned you >> will have the locking problem anyhow if you plan to insert >> concurrently into the same table and be robust. > > In a mass-loading application you can often divide the work between > threads in a manner that doesn't cause conflicts. Yeah, but concurrency might not the only reason to optionally update. If the data is there you might rather want to overwrite it instead of failure. Kind regards robert