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

Le 23 juil. 2014 00:33, "Julien Rouhaud" <rjuju123(at)gmail(dot)com> a écrit :
>
> 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.
>

And someone, on a bad day, will put a COMMIT or ROLLBACK in his queries and
will complain that some were committed and some explained.

Sometime, you just can't protect everyone from everything. And I'm not sure
this would be a good idea.

>>
>>>
>>> * 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
>
>