Re: improve performance of pg_dump with many sequences

Nathan Bossart <nathandbossart(at)gmail(dot)com> writes:
> Here is a patch that does this along with what you described upthread,
> i.e., teaching pg_get_sequence_data to return nulls for missing sequences.
> Apparently pg_dump still runs through dumpSequenceData() for schema-only
> dumps, which is a problem for this patch. I've taught it to immediately
> return for schema-only dumps to evade this problem. That seems like a win
> for older versions, too, as they will no longer run useless queries.

> I believe this helps the reporter's case, as their problem involves dumping
> one schema while dropping another, which v18 indeed makes worse because (as
> you mentioned) we gather data for all sequences in the database.

Looks plausible to me. (I didn't test, just read the code.)

One nitpicky point is that try_sequence_open() will still error out
if it is given an OID that is a non-sequence relation. I think it'd
be more desirable for it to close the relation again and return NULL.
That's probably insignificant for pg_dump's usage, because we could
only hit the case with very improbable OID wraparound timing. But
I think our experience with catalog-inspection functions similar to
pg_get_sequence_data is that it's usually better to return NULL than
throw an error.

regards, tom lane