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

<p dir="ltr">Le 23 juil. 2014 00:33, "Julien Rouhaud" <<a
href="mailto:rjuju123@gmail.com">rjuju123@gmail.com</a>>a écrit :<br /> ><br /> > On Tue, Jul 22, 2014 at
11:24PM, Guillaume Lelarge <<a href="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>> wrote:<br />
>><br/> >> Hi,<br /> >><br /> >> 2014-07-22 20:33 GMT+02:00 Nicolas Anonyme <<a
href="mailto:pathogenyx@gmail.com">pathogenyx@gmail.com</a>>:<br/> >>><br /> >>> Hello,<br />
>>><br/> >>> Today, one of my coworker was preparing several "update" and "insert"<br /> >>>
queries,in a query window, and he pressed the "Explain query" button<br /> >>> to check the query plan,
expectingit to be inoffensive.<br /> >>> He then sent me the requests to play them, but I immediately got
some<br/> >>> errors about duplicate primary keys. After checking the related data,<br /> >>> all but
onehad been changed.<br /> >>> « - Hey, are you sure you did not already played the queries by error ?<br />
>>>- Yes, I only did an "explain" ... what the heck?... »<br /> >>> Hilarity ensues (I had luckily
madea backup of the data just before).<br /> >>><br /> >>> The "Explain query" button seems to add an
"EXPLAIN"keyword at the<br /> >>> start of the text so it only explains the first query, but other<br />
>>>queries will be simply played on the database.<br /> >>><br /> >>> So ok it was a bad
idea,but we were wondering if it was the wisest<br /> >>> behavior or if maybe some countermeasures could be
addedin order to<br /> >>> enforce principle of least astonishment for layman users, like us.<br />
>>><br/> >>> Maybe like, :<br /> >>>  * when pressing that button, only "explain" the first
queryof the<br /> >>> text field, and ignore the following queries + display a warning in<br /> >>>
themessage panel (I think it would be the best solution, imho, but I<br /> >>> guess it would need a basic
syntacticalparser to find the end of the<br /> >>> first query),<br /> >><br /> >><br /> >>
Yeah,and that's not gonna happen anytime soon.<br /> >>  <br /> >>><br /> >>>  * change the
tooltipof the button ("explain the first query, play<br /> >>> the next ones")<br /> >><br />
>><br/> >> Funny, but no :)<br /> >><br /> >> Almost nobody reads message boxes... nobody will
readthe tooltip.<br /> >><br /> >>>  * add a pop-up warning if it seems to have several queries (« We
said<br/> >>> "explain query" not "explain queries", do you still want to continue<br /> >>> ?»)<br
/>>><br /> >><br /> >> That's probably the one I prefer. You still need a parser of some sort
though.<br/> >>  <br /> >>><br /> >>>  * explain all the queries! (but I guess it would be a
bitdifficult<br /> >>> to implement without major UI and logic refactoring)<br /> >><br /> >><br
/>>> Oh yeah. Not gonna happen.<br /> >>  <br /> >>><br /> >>>  * a better solution that
Idid not think of :)<br /> >><br /> >><br /> >> You already had quite a lof of suggestions :)<br />
><br/> ><br /> > Isn't it possible to surround the whole text with BEGIN/ROLLBACK, just in case ? I think
that'swhat is done when doing an explain analyze.<br /> ><p dir="ltr">And someone, on a bad day, will put a COMMIT
orROLLBACK in his queries and will complain that some were committed and some explained.<p dir="ltr">Sometime, you just
can'tprotect everyone from everything. And I'm not sure this would be a good idea.<p dir="ltr">>>  <br />
>>><br/> >>>  * no solution, because "it works like intended" (but I'd then<br /> >>>
respectfullydisagree on the intuitiveness of the feature)<br /> >>><br /> >><br /> >> Well, it
worksas expected. And I agree this isn't intuitive. Though I wonder what your colleague expects launching an EXPLAIN on
anumber of queries? we won't draw all query plans, that has not much sense to me.<br /> >><br /> >><br />
>>-- <br /> >> Guillaume.<br /> >>   <a
href="http://blog.guillaume.lelarge.info">http://blog.guillaume.lelarge.info</a><br/> >>   <a
href="http://www.dalibo.com">http://www.dalibo.com</a><br/> ><br /> ><br />