From aa@devshock.com Thu Oct  4 14:01:21 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1TJlzM-0002t4-QK
	for pgsql-performance@postgresql.org; Thu, 04 Oct 2012 14:01:21 +0000
Received: from mail-yx0-f174.google.com ([209.85.213.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1TJlzL-00020h-Mz
	for pgsql-performance@postgresql.org; Thu, 04 Oct 2012 14:01:20 +0000
Received: by mail-yx0-f174.google.com with SMTP id m12so91148yen.19
	for <pgsql-performance@postgresql.org>;
	Thu, 04 Oct 2012 07:01:18 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=from:to:subject:date:message-id:mime-version:content-type:x-mailer
	:thread-index:content-language:x-gm-message-state;
	bh=0oxEusiCpqt2LxKrZW7VD2ut1uDFQDcLCAPbSWaoBXo=;
	b=jy3/2/ffl6+cGjSypml9K06dIfn4qB4Ce1b986EYsfwHDwiiMr2arq05BEfbIGvvB9
	d4g05K/tvmS/FrrU5YfUBC8IaCKfJBI3uM2tO2A4Xxdly+MSN7OnIMEHTm996zFwRwLG
	nLB/kCDRYcn0WEgWHlgr1QVsTB6w0EiBcrguMDytT7rmbKci4fUYt39cYlM6Pk69shTk
	BwI0rmMrGNrT8NV4KN26xO87puw54SKknB5+K3wQUDJTb59kmPuchDgY3UVjRdaKYD6q
	eWIU/3Y6CrjIzlHkSfAHafOa5Eq2SqXngfiJom8JCDwaLh9j/hZNjcTBOdFs7Tfqx4LZ
	Mr4w==
Received: by 10.236.177.70 with SMTP id c46mr5258140yhm.33.1349359278850;
	Thu, 04 Oct 2012 07:01:18 -0700 (PDT)
Received: from AnibalVAIO (host-1-86.b20.cvc.com.py. [186.2.211.86])
	by mx.google.com with ESMTPS id h22sm10067362yhk.13.2012.10.04.07.01.17
	(version=TLSv1/SSLv3 cipher=OTHER);
	Thu, 04 Oct 2012 07:01:18 -0700 (PDT)
From: "Anibal David Acosta" <aa@devshock.com>
To: <pgsql-performance@postgresql.org>
Subject: how to avoid deadlock on masive update with multiples delete
Date: Thu, 4 Oct 2012 10:01:15 -0400
Message-ID: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0058_01CDA217.333C26A0"
X-Mailer: Microsoft Outlook 14.0
Thread-Index: Ac2iOLgOTRNZ/NTwQ1a1sc4S9b6bRA==
Content-Language: es-py
X-Gm-Message-State: 
 ALoCoQnvVljEQ02BSvTvqXwG51LuFeNzd5fUeIem8y1/RklciCK8YxwuJpJ8tU9ZjcT5v0czjp7s
X-Pg-Spam-Score: -1.2 (-)
X-Archive-Number: 201210/23
X-Sequence-Number: 47982

This is a multipart message in MIME format.

------=_NextPart_000_0058_01CDA217.333C26A0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,

I have a table with about 10 millions of records, this table is update and
inserted very often during the day (approx. 200 per second) , in the night
the activity is a lot less, so in the first seconds of a day (00:00:01) a
batch process update  some columns (used like counters) of this table
setting his value to 0.

 

Yesterday, the first time it occurs, I got a deadlock when other process try
to delete multiple (about 10 or 20) rows of the same table.

 

I think that maybe the situation was:

 

Process A (PA) (massive update)

Process B (PB) (multiple delete)

 

PA Block record 1, update

PA Block record 2, update

PA Block record 3, update

PB Block record 4, delete

PB Block record 5, delete

PA Block record 4, waiting

PB Block record 3, waiting

 

The other situation could be that update process while blocking rows scale
to block page and the try to scale to lock table while the delete process as
some locked rows.

 

Any ideas how to prevent this situation?

 

Thanks!


------=_NextPart_000_0058_01CDA217.333C26A0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta =
http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 14 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EstiloCorreo17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri","sans-serif";}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-US link=3Dblue =
vlink=3Dpurple><div class=3DWordSection1><p =
class=3DMsoNormal>Hi,<o:p></o:p></p><p class=3DMsoNormal>I have a table =
with about 10 millions of records, this table is update and inserted =
very often during the day (approx. 200 per second) , in the night the =
activity is a lot less, so in the first seconds of a day (00:00:01) a =
batch process update &nbsp;some columns (used like counters) of this =
table setting his value to 0.<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Yesterday, =
the first time it occurs, I got a deadlock when other process try to =
delete multiple (about 10 or 20) rows of the same =
table.<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>I think that maybe the situation was:<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Process A =
(PA) (massive update)<o:p></o:p></p><p class=3DMsoNormal>Process B (PB) =
(multiple delete)<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>PA Block =
record 1, update<o:p></o:p></p><p class=3DMsoNormal>PA Block record 2, =
update<o:p></o:p></p><p class=3DMsoNormal>PA Block record 3, =
update<o:p></o:p></p><p class=3DMsoNormal>PB Block record 4, =
delete<o:p></o:p></p><p class=3DMsoNormal>PB Block record 5, =
delete<o:p></o:p></p><p class=3DMsoNormal>PA Block record 4, =
waiting<o:p></o:p></p><p class=3DMsoNormal>PB Block record 3, =
waiting<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>The other situation could be that update process while =
blocking rows scale to block page and the try to scale to lock table =
while the delete process as some locked rows.<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>Any ideas =
how to prevent this situation?<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Thanks!<o:p></o:p></p></div></body></html>
------=_NextPart_000_0058_01CDA217.333C26A0--





From jeff.janes@gmail.com Thu Oct  4 16:10:13 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jeff.janes@gmail.com>) id 1TJo05-0004Dl-9z
	for pgsql-performance@postgresql.org; Thu, 04 Oct 2012 16:10:13 +0000
Received: from mail-ie0-f174.google.com ([209.85.223.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jeff.janes@gmail.com>) id 1TJo02-0004b2-DM
	for pgsql-performance@postgresql.org; Thu, 04 Oct 2012 16:10:12 +0000
Received: by mail-ie0-f174.google.com with SMTP id k13so1326090iea.19
	for <pgsql-performance@postgresql.org>;
	Thu, 04 Oct 2012 09:10:08 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=lM4STKbHHLlBPuqSiQMON8wITh9Vcqc7LADUviupXHc=;
	b=UuEdV62s3Rutln2w+Rw9mCLqPDgpOk0Ex1aVn7vN/9XJ7ljOZen0N8QUQKXRpGgaJz
	F+hvDJDop55J1B65uuTFZNW+6Y3DNGyUbzbOgwgYTiRAm5JrZKSzEBFJG/E8zXF7XmcJ
	Ro3EzgOa2cRlne85Hx+ENZRZi02ixBJIwe8sLq2Ld1ehzTRoUxT8SUxRP+1pUvp3agt1
	c7FebqkOzEBgQMUrMSf9CNx92M1Qs9Sd5BA77sxKreaT1Kn1ZlIMvbYvUuRDXEbhBu4G
	hxlb7cs0basjkZr8HUvFc0NeujGn23l5VuWByU7oDTk3c11mpbwvWUlw6enwnq0+3lK1
	AbDw==
MIME-Version: 1.0
Received: by 10.43.16.67 with SMTP id px3mr4822510icb.17.1349367008872; Thu,
	04 Oct 2012 09:10:08 -0700 (PDT)
Received: by 10.42.255.136 with HTTP; Thu, 4 Oct 2012 09:10:08 -0700 (PDT)
In-Reply-To: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
Date: Thu, 4 Oct 2012 09:10:08 -0700
Message-ID: 
 <CAMkU=1x_tM6ujEY39FUk=Lj2=NoT+-vJzoFMtwuKPN8Jd5PqrQ@mail.gmail.com>
Subject: Re: how to avoid deadlock on masive update with multiples delete
From: Jeff Janes <jeff.janes@gmail.com>
To: Anibal David Acosta <aa@devshock.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201210/24
X-Sequence-Number: 47983

On Thu, Oct 4, 2012 at 7:01 AM, Anibal David Acosta <aa@devshock.com> wrote:
> Hi,
>
> I have a table with about 10 millions of records, this table is update and
> inserted very often during the day (approx. 200 per second) , in the night
> the activity is a lot less, so in the first seconds of a day (00:00:01) a
> batch process update  some columns (used like counters) of this table
> setting his value to 0.
>
>
>
> Yesterday, the first time it occurs, I got a deadlock when other process try
> to delete multiple (about 10 or 20) rows of the same table.
...
>
> Any ideas how to prevent this situation?

The bulk update could take an Exclusive (not Access Exclusive) lock.
Or the delete could perhaps be arranged to delete the records in ctid
order (although that might still deadlock).  Or you could just repeat
the failed transaction.

Cheers,

Jeff




From ineyman@perceptron.com Fri Oct  5 14:06:51 2012
Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ineyman@perceptron.com>) id 1TK8YF-00040a-FZ
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 14:06:51 +0000
Received: from ihub.perceptron.com ([68.22.13.201])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ineyman@perceptron.com>) id 1TK8Y9-0006hV-Cj
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 14:06:51 +0000
Received: from SSL [192.168.4.247] (ineyman@perceptron.com) by Office-Logic
	InterChange for <pgsql-performance@postgresql.org>;
	Fri, 05 Oct 2012 10:06:43 -0400
