| From: | Andres Freund <andres(at)2ndquadrant(dot)com> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com>, Claudio Freire <klaussfreire(at)gmail(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Anibal David Acosta <aa(at)devshock(dot)com> |
| Subject: | Re: how to avoid deadlock on masive update with multiples delete |
| Date: | 2012-10-05 15:36:14 |
| Message-ID: | 201210051736.16460.andres@2ndquadrant.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Friday, October 05, 2012 05:31:43 PM Tom Lane wrote:
> Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2012-10-05 15:46:05 | Re: how to avoid deadlock on masive update with multiples delete |
| Previous Message | Tom Lane | 2012-10-05 15:31:43 | Re: how to avoid deadlock on masive update with multiples delete |