Re: Behavior of the "Explain query" button with multiple queries in the window

On Tue, Jul 22, 2014 at 11:24 PM, Guillaume Lelarge <guillaume(at)lelarge(dot)info>
wrote:

> Hi,
>
> 2014-07-22 20:33 GMT+02:00 Nicolas Anonyme <pathogenyx(at)gmail(dot)com>:
>
>> Hello,
>>
>> 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.
>
>
> --
> Guillaume.
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>