Received: from MAIL.corp.perceptron.com ([fe80::8cf3:7e97:dea9:c4c9]) by
	mail.corp.perceptron.com ([fe80::8cf3:7e97:dea9:c4c9%16]) with mapi id
	14.02.0318.001; Fri, 5 Oct 2012 10:06:42 -0400
From: Igor Neyman <ineyman@perceptron.com>
To: Anibal David Acosta <aa@devshock.com>, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
Subject: Re: how to avoid deadlock on masive update with multiples delete
Thread-Topic: how to avoid deadlock on masive update with multiples delete
Thread-Index: Ac2iOLgOTRNZ/NTwQ1a1sc4S9b6bRAAyWzoA
Date: Fri, 5 Oct 2012 14:06:41 +0000
Message-ID: 
 <A76B25F2823E954C9E45E32FA49D70EC08EB763C@mail.corp.perceptron.com>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
In-Reply-To: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
x-originating-ip: [192.168.2.159]
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-Pg-Spam-Score: -4.0 (----)
X-Archive-Number: 201210/26
X-Sequence-Number: 47985

From: Anibal David Acosta [mailto:aa@devshock.com]=20
Sent: Thursday, October 04, 2012 10:01 AM
To: pgsql-performance@postgresql.org
Subject: how to avoid deadlock on masive update with multiples delete

