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
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 */
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
