On Wed, 9 Mar 2005, Simon Riggs wrote:
Oleg, this idea doesn't seem destine for greatness, so it might be worth
adding that you can avoid the general case problem of incorrectly-
specified-but-long-running query by using statement_timeout...
I have no problem with that ! I just wanted to take a note of such
"could be" mistaken errors.
On Wed, 2005-03-09 at 22:38 +1100, Neil Conway wrote:
Simon Riggs wrote:
Oleg is saying that the optimizer doesn't protect against foolish SQL
requests. His query is an example of a foolishly written query.
IMHO calling this a "foolishly written query" is completely arbitrary.
Well, in this case "foolish" is defined by the person that wrote the
query, as an expression of regret.
I
can imagine plenty of applications for which a cartesian join makes
sense.
Yes, which is why I discussed using a GUC, set only by those people who
want to be protected *from themselves*. It's a safety harness that you
could choose to put on if you wished.
In this case the user didn't write the query they meant to write
-- but it is surely hopeless to prevent that in the general case :)
It seems a reasonable that there might be a GUC such as
enable_cartesian = on (by default)
I think the bar for adding a new GUC ought to be significantly higher
than that.
Well, the point is moot until somebody writes the rest of the code
anyhow. So, add it to the ideas shelf...
In any case, when this problem does occur, it is obvious to the user
that something is wrong, and no harm is done. Given a complex SQL query,
it might take a bit of examination to determine which join clause is
missing -- but the proper way to fix that is better query visualization
tools (perhaps similar RH's Visual Explain, for example). This would
solve the general problem: "the user didn't write the query they
intended to write", rather than a very narrow subset ("the user forgot a
join clause and accidentally computed a cartesian product").
This issue only occurs when using SQL as the user interface language,
which is common when using a database in iterative or exploratory mode
e.g. Data Warehousing. If you are using more advanced BI tools then they
seldom get the SQL wrong.
This is not useful in a situation where people are writing SQL for a
more static application.
Best Regards, Simon Riggs
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