.....
.....=20
.....

The other situation could be that update process while blocking rows scale =
to block page and the try to scale to lock table while the delete process a=
s some locked rows.

Thanks!


This (lock escalation from row -> to page -> to table) is MS SQL Server "fe=
ature", pretty sure Postgres does not do it.

Regards,
Igor Neyman




From klaussfreire@gmail.com Fri Oct  5 14:27:16 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <klaussfreire@gmail.com>) id 1TK8rz-00018D-Ql
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 14:27:16 +0000
Received: from mail-bk0-f46.google.com ([209.85.214.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <klaussfreire@gmail.com>) id 1TK8ry-0006pk-Lp
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 14:27:15 +0000
Received: by mail-bk0-f46.google.com with SMTP id jk13so963095bkc.19
	for <pgsql-performance@postgresql.org>;
	Fri, 05 Oct 2012 07:27:13 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=EY9cL/ZIiWOZl+yPauiOiA9/coWFCxE0EyVvEPWCxKE=;
	b=mmJ04xEN9y2t+1/BhioUqBFwoV99DDNwn/tqYAuRcAoTUvoFbxBQ/SFjMDGO23lckf
	Rk91oOR3T6c0yjcJrAtCYlghnfFobu8WUvZ/GxO/CA0pacoQl0Rj4E1nz2KGv+hi+59W
	nl10629/gpjOoLBEi4sYHqJPc2RGVgbzGhgp8Gwv1FuCg2Y58ZqTvDhPebi+jg4aT2YX
	9F4tdVoxGHf0j898/dX8JC6FST+IilcAKdxAjKrlADJGV/4AofgxzcOqNIdoP1NjoTdn
	T8nsa0kouKSvhe2YhoYV/0+T4JQz5nTU4Mn4ZBh77XhU32/ydVPtDrFfE2UMOTN9K/F/
	6RwA==
MIME-Version: 1.0
Received: by 10.204.9.149 with SMTP id l21mr2897024bkl.57.1349447233096; Fri,
	05 Oct 2012 07:27:13 -0700 (PDT)
Received: by 10.204.156.196 with HTTP; Fri, 5 Oct 2012 07:27:13 -0700 (PDT)
In-Reply-To: 
 <CAMkU=1x_tM6ujEY39FUk=Lj2=NoT+-vJzoFMtwuKPN8Jd5PqrQ@mail.gmail.com>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
	<CAMkU=1x_tM6ujEY39FUk=Lj2=NoT+-vJzoFMtwuKPN8Jd5PqrQ@mail.gmail.com>
Date: Fri, 5 Oct 2012 11:27:13 -0300
Message-ID: 
 <CAGTBQpbK3K56Jzx_xU3ch1QB2TcnbwmQUd0T_98V6BoScGHOMg@mail.gmail.com>
Subject: Re: how to avoid deadlock on masive update with multiples delete
From: Claudio Freire <klaussfreire@gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Anibal David Acosta <aa@devshock.com>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201210/27
X-Sequence-Number: 47986

On Thu, Oct 4, 2012 at 1:10 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> The bulk update could take an Exclusive (not Access Exclusive) lock.
> Or the delete could perhaps be arranged to delete the records in ctid
> order (although that might still deadlock).  Or you could just repeat
> the failed transaction.

How do you make pg update/delete records, in bulk, in some particular order?

(ie, without issuing separate queries for each record)




From m.sakrejda@gmail.com Fri Oct  5 15:08:22 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <m.sakrejda@gmail.com>) id 1TK9Vm-0005DU-Hs
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:08:22 +0000
Received: from mail-qc0-f174.google.com ([209.85.216.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <m.sakrejda@gmail.com>) id 1TK9Vk-0007Og-Tg
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:08:21 +0000
Received: by mail-qc0-f174.google.com with SMTP id d3so1108115qch.19
	for <pgsql-performance@postgresql.org>;
	Fri, 05 Oct 2012 08:08:20 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=O59uscl1DYwJM6d1RaH7Z7ao3EHoKNHq8H+0cWZf96k=;
	b=KaPXpJ49nYyBBy5OZZH7nr15KOEzuD18Pwf1Cq5sQndzv43ZTMIh/S+8g8hkPtJjLg
	x1KF9l9ntiRyq0IEuzbqlCL3/ClQA+QUsnAwRYB997qXpFCMW3Wvx0teHoVMykmjDkPW
	dZ6o7Tt0LfEp2WAagT+TVygtipNaAGA+JJm6MWd5YtFoSiwgYD/2CqAxNWIIF11863K3
	lR2ughB4iFKP3tLugyh4SwYvfWOGqq4k7VEoUffAGiXI/DVdTPfUFA727y2Q1sUGDIVU
	saGdeO2nrVfUdcoPPGeRHYU0viDeQpLArkn6pPlAKVspwomXFcbfie4ZxPHJREreqcVA
	RHvw==
MIME-Version: 1.0
Received: by 10.49.1.36 with SMTP id 4mr25509680qej.46.1349449699942; Fri, 05
	Oct 2012 08:08:19 -0700 (PDT)
Received: by 10.49.24.162 with HTTP; Fri, 5 Oct 2012 08:08:19 -0700 (PDT)
In-Reply-To: 
 <CAGTBQpbK3K56Jzx_xU3ch1QB2TcnbwmQUd0T_98V6BoScGHOMg@mail.gmail.com>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
	<CAMkU=1x_tM6ujEY39FUk=Lj2=NoT+-vJzoFMtwuKPN8Jd5PqrQ@mail.gmail.com>
	<CAGTBQpbK3K56Jzx_xU3ch1QB2TcnbwmQUd0T_98V6BoScGHOMg@mail.gmail.com>
Date: Fri, 5 Oct 2012 08:08:19 -0700
Message-ID: 
 <CAOtHd0A0UNJYTeVDsn6uY2d-6f2+=s5EkQFwNV0ASa4PqAk8yw@mail.gmail.com>
Subject: Re: how to avoid deadlock on masive update with multiples delete
From: Maciek Sakrejda <m.sakrejda@gmail.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>, Anibal David Acosta <aa@devshock.com>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201210/28
X-Sequence-Number: 47987

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;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Delete on test  (cost=188.99..242.34 rows=1940 width=34)
   ->  Hash Join  (cost=188.99..242.34 rows=1940 width=34)
         Hash Cond: (x.g = public.test.g)
         ->  Subquery Scan on x  (cost=135.34..159.59 rows=1940 width=32)
	       ->  Sort  (cost=135.34..140.19 rows=1940 width=10)
                     Sort Key: public.test.ctid
                     ->  Seq Scan on test  (cost=0.00..29.40 rows=1940 width=10)
         ->  Hash  (cost=29.40..29.40 rows=1940 width=10)
	       ->  Seq Scan on test  (cost=0.00..29.40 rows=1940 width=10)
(9 rows)




From tgl@sss.pgh.pa.us Fri Oct  5 15:31:48 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TK9sS-0003uS-B4
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:31:48 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TK9sQ-00080z-Jn
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:31:48 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q95FVhwb009896;
	Fri, 5 Oct 2012 11:31:43 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Maciek Sakrejda <m.sakrejda@gmail.com>
cc: Claudio Freire <klaussfreire@gmail.com>,
 Jeff Janes <jeff.janes@gmail.com>,
	Anibal David Acosta <aa@devshock.com>, pgsql-performance@postgresql.org
Subject: Re: how to avoid deadlock on masive update with multiples delete
In-reply-to: 
 <CAOtHd0A0UNJYTeVDsn6uY2d-6f2+=s5EkQFwNV0ASa4PqAk8yw@mail.gmail.com>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
	<CAMkU=1x_tM6ujEY39FUk=Lj2=NoT+-vJzoFMtwuKPN8Jd5PqrQ@mail.gmail.com>
	<CAGTBQpbK3K56Jzx_xU3ch1QB2TcnbwmQUd0T_98V6BoScGHOMg@mail.gmail.com>
	<CAOtHd0A0UNJYTeVDsn6uY2d-6f2+=s5EkQFwNV0ASa4PqAk8yw@mail.gmail.com>
Comments: In-reply-to Maciek Sakrejda <m.sakrejda@gmail.com>
	message dated "Fri, 05 Oct 2012 08:08:19 -0700"
Date: Fri, 05 Oct 2012 11:31:43 -0400
Message-ID: <9895.1349451103@sss.pgh.pa.us>
X-Pg-Spam-Score: -4.0 (----)
X-Archive-Number: 201210/29
X-Sequence-Number: 47988

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.

			regards, tom lane




From andres@2ndquadrant.com Fri Oct  5 15:36:28 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andres@2ndquadrant.com>) id 1TK9wy-0005kt-3H
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:36:28 +0000
Received: from moutng.kundenserver.de ([212.227.17.8])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andres@2ndquadrant.com>) id 1TK9ww-0007po-BF
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:36:27 +0000
Received: from alap2.localnet (p549F1993.dip.t-dialin.net [84.159.25.147])
	by mrelayeu.kundenserver.de (node=mrbap4) with ESMTP (Nemesis)
	id 0LdmTL-1Tk6te0uBP-00iWrN; Fri, 05 Oct 2012 17:36:17 +0200
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>
X-Provags-ID: V02:K0:W/8BmYVeInhhbYwsoXXLsipQdjd2ttpSdGPAYo/WWJ5
	CTpJUbToYmrZomgIZalcYwSPAaz+IZBN/UV1d7EJ8GGMqOoDeH
	MeACJz8DFRhr8XqTSV3pNJevIlfMC2ag4xpNGQuB4kaHyHN8UA
	nW5q/l7iSy23FGiBtwnyOSkuihp1scvRHSvWWcPk2l4X5CVd9u
	/qn9E97YRi8wYa9en9X/ZcePwATmndrf43ZA6dWXpk43Ort/6s
	tURRiXSJxng+PxzbmpV6Q2+L86Fa23aF85QjkV3xE7ERL//GBp
	MbZkbev00otE+iEftp+eZ1cRXiJ5czueihYR61SfawsvFjDIfb
	/xMjCbv02ZB48oW2XGA81fZ1VWKH+QdN0UiGjNHtOk9p2RRhn2
	6IP0kB/0b0dSw==
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201210/30
X-Sequence-Number: 47989

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




