Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id AAF3DB5DC32 for ; Tue, 13 Sep 2011 17:30:16 -0300 (ADT) 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 46789-07 for ; Tue, 13 Sep 2011 20:30:10 +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 AA575B5DBD6 for ; Tue, 13 Sep 2011 17:30:09 -0300 (ADT) Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1R3ZcN-0008Dl-2Z for pgsql-performance@postgresql.org; Tue, 13 Sep 2011 22:30:07 +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:30:07 +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:30:07 +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:26:04 +0200 Lines: 50 Message-ID: References: <4E6CC458.4010206@squeakycode.net> <4E6E3FE8.5070609@squeakycode.net> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: quoted-printable 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/193 X-Sequence-Number: 44969 On 13.09.2011 20:57, Stefan Keller wrote: > Interesting debate. Indeed. > 2011/9/13 Marti Raudsepp: >> Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a >> SAVEPOINT it can roll back to in case of an error. > > Are you sure? In theory I always understood that there are no > "subtransactions". What theory are you referring to? > In fact when looking at the docs there is chapter 39.6.6. saying "By > default, any error occurring in a PL/pgSQL function aborts execution > of the function, and indeed of the surrounding transaction as well. > You can trap errors and recover from them by using a BEGIN block with > an EXCEPTION clause." > (http://www.postgresql.org/docs/current/interactive/plpgsql-control-str= uctures.html > ) > > So the doc isn't totally explicit about this. But whatever: What would > be the the function of a subtransaction? To give the possibility to > recover and continue within the surrounding transaction? I find this pretty explicit: It is important not to confuse the use of BEGIN/END for grouping=20 statements in PL/pgSQL with the similarly-named SQL commands for=20 transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do = not start or end a transaction. Functions and trigger procedures are=20 always executed within a transaction established by an outer query =97=20 they cannot start or commit that transaction, since there would be no=20 context for them to execute in. However, a block containing an EXCEPTION = clause effectively forms a subtransaction that can be rolled back=20 without affecting the outer transaction. For more about that see Section = 38.6.5. http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html Cheers robert