[DESIGN] Soft DROP TABLE, recoverable drops for PostgreSQL
Hi Hackers,
I would like to discuss adding a soft / recoverable 'DROP TABLE' to
PostgreSQL.
The DROP moves the table into a reserved schema, where it remains
restorable until explicitly purged.
Motivation:
'DROP TABLE' is one of the few SQL statements where the consequences are
wildly out of proportion to the time it takes to type the command.
A few real-world scenarios I have seen were:
- An automated partition-rotation job ran against the wrong cluster after a
config change and
dropped a table holding a lot of data (in order of 10s of GB) of two days
of telemetry before the site reliability engineer stopped it.
- A nightly time-to-live cleanup script applied a stale rule and removed a
reporting table with six months of aggregated data (hundreds of GB), not
reconstructable from upstream sources.
The current recovery path for an accidentally dropped table is
point-in-time recovery or restoring from a logical backup, both of which
are disruptive relative to the size of the data, when all the operator
wanted was the table back five minutes later.
Several other systems offer a lighter-weight first line of defense:
- Oracle Database has Recycle Bin and 'FLASHBACK TABLE... TO BEFORE DROP'
since 10g. The dropped tables are stored in a special schema and remain
queryable until purged.
- IBM Db2 supports recovery of dropped tables on tablespaces configured
with 'DROPPED TABLE RECOVERY', replayed via rollforward.
- Snowflake provides 'UNDROP TABLE' within its Time Travel retention window
to restore the table back.
The recurring design across all of these: an accidental DROP should be
recoverable for some short period without invoking heavy disaster recovery.
The question I would like to put here is whether a more complete
implementation along the lines below would be welcome as a contrib
extension or a core feature.
Proposal:
The fundamental mechanism is inspired from pgtrashcan [1]https://github.com/petere/pgtrashcan by Peter
Eisentraut where a 'ProcessUtility_hook' intercepts 'DROP TABLE' and
rewrites it as 'ALTER TABLE ... SET SCHEMA "Trash"', so the relation
survives in a reserved schema rather than being removed.
What I have been exploring on top of that, to make the feature usable for
real recovery scenarios:
- A set of SQL APIs to list, restore, rename, move, and purge tables (and
their dependent indexes and sequences) from the reserved schema.
- A catalog table inside the reserved schema to capture the original name
of the object, original schema, the OID, drop time, dropped_by user, object
type, and a JSONB metadata blob describing dependent objects like foreign
keys that could not safely be moved.
- CASCADE. 'DROP TABLE t CASCADE' captures dependent foreign keys, views,
materialized views, rules, and RLS policies as JSONB metadata (including
runnable DDL strings built around 'pg_get_constraintdef' and
'pg_get_viewdef'), then permanently drops them. Indexes and owned sequences
ride along with the table, since 'ALTER TABLE SET SCHEMA' carries them.
- Unique names inside the reserved schema. Two drops of the same table name
need to coexist (an operator drops a table, recreates it under the same
name, and drops it again, both versions must remain recoverable
independently). The feature renames every dropped table to a deterministic,
OID-derived name (e.g. 'trash_$16384') inside the reserved schema.
The OID guarantees uniqueness across the cluster's lifetime, the rename is
short enough to never hit NAMEDATALEN truncation, and the original
schema/name are preserved separately in the catalog so the user-visible
naming is unaffected on restore.
- Because each drop produces a unique OID-derived name in the reserved
schema, multiple drops of the same table name sit in the schema as
independent rows in the catalog (each with its own OID, drop time, and
metadata).
The SQL APIs will support two access patterns:
1. restore-by-name which returns the most recently dropped version,
2. restore-by-OID which restores a specific table.
Users can also list all versions and rename older ones into a
quarantine schema before
restoring the most recent useful when the operator wants to compare or
merge data across versions.
- Triggers. Triggers can stay attached to the table across the schema move
because 'pg_trigger.tgrelid' is OID-based. The trade-off is that trigger
function bodies reference other tables by name, so the first firing after
restore can fail if those references were broken while the owner sat in
Trash. Whether this is the right default is an open question.
- A GUC to disable the trash-can per session or system-wide. When set,
'DROP TABLE' bypasses the reserved schema and the table is permanently
dropped, matching standard PostgreSQL semantics.
- Protection on the reserved schema and its catalog. Direct DML on the
catalog table is blocked, the schema cannot be dropped while the extension
is installed, and 'ALTER'/'RENAME' of objects already in the schema is
blocked.
- Misc objects. Extend the above capabilities to support tables like
Partitioned tables.
I am working on a prototype and will share it soon.
[1]: https://github.com/petere/pgtrashcan
Regards,
Surya Poondla
Hi All,
I'm posting a WIP patch for the above design.
The patch adds contrib/pgtrashcan: a new extension that intercepts DROP
TABLE via a ProcessUtility hook and moves the relation to a
reserved schema instead of destroying it, from where it can be restored
with pgtrashcan_restore or permanently deleted with
pgtrashcan_purge. It is a fork of an existing pgtrashcan extension,
extended with additional features.
Any feedback is welcome.
Regards,
Surya Poondla
Hi All,
I created a commitfest entry https://commitfest.postgresql.org/patch/7017/
to hear more about this approach.
Regards,
Surya Poondla
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]/messages/by-id/CAEze2WgMySu2suO_TLvFyGY3URa4mAx22WeoEicnK=PCNWEMrA@mail.gmail.com (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]: /messages/by-id/CAEze2WgMySu2suO_TLvFyGY3URa4mAx22WeoEicnK=PCNWEMrA@mail.gmail.com
Hi Zsolt,
Thanks a lot for the thoughtful review, you raised some great points.
I wonder if this could be simply an extension, similar to the
mentioned pgtrashcan, or even an update to that?
Yes, I think the mechanism can live largely in an extension, and that's
the direction I'd like to take this for now. It keeps the footprint small,
avoids touching core storage paths, and lets people opt in rather than
paying for it everywhere.
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.
I agree the file-level / soft-unlink approach via extensible smgr is a
genuinely good alternative, and arguably the more robust one. It catches
every path that destroys data rather than trying to enumerate statements,
which as you rightly point out is inherently fragile. I'll follow that
patchset with interest, and I can see a version of this feature built on
top of it.
For this round, though, I'd prefer to keep to the higher-level (catalog
/statement) approach.
The smgr route pulls in catalog reconstruction plus special handling for
non-destructive rewrites (VACUUM FULL, rewriting
ALTERs), which is a substantially larger effort and is coupled to a
patchset that isn't committed yet.
I'd rather land something useful and self-contained first, and treat the
file-level mechanism as a possible
future foundation once extensible smgr matures.
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?
Agreed, and I'll be explicit in the docs about which paths are covered
rather than implying it catches everything.
The object-drop paths (DROP SCHEMA/OWNED BY/EXTENSION ... CASCADE) all
funnel through
dependency-based deletion, so I'm hoping to hook at that lower level
rather than per-statement, which should cover them more uniformly.
TRUNCATE is a different codepath and I'll scope it out for now.
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.
Good catch.
A "trashed" relation staying in publications is not acceptable, so
soft-dropped tables need to be removed from / excluded by publications
(and similarly reconsidered for RLS). I'll work through these cases and
document the behavior.
2. security. Current api is public, when most likely it should be
completely revoked by default?
Yes, agreed. The API should be revoked from PUBLIC by default and only
granted deliberately. I'll fix that.
Regards,
Surya Poondla
On 7/2/26 2:55 AM, surya poondla wrote:
The patch adds contrib/pgtrashcan: a new extension that intercepts DROP
TABLE via a ProcessUtility hook and moves the relation to a
reserved schema instead of destroying it, from where it can be restored
with pgtrashcan_restore or permanently deleted with
pgtrashcan_purge. It is a fork of an existing pgtrashcan extension,
extended with additional features.Any feedback is welcome.
Is there any reason your features cannot be added to the existing
extension? Or that extension to be forked but still live outside core
PostgresSQL?
--
Andreas Karlsson
Percona
On Sat, Jul 25, 2026 at 2:10 AM Andreas Karlsson <andreas@proxel.se> wrote:
On 7/2/26 2:55 AM, surya poondla wrote:
The patch adds contrib/pgtrashcan: a new extension that intercepts DROP
TABLE via a ProcessUtility hook and moves the relation to a
reserved schema instead of destroying it, from where it can be restored
with pgtrashcan_restore or permanently deleted with
pgtrashcan_purge. It is a fork of an existing pgtrashcan extension,
extended with additional features.Any feedback is welcome.
Is there any reason your features cannot be added to the existing
extension? Or that extension to be forked but still live outside core
PostgresSQL?
The one argument I see for keeping such a feature in-tree is that it
touches a number of subsystems that evolve with core and would need
coordinated review: dependency handling and CASCADE (foreign keys,
views, etc.), pg_dump/pg_upgrade treatment of the reserved schema and
the objects parked in it, event triggers, triggers that reference
other relations, and interaction with logical/DDL replication. These
interactions are subtle and can change over time, so maintaining them
alongside core might be easier in the tree than chasing core from
outside.
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 think we should first decide whether
such a feature is useful enough to add to core, and then work out the
appropriate design to accomplish it. Surya's initial email lists use
cases that sound reasonable, and several other databases do support
this, but not sure those are enough to justify making it a core
feature.
--
With Regards,
Amit Kapila.
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.
--
Álvaro Herrera
On Sat, Jul 25, 2026 at 2:12 PM Álvaro Herrera <alvherre@kurilemu.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.
Hi Amit, Alvaro, Andreas,
Thanks for the comments and suggestions.
Let me first be clearer about the scope of the current patch, since I think
that is the source of some of the concern.
The WIP patch deliberately covers DROP TABLE only. That was meant as the
smallest useful increment to get the mechanism (reserved schema,
OID-derived naming, catalog of dropped objects, restore/purge APIs)
reviewed, not as a claim that all drop paths are protected.
I should have said so explicitly on the thread, and I will make that a
documented limitation rather than leaving it implied.
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.
Agreed. I looked at this and it matches what you describe: the object list
is collected in deleteObjectsInList() before the deletion loop, but
EventTriggerSQLDrop() itself does not fire until ProcessUtilitySlow(), by
which point the catalog rows are gone.
Combined with event triggers being observe-or-abort, I do not see a way to
preserve the relation from there.
It would give reliable auditing of what was dropped across all paths, which
is valuable in itself, but not recoverability.
Same conclusion for object_access_hook / OAT_DROP better timing, but that
itself cannot suppress the deletion of a table's indexes, TOAST relation
and owned sequences along with it..
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.I think this is the right long-term shape, and I would frame it as an
extension of the current design rather than a replacement for it.
The reason the two converge is that the paths in question already share the
same machinery:
DROP TABLE -> RemoveRelations() -> performMultipleDeletions()
DROP SCHEMA/EXTENSION -> RemoveObjects() -> performMultipleDeletions()
DROP OWNED BY -> shdepDropOwned() -> performMultipleDeletions()
and all of them end at deleteOneObject() -> doDeletion() ->
heap_drop_with_catalog() for the relation itself.
So DROP SCHEMA ... CASCADE is not a separate kind of table drop; it reaches
the same place
with the table pulled in by the dependency walk instead of named directly.
A couple of points:
1. Everything above the interception point in my patch i.e the reserved
schema, the naming scheme, the catalog of dropped objects, the restore and
purge APIs, the protections is independent of where the drop is caught, so
moving the interception point later does not change this.
2. The intermediate step is smaller than it looks: at the statement layer
these paths are all DropStmt (OBJECT_SCHEMA, OBJECT_EXTENSION) or
DropOwnedStmt, all visible to the same hook. The extension can enumerate
the affected tables and move them aside before letting the original
statement proceed, which covers the CASCADE forms Zsolt listed without core
changes. The residual gap is the internal and non-statement paths, and that
is where diverting in the deletion machinery is genuinely necessary.
Which is why I would like to suggest keeping this as a contrib extension
for now, and treating the deletion-machinery diversion as the follow-on
once the mechanism has had some real use.
It keeps the footprint small, it is opt-in, and it does not commit core to
a new behaviour for DROP.
it touches a number of subsystems that evolve with core and would
need coordinated review: dependency handling and CASCADE (foreign keys,
views, etc.), pg_dump/pg_upgrade treatment of the reserved schema and the
objects parked in it, event triggers, triggers that reference
other relations, and interaction with logical/DDL replication.
This is a fair point. None of these are handled in the current patch:
dependent views and matviews are captured as DDL and dropped rather than
preserved, triggers ride along with the relation but their function bodies
can reference objects that moved, pg_dump/pg_upgrade treatment of the
reserved schema is unaddressed, and a soft-dropped relation is still a live
relation, so it stays in publications, as Zsolt pointed out.
Each of these is extendable within the current structure, and I would
rather work through them incrementally with the scope stated.
Regards,
Surya Poondla
On Tue, Jul 28, 2026 at 2:16 AM surya poondla <suryapoondla4@gmail.com> wrote:
Thanks for the comments and suggestions.
Let me first be clearer about the scope of the current patch, since I think that is the source of some of the concern.
The WIP patch deliberately covers DROP TABLE only. That was meant as the smallest useful increment to get the mechanism (reserved schema, OID-derived naming, catalog of dropped objects, restore/purge APIs) reviewed, not as a claim that all drop paths are protected.
I should have said so explicitly on the thread, and I will make that a documented limitation rather than leaving it implied.
Building features incrementally is fine, but doing it in a way that
isn't extensible is not. Here, the hook you're using can't cover all
the drop paths, so even as a contrib module it should be built on a
hook in the right place. Consider either extending an existing hook to
cover this case or proposing a new one unless you can present a way to
address other use cases from the same hook in an acceptable way.
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.Agreed. I looked at this and it matches what you describe: the object list is collected in deleteObjectsInList() before the deletion loop, but
EventTriggerSQLDrop() itself does not fire until ProcessUtilitySlow(), by which point the catalog rows are gone.
Combined with event triggers being observe-or-abort, I do not see a way to preserve the relation from there.
It would give reliable auditing of what was dropped across all paths, which is valuable in itself, but not recoverability.
Same conclusion for object_access_hook / OAT_DROP better timing, but that itself cannot suppress the deletion of a table's indexes, TOAST relation and owned sequences along with it..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.I think this is the right long-term shape, and I would frame it as an extension of the current design rather than a replacement for it.
The reason the two converge is that the paths in question already share the same machinery:
DROP TABLE -> RemoveRelations() -> performMultipleDeletions()
DROP SCHEMA/EXTENSION -> RemoveObjects() -> performMultipleDeletions()
DROP OWNED BY -> shdepDropOwned() -> performMultipleDeletions()
and all of them end at deleteOneObject() -> doDeletion() -> heap_drop_with_catalog() for the relation itself.
So DROP SCHEMA ... CASCADE is not a separate kind of table drop; it reaches the same place
with the table pulled in by the dependency walk instead of named directly.A couple of points:
1. Everything above the interception point in my patch i.e the reserved schema, the naming scheme, the catalog of dropped objects, the restore and purge APIs, the protections is independent of where the drop is caught, so moving the interception point later does not change this.
2. The intermediate step is smaller than it looks: at the statement layer these paths are all DropStmt (OBJECT_SCHEMA, OBJECT_EXTENSION) or DropOwnedStmt, all visible to the same hook. The extension can enumerate the affected tables and move them aside before letting the original statement proceed, which covers the CASCADE forms Zsolt listed without core changes. The residual gap is the internal and non-statement paths, and that is where diverting in the deletion machinery is genuinely necessary.
I think it could be tricky to enumerate all affected tables this way
from the current hook. You require a lot of additional functionality
to do catalog lookups to find required objects and even then it is not
clear how we can alter the schema of all required tables.
--
With Regards,
Amit Kapila.