256,338 rows affected.
You’re stress testing the IT department’s RTO and RPO. This is important to do regularly at random intervals.
Netflix even invented something called Chaos Monkey that randomly breaks shit to make sure they’re ready.
Another company that never had a real DBA tell them about _A tables.
This stuff is literally in the first Database class in any real college.
This is trivial, before any update or delete you put the main table (let us use table foo with a couple columns (row_id,a, b, create_date,create_user_id, update_date and update_user_id) in this example)
For vc in (select * from foo where a=3) Loop Insert into foo_A (row_id,a,b, create_date,create_user_id, update_date, update_user_id, audit_date,audit_user_id) values(vc.row_id,vc.a,vc.b, vc.create_date,vc.create_user_id, vc.update_date, vc.update_user_id, ln_sysdate,ln_audit_user_id); Delete from foo where row_id =vc.row_id; End loop
Now you have a driver that you can examine exactly the records you are going to update, along with ensuring that you will be able to get the old values back, who updated/deleted the values and an audit log for all changes (as you only give accounts insert access to the _A tables and only access to the main tables through stored procedures)
You can just insert directly to the helper/audit table, and delete using it, no need for the cursor loop. If you need a handle to go through the records one by one, something else has already gone wrong.
If you need to speed up your deletes, might I suggest not storing data that you don’t need. It is much faster, cheaper and better protects user privacy.
Modern SQL engines can parallelize the loop and the code is about enabling humans to be able to reason about what exactly is being done and to know that it is being done correctly.
At least in PG, that is explicitly not the case, unless I’m misunderstanding:
Similarly, a PL/pgSQL loop of the form FOR x IN query LOOP … END LOOP will never use a parallel plan, because the parallel query system is unable to verify that the code in the loop is safe to execute while parallel query is active.
https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html
At any rate, I feel like it’s questionable design to have a user making row-by-row decisions on hard deletes.
The key part is while the query is active.
Also you are not doing hard deletes on the main table but only on the _A table. As you can always retrieve the main table values from the _A table (which only deletes records based on audit_date when they have aged out) and that is not something that the user or even any of the service accounts will have access to do. (Only a specialized clean up job on a restricted account would have delete permissions on the _A tables and access to nothing else)
Oh, so that’s what the Delete button does.
It was all a Pentest! The company should have been operating under the Zero Trust Policy and their Security systems should not have permitted a new employee to have that many rights. You’re welcome, the bill for this insightful Security Audit will arrive via mail.
All of my bananas are worthless now!
Ctrl + z.
Thank you for coming to my Ted talk.
Works on my machine (excel sheet)
Elon was onto something after all
Pretend you thought you were hired as disaster recovery tester
Now if you’ll excuse me while I fetch some documents from my car for my formal evaluation of your system
*Gets in car and drives away*
Have you tried turning it off and on again?
256,338 rows affected.
when it gives you a time to rub it in. ‘in 0.00035 seconds’I once bricked all the POS terminals in a 30-store chain, at once.
One checkbox allowed me to do this.
That’s actually pretty impressive
Was it the recompute hash button?
Nah. It was totally a virus attack.
Reminds me of the time all those porn pop-ups hijacked my browser and filled my history. My dad thought I’d visited all those sites on purpose for a second there.
No they were ancient ROM based tills, I unchecked a box that was blocking firmware updates from being pushed to the tills. For some reason I still don’t completely understand, these tills received their settings by Ethernet, but received their data by dialup fucking modems. When I unchecked the box, it told the tills to cease processing commands until the firmware update was completed. But the firmware update wouldn’t happen until I dialled into every single store, one at a time, and sent the firmware down through a 56k modem with horrendous stability, to each till, also one at a time. If one till lost one packet, I had to send it’s firmware again.
I say for 8 hrs watchimg bytes trickle down to the tills while answering calls from frantic wait staff and angry managers.
I worked with POS machines once too. Ugh. Worst. Things. Ever.
I’m curious - was it also a checkbox that immediately applied when toggled, instead of not actually applying until you press save?
Immediately applied, no save button. It was labeled something like “Allow/disable firmware updates” which is bad design. Label should say exactly one thing, either “Allow” or “disable” never “Allow/disable” The software was very antiquated.
Why is the default for some database tools to auto commit after that? Pants on head design decision.
The data was a mess and needed to be cleaned up anyway.
It’s fine, just restore the backup.
The what now?
You know how we installed that system and have been waiting for a chance to see how it works
It’s right there, in the room with the unicorns and leprechauns.
You go ask the other monkey who was in charge of the backup … and all they do is incoherently scream at you.
Backup straight out the building. Ain’t about to be there when they find out.
