On Sat, Jun 21, 2014 at 11:06:26AM -0700, Kevin Grittner wrote:
> Kevin Grittner <[email protected]> wrote:
> > Kevin Grittner <[email protected]> wrote:
> >
> >> I've already said that I now think we should use the standard
> >> CREATE TRIGGER syntax to specify the transition tables, and that
> >> if we do that we don't need the reloption that's in the patch.
> >> If you ignore the 19 lines of new code to add that reloption,
> >> absolutely 100% of the code changes in the patch so far are in
> >> trigger.c and trigger.h.
> >
> > Although nobody has actually framed their feedback as a review, I
> > feel that I have enough to work with to throw the patch into
> > Waiting on Author status. Since I started with the assumption
> > that I was going to be using standard syntax and got a ways into
> > that before convincing myself it was a bad idea, I should have a
> > new version of the patch working that way in a couple days.
>
> Here is v2.
Thanks!
I've taken the liberty of making an extension that uses this.
Preliminary tests indicate a 10x performance improvement over the
user-space hack I did that's similar in functionality.
Please find attached the extension, etc., which I've published to
https://github.com/davidfetter/postgresql_projects/tree/test_delta_v2
Cheers,
David.
--
David Fetter <[email protected]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [email protected]
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/contrib/statement_trigger_row/Makefile
b/contrib/statement_trigger_row/Makefile
new file mode 100644
index 0000000..e0cf006
--- /dev/null
+++ b/contrib/statement_trigger_row/Makefile
@@ -0,0 +1,17 @@
+# contrib/statement_trigger_row/Makefile
+
+MODULES = statement_trigger_row
+
+EXTENSION = statement_trigger_row
+DATA = statement_trigger_row--1.0.sql
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/statement_trigger_row
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/statement_trigger_row/sql/easy_way.sql
b/contrib/statement_trigger_row/sql/easy_way.sql
new file mode 100644
index 0000000..019ae7f
--- /dev/null
+++ b/contrib/statement_trigger_row/sql/easy_way.sql
@@ -0,0 +1,85 @@
+/*
+ * If these were surfaced to PL/pgsql, this is what it might look like.
+ */
+
+BEGIN;
+
+CREATE TABLE a(
+ id SERIAL PRIMARY KEY,
+ i INT
+);
+
+CREATE FUNCTION summarize_a_inserts()
+RETURNS TRIGGER LANGUAGE plpgsql
+AS $$
+DECLARE
+ the_sum BIGINT;
+BEGIN
+ SELECT INTO the_sum sum(NEW.i)
+ FROM
+ new_a;
+ RAISE NOTICE 'Total change: %.', the_sum;
+ RETURN NULL;
+END;
+$$;
+
+CREATE FUNCTION summarize_a_updates()
+RETURNS TRIGGER LANGUAGE plpgsql
+AS $$
+DECLARE
+ the_sum BIGINT;
+BEGIN
+ SELECT INTO the_sum sum(COALESCE(NEW.i,0) - COALESCE(OLD.i, 0))
+ FROM
+ old_a
+ JOIN
+ new_a
+ ON(old_a.id = new_a.id);
+ RAISE NOTICE 'Total change: %.', the_sum;
+ RETURN NULL;
+END;
+$$;
+
+CREATE FUNCTION summarize_a_deletes()
+RETURNS TRIGGER LANGUAGE plpgsql
+AS $$
+DECLARE
+ the_sum BIGINT;
+BEGIN
+ SELECT INTO the_sum -1 * sum(OLD.i)
+ FROM
+ old_a;
+ RAISE NOTICE 'Total change: %.', the_sum;
+ RETURN NULL;
+END;
+$$;
+
+CREATE TRIGGER statement_after_insert_a
+ AFTER INSERT ON a
+ REFERENCING
+ NEW TABLE AS new_a
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE summarize_a_inserts();
+
+CREATE TRIGGER statement_after_update_a
+ AFTER UPDATE ON a
+ REFERENCING
+ OLD TABLE AS old_a
+ NEW TABLE AS new_a
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE summarize_a_updates();
+
+CREATE TRIGGER statement_after_delete_a
+ AFTER DELETE ON a
+ REFERENCING
+ OLD TABLE AS old_a
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE summarize_a_deletes();
+
+INSERT INTO a(i)
+SELECT * FROM generate_series(1,10000);
+
+UPDATE a SET i=i+1;
+
+ROLLBACK;
+
diff --git a/contrib/statement_trigger_row/sql/hard_way.sql
b/contrib/statement_trigger_row/sql/hard_way.sql
new file mode 100644
index 0000000..c6f7c1d
--- /dev/null
+++ b/contrib/statement_trigger_row/sql/hard_way.sql
@@ -0,0 +1,68 @@
+CREATE TABLE IF NOT EXISTS h(
+ i INTEGER
+);
+
+CREATE FUNCTION set_up_h_rows()
+RETURNS TRIGGER LANGUAGE plpgsql
+AS $$
+BEGIN
+ CREATE TEMPORARY TABLE IF NOT EXISTS h_rows(LIKE a) ON COMMIT DROP;
+ RETURN NULL;
+END;
+$$;
+
+CREATE TRIGGER statement_before_writing_h
+ BEFORE INSERT OR UPDATE OR DELETE ON a
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE set_up_h_rows();
+
+CREATE OR REPLACE FUNCTION stash_h_row_deltas()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ INSERT INTO h_rows(i)
+ VALUES(
+ CASE TG_OP
+ WHEN 'INSERT' THEN COALESCE(NEW.i,0)
+ WHEN 'UPDATE' THEN COALESCE(NEW.i,0) - COALESCE(OLD.i,0)
+ WHEN 'DELETE' THEN -1 * COALESCE(OLD.i,0)
+ END
+ );
+ IF TG_OP IN ('INSERT','UPDATE')
+ THEN
+ RETURN NEW;
+ ELSE
+ RETURN OLD;
+ END IF;
+END;
+$$;
+
+CREATE TRIGGER during_trg
+ BEFORE INSERT OR UPDATE OR DELETE ON a
+ FOR EACH ROW
+ EXECUTE PROCEDURE stash_h_row_deltas();
+
+CREATE FUNCTION summarize_h_rows()
+RETURNS TRIGGER LANGUAGE plpgsql
+AS $$
+DECLARE the_sum BIGINT;
+BEGIN
+ SELECT INTO the_sum sum(i) FROM h_rows;
+ RAISE NOTICE 'Total change: %.', the_sum;
+ TRUNCATE h_rows;
+ RETURN NULL;
+END;
+$$;
+
+CREATE TRIGGER statement_after_writing_h
+ AFTER INSERT OR UPDATE OR DELETE ON a
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE summarize_h_rows();
+
+INSERT INTO h(i)
+SELECT * FROM generate_series(1,10000);
+
+UPDATE h SET i=i+1;
+
+DELETE FROM h WHERE i < 5000;
diff --git a/contrib/statement_trigger_row/sql/statement_trigger_row.sql
b/contrib/statement_trigger_row/sql/statement_trigger_row.sql
new file mode 100644
index 0000000..4d5925a
--- /dev/null
+++ b/contrib/statement_trigger_row/sql/statement_trigger_row.sql
@@ -0,0 +1,18 @@
+CREATE TABLE IF NOT EXISTS e(
+ i INT
+);
+
+CREATE TRIGGER statement_dml_e
+ AFTER INSERT OR UPDATE OR DELETE ON e
+ REFERENCING
+ OLD TABLE AS old_e
+ NEW TABLE AS new_e
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE statement_trigger_row();
+
+INSERT INTO e(i)
+SELECT * FROM generate_series(1,10000);
+
+UPDATE e SET i=i+1;
+
+DELETE FROM e WHERE i < 5000;
diff --git a/contrib/statement_trigger_row/statement_trigger_row--1.0.sql
b/contrib/statement_trigger_row/statement_trigger_row--1.0.sql
new file mode 100644
index 0000000..ffc0e36
--- /dev/null
+++ b/contrib/statement_trigger_row/statement_trigger_row--1.0.sql
@@ -0,0 +1,10 @@
+/* contrib/statement_trigger_row--1.0.sql */
+
+
+-- Complain if script is sourced in psql, rather than via CREATE EXTENSION.
+\echo Use "CREATE EXTENSION statement_trigger_row" to load this file. \quit
+
+CREATE FUNCTION statement_trigger_row()
+RETURNS pg_catalog.trigger
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
diff --git a/contrib/statement_trigger_row/statement_trigger_row.c
b/contrib/statement_trigger_row/statement_trigger_row.c
new file mode 100644
index 0000000..048eb3b
--- /dev/null
+++ b/contrib/statement_trigger_row/statement_trigger_row.c
@@ -0,0 +1,140 @@
+/*-------------------------------------------------------------------------
+ *
+ * statement_trigger_row.c
+ * statement_trigger_row support for PostgreSQL
+ *
+ * Portions Copyright (c) 2011-2014, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * contrib/statement_trigger_row.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "executor/spi.h"
+#include "commands/trigger.h"
+#include "utils/rel.h"
+
+#ifdef PG_MODULE_MAGIC
+PG_MODULE_MAGIC;
+#endif
+
+extern Datum statement_trigger_row(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(statement_trigger_row);
+
+Datum
+statement_trigger_row(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ TupleDesc tupdesc;
+ TupleTableSlot *slot;
+ Tuplestorestate *new_tuplestore;
+ Tuplestorestate *old_tuplestore;
+ int64 delta = 0;
+
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("statement_trigger_row: not
called by trigger manager")));
+ }
+
+ if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("statement_trigger_row: not
called by AFTER trigger")));
+ }
+
+ if (TRIGGER_FIRED_BY_TRUNCATE(trigdata->tg_event))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("You may not call this function
in a TRUNCATE trigger.")));
+ }
+
+ tupdesc = trigdata->tg_relation->rd_att;
+
+ slot = MakeSingleTupleTableSlot(tupdesc);
+
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ {
+ if (trigdata->tg_newdelta == NULL)
+ ereport(ERROR,
+ (errmsg("You must include NEW TABLE AS
in your CREATE TRIGGER statement")));
+
+ new_tuplestore = trigdata->tg_newdelta;
+ /*
+ * Ensure that we're at the right place in the tuplestore, as
+ * other triggers may have messed with the state.
+ */
+ tuplestore_rescan(new_tuplestore);
+
+ /* Iterate through the new tuples, adding. */
+ while (tuplestore_gettupleslot(new_tuplestore, true, false,
slot)) {
+ bool isnull;
+ Datum val = slot_getattr(slot, 1, &isnull);
+ if (!isnull)
+ delta += DatumGetInt32(val);
+ }
+ }
+ else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
+ {
+ if (trigdata->tg_olddelta == NULL)
+ ereport(ERROR,
+ (errmsg("You must include OLD TABLE AS
in your CREATE TRIGGER statement")));
+
+ old_tuplestore = trigdata->tg_olddelta;
+ tuplestore_rescan(old_tuplestore);
+ /* Iterate through the old tuples, subtracting. */
+ while (tuplestore_gettupleslot(old_tuplestore, true, false,
slot)) {
+ bool isnull;
+ Datum val = slot_getattr(slot, 1, &isnull);
+ if (!isnull)
+ delta -= DatumGetInt32(val);
+ }
+ }
+ else /* It's an UPDATE */
+ {
+ if (trigdata->tg_olddelta == NULL)
+ ereport(ERROR,
+ (errmsg("You must include OLD TABLE AS
in your CREATE TRIGGER statement")));
+ if (trigdata->tg_newdelta == NULL)
+ ereport(ERROR,
+ (errmsg("You must include NEW TABLE AS
in your CREATE TRIGGER statement")));
+
+ old_tuplestore = trigdata->tg_olddelta;
+ new_tuplestore = trigdata->tg_newdelta;
+
+ tuplestore_rescan(old_tuplestore);
+ tuplestore_rescan(new_tuplestore);
+
+ /* Iterate through both the new and old tuples, incrementing
+ * or decrementing as needed. */
+ while (tuplestore_gettupleslot(new_tuplestore, true, false,
slot)) {
+ bool isnull;
+ Datum val = slot_getattr(slot, 1, &isnull);
+ if (!isnull)
+ delta += DatumGetInt32(val);
+ }
+
+ while (tuplestore_gettupleslot(old_tuplestore, true, false,
slot)) {
+ bool isnull;
+ Datum val = slot_getattr(slot, 1, &isnull);
+ if (!isnull)
+ delta -= DatumGetInt32(val);
+ }
+
+ }
+
+ ExecDropSingleTupleTableSlot(slot);
+
+ ereport(NOTICE, (errmsg("Total change: " INT64_FORMAT, delta)));
+
+ return PointerGetDatum(NULL);
+
+}
diff --git a/contrib/statement_trigger_row/statement_trigger_row.control
b/contrib/statement_trigger_row/statement_trigger_row.control
new file mode 100644
index 0000000..846ea2d
--- /dev/null
+++ b/contrib/statement_trigger_row/statement_trigger_row.control
@@ -0,0 +1,5 @@
+# statement_trigger_row extension
+comment = 'Statement trigger row'
+default_version = '1.0'
+module_pathname = '$libdir/statement_trigger_row'
+relocatable = true
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
