X-Original-To: pgsql-patches-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0C2DE9DC960; Tue, 10 Jan 2006 22:18:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50828-06; Tue, 10 Jan 2006 22:18:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from linda-4.paradise.net.nz (bm-4a.paradise.net.nz [203.96.152.183]) by postgresql.org (Postfix) with ESMTP id ABED79DC890; Tue, 10 Jan 2006 22:18:48 -0400 (AST) Received: from smtp-3.paradise.net.nz (tclsnelb1-src-1.paradise.net.nz [203.96.152.172]) by linda-4.paradise.net.nz (Paradise.net.nz) with ESMTP id <0ISW006FGP3GOD@linda-4.paradise.net.nz>; Wed, 11 Jan 2006 15:18:52 +1300 (NZDT) Received: from [192.168.1.11] (218-101-28-20.dsl.clear.net.nz [218.101.28.20]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id AA9C6243B23; Wed, 11 Jan 2006 15:18:51 +1300 (NZDT) Date: Wed, 11 Jan 2006 15:18:49 +1300 From: Mark Kirkwood Subject: Re: Summary table trigger example race condition In-reply-to: <43C45405.40002@paradise.net.nz> To: Mark Kirkwood Cc: "Jim C. Nasby" , pgsql-patches@postgresql.org, pgsql-docs@postgresql.org Message-id: <43C46B09.8080300@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106) References: <20060105213645.GY43311@pervasive.com> <43BDC132.90007@paradise.net.nz> <20060106030415.GL43311@pervasive.com> <43BDE812.5000103@paradise.net.nz> <43C0833D.4040607@paradise.net.nz> <20060111000951.GQ3902@pervasive.com> <43C45405.40002@paradise.net.nz> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.28 required=5 tests=[AWL=0.280] X-Spam-Score: 0.28 X-Spam-Level: X-Archive-Number: 200601/134 X-Sequence-Number: 18470 Mark Kirkwood wrote: > Jim C. Nasby wrote: > >> On Sun, Jan 08, 2006 at 04:13:01PM +1300, Mark Kirkwood wrote: >> >> >> >> What happens if someone deletes the row between the failed insert and >> the second update? :) >> > > In this example the rows in the summary table never get deleted by > DELETE operations on that main one - the trigger just decrements the > various amounts - i.e DELETE becomes UPDATE, so no problem there. > Sorry Jim, I just realized you probably meant someone directly deleting rows in the summary table itself. Well yes, that would certainly fox it! I guess I was implicitly considering a use case where the only direct DML on the summary table would be some sort of ETL process, which would probably lock out other changes (and re-create the summary table data afterwards in all likelihood). Cheers Mark