From: Tom Lane <tgl@sss.pgh.pa.us>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Andres Freund <andres@2ndquadrant.com>
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, 05 Oct 2012 12:21:01 -0400
Message-ID: <10891.1349454061@sss.pgh.pa.us> (raw)
In-Reply-To: <CAHyXU0yZ+qvuE_erWx86GSzTE6KTH3rtc-KEyDgJaTYM5Smx3g@mail.gmail.com>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
<CAOtHd0A0UNJYTeVDsn6uY2d-6f2+=s5EkQFwNV0ASa4PqAk8yw@mail.gmail.com>
<9895.1349451103@sss.pgh.pa.us>
<201210051736.16460.andres@2ndquadrant.com>
<10193.1349451965@sss.pgh.pa.us>
<CAHyXU0yZ+qvuE_erWx86GSzTE6KTH3rtc-KEyDgJaTYM5Smx3g@mail.gmail.com>
Merlin Moncure <mmoncure@gmail.com> writes:
> Can't it be forced like this (assuming it is in fact a vanilla order
> by problem)?
> EXPLAIN DELETE FROM test USING (SELECT g FROM test ORDER BY
> ctid FOR UPDATE) x where x.g = test.g;
> (emphasis on 'for update')
Hm ... yeah, that might work, once you redefine the problem as "get the
row locks in a consistent order" rather than "do the updates in a
consistent order". But I'd be inclined to phrase it as
EXPLAIN DELETE FROM test USING (SELECT ctid FROM test ORDER BY
g FOR UPDATE) x where x.ctid = test.ctid;
I'm not sure that "ORDER BY ctid" is really very meaningful here; think
about FOR UPDATE switching its attention to updated versions of rows.
regards, tom lane
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: tgl@sss.pgh.pa.us, mmoncure@gmail.com, andres@2ndquadrant.com, 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: <10891.1349454061@sss.pgh.pa.us>
* 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