From klodoma@ar-sd.net Tue Nov 9 08:47:25 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 193133A42EC for ; Tue, 9 Nov 2004 08:47:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22225-02 for ; Tue, 9 Nov 2004 08:47:17 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id 6BBC43A4300 for ; Tue, 9 Nov 2004 08:47:15 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id E1974E3D0 for ; Tue, 9 Nov 2004 10:49:16 +0200 (EET) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 12742-05 for ; Tue, 9 Nov 2004 10:49:15 +0200 (EET) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 6C198E3BA for ; Tue, 9 Nov 2004 10:49:15 +0200 (EET) Message-ID: <014301c4c638$b33af430$0b00a8c0@forge> From: "Andrei Bintintan" To: Subject: A transaction in transaction? Possible? Date: Tue, 9 Nov 2004 10:47:06 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0140_01C4C649.74D89600" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.4 tagged_above=0.0 required=5.0 tests=HTML_70_80, HTML_FONTCOLOR_GREEN, HTML_FONTCOLOR_RED, HTML_FONTCOLOR_UNSAFE, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200411/76 X-Sequence-Number: 19395 This is a multi-part message in MIME format. ------=_NextPart_000_0140_01C4C649.74D89600 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is it possible to have another transatction in a transaction??? In the = following example the last ROLLBACK is totally ignored(transaction1).=20 //connect to database $database =3D dbConnect($dbhost, $dbuser, $dbpass, $dbname); dbExec($database, "BEGIN"); //transaction1 //* dbExec($database, "BEGIN");//transaction2 $sql =3D "UPDATE orders SET technikernotiz=3D'51' WHERE = id=3D16143"; dbExec($database, $sql); dbExec($database, "COMMIT");//transaction2 /**/ $sql =3D "UPDATE orders SET reklamationsdetail=3D'51' WHERE = id=3D16143"; dbExec($database, $sql); dbExec($database, "ROLLBACK");//transaction1 dbClose($database); ------=_NextPart_000_0140_01C4C649.74D89600 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Is it possible=20 to have another transatction in a transaction??? In the following = example the=20 last ROLLBACK is totally ignored(transaction1).

//connect to=20 database
$database=20 =3D=20 dbConnect($dbhost, $dbuser, $dbpass, $dbname);
dbExec($database, = "BEGIN");=20 //transaction1

//*
   =20     dbExec($database, "BEGIN");//transaction2
         &nb= sp; =20 $sql =3D "UPDATE orders SET = technikernotiz=3D'51' WHERE=20 id=3D16143";
        =    =20 dbExec($database, $sql);
    =    =20 dbExec($database, "COMMIT");//transaction2
/**/

    $sql =3D "UPDATE orders SET=20 reklamationsdetail=3D'51' WHERE id=3D16143";
   =20 dbExec($database, $sql);
dbExec($database, = "ROLLBACK");//transaction1

dbClose($database);

 
------=_NextPart_000_0140_01C4C649.74D89600-- From mike@fuhr.org Tue Nov 9 13:55:25 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3CE1A3A3C77 for ; Tue, 9 Nov 2004 13:55:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22254-02 for ; Tue, 9 Nov 2004 13:55:20 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 171A23A3C79 for ; Tue, 9 Nov 2004 13:55:22 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id iA9Dt1Vw005656 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 9 Nov 2004 06:55:04 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id iA9Dt13M004147; Tue, 9 Nov 2004 06:55:01 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id iA9Dt1E6004146; Tue, 9 Nov 2004 06:55:01 -0700 (MST) (envelope-from mfuhr) Date: Tue, 9 Nov 2004 06:55:01 -0700 From: Michael Fuhr To: Andrei Bintintan Cc: pgsql-sql@postgresql.org Subject: Re: A transaction in transaction? Possible? Message-ID: <20041109135501.GA4099@winnie.fuhr.org> References: <014301c4c638$b33af430$0b00a8c0@forge> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <014301c4c638$b33af430$0b00a8c0@forge> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/79 X-Sequence-Number: 19398 On Tue, Nov 09, 2004 at 10:47:06AM +0200, Andrei Bintintan wrote: > Is it possible to have another transatction in a transaction??? PostgreSQL 8.0 (currently in beta) has savepoints, so you'll be able to do this: BEGIN; UPDATE orders SET technikernotiz='51' WHERE id=16143; SAVEPOINT foo; UPDATE orders SET reklamationsdetail='51' WHERE id=16143; ROLLBACK TO foo; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ From tedpet5@yahoo.com Tue Nov 9 15:25:18 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D142F3A3C4D for ; Tue, 9 Nov 2004 15:25:18 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 54526-07 for ; Tue, 9 Nov 2004 15:25:12 +0000 (GMT) Received: from web41007.mail.yahoo.com (web41007.mail.yahoo.com [66.218.93.6]) by svr1.postgresql.org (Postfix) with SMTP id 8AC803A1EC4 for ; Tue, 9 Nov 2004 15:25:14 +0000 (GMT) Received: (qmail 51570 invoked by uid 60001); 9 Nov 2004 15:25:03 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=gf+KXB5nSrLFSkvcKadzEQ0MkSYCUITvJYMWFW4QyGnOpx2PdJ7KeZWQUHp+gKGT20JrJi6MHxp+ogCGKylWgIsBTYIGRUeX2VFgSxsmaXZThUqw7f/Y8Hx4a4c5fat42AhWFZIHPUtVImEEnV0hH3cHTJpnegSA5A4/HV111QQ= ; Message-ID: <20041109152458.51487.qmail@web41007.mail.yahoo.com> Received: from [151.204.106.98] by web41007.mail.yahoo.com via HTTP; Tue, 09 Nov 2004 07:24:57 PST Date: Tue, 9 Nov 2004 07:24:57 -0800 (PST) From: Theodore Petrosky Subject: Re: A transaction in transaction? Possible? To: Andrei Bintintan , pgsql-sql@postgresql.org In-Reply-To: <014301c4c638$b33af430$0b00a8c0@forge> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/84 X-Sequence-Number: 19403 I thought nested transactions are available in the new release (8) coming up. Ted --- Andrei Bintintan wrote: > Is it possible to have another transatction in a > transaction??? In the following example the last > ROLLBACK is totally ignored(transaction1). > > //connect to database > $database = dbConnect($dbhost, $dbuser, $dbpass, > $dbname); > dbExec($database, "BEGIN"); //transaction1 > > //* > dbExec($database, "BEGIN");//transaction2 > $sql = "UPDATE orders SET > technikernotiz='51' WHERE id=16143"; > dbExec($database, $sql); > dbExec($database, "COMMIT");//transaction2 > /**/ > > $sql = "UPDATE orders SET > reklamationsdetail='51' WHERE id=16143"; > dbExec($database, $sql); > dbExec($database, "ROLLBACK");//transaction1 > > dbClose($database); > > > __________________________________ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com From bricklen@zyahoo.zcomz Tue Nov 9 16:25:11 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9BE973A3CF3 for ; Tue, 9 Nov 2004 16:25:11 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78426-03 for ; Tue, 9 Nov 2004 16:24:56 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 994A33A3C79 for ; Tue, 9 Nov 2004 16:24:54 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id iA9GOrJ7078525 for ; Tue, 9 Nov 2004 16:24:53 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id iA9GKCfo076813 for pgsql-sql@postgresql.org; Tue, 9 Nov 2004 16:20:12 GMT From: Bricklen User-Agent: Mozilla Thunderbird 0.8 (X11/20040926) X-Accept-Language: en-us, en MIME-Version: 1.0 X-Newsgroups: comp.databases.postgresql.sql Subject: Re: A transaction in transaction? Possible? References: <014301c4c638$b33af430$0b00a8c0@forge> In-Reply-To: <014301c4c638$b33af430$0b00a8c0@forge> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Lines: 23 Message-ID: <%66kd.134544$df2.123001@edtnps89> Date: Tue, 09 Nov 2004 16:20:11 GMT To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=NO_DNS_FOR_FROM X-Spam-Level: * X-Archive-Number: 200411/128 X-Sequence-Number: 19447 Andrei Bintintan wrote: > //Is it possible to have another transatction in a transaction??? In the > following example the last ROLLBACK is totally ignored(transaction1). > > ///connect to database/ > $database = dbConnect($dbhost, $dbuser, $dbpass, $dbname); > dbExec($database, "BEGIN"); //transaction1 > > ///*/ > / / dbExec($database, "BEGIN");//transaction2 > $sql = "UPDATE orders SET technikernotiz='51' WHERE id=16143"; > dbExec($database, $sql); > dbExec($database, "COMMIT");//transaction2 > //**/ > / > $sql = "UPDATE orders SET reklamationsdetail='51' WHERE id=16143"; > dbExec($database, $sql); > dbExec($database, "ROLLBACK");//transaction1 > > dbClose($database); > > This appears to be the same as Oracle's "autonomous transactions", fwiw. From sad@bankir.ru Wed Nov 10 06:12:39 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 348FA3A2D50 for ; Wed, 10 Nov 2004 06:12:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15878-08 for ; Wed, 10 Nov 2004 06:12:33 +0000 (GMT) Received: from rock.spider.ru (rock.spider.ru [62.183.34.226]) by svr1.postgresql.org (Postfix) with ESMTP id C65BE3A1D98 for ; Wed, 10 Nov 2004 06:12:32 +0000 (GMT) Received: from rock (localhost [127.0.0.1]) by rock (Postfix) with SMTP id D8651274DC; Wed, 10 Nov 2004 09:12:29 +0300 (MSK) Received: from sad.localnet.ru (sad.localnet.ru [10.0.0.72]) by rock.spider.ru (Postfix) with ESMTP id A20A5273FA; Wed, 10 Nov 2004 09:12:28 +0300 (MSK) Content-Type: text/plain; charset="iso-8859-1" From: sad Organization: IT To: Theodore Petrosky , Andrei Bintintan , pgsql-sql@postgresql.org Subject: Re: A transaction in transaction? Possible? Date: Wed, 10 Nov 2004 09:23:02 +0300 User-Agent: KMail/1.4.3 References: <20041109152458.51487.qmail@web41007.mail.yahoo.com> In-Reply-To: <20041109152458.51487.qmail@web41007.mail.yahoo.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200411100923.02316.sad@bankir.ru> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/97 X-Sequence-Number: 19416 On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote: > I thought nested transactions are available in the new > release (8) coming up. how to commit/rollback them ? From mike@fuhr.org Wed Nov 10 06:26:17 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CD1953A3B82 for ; Wed, 10 Nov 2004 06:26:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 19802-05 for ; Wed, 10 Nov 2004 06:26:12 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id AEE913A4112 for ; Wed, 10 Nov 2004 06:26:11 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id iAA6PDRo006502 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 9 Nov 2004 23:25:15 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id iAA6PCOq054459; Tue, 9 Nov 2004 23:25:12 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id iAA6PB9s054458; Tue, 9 Nov 2004 23:25:11 -0700 (MST) (envelope-from mfuhr) Date: Tue, 9 Nov 2004 23:25:10 -0700 From: Michael Fuhr To: sad Cc: Theodore Petrosky , Andrei Bintintan , pgsql-sql@postgresql.org Subject: Re: A transaction in transaction? Possible? Message-ID: <20041110062510.GA54426@winnie.fuhr.org> References: <20041109152458.51487.qmail@web41007.mail.yahoo.com> <200411100923.02316.sad@bankir.ru> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200411100923.02316.sad@bankir.ru> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200411/98 X-Sequence-Number: 19417 On Wed, Nov 10, 2004 at 09:23:02AM +0300, sad wrote: > On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote: > > I thought nested transactions are available in the new > > release (8) coming up. > > how to commit/rollback them ? CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL); BEGIN; INSERT INTO person (name) VALUES ('Tom'); SAVEPOINT foo; INSERT INTO person (name) VALUES ('Dick'); ROLLBACK TO foo; INSERT INTO person (name) VALUES ('Harry'); COMMIT; SELECT * FROM person; id | name ----+------- 1 | Tom 3 | Harry (2 rows) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ From abief_ag_-postgresql@yahoo.com Wed Nov 10 08:45:29 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3A4CE3A1D8F for ; Wed, 10 Nov 2004 08:45:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55547-02 for ; Wed, 10 Nov 2004 08:45:21 +0000 (GMT) Received: from web13902.mail.yahoo.com (web13902.mail.yahoo.com [216.136.175.28]) by svr1.postgresql.org (Postfix) with SMTP id 2685C3A3C0B for ; Wed, 10 Nov 2004 08:45:21 +0000 (GMT) Received: (qmail 10683 invoked by uid 60001); 10 Nov 2004 08:45:19 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=k+sPnDT/O66aPRM5gTSCayViUQluC+WWO6Ar51socF7Hxz0Upbnacg/BDr0hQJ2433I8hoA9F///UWi+S5QPgCaltgGSXS7fPXauzPFf7BmABP5VcnEy/RPWiHwHeVDn4TyH7tcMeztmsWQa0hugdmEoMcwhwo3niXuXhjHVfGY= ; Message-ID: <20041110084519.10681.qmail@web13902.mail.yahoo.com> Received: from [80.67.100.162] by web13902.mail.yahoo.com via HTTP; Wed, 10 Nov 2004 00:45:19 PST Date: Wed, 10 Nov 2004 00:45:19 -0800 (PST) From: "Riccardo G. Facchini" Reply-To: abief_ag_-postgresql@yahoo.com Subject: Re: A transaction in transaction? Possible? To: pgsql-sql@postgresql.org Cc: Theodore Petrosky , Andrei Bintintan , Michael Fuhr , sad In-Reply-To: <20041110062510.GA54426@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/99 X-Sequence-Number: 19418 Sorry, but I understand that your example is not really about nested transactions, but about sequential transactions. so, the primary question remains: how to commit/rollback them ? --- Michael Fuhr <__> wrote: > On Wed, Nov 10, 2004 at 09:23:02AM +0300, sad wrote: > > On Tuesday 09 November 2004 18:24, Theodore Petrosky wrote: > > > I thought nested transactions are available in the new > > > release (8) coming up. > > > > how to commit/rollback them ? > > CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL); > > BEGIN; > INSERT INTO person (name) VALUES ('Tom'); > SAVEPOINT foo; > INSERT INTO person (name) VALUES ('Dick'); > ROLLBACK TO foo; > INSERT INTO person (name) VALUES ('Harry'); > COMMIT; > > SELECT * FROM person; > id | name > ----+------- > 1 | Tom > 3 | Harry > (2 rows) > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > From mike@fuhr.org Wed Nov 10 09:54:30 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3D5293A3C1D for ; Wed, 10 Nov 2004 09:54:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78067-01 for ; Wed, 10 Nov 2004 09:54:23 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id B29833A3BB6 for ; Wed, 10 Nov 2004 09:54:24 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id iAA9rrYC006679 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 10 Nov 2004 02:53:56 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id iAA9rr5M055628; Wed, 10 Nov 2004 02:53:53 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id iAA9rqWs055627; Wed, 10 Nov 2004 02:53:52 -0700 (MST) (envelope-from mfuhr) Date: Wed, 10 Nov 2004 02:53:52 -0700 From: Michael Fuhr To: "Riccardo G. Facchini" Cc: pgsql-sql@postgresql.org, Theodore Petrosky , Andrei Bintintan , sad Subject: Re: A transaction in transaction? Possible? Message-ID: <20041110095352.GA55504@winnie.fuhr.org> References: <20041110062510.GA54426@winnie.fuhr.org> <20041110084519.10681.qmail@web13902.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20041110084519.10681.qmail@web13902.mail.yahoo.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200411/101 X-Sequence-Number: 19420 On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote: > Sorry, but I understand that your example is not really about nested > transactions, but about sequential transactions. Here's a more elaborate example. If this doesn't demonstrate the capability you're looking for, then please provide an example of what you'd like to do and describe the desired behavior. CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL); BEGIN; INSERT INTO person (name) VALUES ('Alice'); SAVEPOINT s1; INSERT INTO person (name) VALUES ('Bob'); SAVEPOINT s2; INSERT INTO person (name) VALUES ('Charles'); SAVEPOINT s3; INSERT INTO person (name) VALUES ('David'); ROLLBACK TO s3; INSERT INTO person (name) VALUES ('Edward'); ROLLBACK TO s2; INSERT INTO person (name) VALUES ('Frank'); RELEASE s1; INSERT INTO person (name) VALUES ('George'); COMMIT; SELECT * FROM person; id | name ----+-------- 1 | Alice 2 | Bob 6 | Frank 7 | George If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this: id | name ----+--------- 1 | Alice 2 | Bob 3 | Charles 5 | Edward 6 | Frank 7 | George If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this: id | name ----+-------- 1 | Alice 7 | George -- Michael Fuhr http://www.fuhr.org/~mfuhr/ From klodoma@ar-sd.net Wed Nov 10 10:37:59 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D464D3A3E4B for ; Wed, 10 Nov 2004 10:37:59 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93615-01 for ; Wed, 10 Nov 2004 10:37:52 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id 256DE3A3D45 for ; Wed, 10 Nov 2004 10:37:54 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 2774AE82E; Wed, 10 Nov 2004 12:39:53 +0200 (EET) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31568-04; Wed, 10 Nov 2004 12:39:51 +0200 (EET) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 5FCB3E825; Wed, 10 Nov 2004 12:39:51 +0200 (EET) Message-ID: <019801c4c711$57185380$0b00a8c0@forge> From: "Andrei Bintintan" To: "Achilleus Mantzios" , "Michael Fuhr" Cc: "Riccardo G. Facchini" , , "Theodore Petrosky" , "sad" References: Subject: Re: A transaction in transaction? Possible? Date: Wed, 10 Nov 2004 12:37:54 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-7" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/103 X-Sequence-Number: 19422 Okay, I see you're speaking about pgsql 8.0 What about 7.4? Andy. ----- Original Message ----- From: "Achilleus Mantzios" To: "Michael Fuhr" Cc: "Riccardo G. Facchini" ; ; "Theodore Petrosky" ; "Andrei Bintintan" ; "sad" Sent: Wednesday, November 10, 2004 12:58 PM Subject: Re: [SQL] A transaction in transaction? Possible? > O Michael Fuhr έγραψε στις Nov 10, 2004 : > > > On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote: > > > > > Sorry, but I understand that your example is not really about nested > > > transactions, but about sequential transactions. > > > > Here's a more elaborate example. If this doesn't demonstrate the > > capability you're looking for, then please provide an example of > > what you'd like to do and describe the desired behavior. > > > > CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL); > > > > BEGIN; > > INSERT INTO person (name) VALUES ('Alice'); > > > > SAVEPOINT s1; > > INSERT INTO person (name) VALUES ('Bob'); > > > > SAVEPOINT s2; > > INSERT INTO person (name) VALUES ('Charles'); > > > > SAVEPOINT s3; > > INSERT INTO person (name) VALUES ('David'); > > ROLLBACK TO s3; > > > > INSERT INTO person (name) VALUES ('Edward'); > > ROLLBACK TO s2; > > > > INSERT INTO person (name) VALUES ('Frank'); > > RELEASE s1; > > > > INSERT INTO person (name) VALUES ('George'); > > COMMIT; > > Just a very naive thought.... > Wouldn't make more sense to allow nested begin/commit/rollback blocks? > > > > > SELECT * FROM person; > > id | name > > ----+-------- > > 1 | Alice > > 2 | Bob > > 6 | Frank > > 7 | George > > > > If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this: > > > > id | name > > ----+--------- > > 1 | Alice > > 2 | Bob > > 3 | Charles > > 5 | Edward > > 6 | Frank > > 7 | George > > > > If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this: > > > > id | name > > ----+-------- > > 1 | Alice > > 7 | George > > > > > > -- > -Achilleus > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > From achill@matrix.gatewaynet.com Wed Nov 10 10:28:16 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CEC3D3A3D36 for ; Wed, 10 Nov 2004 10:28:16 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89677-01 for ; Wed, 10 Nov 2004 10:28:08 +0000 (GMT) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id 1DE403A3AE1 for ; Wed, 10 Nov 2004 10:28:07 +0000 (GMT) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id iAAAwS5x006914; Wed, 10 Nov 2004 12:58:28 +0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id iAAAwRWZ006910; Wed, 10 Nov 2004 12:58:27 +0200 Date: Wed, 10 Nov 2004 12:58:27 +0200 (EET) From: Achilleus Mantzios To: Michael Fuhr Cc: "Riccardo G. Facchini" , , Theodore Petrosky , Andrei Bintintan , sad Subject: Re: A transaction in transaction? Possible? In-Reply-To: <20041110095352.GA55504@winnie.fuhr.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-7 Content-Transfer-Encoding: 8BIT X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200411/102 X-Sequence-Number: 19421 O Michael Fuhr έγραψε στις Nov 10, 2004 : > On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote: > > > Sorry, but I understand that your example is not really about nested > > transactions, but about sequential transactions. > > Here's a more elaborate example. If this doesn't demonstrate the > capability you're looking for, then please provide an example of > what you'd like to do and describe the desired behavior. > > CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL); > > BEGIN; > INSERT INTO person (name) VALUES ('Alice'); > > SAVEPOINT s1; > INSERT INTO person (name) VALUES ('Bob'); > > SAVEPOINT s2; > INSERT INTO person (name) VALUES ('Charles'); > > SAVEPOINT s3; > INSERT INTO person (name) VALUES ('David'); > ROLLBACK TO s3; > > INSERT INTO person (name) VALUES ('Edward'); > ROLLBACK TO s2; > > INSERT INTO person (name) VALUES ('Frank'); > RELEASE s1; > > INSERT INTO person (name) VALUES ('George'); > COMMIT; Just a very naive thought.... Wouldn't make more sense to allow nested begin/commit/rollback blocks? > > SELECT * FROM person; > id | name > ----+-------- > 1 | Alice > 2 | Bob > 6 | Frank > 7 | George > > If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this: > > id | name > ----+--------- > 1 | Alice > 2 | Bob > 3 | Charles > 5 | Edward > 6 | Frank > 7 | George > > If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this: > > id | name > ----+-------- > 1 | Alice > 7 | George > > -- -Achilleus From peter_e@gmx.net Wed Nov 10 15:23:57 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 66CFE3A3C22 for ; Wed, 10 Nov 2004 15:23:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84004-10 for ; Wed, 10 Nov 2004 15:23:50 +0000 (GMT) Received: from mail.gmx.net (pop.gmx.net [213.165.64.20]) by svr1.postgresql.org (Postfix) with SMTP id 8BEC23A1D95 for ; Wed, 10 Nov 2004 15:23:52 +0000 (GMT) Received: (qmail 24538 invoked by uid 65534); 10 Nov 2004 15:23:52 -0000 Received: from dsl-082-082-234-251.arcor-ip.net (EHLO colt.pezone.net) (82.82.234.251) by mail.gmx.net (mp027) with SMTP; 10 Nov 2004 16:23:52 +0100 X-Authenticated: #495269 From: Peter Eisentraut To: Achilleus Mantzios , Michael Fuhr Subject: Re: A transaction in transaction? Possible? Date: Wed, 10 Nov 2004 16:23:50 +0100 User-Agent: KMail/1.5.1 Cc: "Riccardo G. Facchini" , , Theodore Petrosky , Andrei Bintintan , sad References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-7" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200411101623.50254.peter_e@gmx.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/104 X-Sequence-Number: 19423 Achilleus Mantzios wrote: > Wouldn't make more sense to allow nested begin/commit/rollback > blocks? Possibly. But that consideration would have been more relevant about 6 years ago when they wrote the SAVEPOINT syntax into the SQL standard. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ From achill@matrix.gatewaynet.com Wed Nov 10 15:32:00 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4556A3A41DD for ; Wed, 10 Nov 2004 15:32:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88032-09 for ; Wed, 10 Nov 2004 15:31:54 +0000 (GMT) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id B86343A41CE for ; Wed, 10 Nov 2004 15:31:56 +0000 (GMT) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id iAAG1a5x007615; Wed, 10 Nov 2004 18:01:36 +0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id iAAG1amV007611; Wed, 10 Nov 2004 18:01:36 +0200 Date: Wed, 10 Nov 2004 18:01:36 +0200 (EET) From: Achilleus Mantzios To: Peter Eisentraut Cc: Michael Fuhr , "Riccardo G. Facchini" , , Theodore Petrosky , Andrei Bintintan , sad Subject: Re: A transaction in transaction? Possible? In-Reply-To: <200411101623.50254.peter_e@gmx.net> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-7 Content-Transfer-Encoding: 8BIT X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/105 X-Sequence-Number: 19424 O Peter Eisentraut έγραψε στις Nov 10, 2004 : > Achilleus Mantzios wrote: > > Wouldn't make more sense to allow nested begin/commit/rollback > > blocks? > > Possibly. But that consideration would have been more relevant about 6 > years ago when they wrote the SAVEPOINT syntax into the SQL standard. > :) In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK; can be replaced with SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively. If only transactions weren't a requirement for SAVEPOINTs, what would we then need BEGIN; COMMIT; ROLLBACK; for? > > -- -Achilleus From peter_e@gmx.net Wed Nov 10 16:10:06 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4E4833A1D95 for ; Wed, 10 Nov 2004 16:10:06 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03657-06 for ; Wed, 10 Nov 2004 16:09:52 +0000 (GMT) Received: from mail.gmx.net (imap.gmx.net [213.165.64.20]) by svr1.postgresql.org (Postfix) with SMTP id EC37D3A421C for ; Wed, 10 Nov 2004 16:09:50 +0000 (GMT) Received: (qmail 25672 invoked by uid 65534); 10 Nov 2004 16:09:49 -0000 Received: from dsl-082-082-234-251.arcor-ip.net (EHLO colt.pezone.net) (82.82.234.251) by mail.gmx.net (mp020) with SMTP; 10 Nov 2004 17:09:49 +0100 X-Authenticated: #495269 From: Peter Eisentraut To: Achilleus Mantzios Subject: Re: A transaction in transaction? Possible? Date: Wed, 10 Nov 2004 17:09:48 +0100 User-Agent: KMail/1.5.1 Cc: pgsql-sql@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-7" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200411101709.48968.peter_e@gmx.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/106 X-Sequence-Number: 19425 Achilleus Mantzios wrote: > In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK; > can be replaced with > SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively. > > If only transactions weren't a requirement for SAVEPOINTs, > what would we then need BEGIN; COMMIT; ROLLBACK; for? Note that under the current arrangement, it doesn't make much sense to "commit" a subtransaction. It will be committed anyway when the main transactions commits, and you cannot commit it earlier because the main transaction could still roll back. So savepoint blocks are not really transactions, but more like semi-transactions. In other nested transaction models, things can be different. If you have so-called open nested transactions, which expose their results to other transactions already before the parent transaction commits, then a subtransaction commit is useful. But that behavior violates the isolation criterion of transactions and therefore needs additional facilities to behave tolerably. -- Peter Eisentraut http://developer.postgresql.org/~petere/ From tgl@sss.pgh.pa.us Wed Nov 10 17:29:00 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 939A23A424D for ; Wed, 10 Nov 2004 17:29:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32701-08 for ; Wed, 10 Nov 2004 17:28:53 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5A88E3A4235 for ; Wed, 10 Nov 2004 17:28:52 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iAAHSHXU013825; Wed, 10 Nov 2004 12:28:17 -0500 (EST) To: Achilleus Mantzios Cc: Michael Fuhr , "Riccardo G. Facchini" , pgsql-sql@postgresql.org, Theodore Petrosky , Andrei Bintintan , sad Subject: Re: A transaction in transaction? Possible? In-reply-to: References: Comments: In-reply-to Achilleus Mantzios message dated "Wed, 10 Nov 2004 12:58:27 +0200" Date: Wed, 10 Nov 2004 12:28:17 -0500 Message-ID: <13824.1100107697@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/110 X-Sequence-Number: 19429 Achilleus Mantzios writes: > Just a very naive thought.... > Wouldn't make more sense to allow nested begin/commit/rollback blocks? We actually had it working that way initially, but changed to the spec-defined behavior, because (a) it wasn't standard, and (b) it was confusing. See the pghackers archives. regards, tom lane From spaceman@foo.at Wed Nov 10 21:36:57 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6BE023A432A for ; Wed, 10 Nov 2004 21:36:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30935-03 for ; Wed, 10 Nov 2004 21:36:52 +0000 (GMT) Received: from mx-00.sil.at (mx-00.sil.at [62.116.68.196]) by svr1.postgresql.org (Postfix) with ESMTP id DD5603A1D95 for ; Wed, 10 Nov 2004 21:36:52 +0000 (GMT) Received: (qmail-ldap/ctrl 23871 invoked from network); 10 Nov 2004 21:36:48 -0000 Received: from unknown (HELO bazaar.foomatic.at) ([213.129.239.202]) (envelope-sender ) by mx-00.sil.at (qmail-ldap-1.03) with DES-CBC3-SHA encrypted SMTP for ; 10 Nov 2004 21:36:48 -0000 Received: from localhost (bazaar.foomatic.lan [127.0.0.1]) by bazaar.foomatic.at (Postfix) with ESMTP id 04E94DF436 for ; Wed, 10 Nov 2004 22:36:51 +0100 (CET) Received: from bazaar.foomatic.at ([127.0.0.1]) by localhost (bazaar.foomatic.at [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26445-20 for ; Wed, 10 Nov 2004 22:36:50 +0100 (CET) Received: from weyoun (Weyoun.foomatic.lan [192.168.15.239]) (using TLSv1 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by bazaar.foomatic.at (Postfix) with ESMTP id 3D721DF425 for ; Wed, 10 Nov 2004 22:36:50 +0100 (CET) Content-Type: text/plain; charset="iso-8859-1" From: Stefan Weiss Organization: Foo Orbital Operations To: pgsql-sql@postgresql.org Subject: Re: A transaction in transaction? Possible? Date: Wed, 10 Nov 2004 22:36:41 +0100 User-Agent: KMail/1.4.3 References: <13824.1100107697@sss.pgh.pa.us> In-Reply-To: <13824.1100107697@sss.pgh.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200411102236.41971.spaceman@foo.at> X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at foomatic.at X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/117 X-Sequence-Number: 19436 On Wednesday, 10 November 2004 18:28, Tom Lane wrote: > Achilleus Mantzios writes: > > Just a very naive thought.... > > Wouldn't make more sense to allow nested begin/commit/rollback blocks? > > We actually had it working that way initially, but changed to the > spec-defined behavior, because (a) it wasn't standard, and (b) it > was confusing. See the pghackers archives. We used to run into problems with nested transactions in scenarios like this: Imagine a database where you have a table for customers, and each customer can have (in a seperate table) several contacts; a contact can have one or more addresses, phone numbers, etc. These tables are connected by foreign keys, but without "on delete" triggers. The frontend application has a function for deleting a contact, which works something like this: * begin transaction * delete the contact's addresses, phone numbers, etc * ... * delete the contact record itself * commit Then there is a function for deleting a customer: * begin transaction * for all contacts, call the "delete contact" function * ... * delete the customer record itself * commit At the moment the application is "simulating" support for nested transactions: We use a wrapper for the BEGIN and COMMIT calls, and an internal counter, which is incremented for each BEGIN. Only the first BEGIN gets sent to the backend. When COMMIT has been called as many times as BEGIN, we send a real commit (errors and ROLLBACK are handled too, of course). It's not perfect, but it does what we need. Savepoints are a nice feature, but I don't think they could help us here. cheers, stefan From mendola@bigfoot.com Thu Nov 11 17:06:54 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C5D783A4759 for ; Thu, 11 Nov 2004 17:06:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36698-09 for ; Thu, 11 Nov 2004 17:06:51 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 3A9033A4A48 for ; Thu, 11 Nov 2004 15:24:06 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id iABFO4g9001466 for ; Thu, 11 Nov 2004 15:24:05 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id iAB8B6d4092334 for pgsql-sql@postgresql.org; Thu, 11 Nov 2004 08:11:06 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.sql Subject: Re: A transaction in transaction? Possible? Date: Thu, 11 Nov 2004 09:10:56 +0100 Organization: PYRENET Midi-pyrenees Provider Lines: 52 Message-ID: <41931E90.2050108@bigfoot.com> References: <20041110095352.GA55504@winnie.fuhr.org> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-7; format=flowed Content-Transfer-Encoding: 8bit X-Complaints-To: abuse@pyrenet.fr To: Achilleus Mantzios User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en In-Reply-To: X-Enigmail-Version: 0.86.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200411/119 X-Sequence-Number: 19438 Achilleus Mantzios wrote: > O Michael Fuhr έγραψε στις Nov 10, 2004 : > > >>On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote: >> >> >>>Sorry, but I understand that your example is not really about nested >>>transactions, but about sequential transactions. >> >>Here's a more elaborate example. If this doesn't demonstrate the >>capability you're looking for, then please provide an example of >>what you'd like to do and describe the desired behavior. >> >>CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL); >> >>BEGIN; >> INSERT INTO person (name) VALUES ('Alice'); >> >> SAVEPOINT s1; >> INSERT INTO person (name) VALUES ('Bob'); >> >> SAVEPOINT s2; >> INSERT INTO person (name) VALUES ('Charles'); >> >> SAVEPOINT s3; >> INSERT INTO person (name) VALUES ('David'); >> ROLLBACK TO s3; >> >> INSERT INTO person (name) VALUES ('Edward'); >> ROLLBACK TO s2; >> >> INSERT INTO person (name) VALUES ('Frank'); >> RELEASE s1; >> >> INSERT INTO person (name) VALUES ('George'); >>COMMIT; > > > Just a very naive thought.... > Wouldn't make more sense to allow nested begin/commit/rollback blocks? Is not naive because in this way you can do what you could do with nested begin/commit/rollback blocks, in this way you can do more. Think for example in the example above to convert the "ROLLBACK TO s3" in a "ROLLBACK TO s1", that is impossible to do with begin/commit/rollback blocks. Regards Gaetano Mendola From mendola@bigfoot.com Thu Nov 11 17:38:36 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BF2473A4505 for ; Thu, 11 Nov 2004 17:38:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 53446-10 for ; Thu, 11 Nov 2004 17:38:31 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 081413A4BD0 for ; Thu, 11 Nov 2004 15:54:05 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id iABFs4g5012427 for ; Thu, 11 Nov 2004 15:54:04 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id iABFhifC007941 for pgsql-sql@postgresql.org; Thu, 11 Nov 2004 15:43:44 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.sql Subject: Re: A transaction in transaction? Possible? Date: Thu, 11 Nov 2004 09:23:24 +0100 Organization: PYRENET Midi-pyrenees Provider Lines: 100 Message-ID: <4193217C.9080900@bigfoot.com> References: <13824.1100107697@sss.pgh.pa.us> <200411102236.41971.spaceman@foo.at> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: Stefan Weiss User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en In-Reply-To: <200411102236.41971.spaceman@foo.at> X-Enigmail-Version: 0.86.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=DATE_IN_PAST_06_12 X-Spam-Level: X-Archive-Number: 200411/118 X-Sequence-Number: 19437 Stefan Weiss wrote: > On Wednesday, 10 November 2004 18:28, Tom Lane wrote: > >>Achilleus Mantzios writes: >> >>>Just a very naive thought.... >>>Wouldn't make more sense to allow nested begin/commit/rollback blocks? >> >>We actually had it working that way initially, but changed to the >>spec-defined behavior, because (a) it wasn't standard, and (b) it >>was confusing. See the pghackers archives. > > > We used to run into problems with nested transactions in scenarios > like this: > > Imagine a database where you have a table for customers, and > each customer can have (in a seperate table) several contacts; a > contact can have one or more addresses, phone numbers, etc. These > tables are connected by foreign keys, but without "on delete" > triggers. Why "without" ? Are you looking to solve a problem introduced by yourself ? > The frontend application has a function for deleting a contact, > which works something like this: > > * begin transaction > * delete the contact's addresses, phone numbers, etc > * ... > * delete the contact record itself > * commit > > Then there is a function for deleting a customer: > > * begin transaction > * for all contacts, call the "delete contact" function > * ... > * delete the customer record itself > * commit > > At the moment the application is "simulating" support for nested > transactions: We use a wrapper for the BEGIN and COMMIT calls, > and an internal counter, which is incremented for each BEGIN. > Only the first BEGIN gets sent to the backend. When COMMIT has > been called as many times as BEGIN, we send a real commit (errors > and ROLLBACK are handled too, of course). > > It's not perfect, but it does what we need. Savepoints are a nice > feature, but I don't think they could help us here. You can handle this task using the new functionality introduced with savepoint: the exception. For more information look at: http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Your delete customer can do: * BEGIN * for all contacts call delete contact * ... * EXCEPTION * handle your exception * END; * * delete the customer record itself Regards Gaetano Mendola From spaceman@foo.at Thu Nov 11 19:31:50 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5CF8C3A3E90 for ; Thu, 11 Nov 2004 19:31:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 10331-03 for ; Thu, 11 Nov 2004 19:31:40 +0000 (GMT) Received: from mx-00.sil.at (mx-00.sil.at [62.116.68.196]) by svr1.postgresql.org (Postfix) with ESMTP id BFDF83A4157 for ; Thu, 11 Nov 2004 19:31:39 +0000 (GMT) Received: (qmail-ldap/ctrl 52804 invoked from network); 11 Nov 2004 19:31:35 -0000 Received: from unknown (HELO bazaar.foomatic.at) ([213.129.239.202]) (envelope-sender ) by mx-00.sil.at (qmail-ldap-1.03) with DES-CBC3-SHA encrypted SMTP for ; 11 Nov 2004 19:31:35 -0000 Received: from localhost (bazaar.foomatic.lan [127.0.0.1]) by bazaar.foomatic.at (Postfix) with ESMTP id F266BDF436; Thu, 11 Nov 2004 20:31:37 +0100 (CET) Received: from bazaar.foomatic.at ([127.0.0.1]) by localhost (bazaar.foomatic.at [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 28959-06; Thu, 11 Nov 2004 20:31:37 +0100 (CET) Received: from weyoun (Weyoun.foomatic.lan [192.168.15.239]) (using TLSv1 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by bazaar.foomatic.at (Postfix) with ESMTP id 71C05DF425; Thu, 11 Nov 2004 20:31:37 +0100 (CET) Content-Type: text/plain; charset="iso-8859-1" From: Stefan Weiss Organization: Foo Orbital Operations To: Gaetano Mendola Subject: Re: A transaction in transaction? Possible? Date: Thu, 11 Nov 2004 20:31:27 +0100 User-Agent: KMail/1.4.3 References: <200411102236.41971.spaceman@foo.at> <4193217C.9080900@bigfoot.com> In-Reply-To: <4193217C.9080900@bigfoot.com> Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200411112031.27766.spaceman@foo.at> X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at foomatic.at X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/121 X-Sequence-Number: 19440 On Thursday, 11 November 2004 09:23, Gaetano Mendola wrote: > Stefan Weiss wrote: > > These tables are connected by foreign keys, but without "on delete" > > triggers. > > Why "without" ? Are you looking to solve a problem introduced by > yourself ? There are numerous checks involved before a customer (or a contact) can be deleted, and not all of these checks can be done by querying the database. Sometimes triggers aren't enough. > You can handle this task using the new functionality introduced with > savepoint: the exception. For more information look at: > http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-control-structures >.html#PLPGSQL-ERROR-TRAPPING The "delete contact" and "delete customer" functions are not stored procedures, so I'm not sure how this can help. thanks, stefan