Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TKAeC-0005tm-N0 for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 16:21:08 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TKAeA-0000KL-Po for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 16:21:08 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q95GL2xe010892; Fri, 5 Oct 2012 12:21:02 -0400 (EDT) From: Tom Lane To: Merlin Moncure cc: Andres Freund , pgsql-performance@postgresql.org, Maciek Sakrejda , Claudio Freire , Jeff Janes , Anibal David Acosta Subject: Re: how to avoid deadlock on masive update with multiples delete In-reply-to: References: <005701cda238$ba4d0350$2ee709f0$@devshock.com> <9895.1349451103@sss.pgh.pa.us> <201210051736.16460.andres@2ndquadrant.com> <10193.1349451965@sss.pgh.pa.us> Comments: In-reply-to Merlin Moncure message dated "Fri, 05 Oct 2012 10:55:39 -0500" Date: Fri, 05 Oct 2012 12:21:01 -0400 Message-ID: <10891.1349454061@sss.pgh.pa.us> X-Pg-Spam-Score: -4.0 (----) X-Archive-Number: 201210/34 X-Sequence-Number: 47993 Merlin Moncure writes: > Can't it be forced like this (assuming it is in fact a vanilla order > by problem)? > EXPLAIN DELETE FROM test USING (SELECT g FROM test ORDER BY > ctid FOR UPDATE) x where x.g = test.g; > (emphasis on 'for update') Hm ... yeah, that might work, once you redefine the problem as "get the row locks in a consistent order" rather than "do the updates in a consistent order". But I'd be inclined to phrase it as EXPLAIN DELETE FROM test USING (SELECT ctid FROM test ORDER BY g FOR UPDATE) x where x.ctid = test.ctid; I'm not sure that "ORDER BY ctid" is really very meaningful here; think about FOR UPDATE switching its attention to updated versions of rows. regards, tom lane