Re: [DESIGN] Soft DROP TABLE, recoverable drops for PostgreSQL - Mailing list pgsql-hackers
| From | surya poondla |
|---|---|
| Subject | Re: [DESIGN] Soft DROP TABLE, recoverable drops for PostgreSQL |
| Date | |
| Msg-id | CAOVWO5rxqereyWxarOFvQgvArEWtYzzcG1VmKpVM2P5G452TCg@mail.gmail.com Whole thread |
| In response to | Re: [DESIGN] Soft DROP TABLE, recoverable drops for PostgreSQL (Zsolt Parragi <zsolt.parragi@percona.com>) |
| List | pgsql-hackers |
Hi Zsolt,
Thanks a lot for the thoughtful review, you raised some great points.
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.
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?
granted deliberately. I'll fix that.
Regards,
Surya Poondla
Regards,
Surya Poondla
pgsql-hackers by date: