Content-Type: multipart/alternative;
 boundary="------------4H0qDf4V3tmFkJQumHJysFS9"
Message-ID: <7b30c39f-2d36-e540-7494-c41b7e8b7179@gmail.com>
Date: Fri, 23 Sep 2022 10:04:56 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.12.0
Subject: Re: Delete from locking ordering differences
Content-Language: en-CA
To: pgsql-sql@lists.postgresql.org
References: 
 <CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com>
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: 
 <CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/7b30c39f-2d36-e540-7494-c41b7e8b7179%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------4H0qDf4V3tmFkJQumHJysFS9
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 9/23/22 01:31, Peter Hendriks wrote:
> We are wondering if anyone can explain the difference we are having in 
> production with the following queries:
>
> DELETE FROM store
> WHERE id IN (
>   SELECT id FROM store
>   FOR UPDATE SKIP LOCKED
>   ORDER BY ID
>   LIMIT 1000
> )
> RETURNING id, payload
>
> This query is sometimes executed with high concurrency, and then can 
> hang indefinitely, we assume because of a locking problem that 
> postgresql is not detecting as a deadlock.
>
> This alternative query does not have the hanging problem:
>
> WITH store_ids AS (
>   SELECT id FROM store
>   FOR UPDATE SKIP LOCKED
>   ORDER BY ID
>   LIMIT 1000
> )
> DELETE FROM store s
> USING store_ids si
> WHERE s.id <http://s.id> = si.id <http://si.id>
> RETURNING s.id <http://s.id>, s.payload
>
> Can anyone explain why the first query is expected to fail (hang), and 
> the second query does not have this problem? We would be interested in 
> more understanding on this. Thanks!

The "optimizatin fence" nature of CTEs appears to be a win in this 
case.  Why the "order by"?  I assume these are down within a transaction?

--------------4H0qDf4V3tmFkJQumHJysFS9
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 9/23/22 01:31, Peter Hendriks wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">We are wondering if anyone can explain the
        difference we are having in production with the following
        queries:
        <div><br>
        </div>
        <div>DELETE FROM store</div>
        <div>WHERE id IN (</div>
        <div>  SELECT id FROM store </div>
        <div>  FOR UPDATE SKIP LOCKED </div>
        <div>  ORDER BY ID</div>
        <div>  LIMIT 1000</div>
        <div>)</div>
        <div>RETURNING id, payload</div>
        <div><br>
        </div>
        <div>This query is sometimes executed with high concurrency, and
          then can hang indefinitely, we assume because of a locking
          problem that postgresql is not detecting as a deadlock.</div>
        <div><br>
        </div>
        <div>This alternative query does not have the hanging problem:</div>
        <div><br>
        </div>
        <div>WITH store_ids AS (</div>
        <div>
          <div>  SELECT id FROM store </div>
          <div>  FOR UPDATE SKIP LOCKED </div>
          <div>  ORDER BY ID</div>
          <div>  LIMIT 1000</div>
        </div>
        <div>)</div>
        <div>
          <div>DELETE FROM store s</div>
          <div>USING store_ids si </div>
          <div>WHERE <a href="http://s.id" moz-do-not-send="true">s.id</a>
            = <a href="http://si.id" moz-do-not-send="true">si.id</a></div>
          <div>RETURNING <a href="http://s.id" moz-do-not-send="true">s.id</a>,
            s.payload</div>
        </div>
        <div><br>
        </div>
        <div>Can anyone explain why the first query is expected to fail
          (hang), and the second query does not have this problem? We
          would be interested in more understanding on this. Thanks! </div>
      </div>
    </blockquote>
    <br>
    <font face="Courier New, Courier, monospace">The "optimizatin fence"
      nature of CTEs appears to be a win in this case.  Why the "order
      by"?  I assume these are down within a transaction?<br>
      <br>
    </font>
  </body>
</html>

--------------4H0qDf4V3tmFkJQumHJysFS9--