From tgl@sss.pgh.pa.us Fri Oct  5 15:46:11 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TKA6N-0002Cl-HO
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:46:11 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TKA6M-0007z2-6X
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:46:10 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q95Fk5fb010194;
	Fri, 5 Oct 2012 11:46:05 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@2ndquadrant.com>
cc: pgsql-performance@postgresql.org, Maciek Sakrejda <m.sakrejda@gmail.com>,
	Claudio Freire <klaussfreire@gmail.com>, Jeff Janes <jeff.janes@gmail.com>,
	Anibal David Acosta <aa@devshock.com>
Subject: Re: how to avoid deadlock on masive update with multiples delete
In-reply-to: <201210051736.16460.andres@2ndquadrant.com>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
	<CAOtHd0A0UNJYTeVDsn6uY2d-6f2+=s5EkQFwNV0ASa4PqAk8yw@mail.gmail.com>
	<9895.1349451103@sss.pgh.pa.us>
	<201210051736.16460.andres@2ndquadrant.com>
Comments: In-reply-to Andres Freund <andres@2ndquadrant.com>
	message dated "Fri, 05 Oct 2012 17:36:14 +0200"
Date: Fri, 05 Oct 2012 11:46:05 -0400
Message-ID: <10193.1349451965@sss.pgh.pa.us>
X-Pg-Spam-Score: -4.0 (----)
X-Archive-Number: 201210/31
X-Sequence-Number: 47990

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.  The problem is that of the several ways the planner
might choose to join the subquery output to the original table, not all
will produce the join rows in the same order as the subquery's result
is.  For instance, when I tried his example I initially got

 Delete on test  (cost=400.88..692.85 rows=18818 width=34)
   ->  Merge Join  (cost=400.88..692.85 rows=18818 width=34)
         Merge Cond: (test.g = x.g)
         ->  Sort  (cost=135.34..140.19 rows=1940 width=10)
               Sort Key: test.g
               ->  Seq Scan on test  (cost=0.00..29.40 rows=1940 width=10)
         ->  Sort  (cost=265.53..270.38 rows=1940 width=32)
               Sort Key: x.g
               ->  Subquery Scan on x  (cost=135.34..159.59 rows=1940 width=32)
                     ->  Sort  (cost=135.34..140.19 rows=1940 width=10)
                           Sort Key: test_1.ctid
                           ->  Seq Scan on test test_1  (cost=0.00..29.40 rows=1940 width=10)

