Re: Can we let extensions change their dumped catalog schemas?

Jacob Champion <jchampion(at)timescale(dot)com> writes:
> 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.

I agree that's a problem, but it's not that we're arbitrarily prohibiting
something that would work. What, concretely, do you think could be
done to improve the situation?

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

Don't really see how that helps? I also fear that it will break
a bunch of use-cases that work fine today, which are exactly the
ones for which we originally defined pg_dump as *not* committing
to a particular extension version.

It feels like what we really need here is some way to mutate the
old format of an extension config table into the new format.
Simple addition of new columns shouldn't be a problem (in fact,
I think that works already, or could easily be made to). If you
want some ETL processing then it's harder :-(. Could an ON INSERT
trigger on an old config table transpose converted data into a
newer config table?

Another point that ought to be made here is that pg_dump is not
the only outside consumer of extension config data. You're likely
to break some applications if you change a config table too much.
That's not an argument that we shouldn't try to make pg_dump more
forgiving, but I'm not sure that we need to move heaven and earth.

regards, tom lane