From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nathan Bossart <nathandbossart@gmail.com>
cc: Michael Paquier <michael@paquier.xyz>, Euler Taveira <euler@eulerto.com>,
        pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: improve performance of pg_dump with many sequences
In-reply-to: <ZqpV5Sb7YWauwYnx@nathan>
References: <Zpc5HGb7yDfTNDy5@nathan> <ZpgR4OMpbekeja_n@nathan>
 <3578088.1721242766@sss.pgh.pa.us> <ZpgV32NTM0ADvJop@nathan>
 <3585715.1721243507@sss.pgh.pa.us> <ZpiP3I6UVcgsCCWf@nathan>
 <3640163.1721275101@sss.pgh.pa.us> <ZpldUoP0XjHszExF@nathan>
 <Zpl7HywbF2qAkTVf@nathan> <ZqFJspSbieuTE4X0@nathan> <ZqpV5Sb7YWauwYnx@nathan>
Comments: In-reply-to Nathan Bossart <nathandbossart@gmail.com>
	message dated "Wed, 31 Jul 2024 10:19:01 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2885943.1767029161.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 29 Dec 2025 12:26:01 -0500
Message-ID: <2885944.1767029161@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2885944.1767029161%40sss.pgh.pa.us>
Precedence: bulk

Nathan Bossart <nathandbossart@gmail.com> writes:
> Committed.

In the no-good-deed-goes-unpunished department: pg_dump's use
of pg_get_sequence_data() (nee pg_sequence_read_tuple()) is
evidently responsible for the complaint in bug #19365 [1]
that pg_dump can no longer survive concurrent sequence drops.

Given that that function already silently returns NULLs if the
sequence isn't readable for other reasons, I think it'd be
sane to make it silently return NULL if the sequence isn't
there anymore.  Unfortunately, that looks like it'd require
nontrivial restructuring of init_sequence().

Or maybe we could make it not use init_sequence()?  For the moment
a plain try_relation_open and check that it's a sequence should do,
but I'm not sure how that'd fit into people's plans for future
improvement of the sequence API.

There are other reasons not to like use of init_sequence in this
code path, too.  pg_dump's session will build a SeqTable entry for
every sequence in the database, which there could be a lot of,
and it will acquire RowExclusiveLock on every sequence and hold
that to the end of the dump, which seems likely to be troublesome
from a concurrency standpoint.  Since pg_get_sequence_data is a
read-only operation this lock level feels wrong.

BTW, I'm unconvinced that pg_dump behaves sanely when this function
does return nulls.  I think the ideal thing would be for it to skip
issuing setval(), but right now it looks like it will issue one with
garbage values.

			regards, tom lane

[1] https://www.postgresql.org/message-id/19365-6245240d8b926327%40postgre=
sql.org