From: Andres Freund <andres@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org, Maciek Sakrejda <m.sakrejda@gmail.com>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Anibal David Acosta <aa@devshock.com>
Subject: Re: how to avoid deadlock on masive update with multiples delete
Date: Fri, 5 Oct 2012 18:33:53 +0200
Message-ID: <201210051833.55615.andres@2ndquadrant.com> (raw)
In-Reply-To: <10193.1349451965@sss.pgh.pa.us>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
<201210051736.16460.andres@2ndquadrant.com>
<10193.1349451965@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andres@2ndquadrant.com, tgl@sss.pgh.pa.us, m.sakrejda@gmail.com, klaussfreire@gmail.com, jeff.janes@gmail.com, aa@devshock.com
Subject: Re: how to avoid deadlock on masive update with multiples delete
In-Reply-To: <201210051833.55615.andres@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox