From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Assorted improvements in pg_dump
In-reply-to: <20211021142852.GD741@telsasoft.com>
References: <20211021142852.GD741@telsasoft.com>
Comments: In-reply-to Justin Pryzby <pryzby@telsasoft.com>
	message dated "Thu, 21 Oct 2021 09:28:52 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2408274.1634828038.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 21 Oct 2021 10:53:58 -0400
Message-ID: <2408275.1634828038@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2408275.1634828038%40sss.pgh.pa.us>
Precedence: bulk

Justin Pryzby <pryzby@telsasoft.com> writes:
> On Wed, Oct 20, 2021 at 05:14:45PM -0400, Tom Lane wrote:
>> Arguably, 0003 is a bug fix that we should consider back-patching.
>> However, I've not heard field reports of the problems it fixes,
>> so maybe there's no need to bother.

> FYI, I see this issue happen in production environment.
> I never reported it, since I think it's a documented issue, and it's no =
great
> problem, so long as it runs the next day.  But it'd be a pain to find th=
at the
> backup was incomplete when we needed it.  Also, I use the backups to mig=
rate to
> new servers, and it would be a pain to start the job at a calculated tim=
e
> expecting it to finish at the beginning of a coordinated maintenance win=
dow,
> but then discover that it had failed and needs to be rerun with fingers
> crossed.

Yeah, if you're dropping tables all the time, pg_dump is going to have
a problem with that.  The fix I'm suggesting here would only ensure
that you get a "clean" failure at the LOCK TABLE command --- but from
an operational standpoint, that's little improvement.

The natural response is to consider retrying the whole dump after a lock
failure.  I'm not sure if it'd be practical to do that within pg_dump
itself, as opposed to putting a loop in whatever script calls it.

			regards, tom lane