Bonjour Vik,
>> Do we really want default_explain_analyze ?
>> It sounds like bad news that EXPLAIN DELETE might or might not remove rows
>> depending on the state of a variable.
>
> I have had sessions where not using ANALYZE was the exception, not the
> rule. I would much prefer to type EXPLAIN (ANALYZE OFF) in those cases.
I concur with Justin that having EXPLAIN DELETE/UPDATE actually executing
the query can be too much a bit of a surprise for a user attempting it.
A typical scenario would be "this DELETE/UPDATE query is too slow", admin
connects to production and try safe EXPLAIN on some random sample, and get
bitten because the default was changed.
A way out could be having 3 states for analyse (off, read-only, on) which
would block updates/deletes by making the transaction/operation read-only
to prevent side effects, unless explicitely asked for? I'm not sure if
this can be easily implemented, though. Or maybe run the query in a
separate transaction which is then coldly rollbacked? Hmmm, I'm not really
convincing myself on this one… The safe option seems not allowing to
change ANALYZE option default.
While testing the issue, I'm surprised at the syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
Why not allowing the following:
EXPLAIN [ ANALYZE ] [ VERBOSE ] [ ( option [, ...] ) ] statement
--
Fabien.