Re: improve performance of pg_dump with many sequences

Nathan Bossart <nathandbossart@gmail.com> writes:
> Here is an attempt at adding a new function that returns the sequence tuple
> and using that to avoid querying each sequence relation individually in
> dumpSequenceData().

Didn't read the patch yet, but ...

> If we instead wanted to change pg_sequence_last_value() to return both
> is_called and last_value, I think we could modify the pg_sequences system
> view to use a LATERAL subquery, i.e.,
> ...
> That doesn't seem so bad, and it'd avoid an extra pg_proc entry, but it
> would probably break anything that calls pg_sequence_last_value() directly.
> Thoughts?

... one more pg_proc entry is pretty cheap.  I think we should leave
pg_sequence_last_value alone.  We don't know if anyone is depending
on it, and de-optimizing the pg_sequences view doesn't seem like a
win either.

... 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.

			regards, tom lane