Today, one of my coworker was preparing several "update" and "insert" queries, in a query window, and he pressed the "Explain query" button to check the query plan, expecting it to be inoffensive. He then sent me the requests to play them, but I immediately got some errors about duplicate primary keys. After checking the related data, all but one had been changed. « - Hey, are you sure you did not already played the queries by error ? - Yes, I only did an "explain" ... what the heck?... » Hilarity ensues (I had luckily made a backup of the data just before).
The "Explain query" button seems to add an "EXPLAIN" keyword at the start of the text so it only explains the first query, but other queries will be simply played on the database.
So ok it was a bad idea, but we were wondering if it was the wisest behavior or if maybe some countermeasures could be added in order to enforce principle of least astonishment for layman users, like us.
Maybe like, : * when pressing that button, only "explain" the first query of the text field, and ignore the following queries + display a warning in the message panel (I think it would be the best solution, imho, but I guess it would need a basic syntactical parser to find the end of the first query),
Yeah, and that's not gonna happen anytime soon.
* change the tooltip of the button ("explain the first query, play the next ones")
Funny, but no :)
Almost nobody reads message boxes... nobody will read the tooltip.
* add a pop-up warning if it seems to have several queries (« We said "explain query" not "explain queries", do you still want to continue ?»)
That's probably the one I prefer. You still need a parser of some sort though.
* explain all the queries! (but I guess it would be a bit difficult to implement without major UI and logic refactoring)
Oh yeah. Not gonna happen.
* a better solution that I did not think of :)
You already had quite a lof of suggestions :)
Isn't it possible to surround the whole text with BEGIN/ROLLBACK, just in case ? I think that's what is done when doing an explain analyze.
* no solution, because "it works like intended" (but I'd then respectfully disagree on the intuitiveness of the feature)
Well, it works as expected. And I agree this isn't intuitive. Though I wonder what your colleague expects launching an EXPLAIN on a number of queries? we won't draw all query plans, that has not much sense to me.
