pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jim Jarvie <jim@talentstack.to>
To: raj@gusw.net
To: pgsql-performance@lists.postgresql.org
Subject: Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
Date: Fri, 11 Sep 2020 14:29:06 -0400
Message-ID: <4c2ee3f0-4f6a-f9d7-34fd-11c91f2f34f2@talentstack.to> (raw)
In-Reply-To: <eec4db8f-336a-8bb1-fc82-086abadec9bd@gusw.net>
References: <c192f8bf-a747-6ad9-c54d-1bd6febafc4f@talentstack.to>
	<CAHOFxGodYysPhKRwybWksDdxQu0EGVMtgL1dGpLrdXxj382ttg@mail.gmail.com>
	<5e5ba420-9365-265d-c62a-4586cfb7c418@talentstack.to>
	<CAHOFxGq6qCkyDV3DYynxp8BKg4Q5uPQcjUSzXH=OFBnm70S6Og@mail.gmail.com>
	<ccaa5c05-196a-50c4-9d9d-705e3a6bccac@talentstack.to>
	<CAHOFxGo1Aq1+c++1-p1oy2J13D2+oQJ2r=_oXH2F3y-wJ3Uc9Q@mail.gmail.com>
	<e97e1113-afb2-54dc-93f8-75cf8f9559c3@talentstack.to>
	<CAHOFxGp_r5=TLNJX7insLP=L=o24tojKnopRo9fvzKPFxJi8Ww@mail.gmail.com>
	<0e537a50-a43f-3404-27ed-3de9c3f7c281@talentstack.to>
	<eec4db8f-336a-8bb1-fc82-086abadec9bd@gusw.net>

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

view thread (21+ messages)  latest in thread

Message-ID: <4c2ee3f0-4f6a-f9d7-34fd-11c91f2f34f2@talentstack.to>
Permalink:  ../4c2ee3f0-4f6a-f9d7-34fd-11c91f2f34f2@talentstack.to/
Also on:    postgresql.org/message-id/4c2ee3f0-4f6a-f9d7-34fd-11c91f2f34f2@talentstack.to

 · 

reply
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: jim@talentstack.to, raj@gusw.net, pgsql-performance@lists.postgresql.org
  Subject: Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED
  In-Reply-To: <4c2ee3f0-4f6a-f9d7-34fd-11c91f2f34f2@talentstack.to>

* 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