Can we let extensions change their dumped catalog schemas? - Mailing list pgsql-hackers

Hi all,

I've been talking to other Timescale devs about a requested change to
pg_dump, and there's been quite a bit of back-and-forth to figure out
what, exactly, we want. Any mistakes here are mine, but I think we've
been able to distill it down to the following request:

We'd like to be allowed to change the schema for a table that's been
marked in the past with pg_extension_config_dump().

Unless I'm missing something obvious (please, let it be that) there's no
way to do this safely. Once you've marked an internal table as dumpable,
its schema is effectively frozen if you want your dumps to work across
versions, because otherwise you'll try to restore that "catalog" data
into a table that has different columns. And while sometimes you can
make that work, it doesn't in the general case.

We (Timescale) do already change the schemas today, but we pay the
associated costs in that dump/restore doesn't work without manual
version bookkeeping and user fiddling -- and in the worst cases, it
appears to "work" across versions but leaves our catalog tables in an
inconsistent state. So the request is to come up with a way to support
this case.

Some options that have been proposed so far:

1) Don't ask for a new feature, and instead try to ensure infinite
backwards compatibility for those tables.

For extension authors who have already done this -- and have likely done
some heavy architectural lifting to make it work -- this is probably the
first thing that will come to mind, and it was the first thing I said,
too.

But the more I say it, the less acceptable it feels. Not even Postgres
is expected to maintain infinite catalog compatibility into the future.
We need to evolve our catalogs, too -- and we already provide the
standard update scripts to perform migrations of those tables, but a
dump/restore doesn't have any way to use them today.

2) Provide a way to record the exact version of an extension in a dump.

Brute-force, but pretty much guaranteed to fix the cross-version
problem, because the dump can't be accidentally restored to an extension
version with a different catalog schema. Users then manually ALTER
EXTENSION ... UPDATE (or we could even include that in the dump itself,
as the final action). Doing this by default would punish extensions that
don't have this problem, so it would have to be opt-in in some way.

It's also unnecessarily strict IMO -- even if we don't have a config
table change in a new version, we'll still require the old extension
version to be available alongside the new version during a restore.
Maybe a tweak on this idea would be to introduce a catversion for
extensions.

3) Provide a way to record the entire internal state of an extension in
a dump.

Every extension is already expected to handle the case where the
internal state is at version X but the installed extension is at version
X+N, and the update scripts we provide will perform the necessary
migrations. But there's no way to reproduce this case using
dump/restore, because dumping an extension omits its internals.

If a dump could instead include the entire internal state of an
extension, then we'd be guaranteed to reproduce the exact situation that
we already have to support for an in-place upgrade. After a restore, the
SQL is at version X, the installed extension is some equal or later
version, and all that remains is to run the update scripts, either
manually or within the dump itself.

Like (2), I think there's no way you'd all accept this cost for every
extension. It'd have to be opt-in.

--

Hopefully that makes a certain amount of sense. Does it seem like a
reasonable thing to ask?

I'm happy to clarify anything above, and if you know of an obvious
solution I'm missing, I would love to be corrected. :D

Thanks,
--Jacob