which is going to do the deletes in "g" order, not ctid order;
and then after an ANALYZE I got

 Delete on test  (cost=90.83..120.58 rows=1000 width=34)
   ->  Hash Join  (cost=90.83..120.58 rows=1000 width=34)
         Hash Cond: (test.g = x.g)
         ->  Seq Scan on test  (cost=0.00..16.00 rows=1000 width=10)
         ->  Hash  (cost=78.33..78.33 rows=1000 width=32)
               ->  Subquery Scan on x  (cost=65.83..78.33 rows=1000 width=32)
                     ->  Sort  (cost=65.83..68.33 rows=1000 width=10)
                           Sort Key: test_1.ctid
                           ->  Seq Scan on test test_1  (cost=0.00..16.00 rows=1000 width=10)

which is going to do the deletes in ctid order, but that's an artifact
of using a seqscan on the test table; the order of the subquery's output
is irrelevant, since it got hashed.

> 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.  Personally, if I had to
deal with this I'd use a plpgsql function (or DO command) that does

	FOR c IN SELECT ctid FROM table WHERE ... ORDER BY ... LOOP
		DELETE FROM table WHERE ctid = c;
	END LOOP;

which is not great but at least it avoids client-to-server traffic.

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.

			regards, tom lane




From mmoncure@gmail.com Fri Oct  5 15:55:42 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mmoncure@gmail.com>) id 1TKAFa-00065c-2Y
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:55:42 +0000
Received: from mail-vc0-f174.google.com ([209.85.220.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mmoncure@gmail.com>) id 1TKAFY-000887-6E
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:55:41 +0000
Received: by mail-vc0-f174.google.com with SMTP id fo13so2162490vcb.19
	for <pgsql-performance@postgresql.org>;
	Fri, 05 Oct 2012 08:55:39 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=9sScSrk/vv2alwVy+OBg6fFGuYKV0N8oHeC4jrybHX8=;
	b=TLJStuTLAGtfEuFYKExB0t6yT2z0TsBESxkfFSeRrXZ9Da7umIGhvxFRWts3rKmp/B
	AztB8v6hrrEIAEJ9hyuneo3xlO2Z+4RYgH1KK7fdtBcjNMDJ3ffaFmmUdqeDJfbVXL9g
	e2agFy0jPWXXJrc/oJBjDYVV7igcSUy6iIg86kYpDzZXuRBdYCzvkXDS8H+J2dttkXbi
	W9LIo/0bwDt77jAuHP7lWvbTyA1rNcXKCUf5fS27yOXVzVauDWiniK16K+tKHldrFqnQ
	k+CV+LWdmhmcKdFDB03efybe86HW4wkcVx+faWAO3XYNFbTvf2rx63Db9q3SBZhcgRuW
	hgyA==
MIME-Version: 1.0
Received: by 10.58.13.33 with SMTP id e1mr5544441vec.51.1349452539104; Fri, 05
	Oct 2012 08:55:39 -0700 (PDT)
Received: by 10.58.245.200 with HTTP; Fri, 5 Oct 2012 08:55:39 -0700 (PDT)
In-Reply-To: <10193.1349451965@sss.pgh.pa.us>
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>
Date: Fri, 5 Oct 2012 10:55:39 -0500
Message-ID: 
 <CAHyXU0yZ+qvuE_erWx86GSzTE6KTH3rtc-KEyDgJaTYM5Smx3g@mail.gmail.com>
Subject: Re: how to avoid deadlock on masive update with multiples delete
From: Merlin Moncure <mmoncure@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andres Freund <andres@2ndquadrant.com>, pgsql-performance@postgresql.org,
	Maciek Sakrejda <m.sakrejda@gmail.com>,
	Claudio Freire <klaussfreire@gmail.com>,
	Jeff Janes <jeff.janes@gmail.com>, Anibal David Acosta <aa@devshock.com>
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201210/32
X-Sequence-Number: 47991

On Fri, Oct 5, 2012 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> 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.  The problem is that of the several ways the planner
> might choose to join the subquery output to the original table, not all
> will produce the join rows in the same order as the subquery's result
> is.  For instance, when I tried his example I initially got
>
>  Delete on test  (cost=400.88..692.85 rows=18818 width=34)
>    ->  Merge Join  (cost=400.88..692.85 rows=18818 width=34)
>          Merge Cond: (test.g = x.g)
>          ->  Sort  (cost=135.34..140.19 rows=1940 width=10)
>                Sort Key: test.g
>                ->  Seq Scan on test  (cost=0.00..29.40 rows=1940 width=10)
>          ->  Sort  (cost=265.53..270.38 rows=1940 width=32)
>                Sort Key: x.g
>                ->  Subquery Scan on x  (cost=135.34..159.59 rows=1940 width=32)
>                      ->  Sort  (cost=135.34..140.19 rows=1940 width=10)
>                            Sort Key: test_1.ctid
>                            ->  Seq Scan on test test_1  (cost=0.00..29.40 rows=1940 width=10)
>
> which is going to do the deletes in "g" order, not ctid order;
> and then after an ANALYZE I got
>
>  Delete on test  (cost=90.83..120.58 rows=1000 width=34)
>    ->  Hash Join  (cost=90.83..120.58 rows=1000 width=34)
>          Hash Cond: (test.g = x.g)
>          ->  Seq Scan on test  (cost=0.00..16.00 rows=1000 width=10)
>          ->  Hash  (cost=78.33..78.33 rows=1000 width=32)
>                ->  Subquery Scan on x  (cost=65.83..78.33 rows=1000 width=32)
>                      ->  Sort  (cost=65.83..68.33 rows=1000 width=10)
>                            Sort Key: test_1.ctid
>                            ->  Seq Scan on test test_1  (cost=0.00..16.00 rows=1000 width=10)
>
> which is going to do the deletes in ctid order, but that's an artifact
> of using a seqscan on the test table; the order of the subquery's output
> is irrelevant, since it got hashed.
>
>> 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.  Personally, if I had to
> deal with this I'd use a plpgsql function (or DO command) that does

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')

merlin




From klaussfreire@gmail.com Fri Oct  5 15:58:55 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <klaussfreire@gmail.com>) id 1TKAIh-0007bE-2W
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:58:55 +0000
Received: from mail-bk0-f46.google.com ([209.85.214.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <klaussfreire@gmail.com>) id 1TKAIf-00088Q-3c
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 15:58:53 +0000
Received: by mail-bk0-f46.google.com with SMTP id jk13so1017220bkc.19
	for <pgsql-performance@postgresql.org>;
	Fri, 05 Oct 2012 08:58:52 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=HndDKITy8MGd47NQI6D3/DrPJF6EHp1DQPz/OjKnzJM=;
	b=RjiMuluAgUpJFGbeN6jWqdhqubRG1Auly91KJlieUEBAv7dU/2Xq+zTSeWzRz3vzvk
	Uj3PkFpf0DYVoYlJQAmQCy0TyrnolQYRVg831JpnodQSkGrpLVMkTjKtPK6EMBggOwsD
	Fa8vb41AouA4hI9QDwbtKlZq3DgtCWba1A+Yduyn+OAkcxgXwqnwHGi5qor3pCD1ixCU
	0Mek4Mk5Euiubff6wY89TYMHnURllgC4Cyn/hyRoYKEo1zXifVDHIFHjk1vPFQ0Qqx97
	IwCsyxnmpG3wnFSXVseogJovPwZhRusCtCS+UVgnw1M+onPqElcorMtyAWFM1qKZilFc
	YXFg==
MIME-Version: 1.0
Received: by 10.204.9.149 with SMTP id l21mr3004575bkl.57.1349452732080; Fri,
	05 Oct 2012 08:58:52 -0700 (PDT)
Received: by 10.204.156.196 with HTTP; Fri, 5 Oct 2012 08:58:52 -0700 (PDT)
In-Reply-To: <10193.1349451965@sss.pgh.pa.us>
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>
Date: Fri, 5 Oct 2012 12:58:52 -0300
Message-ID: 
 <CAGTBQpaN6zwr=-EQYYC4cR4iK0PaKTTckA3ChBzppu90rYwJuQ@mail.gmail.com>
Subject: Re: how to avoid deadlock on masive update with multiples delete
From: Claudio Freire <klaussfreire@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andres Freund <andres@2ndquadrant.com>, pgsql-performance@postgresql.org,
	Maciek Sakrejda <m.sakrejda@gmail.com>, Jeff Janes <jeff.janes@gmail.com>,
	Anibal David Acosta <aa@devshock.com>
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201210/33
X-Sequence-Number: 47992

On Fri, Oct 5, 2012 at 12:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>         FOR c IN SELECT ctid FROM table WHERE ... ORDER BY ... LOOP
>                 DELETE FROM table WHERE ctid = c;
>         END LOOP;

Maybe, in that sense, it would be better to optimize client-server
protocol for batch operations.

PREPARE blah(c) AS DELETE FROM table WHERE ctid = $1;

EXECUTE blah(c1), blah(c2), blah(c3), ...
 ^ 1 transaction, 1 roundtrip, multiple queries




From tgl@sss.pgh.pa.us Fri Oct  5 16:21:08 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TKAeC-0005tm-N0
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 16:21:08 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TKAeA-0000KL-Po
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 16:21:08 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q95GL2xe010892;
	Fri, 5 Oct 2012 12:21:02 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Merlin Moncure <mmoncure@gmail.com>
cc: Andres Freund <andres@2ndquadrant.com>, pgsql-performance@postgresql.org,
	Maciek Sakrejda <m.sakrejda@gmail.com>,
	Claudio Freire <klaussfreire@gmail.com>, Jeff Janes <jeff.janes@gmail.com>,
	Anibal David Acosta <aa@devshock.com>
Subject: Re: how to avoid deadlock on masive update with multiples delete
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>
Comments: In-reply-to Merlin Moncure <mmoncure@gmail.com>
	message dated "Fri, 05 Oct 2012 10:55:39 -0500"
Date: Fri, 05 Oct 2012 12:21:01 -0400
Message-ID: <10891.1349454061@sss.pgh.pa.us>
X-Pg-Spam-Score: -4.0 (----)
X-Archive-Number: 201210/34
X-Sequence-Number: 47993

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




From andres@2ndquadrant.com Fri Oct  5 16:33:59 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andres@2ndquadrant.com>) id 1TKAqd-0000R6-3e
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 16:33:59 +0000
Received: from moutng.kundenserver.de ([212.227.17.9])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andres@2ndquadrant.com>) id 1TKAqb-0000BG-AX
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 16:33:58 +0000
Received: from alap2.localnet (p549F1993.dip.t-dialin.net [84.159.25.147])
	by mrelayeu.kundenserver.de (node=mrbap3) with ESMTP (Nemesis)
	id 0MAeNv-1T8N8Z23D9-00BttL; Fri, 05 Oct 2012 18:33:56 +0200
