Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

Hi Gunther

On 07-Sep.-2020 14:04, raj(at)gusw(dot)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
>