Re: [DESIGN] Soft DROP TABLE, recoverable drops for PostgreSQL

On Sat, Jul 25, 2026 at 2:12 PM Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
>
> On 2026-07-25, Amit Kapila wrote:
>
> > On the other hand, I'm not sure if it's a good idea to protect only
> > DROP TABLE and not the other paths like DROP SCHEMA ... CASCADE, DROP
> > OWNED,
> > etc., as pointed out by Zsolt.
>
> I wonder if this would be better implemented as an sql_drop event trigger. Then all these things are covered.
>

Thanks for the idea. I'd like to understand how you're imagining to
implement this, because sql_drop seems to fire too late for the
recovery part, so you may have something in mind that I'm not seeing.

AFAICU, the sql_drop event fires after the objects have already been
removed from the catalogs, and event triggers can only observe the
command or abort it with an error, not rewrite a DROP into something
that keeps the relation around. So it would let us reliably record
what was dropped across all paths (DROP TABLE, DROP SCHEMA ...
CASCADE, DROP OWNED, cascades), but I don't see how it lets us
preserve the table for a later restore. Do you have a mechanism in
mind for the restore side? For example, capturing something more than
metadata at sql_drop time, or is the intent closer to an audit/logging
layer than true recoverability?

The other place to explore is object_access_hook (OAT_DROP) which
looks like a better fit than sql_drop: it fires per object just before
deletion, so it should cover the same cascade/DROP OWNED paths, but
the catalog rows are still readable at that point. Like event
triggers, though, it's observe-or-abort, so it still can't divert a
deletion into a "keep it" action.

For actually preserving the data across all the paths, we can actually
implement it inside the deletion machinery itself, i.e. teaching
performDeletion to divert a table into a move-to-reserved-schema (when
the feature is enabled) instead of a hard delete, rather than
intercepting at the statement layer. That reuses the property that a
relation in another schema is still fully valid and covers cascades
for free. This needs much more analysis.

Yet another idea could be to do this fully natively via a soft-drop
flag in pg_class (a relisdropped-style marker), marking the
corresponding pg_depend edges as soft-dropped rather than deleting
them, and skipping the physical unlink so the heap/TOAST/index files
survive. The challenge is that it introduces a present-but-invisible
relation state that PostgreSQL doesn't have today which means it would
need to touch multiple parts of the system, so could be complex,
though I haven't analyzed the idea in detail; just mentioning here for
the sake of brainstorming various ways by which we can implement this
feature.

--
With Regards,
Amit Kapila.