From: Andres Freund <andres@2ndquadrant.com>
Organization: 2ndQuadrant.com
To: pgsql-performance@postgresql.org
Subject: Re: how to avoid deadlock on masive update with multiples delete
Date: Fri, 5 Oct 2012 17:36:14 +0200
User-Agent: KMail/1.13.7 (Linux/3.5.0-rc3-andres-00231-g002b758; KDE/4.8.4;
	x86_64; ; )
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 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>
	<CAOtHd0A0UNJYTeVDsn6uY2d-6f2+=s5EkQFwNV0ASa4PqAk8yw@mail.gmail.com>
	<9895.1349451103@sss.pgh.pa.us>
In-Reply-To: <9895.1349451103@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201210051736.16460.andres@2ndquadrant.com>

On Friday, October 05, 2012 05:31:43 PM Tom Lane wrote:
> Maciek Sakrejda <m.sakrejda@gmail.com> writes:
> > Presumably something like this?:
> > maciek=# CREATE TABLE test AS SELECT g, random() FROM
> > generate_series(1,1000) g;
> > CREATE
> > maciek=# EXPLAIN DELETE FROM test USING (SELECT g FROM test ORDER BY
> > ctid) x where x.g = test.g;
> 
> 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.

We really need ORDER BY for DML.

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