From: Andres Freund <andres@2ndquadrant.com>
Organization: 2ndQuadrant.com
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: how to avoid deadlock on masive update with multiples delete
Date: Fri, 5 Oct 2012 18:33:53 +0200
User-Agent: KMail/1.13.7 (Linux/3.5.0-rc3-andres-00231-g002b758; KDE/4.8.4;
	x86_64; ; )
Cc: pgsql-performance@postgresql.org, Maciek Sakrejda <m.sakrejda@gmail.com>,
	Claudio Freire <klaussfreire@gmail.com>, Jeff Janes <jeff.janes@gmail.com>,
	Anibal David Acosta <aa@devshock.com>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
	<201210051736.16460.andres@2ndquadrant.com>
	<10193.1349451965@sss.pgh.pa.us>
In-Reply-To: <10193.1349451965@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201210051833.55615.andres@2ndquadrant.com>

On Friday, October 05, 2012 05:46:05 PM Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
> > On Friday, October 05, 2012 05:31:43 PM Tom Lane wrote:
> >> There's no guarantee that the planner won't re-sort the rows coming from
> >> the sub-select, unfortunately.
> > 
> > More often than not you can prevent the planner from doing that by
> > putting a OFFSET 0 in the query. Not 100% but better than nothing.
> 
> No, that will accomplish exactly nothing.  The ORDER BY is already an
> optimization fence.
Yea, sorry. I was thinking of related problem/solution.

> > We really need ORDER BY for DML.
> 
> Meh.  That's outside the SQL standard (not only outside the letter of
> the standard, but foreign to its very conceptual model) and I don't
> think the problem really comes up that often.
Back when I mostly did consulting/development on client code it came up about 
once a week. I might have  a warped view though because thats the kind of 
thing you would ask a consultant about...

> Having said all that, are we sure this is even a deletion-order
> problem?  I was wondering about deadlocks from foreign key references,
> for instance.
Absolutely not sure, no.

Andres
-- 
Andres Freund		http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services