Content-Type: multipart/alternative;
 boundary="------------7BmLFQhaTny0Q0u6DbAmrNy9"
Message-ID: <45c93d57-9973-248e-d2df-e02ca9af48d4@darold.net>
Date: Thu, 17 Nov 2022 17:43:38 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Content-Language: en-US
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
From: Gilles Darold <gilles@darold.net>
Subject: [BUG] pg_dump blocked
Archived-At: 
 <https://www.postgresql.org/message-id/45c93d57-9973-248e-d2df-e02ca9af48d4%40darold.net>
Precedence: bulk

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

Hi,


I have an incorrect behavior with pg_dump prior PG version 15. With 
PostgreSQL 15, thanks to commit e3fcbbd623b9ccc16cdbda374654d91a4727d173 
the problem is gone but for older versions it persists with locks on 
partitioned tables.


When we try to dump a database where a table is locked, pg_dump wait 
until the lock is released, this is expected. Now if the table is 
excluded from the dump using the -T option, obviously pg_dump is not 
concerned by the lock. Unfortunately this is not the case when the table 
is partitioned because of the call to pg_get_partkeydef(), pg_get_expr() 
in the query generated in getTables().  Here is the use case to reproduce.


In a psql session execute:

    BEGIN;

    LOCK TABLE measurement;

then run a pg_dump command excluding the measurement partitions:

     pg_dump -d test -T "measurement*" > /dev/null

it will not end until the lock on the partition is released.

I think the problem is the same if we use a schema exclusion where the 
partitioned table is locked.


Is it possible to consider a backport fix? If yes, does adding the 
table/schema filters in the query generated in getTables() is enough or 
do you think about of a kind of backport of commit 
e3fcbbd623b9ccc16cdbda374654d91a4727d173 ?


Best regards,

-- 
Gilles Darold

--------------7BmLFQhaTny0Q0u6DbAmrNy9
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>
    <p>Hi,</p>
    <p><br>
    </p>
    <p>I have an incorrect behavior with pg_dump prior PG version 15.
      With PostgreSQL 15, thanks to commit
      e3fcbbd623b9ccc16cdbda374654d91a4727d173 the problem is gone but
      for older versions it persists with locks on partitioned tables.</p>
    <p><br>
    </p>
    <p>When we try to dump a database where a table is locked, pg_dump
      wait until the lock is released, this is expected. Now if the
      table is excluded from the dump using the -T option, obviously
      pg_dump is not concerned by the lock. Unfortunately this is not
      the case when the table is partitioned because of the call to
      pg_get_partkeydef(), pg_get_expr() in the query generated in
      getTables().  Here is the use case to reproduce.</p>
    <p><br>
    </p>
    <p>In a psql session execute:</p>
    <blockquote>
      <p>BEGIN;</p>
      LOCK TABLE measurement;</blockquote>
    <p>then run a pg_dump command excluding the measurement partitions:</p>
    <p>    pg_dump -d test -T "measurement*" &gt; /dev/null</p>
    <p>it will not end until the lock on the partition is released.</p>
    <p>I think the problem is the same if we use a schema exclusion
      where the partitioned table is locked. <br>
    </p>
    <p><br>
    </p>
    <p>Is it possible to consider a backport fix? If yes, does adding
      the table/schema filters in the query generated in getTables() is
      enough or do you think about of a kind of backport of commit
      e3fcbbd623b9ccc16cdbda374654d91a4727d173 ?</p>
    <p><br>
    </p>
    <p>Best regards,<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Gilles Darold
</pre>
  </body>
</html>

--------------7BmLFQhaTny0Q0u6DbAmrNy9--