Folks,
Please find attached a patch which makes it possible to disallow
UPDATEs and DELETEs which lack a WHERE clause. As this changes query
behavior, I've made the new GUCs PGC_SUSET.
What say?
Thanks to Gurjeet Singh for the idea and Andrew Gierth for the tips
implementing.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index c9e0ec2..c01db8d 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7019,6 +7019,32 @@ dynamic_library_path =
'C:\tools\postgresql;H:\my_project\lib;$libdir'
</listitem>
</varlistentry>
+ <varlistentry id="guc-allow-empty-updates"
xreflabel="allow_empty_updates">
+ <term><varname>allow_empty_updates</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>allow_empty_updates</varname> configuration
parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Allow UPDATE statements that lack a WHERE clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-allow-empty-deletes"
xreflabel="allow_empty_deletes">
+ <term><varname>allow_empty_deletes</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>allow_empty_deletes</varname> configuration
parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Allow DELETE statements that lack a WHERE clause.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
</sect1>
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 29c8c4e..0c8786e 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -48,6 +48,9 @@
/* Hook for plugins to get control at end of parse analysis */
post_parse_analyze_hook_type post_parse_analyze_hook = NULL;
+bool allow_empty_deletes = true;
+bool allow_empty_updates = true;
+
static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt);
static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt);
static List *transformInsertRow(ParseState *pstate, List *exprlist,
@@ -408,6 +411,12 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
+ /* Check for allow_empty_deletes */
+ if (!allow_empty_deletes && qual == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_STATEMENT_HAS_NO_WHERE),
+ errmsg("DELETE without a WHERE clause is
disallowed")));
+
qry->returningList = transformReturningList(pstate,
stmt->returningList);
/* done building the range table and jointree */
@@ -2110,6 +2119,12 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
+ /* Check for allow_empty_updates */
+ if (!allow_empty_updates && qual == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_STATEMENT_HAS_NO_WHERE),
+ errmsg("UPDATE without a WHERE clause is
disallowed")));
+
qry->returningList = transformReturningList(pstate,
stmt->returningList);
/*
diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt
index be924d5..254cf10 100644
--- a/src/backend/utils/errcodes.txt
+++ b/src/backend/utils/errcodes.txt
@@ -388,6 +388,7 @@ Section: Class 54 - Program Limit Exceeded
# this is for wired-in limits, not resource exhaustion problems (class
borrowed from DB2)
54000 E ERRCODE_PROGRAM_LIMIT_EXCEEDED
program_limit_exceeded
54001 E ERRCODE_STATEMENT_TOO_COMPLEX
statement_too_complex
+54002 E ERRCODE_STATEMENT_HAS_NO_WHERE
statement_has_no_where
54011 E ERRCODE_TOO_MANY_COLUMNS
too_many_columns
54023 E ERRCODE_TOO_MANY_ARGUMENTS
too_many_arguments
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 6ac5184..fca6b71 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -47,6 +47,7 @@
#include "optimizer/geqo.h"
#include "optimizer/paths.h"
#include "optimizer/planmain.h"
+#include "parser/analyze.h"
#include "parser/parse_expr.h"
#include "parser/parse_type.h"
#include "parser/parser.h"
@@ -1653,6 +1654,26 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
+ {
+ {"allow_empty_deletes", PGC_SUSET, QUERY_TUNING,
+ gettext_noop("Allow DELETE without a WHERE clause"),
+ NULL
+ },
+ &allow_empty_deletes,
+ true,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"allow_empty_updates", PGC_SUSET, QUERY_TUNING,
+ gettext_noop("Allow UPDATE without a WHERE clause"),
+ NULL
+ },
+ &allow_empty_updates,
+ true,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h
index 5ba322a..b88f8cd 100644
--- a/src/include/parser/analyze.h
+++ b/src/include/parser/analyze.h
@@ -42,4 +42,7 @@ extern void applyLockingClause(Query *qry, Index rtindex,
LockClauseStrength strength,
LockWaitPolicy waitPolicy, bool pushedDown);
+extern bool allow_empty_deletes;
+extern bool allow_empty_updates;
+
#endif /* ANALYZE_H */
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
