Hello! I wonder if this could be simply an extension, similar to the mentioned pgtrashcan, or even an update to that?
> 3. CASCADE support. DROP TABLE ... CASCADE walks pg_depend and > pg_constraint to move FK-dependent child tables into the > trash schema atomically with the parent; dependent views and > matviews are captured (as above) rather than moved. DROP TABLE ... CASCADE isn't the only CASCADE way to drop tables: * DROP SCHEMA ... CASCADE * DROP OWNED BY * DROP EXTENSION ... CASCADE * possibly others, isn't TRUNCATE similarly dangerous for example? Since you are thinking about this as a patch and not a separate extension, maybe the answer could be a different mechanism instead of using one of the preexisting hooks trying to catch all possible statements? I am not sure what that other mechanism could be, I just wanted to point out that statement based tracking will always be fragile. Since you are mostly worried about the data being destroyed immediately, could be the solution file level instead? There's a patchset about making the smgr extensible [1] (I am one of the authors/promoters of that patchset, and it is only slightly related to this, but I do see a possible connection). Or regardless of that patchset you could look into that area: making unlink a soft-delete, so that even if a table is dropped/truncated, the data is no longer automatically deleted. That would still require reconstructing the catalog part of it, and non-destructive operations such as vacuum full or rewriting alters would require special handling. Other than the other ways to drop a table, this feature would also have to think about a few more things: 1. interaction with other features, such as logical replication. If I understand correctly, with the current WIP patch a dropped table would remain a live relation, so it would stay in publications. Row level security could be another concern. Most likely there are several more similar corner cases. 2. security. Current api is public, when most likely it should be completely revoked by default? [1] : https://www.postgresql.org/message-id/CAEze2WgMySu2suO_TLvFyGY3URa4mAx22WeoEicnK%3DPCNWEMrA%40mail.gmail.com
