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

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