Обсуждение: Behavior of the "Explain query" button with multiple queries in the window

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),* change the tooltip of the button ("explain the first query, play
the next ones")* 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
?»)* explain all the queries! (but I guess it would be a bit difficult
to implement without major UI and logic refactoring)* a better solution that I did not think of :)* no solution,
because"it works like intended" (but I'd then 
respectfully disagree on the intuitiveness of the feature)

(pgAdmin version : 1.18.1, linux and windows)

Thanks in advance for any feedback on this topic.

Best regards,
--
Nicolas

On 22/07/2014 19:39, Cecil Eduardo Campos Costa wrote:
> 
> How could I unsubscribe from this group?

Hi there,

The answer is in the footer of every email:

> -- 
> Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org
> <mailto:pgadmin-support@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support

HTH,

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

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

2014-07-22 23:24 UTC+02:00, Guillaume Lelarge <guillaume@lelarge.info>:
> 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.

He usually uses the feature with only one query at a time and he
didn't give a second thought.

But to my surprise, he admitted he (ab)uses the "explain" more often
as a syntax checker, than to check the query plan ... I know ...

But that doesn't change the root intuitiveness problem, does it ? :)

--
Nyx

<p dir="ltr">Le 23 juil. 2014 10:17, "Nicolas Anonyme" <<a
href="mailto:pathogenyx@gmail.com">pathogenyx@gmail.com</a>>a écrit :<br /> ><br /> > 2014-07-22 23:24
UTC+02:00,Guillaume Lelarge <<a href="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>>:<br /> > >
ThoughI wonder what your colleague expects launching an EXPLAIN on a number of<br /> > > queries? we won't draw
allquery plans, that has not much sense to me.<br /> ><br /> > He usually uses the feature with only one query at
atime and he<br /> > didn't give a second thought.<br /> ><br /> > But to my surprise, he admitted he (ab)uses
the"explain" more often<br /> > as a syntax checker, than to check the query plan ... I know ...<br /> ><br />
>But that doesn't change the root intuitiveness problem, does it ? :)<br /> ><p dir="ltr">Nope, it doesn't change
it.It doesn't provide a solution either.