On Tue, Jan 10, 2017 at 08:31:47AM -0800, David Fetter wrote:
> On Mon, Jan 09, 2017 at 07:52:11PM -0300, Alvaro Herrera wrote:
> > David Fetter wrote:
> >
> > > + if (query->commandType == CMD_UPDATE || query->commandType ==
> > > CMD_DELETE)
> > > + {
> > > + /* Make sure there's something to look at. */
> > > + Assert(query->jointree != NULL);
> > > + if (query->jointree->quals == NULL)
> > > + ereport(ERROR,
> > > + (errcode(ERRCODE_SYNTAX_ERROR),
> > > + errmsg("%s requires a WHERE clause
> > > when the require_where hook is enabled.",
> > > + query->commandType ==
> > > CMD_UPDATE ? "UPDATE" : "DELETE"),
> > > + errhint("To %s all rows, use \"WHERE
> > > true\" or similar.",
> > > + query->commandType ==
> > > CMD_UPDATE ? "update" : "delete")));
> > > + }
> >
> > Per my earlier comment, I think this should use
> > ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED instead.
>
> Fixed.
>
> > I think this should say "the \"require_hook\" extension" rather than
> > use the term "hook".
>
> Fixed.
>
> > (There are two or three translatability rules violations in this
> > snippet,
>
> Based on the hints in the docs docs around translation, I've
> refactored this a bit.
>
> > but since this is an extension and those are not translatable, I
> > won't say elaborate further.)
>
> "Not translatable," or "not currently translated?"
Oops^2. Correct patch attached and sent to correct list. :P
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
commit 9e65a67434a553b717ba735472cf3108f4ee0e23
Author: David Fetter <da...@fetter.org>
Date: Thu Jul 21 23:34:21 2016 -0700
require_where: a contrib hook
This adds a process utility hook which makes simple UPDATE and DELETE
statements require a WHERE clause when loaded.
It is not intended to provide a general capability. Instead, its job is to
prevent common human errors made by people who only rarely use SQL. The
hook
is small enough to be usable as part of a short lesson on hooks.
diff --git a/contrib/Makefile b/contrib/Makefile
index 25263c0..4bd456f 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -40,6 +40,7 @@ SUBDIRS = \
pgstattuple \
pg_visibility \
postgres_fdw \
+ require_where \
seg \
spi \
tablefunc \
diff --git a/contrib/require_where/Makefile b/contrib/require_where/Makefile
new file mode 100644
index 0000000..933eb00
--- /dev/null
+++ b/contrib/require_where/Makefile
@@ -0,0 +1,19 @@
+# contrib/require_where/Makefile
+
+MODULE_big = require_where
+OBJS = require_where.o
+
+PGFILEDESC = 'require_where - require simple DELETEs and UPDATEs to have a
WHERE clause'
+
+REGRESS = require_where
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS = $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/require_where
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_builddir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/require_where/expected/require_where.out
b/contrib/require_where/expected/require_where.out
new file mode 100644
index 0000000..3fe28ec
--- /dev/null
+++ b/contrib/require_where/expected/require_where.out
@@ -0,0 +1,16 @@
+--
+-- Test require_where
+--
+\set echo all
+CREATE TABLE test_require_where(t TEXT);
+UPDATE test_require_where SET t=t; -- succeeds
+DELETE FROM test_require_where; -- succeeds
+LOAD 'require_where';
+UPDATE test_require_where SET t=t; -- fails
+ERROR: UPDATE requires a WHERE clause when the "require_where" extension is
loaded.
+HINT: To update all rows, use "WHERE true" or similar.
+UPDATE test_require_where SET t=t WHERE true; -- succeeds
+DELETE FROM test_require_where; -- fails
+ERROR: DELETE requires a WHERE clause when the "require_where" extension is
loaded.
+HINT: To delete all rows, use "WHERE true" or similar.
+DELETE FROM test_require_where WHERE true; -- succeeds
diff --git a/contrib/require_where/require_where.c
b/contrib/require_where/require_where.c
new file mode 100644
index 0000000..0152a25
--- /dev/null
+++ b/contrib/require_where/require_where.c
@@ -0,0 +1,68 @@
+/*
+ * --------------------------------------------------------------------------
+ *
+ * require_where.c
+ *
+ * Copyright (C) 2017, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/require_where/require_where.c
+ *
+ * --------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#include "parser/analyze.h"
+
+#include "utils/elog.h"
+
+PG_MODULE_MAGIC;
+
+void _PG_init(void);
+void _PG_fini(void);
+
+static post_parse_analyze_hook_type original_post_parse_analyze_hook =
NULL;
+
+/*
+ * This module makes simple UPDATE and DELETE statements require a WHERE clause
+ * and complains when this is not present.
+ */
+static void
+require_where_check(ParseState *pstate, Query *query)
+{
+
+ if (query->commandType == CMD_UPDATE || query->commandType ==
CMD_DELETE)
+ {
+ /* Make sure there's something to look at. */
+ Assert(query->jointree != NULL);
+ if (query->jointree->quals == NULL)
+ {
+ if (query->commandType == CMD_UPDATE)
+ ereport(ERROR,
+
(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
+ errmsg("UPDATE requires a
WHERE clause when the \"require_where\" extension is loaded."),
+ errhint("To update all rows,
use \"WHERE true\" or similar.")));
+ else
+ ereport(ERROR,
+
(errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
+ errmsg("DELETE requires a
WHERE clause when the \"require_where\" extension is loaded."),
+ errhint("To delete all rows,
use \"WHERE true\" or similar.")));
+ }
+ }
+
+}
+
+void
+_PG_init(void)
+{
+ original_post_parse_analyze_hook = post_parse_analyze_hook;
+ post_parse_analyze_hook = require_where_check;
+}
+
+void
+_PG_fini(void)
+{
+ post_parse_analyze_hook = original_post_parse_analyze_hook;
+}
diff --git a/contrib/require_where/sql/require_where.sql
b/contrib/require_where/sql/require_where.sql
new file mode 100644
index 0000000..db8e078
--- /dev/null
+++ b/contrib/require_where/sql/require_where.sql
@@ -0,0 +1,21 @@
+--
+-- Test require_where
+--
+
+\set echo all
+
+CREATE TABLE test_require_where(t TEXT);
+
+UPDATE test_require_where SET t=t; -- succeeds
+
+DELETE FROM test_require_where; -- succeeds
+
+LOAD 'require_where';
+
+UPDATE test_require_where SET t=t; -- fails
+
+UPDATE test_require_where SET t=t WHERE true; -- succeeds
+
+DELETE FROM test_require_where; -- fails
+
+DELETE FROM test_require_where WHERE true; -- succeeds
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index c8708ec..48ca717 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -135,6 +135,7 @@ CREATE EXTENSION <replaceable>module_name</> FROM
unpackaged;
&pgtrgm;
&pgvisibility;
&postgres-fdw;
+ &require-where;
&seg;
&sepgsql;
&contrib-spi;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 69649a7..4552273 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -141,6 +141,7 @@
<!ENTITY pgtrgm SYSTEM "pgtrgm.sgml">
<!ENTITY pgvisibility SYSTEM "pgvisibility.sgml">
<!ENTITY postgres-fdw SYSTEM "postgres-fdw.sgml">
+<!ENTITY require-where SYSTEM "require_where.sgml">
<!ENTITY seg SYSTEM "seg.sgml">
<!ENTITY contrib-spi SYSTEM "contrib-spi.sgml">
<!ENTITY sepgsql SYSTEM "sepgsql.sgml">
diff --git a/doc/src/sgml/require_where.sgml b/doc/src/sgml/require_where.sgml
new file mode 100644
index 0000000..290ce7b
--- /dev/null
+++ b/doc/src/sgml/require_where.sgml
@@ -0,0 +1,54 @@
+<!-- doc/src/sgml/require_where.sgml -->
+
+<sect1 id="require-where" xreflabel="require_where">
+ <title>require_where</title>
+
+ <indexterm zone="require-where">
+ <primary>require_where</primary>
+ </indexterm>
+
+ <para>
+ This module makes it a requirement that WHERE on both DELETE or UPDATE be
present.
+ </para>
+
+ <para>
+ To use this module, you need to include <literal>require_where</literal> in
+ the either the <xref linkend="guc-shared-preload-libraries"> parameter, or
+ <command>LOAD</command> the library directly.
+ </para>
+
+ <para>
+ Here is an example showing how to set up a database cluster with
+ <literal>require_where</literal>.
+<screen>
+$ psql -U postgres
+# SHOW shared_preload_libraries; /* Make sure not to clobber something by
accident */
+
+If you found something,
+# ALTER SYSTEM SET
shared_preload_libraries='the,stuff,you,found,require_where';
+
+Otherwise,
+# ALTER SYSTEM SET shared_preload_libraries='require_where';
+
+Then restart <productname>PostgreSQL</productname>
+</screen>
+ </para>
+
+ <para>
+ Here is an example using <command>LOAD</command>:
+<screen>
+$ psql -U postgres
+# LOAD '$libdir/require_where';
+</screen>
+ </para>
+
+ <sect2>
+ <title>Authors</title>
+
+ <para>
+ David Fetter <email>da...@fetter.org</email>,
+ Robert Haas <email>robertmh...@gmail.com</email> and
+ Andrew Gierth <email>and...@tao11.riddles.org.uk</email>.
+ </para>
+ </sect2>
+</sect1>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
