Horton

Thread

Commits

  1. pg_dump: avoid unsafe function calls in getPolicies().

  2. Postpone calls of unsafe server-side functions in pg_dump.

  1. [BUG] pg_dump blocked

    Gilles DAROLD <gilles@darold.net> — 2022-11-17T16:43:38Z

    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

  2. Re: [BUG] pg_dump blocked

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-11-17T16:59:18Z

    Gilles Darold <gilles@darold.net> writes:
> 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.

I didn't want to back-patch e3fcbbd62 at the time, but it's probably aged
long enough now to be safe to back-patch.  If we do anything here,
it should be to back-patch the whole thing, else we've only partially
fixed the issue.

			regards, tom lane

  3. Re: [BUG] pg_dump blocked

    Gilles DAROLD <gilles@darold.net> — 2022-11-17T17:03:55Z

    Le 17/11/2022 à 17:59, Tom Lane a écrit :
> Gilles Darold <gilles@darold.net> writes:
>> 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.
> I didn't want to back-patch e3fcbbd62 at the time, but it's probably aged
> long enough now to be safe to back-patch.  If we do anything here,
> it should be to back-patch the whole thing, else we've only partially
> fixed the issue.


I can handle this work.

-- 
Gilles Darold

  4. Re: [BUG] pg_dump blocked

    Gilles DAROLD <gilles@darold.net> — 2022-11-19T06:30:59Z

    Le 17/11/2022 à 17:59, Tom Lane a écrit :
> Gilles Darold <gilles@darold.net> writes:
>> 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.
> I didn't want to back-patch e3fcbbd62 at the time, but it's probably aged
> long enough now to be safe to back-patch.  If we do anything here,
> it should be to back-patch the whole thing, else we've only partially
> fixed the issue.


Here are the different patched following the PostgreSQL version from 11 
to 14, they should apply on the corresponding stable branches. The 
patches only concern the move of the unsafe functions, 
pg_get_partkeydef() and pg_get_expr(). They should all apply without 
problem on their respective branch, pg_dump tap regression test passed 
on all versions.

Regards,

-- 
Gilles Darold

  5. Re: [BUG] pg_dump blocked

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-11-19T17:02:32Z

    Gilles Darold <gilles@darold.net> writes:
> Le 17/11/2022 à 17:59, Tom Lane a écrit :
>> I didn't want to back-patch e3fcbbd62 at the time, but it's probably aged
>> long enough now to be safe to back-patch.  If we do anything here,
>> it should be to back-patch the whole thing, else we've only partially
>> fixed the issue.

> Here are the different patched following the PostgreSQL version from 11 
> to 14, they should apply on the corresponding stable branches.

Reviewed and pushed --- thanks for doing the legwork!

Trawling the commit log, I found the follow-on patch 3e6e86abc,
which fixed another issue of the same kind.  I back-patched that
too.

			regards, tom lane