Hackers,
The concept was driven by an all too common support request. A user
accidentally dropped the wrong table. (this could also be applied to
dropping a database, etc).
If we had the LSN before the drop, this would be easier. So we actually
log the LSN when the lock is required so that we have an accurate LSN and
the recovery is much simpler.
All we are doing is inserting a simple LOG message:
Acquired drop table lock on table <relname>. Restore at <LSN>
This is a rough patch, very simple and effective. We are looking for
feedback.
Comments are appreciated!
Should we ALSO consider this for:
- DROP DATABASE
- TRUNCATE TABLE
- DELETE (only when it is without a WHERE clause?)
- UPDATE (only when it is without a WHERE clause?)
Regards
Andrey, Nikolay, Kirk
From 9a5fad6e73b05ba7339a628f2058c2577fc6cab7 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <amborodin@acm.org>
Date: Thu, 7 Nov 2024 23:11:22 +0500
Subject: [PATCH vPoC] Simplify recovery after dropping a table by giving
restore LSN in logs
Hacked online on postgres.tv by Nik, Kirk and Andrey.
---
src/backend/catalog/dependency.c | 8 ++++++++
1 file changed, 8 insertions(+)
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 0489cbabcb..81e1b7d519 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1507,6 +1507,14 @@ AcquireDeletionLock(const ObjectAddress *object, int flags)
LockRelationOid(object->objectId, ShareUpdateExclusiveLock);
else
LockRelationOid(object->objectId, AccessExclusiveLock);
+
+ if (get_rel_relkind(object->objectId) == RELKIND_RELATION ||
+ get_rel_relkind(object->objectId) == RELKIND_PARTITIONED_TABLE)
+ {
+ XLogRecPtr ptr = GetInsertRecPtr();
+ char *relname = get_rel_name(object->objectId);
+ elog(LOG, "Aquired drop table lock on table %s. Restore at %X/%X", relname, (uint32)(ptr >> 32), (uint32) ptr);
+ }
}
else if (object->classId == AuthMemRelationId)
LockSharedObject(object->classId, object->objectId, 0,
--
2.39.5 (Apple Git-154)
