statement_cost_limit
On Tue, 2006-02-21 at 11:45 +1300, Mark Kirkwood wrote:
Simon Riggs wrote:
A new parameter that allows the administrator to place sensible limits
on the size of queries executed.This is useful for ad-hoc SQL access
- when a very large table cannot realistically be sorted etc, so
prevents general users from saying "SELECT * FROM TABLE ORDER BY 1"- for preventing poorly coded SQL with missing join conditions from
causing impossibly huge cartesian joins which can tie up an important
production system for the weekend etc..Use EXPLAIN to find out what to set this to.
Generally useful?
Yes, sure does look useful to me!
e.g. statement_cost_limit = 10000000
This patch was discussed briefly on bizgres-general and is now being
submitted for discussion on main -hackers list.
Best Regards, Simon Riggs
Attachments:
statement_cost_limit.patchtext/x-patch; charset=UTF-8; name=statement_cost_limit.patchDownload
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.474
diff -c -r1.474 postgres.c
*** src/backend/tcop/postgres.c 31 Dec 2005 16:50:44 -0000 1.474
--- src/backend/tcop/postgres.c 8 Jan 2006 21:45:16 -0000
***************
*** 84,89 ****
--- 84,90 ----
/* GUC variable for maximum stack depth (measured in kilobytes) */
int max_stack_depth = 2048;
+ int StatementCostLimit = 0;
/* ----------------
* private variables
***************
*** 724,729 ****
--- 725,741 ----
/* call the optimizer */
plan = planner(querytree, false, 0, boundParams);
+ if (StatementCostLimit > 0 &&
+ plan->total_cost > StatementCostLimit)
+ ereport(ERROR,
+ (errcode(ERRCODE_STATEMENT_TOO_COMPLEX),
+ errmsg("statement cost limit exceeded"),
+ errhint("The plan for your query shows that it would likely "
+ "have an excessive run time. This may be due to a "
+ "logic error in the SQL, or it maybe just a very "
+ "costly query. Rewrite your query or increase the "
+ "configuration parameter \"statement_cost_limit\".")));
+
if (log_planner_stats)
ShowUsage("PLANNER STATISTICS");
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.305
diff -c -r1.305 guc.c
*** src/backend/utils/misc/guc.c 30 Dec 2005 00:13:50 -0000 1.305
--- src/backend/utils/misc/guc.c 8 Jan 2006 21:45:21 -0000
***************
*** 1270,1275 ****
--- 1270,1284 ----
},
{
+ {"statement_cost_limit", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Sets the maximum allowed plan cost for any query."),
+ gettext_noop("A value of 0 turns off the the cost limit.")
+ },
+ &StatementCostLimit,
+ 0, 0, INT_MAX, NULL, NULL
+ },
+
+ {
{"max_fsm_relations", PGC_POSTMASTER, RESOURCES_FSM,
gettext_noop("Sets the maximum number of tables and indexes for which free space is tracked."),
NULL
Index: src/include/tcop/tcopprot.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/tcop/tcopprot.h,v
retrieving revision 1.78
diff -c -r1.78 tcopprot.h
*** src/include/tcop/tcopprot.h 15 Oct 2005 02:49:46 -0000 1.78
--- src/include/tcop/tcopprot.h 8 Jan 2006 21:45:22 -0000
***************
*** 28,33 ****
--- 28,34 ----
extern CommandDest whereToSendOutput;
extern DLLIMPORT const char *debug_query_string;
extern int max_stack_depth;
+ extern int StatementCostLimit;
/* GUC-configurable parameters */
Import Notes
Reply to msg id not found: 43FA466D.3030908@paradise.net.nzReference msg id not found: 1140470867.12131.477.camel@localhost.localdomainReference msg id not found: 43FA466D.3030908@paradise.net.nz
Simon Riggs <simon@2ndquadrant.com> writes:
A new parameter that allows the administrator to place sensible limits
on the size of queries executed.
As I said when the idea was floated originally, I don't think this is a
very good idea at all. The planner's estimates are sufficiently often
wrong that refusing to execute queries on the strength of an estimated
cost is going to burn you in both directions.
Even if it were a good idea, the proposed location of the test is 100%
wrong, as you are only guarding one path of query submission. Or were
you intending that the restriction be trivial to subvert?
regards, tom lane
On Wed, 2006-03-01 at 11:47 -0500, Tom Lane wrote:
Simon Riggs <simon@2ndquadrant.com> writes:
A new parameter that allows the administrator to place sensible limits
on the size of queries executed.As I said when the idea was floated originally, I don't think this is a
very good idea at all. The planner's estimates are sufficiently often
wrong that refusing to execute queries on the strength of an estimated
cost is going to burn you in both directions.
That depends upon your view on risk. Some admins would rather abort a
few queries wrongly in less than a second than risk having a query run
for hours before being cancelled by statement_timeout. Most end-users
would agree with this, because if the answer is No they want to hear it
quickly so they can correct their mistake and continue.
But I think the estimates aren't sufficiently wrong to make a big
difference. People with a 100GB+ table can set it with sufficiently
useful accuracy to avoid pointless attempts to sort that table, for
example.
Even if it were a good idea, the proposed location of the test is 100%
wrong, as you are only guarding one path of query submission. Or were
you intending that the restriction be trivial to subvert?
The main idea was to guard the path by which ad-hoc queries would come,
but you might want to set it on a dev server also for example.
Its a discussion point as to whether we'd want it the way I've coded, or
whether you want to block other routes also. I can see things both ways
on that and have no problem changing the behaviour if that is the
consensus; that change would be fairly quick.
Best Regards, Simon Riggs
I can see this as useful for newbies who don't want to accidentally
overload the system.
---------------------------------------------------------------------------
Simon Riggs wrote:
On Wed, 2006-03-01 at 11:47 -0500, Tom Lane wrote:
Simon Riggs <simon@2ndquadrant.com> writes:
A new parameter that allows the administrator to place sensible limits
on the size of queries executed.As I said when the idea was floated originally, I don't think this is a
very good idea at all. The planner's estimates are sufficiently often
wrong that refusing to execute queries on the strength of an estimated
cost is going to burn you in both directions.That depends upon your view on risk. Some admins would rather abort a
few queries wrongly in less than a second than risk having a query run
for hours before being cancelled by statement_timeout. Most end-users
would agree with this, because if the answer is No they want to hear it
quickly so they can correct their mistake and continue.But I think the estimates aren't sufficiently wrong to make a big
difference. People with a 100GB+ table can set it with sufficiently
useful accuracy to avoid pointless attempts to sort that table, for
example.Even if it were a good idea, the proposed location of the test is 100%
wrong, as you are only guarding one path of query submission. Or were
you intending that the restriction be trivial to subvert?The main idea was to guard the path by which ad-hoc queries would come,
but you might want to set it on a dev server also for example.Its a discussion point as to whether we'd want it the way I've coded, or
whether you want to block other routes also. I can see things both ways
on that and have no problem changing the behaviour if that is the
consensus; that change would be fairly quick.Best Regards, Simon Riggs
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com
+ If your life is a hard drive, Christ can be your backup. +