Re: how to avoid deadlock on masive update with multiples delete

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