From: Andres Freund <andres@2ndquadrant.com>
Organization: 2ndQuadrant.com
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: how to avoid deadlock on masive update with multiples delete
Date: Fri, 5 Oct 2012 18:33:53 +0200
User-Agent: KMail/1.13.7 (Linux/3.5.0-rc3-andres-00231-g002b758; KDE/4.8.4;
	x86_64; ; )
Cc: pgsql-performance@postgresql.org, 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>
	<201210051736.16460.andres@2ndquadrant.com>
	<10193.1349451965@sss.pgh.pa.us>
In-Reply-To: <10193.1349451965@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201210051833.55615.andres@2ndquadrant.com>
X-Provags-ID: V02:K0:lsrlnEmTjZ3jmYGoQq3zK5IqjPYdbrrPWNARPk1A0lK
	h3KuJczYyPJGiAOnik8AJARhcd0BU4hsJ+2Gmh2IbvLA1GoAoY
	2BXjjGYCie7ImApsQ9kgm9lyjykIzbNSEtGoXH3UJ5Tac4vFTB
	IxkHgEyH0Zow1PyS11HBDUlhGhqQ5N1xEMZfcrLl3hYNg368F2
	sK2CD7Iv2O6VAAI9wSdIzzt0APfM8k/D3W8Od3lzFeSfDhBIhF
	ov1IbZfuayIqtT+v8LqfbTDIIt643mQ+eYUeowSonQ7jt5cRNd
	oqjYe1ZgXY5+ALwp1VUqr7UjnPRzyUNyxAOuljf8HsWOtk5EbS
	BITCAPQN2EuAoLR5PtVz7G3Ha9MHMjNoUiCTPeryFHrqjilUA4
	f4hSJZ7sghZ6g==
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201210/35
X-Sequence-Number: 47994

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




