Catch Accidental Drop

Have You Ever Wished for a Database Time Machine?

Picture this: You’re working in a bustling, data-driven environment, performing routine database cleanup tasks, and then it happens — the accidental slip of a finger, and you’ve just hit the dreaded “DROP” command on a production table. Panic sets in as you desperately wish for a way to turn back time and cancel that perilous command. If only there was someone to review your actions in real-time, right when you were typing that table name…

I found myself in precisely that situation not too long ago, and let me tell you, it can happen to anyone. But there’s a silver lining to this tale of database mishaps — a solution that revolves around sharing responsibilities, particularly when undertaking database cleanup or drop operations. It’s all about taking a proactive approach to prevent accidents.

The Event Trigger Solution

So, what’s the secret sauce to avoiding these heart-pounding moments? Event triggers. These provide a safety net when it comes to critical database operations.

The Toolset

I’ve put together a comprehensive set of scripts that can help you implement this safety net in your database. These scripts include functions, procedures, event triggers, and even a monitoring job to keep a vigilant eye on any DROP statements executed in your database.

How to Use It: An Example

Let’s dive into how you can use this setup. Imagine you have a table named “channa.”

create table channa (a int, b int);

• At the moment I have compiled the complete package of script. I just need to schedule monitor.sh as a cron with my email address(You can even add entire DBRE,SRE,Dev team here as a receiver). So that all important stackholders are aware whenever someone hits any drop from db. It can also be configured via any monitoring tool/slack alerting
• Now, I’m running a drop table statement:

drop table channa;

You will notice my statement takes longer to run -it actually goes to waiting(to allow some time for your admin to review)

• My monitoring is working perfect, I received details about drop query:

 pid  |       age       |       usename        |       query       | client_hostname
-------+-----------------+----------------------+-------------------+-----------------
 11154 | 00:00:27.991008 | shiwangini | drop table channa |
(1 row)

• Now, I realised that I hit wrong drop statement, I had to drop channa2 table instead of channa.Need to stop it immediately now. I connected to db and call avoid_ddl proc:

call avoid_ddl () ;

• The reason I added this proc for me because when really the drop statement run we don’t have time to actually dig into database and check from which pid the drop was running.
• And, now I came back to the session from where I initiated drop statement and see it’s cancelled with below message.

SQL Error [57P01]: FATAL: terminating connection due to administrator command
  

I was able to prevent my wrong drop table.