Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kGnnh-0002K7-KN for pgsql-performance@arkaria.postgresql.org; Fri, 11 Sep 2020 18:29:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kGnng-0004KE-GY for pgsql-performance@arkaria.postgresql.org; Fri, 11 Sep 2020 18:29:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kGnng-0004I3-8o for pgsql-performance@lists.postgresql.org; Fri, 11 Sep 2020 18:29:32 +0000 Received: from mail4.network-systems-solutions.net ([162.222.184.250]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kGnnW-0006Bf-NK for pgsql-performance@lists.postgresql.org; Fri, 11 Sep 2020 18:29:28 +0000 Received: from messaging.network-systems-solutions.net (unknown [192.168.242.37]) by mail4.network-systems-solutions.net (Postfix) with ESMTP id DB9A8120A0E; Fri, 11 Sep 2020 19:29:16 +0100 (BST) Received: from aox (localhost [127.0.0.1]) by messaging.network-systems-solutions.net (Postfix) with ESMTP id 45C048E0614; Fri, 11 Sep 2020 14:29:18 -0400 (EDT) Received: from jim@talentstack.to by aox (Archiveopteryx 3.2.0) with esmtpsa id 1599848957-20039-20035/6/287; Fri, 11 Sep 2020 18:29:17 +0000 Subject: Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED To: raj@gusw.net, pgsql-performance@lists.postgresql.org References: <5e5ba420-9365-265d-c62a-4586cfb7c418@talentstack.to> <0e537a50-a43f-3404-27ed-3de9c3f7c281@talentstack.to> From: Jim Jarvie Message-Id: <4c2ee3f0-4f6a-f9d7-34fd-11c91f2f34f2@talentstack.to> Date: Fri, 11 Sep 2020 14:29:06 -0400 User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:68.0) Gecko/20100101 Thunderbird/68.12.0 Mime-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary=------------B77B5F0C50FF0737EEBF6CDD Content-Language: en-CA X-NSSLTD-SpamTemp: 1271481 X-Spam-Score: -1.105 () BAYES_00,HTML_MESSAGE,RDNS_NONE,SPF_FAIL X-NSSLTD-Archiving: Added to store 1 as 84539 X-Scanned-By: MIMEDefang 2.71 on 192.168.241.40 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --------------B77B5F0C50FF0737EEBF6CDD Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Hi Gunther On 07-Sep.-2020 14:04, raj@gusw.net wrote: > Hi all, especially Jim Jarvie, I saw your email to me only now on my=20 > related issue. My issue remains this one: ---8<--- > I remain with my original question from 30th of June, to me it feels=20 > like a bug of some sort: > >> "tuple to be locked was already moved to another partition due to=20 >> concurrent update" >> ---8<--- > I still think that it should simply not happen. Don't waste time on=20 > old tuples trying to fetch and lock something that's no longer there.=20 > It's a waste of resources. > I'm inclined to agree that the error seems to indicate PostgreSQL knows=20 the row was locked & migrated, so attempting to lock it should not=20 really result in the error when SKIP LOCKED is set (but it should behave=20 as it does when there is no SKIP LOCKED).=C2=A0 For the SKIP LOCKED = case, it=20 should treat the migrated as being exactly the same as already locked. I think this is an edge case on the SKIP LOCKED that is not handled as=20 it should be. Do others agree? Jim > regards, > -Gunther > --------------B77B5F0C50FF0737EEBF6CDD Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Hi Gunther

On 07-Sep.-2020 14:04, raj@gusw.net wrote:
Hi all, especially Jim Jarvie, I saw your email to me only now on my related issue. My issue remains this one:
---8<---
I remain with my original question from 30th of June, to me it feels like a bug of some sort:

"tuple to be locked was already moved to another partition due to concurrent update"

---8<---
I still think that it should simply not happen. Don't waste time on old tuples trying to fetch and lock something that's no longer there. It's a waste of resources.

I'm inclined to agree that the error seems to indicate PostgreSQL knows the row was locked & migrated, so attempting to lock it should not really result in the error when SKIP LOCKED is set (but it should behave as it does when there is no SKIP LOCKED).=C2=A0 = For the SKIP LOCKED case, it should treat the migrated as being exactly the same as already locked.

I think this is an edge case on the SKIP LOCKED that is not handled as it should be.

Do others agree?

Jim



regards,
-Gunther

--------------B77B5F0C50FF0737EEBF6CDD--