From aa@devshock.com Fri Oct  5 18:33:59 2012
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1TKCil-0000qw-Tg
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 18:33:59 +0000
Received: from mail-gg0-f174.google.com ([209.85.161.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1TKCii-0002Bi-6K
	for pgsql-performance@postgresql.org; Fri, 05 Oct 2012 18:33:59 +0000
Received: by mail-gg0-f174.google.com with SMTP id r5so575762ggn.19
	for <pgsql-performance@postgresql.org>;
	Fri, 05 Oct 2012 11:33:54 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=from:to:cc:references:in-reply-to:subject:date:message-id
	:mime-version:content-type:content-transfer-encoding:x-mailer
	:thread-index:content-language:x-gm-message-state;
	bh=7H9fb27pLYHBhcuO2/FJV32lWo3NFGTEWvVtpux4k+o=;
	b=esS1jHWGPFbMHdPyBgnWl/KaCdg1fjSkxCx/w8HjOBRCnA1KK0cYfip42uPzCSk7HF
	z8sMWKJ/Bvn/0bnEEq50InjD4d7mVnq/dpTyqfMg4tWgd5sv7JQHFTNz/7CiTplOtZjG
	/++OGskufrqHcy49JJgeX4/gpJyiQqBKMprNbHythwgxCNlDyGm9Y3SmUK7gSqIHA0lL
	dYsXtsozwXNkYD7D+07PHrfXvLWh81BABfoJlxawPLMr34Fy/0UhYwTttLf7KKLRP9tX
	gnAHBLhRfpZHXairDAHiFzLXlC3CRI3XG9nZHaE8XRfWPqSMTWzRn7bCbAPo8Ekstq65
	J/iw==
Received: by 10.236.161.233 with SMTP id w69mr9183934yhk.74.1349462034559;
	Fri, 05 Oct 2012 11:33:54 -0700 (PDT)
Received: from AnibalVAIO ([190.128.222.90])
	by mx.google.com with ESMTPS id p1sm3717277anj.13.2012.10.05.11.33.51
	(version=TLSv1/SSLv3 cipher=OTHER);
	Fri, 05 Oct 2012 11:33:53 -0700 (PDT)
From: "Anibal David Acosta" <aa@devshock.com>
To: "'Claudio Freire'" <klaussfreire@gmail.com>,
	"'Jeff Janes'" <jeff.janes@gmail.com>
Cc: <pgsql-performance@postgresql.org>
References: <005701cda238$ba4d0350$2ee709f0$@devshock.com>
	<CAMkU=1x_tM6ujEY39FUk=Lj2=NoT+-vJzoFMtwuKPN8Jd5PqrQ@mail.gmail.com>
	<CAGTBQpbK3K56Jzx_xU3ch1QB2TcnbwmQUd0T_98V6BoScGHOMg@mail.gmail.com>
In-Reply-To: 
 <CAGTBQpbK3K56Jzx_xU3ch1QB2TcnbwmQUd0T_98V6BoScGHOMg@mail.gmail.com>
Subject: Re: how to avoid deadlock on masive update with multiples delete
Date: Fri, 5 Oct 2012 14:33:49 -0400
Message-ID: <011201cda327$f99d73b0$ecd85b10$@devshock.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQHzC99ysmZx4K05UeiKZt/Jz1ndJAFwPJ/CAjL01aGXQx14wA==
Content-Language: es-py
X-Gm-Message-State: 
 ALoCoQkiIzzqQ90P0VtrxXa7lIKvrVnQZCNtjaCVkKKgSzSrsJkGdm6JazZ7NT7DuqJtk0eLlcGr
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201210/36
X-Sequence-Number: 47995

Process 1 (massive update): update table A set column1=0, column2=0 

Process 2 (multiple delete): perform delete_row(user_name, column1, column2)
from table A where user_name=YYY

The pgsql function delete_row delete the row and do other business logic not
related to table A.



-----Mensaje original-----
De: Claudio Freire [mailto:klaussfreire@gmail.com] 
Enviado el: viernes, 05 de octubre de 2012 10:27 a.m.
Para: Jeff Janes
CC: Anibal David Acosta; pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] how to avoid deadlock on masive update with multiples
delete

On Thu, Oct 4, 2012 at 1:10 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> The bulk update could take an Exclusive (not Access Exclusive) lock.
> Or the delete could perhaps be arranged to delete the records in ctid 
> order (although that might still deadlock).  Or you could just repeat 
> the failed transaction.

How do you make pg update/delete records, in bulk, in some particular order?

(ie, without issuing separate queries for each record)