Horton

Re: improve performance of pg_dump with many sequences

Nathan Bossart <nathandbossart@gmail.com>

View thread View on postgr.es
From: Nathan Bossart <nathandbossart@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paquier <michael@paquier.xyz>, Euler Taveira <euler@eulerto.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2024-07-18T18:22:10Z
Lists: pgsql-hackers

Attachments

On Wed, Jul 17, 2024 at 11:58:21PM -0400, Tom Lane wrote:
> ... okay, I lied, I looked at the patch.  Why are you testing
> 
> +	if (pg_class_aclcheck(relid, GetUserId(), ACL_SELECT | ACL_USAGE) == ACLCHECK_OK &&
> 
> ?  This is a substitute for a SELECT from the sequence and it seems
> like it ought to demand exactly the same privilege as SELECT.
> (If you want to get more technical, USAGE allows nextval() which
> gives strictly less information than what this exposes; that's why
> we're here after all.)  So there is a difference in the privilege
> levels, which is another reason for not combining this with
> pg_sequence_last_value.

Oh, that's a good point.  I wrongly assumed the privilege checks would be
the same as pg_sequence_last_value().  I fixed this in v5.

I also polished the rest of the patches a bit.  Among other things, I
created an enum for the sequence data types to avoid the hacky strncpy()
stuff, which was causing weird CI failures [0].

[0] https://cirrus-ci.com/task/4614801962303488

-- 
nathan

Commits

  1. pg_dump: Fix gathering of sequence information.

  2. Improve performance of dumpSequence().

  3. Improve performance of dumpSequenceData().

  4. Introduce pg_sequence_read_tuple().

  5. Parse sequence type and integer metadata in dumpSequence().