On Sat, Jul 25, 2026 at 2:12 PM Álvaro Herrera <[email protected]> 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.
