Extension-defined restore actions depending on dumped objects - Mailing list pgsql-hackers

Hi, Hackers

While working on a PostgreSQL extension, I ran into a dump/restore ordering
problem that I don’t think the current extension dump mechanism can express.
I wanted to ask whether there is already a supported mechanism I’ve missed,
and if not, whether this is something worth generalizing in pg_dump.

The concrete case is an index utility extension that stores an “invisible”
property for indexes. At runtime, the metadata can efficiently refer to an
index by OID, but an OID obviously cannot be used as persistent identity across
 a logical dump/restore. The state can instead be serialized using the schema
 and index name, but it cannot be applied on the destination until the
 corresponding index has been created.

Conceptually, the ordering needed is:

restore extension metadata describing index X
        |
        v
restore/create index X
        |
        v
apply extension state to index X

pg_extension_config_dump() does not seem sufficient for this case. It can
arrange for configuration-table contents to be dumped, but it does not
provide a way for an extension to say that applying some state depends on
a particular user object restored later.

A trigger on a configuration table doesn’t really solve that dependency
problem either: it fires when the configuration data is loaded, not because
some other object such as index X has been restored. There is currently no
dependency edge connecting those two events, and parallel restore makes that
distinction particularly visible.

I initially thought about this as a “post-restore callback”, but after looking
at pg_dump that seems like the wrong abstraction. pg_dump already represents
similar internal operations as dependency-aware dump objects. For example,
index attachment is a synthetic action with dependencies on its prerequisites,
 and newer relation/index statistics restoration also restores state after the
 relevant object exists.

So what seems to be missing is not restore scheduling, but an extension-facing
way to describe something roughly like:

extension restore action
    depends on index A
    depends on index B
    emits/applies extension-specific state

with the normal dump dependency machinery determining its position.

I realize that the existing examples are built-in object types known to pg_dump.
Supporting extensions would therefore require some generic extension-facing
representation for dependency-bearing restore actions; it would be more than
just assigning another priority to an existing built-in object type. I don’t
have a specific API in mind yet.

In principle such an action would also fit parallel restore better than a
global completion callback, and, if it participates in pg_dump’s normal
dependency sort, could be emitted in the appropriate order for plain SQL dumps
as well.

I’m aware of the current thread:

“[PATCH] pg_dump: Restore extension config table data before user
objects during pg_upgrade”

and CF 6654. That work appears to address the complementary ordering:

extension configuration data -> user objects

whereas this case needs:

user objects -> extension state/action

I’m not suggesting that a patch should necessarily take on this
additional scope,
but the introduction of an extension-specific dump object there made me wonder
 whether there is a useful more general abstraction underneath both cases.

There are some details that would clearly need thought before proposing an
interface. In particular, dependencies guarantee ordering but don’t by
themselves define what should happen during a selective restore if a
prerequisite wasn’t selected. Similarly, pg_restore can continue after a
prerequisite command fails, so “dependency processed” is not necessarily the
same as “referenced object successfully restored”. I also don’t think an
interface that simply allows extensions to inject arbitrary unchecked SQL into
a dump would be desirable.

Is there an existing mechanism or previous discussion for extension-defined,
dependency-aware restore actions that I’ve missed?

If not, does exposing or generalizing the existing dump-object dependency model
for this kind of extension state sound like a reasonable direction, or would
you prefer extensions to continue handling cases like this outside pg_dump?

Thanks,
Haibo