David Fetter <da...@fetter.org> wrote:
> It's missing a few pieces like surfacing transition table names.
> I'll work on those. Also, it's not clear to me how to access the
> pre- and post- relations at the same time, this being necessary
> for many use cases. I guess I need to think more about how that
> would be done.
If you're going to do any work on the C extension, please start
from the attached. I renamed it to something which seemed more
meaningful (to me, at least), and cleaned up some cosmetic issues.
The substance is the same.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/Makefile b/contrib/Makefile
index b37d0dd..bcd7c28 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -48,6 +48,7 @@ SUBDIRS = \
postgres_fdw \
seg \
spi \
+ transition_tables \
tablefunc \
tcn \
test_decoding \
diff --git a/contrib/transition_tables/.gitignore b/contrib/transition_tables/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/contrib/transition_tables/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/transition_tables/Makefile b/contrib/transition_tables/Makefile
new file mode 100644
index 0000000..8a75350
--- /dev/null
+++ b/contrib/transition_tables/Makefile
@@ -0,0 +1,20 @@
+# contrib/transition_tables/Makefile
+
+MODULE_big = transition_tables
+OBJS = transition_tables.o
+
+EXTENSION = transition_tables
+DATA = transition_tables--1.0.sql
+
+REGRESS = transition_tables
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/transition_tables
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/transition_tables/expected/transition_tables.out b/contrib/transition_tables/expected/transition_tables.out
new file mode 100644
index 0000000..7dfed35
--- /dev/null
+++ b/contrib/transition_tables/expected/transition_tables.out
@@ -0,0 +1,18 @@
+CREATE EXTENSION transition_tables;
+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 transition_tables();
+INSERT INTO e(i)
+SELECT * FROM generate_series(1,10000);
+NOTICE: Total change: 50005000
+UPDATE e SET i=i+1;
+NOTICE: Total change: 10000
+DELETE FROM e WHERE i < 5000;
+NOTICE: Total change: -12497499
diff --git a/contrib/transition_tables/sql/transition_tables.sql b/contrib/transition_tables/sql/transition_tables.sql
new file mode 100644
index 0000000..04f8bd7
--- /dev/null
+++ b/contrib/transition_tables/sql/transition_tables.sql
@@ -0,0 +1,20 @@
+CREATE EXTENSION transition_tables;
+
+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 transition_tables();
+
+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/transition_tables/transition_tables--1.0.sql b/contrib/transition_tables/transition_tables--1.0.sql
new file mode 100644
index 0000000..6c1625e
--- /dev/null
+++ b/contrib/transition_tables/transition_tables--1.0.sql
@@ -0,0 +1,10 @@
+/* contrib/transition_tables--1.0.sql */
+
+
+-- Complain if script is sourced in psql, rather than via CREATE EXTENSION.
+\echo Use "CREATE EXTENSION transition_tables" to load this file. \quit
+
+CREATE FUNCTION transition_tables()
+RETURNS pg_catalog.trigger
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
diff --git a/contrib/transition_tables/transition_tables.c b/contrib/transition_tables/transition_tables.c
new file mode 100644
index 0000000..2b9264c
--- /dev/null
+++ b/contrib/transition_tables/transition_tables.c
@@ -0,0 +1,144 @@
+/*-------------------------------------------------------------------------
+ *
+ * transition_tables.c
+ * sample of accessing trigger transition tables from a C trigger
+ *
+ * Portions Copyright (c) 2011-2014, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * contrib/transition_tables.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "commands/trigger.h"
+#include "utils/rel.h"
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(transition_tables);
+
+Datum
+transition_tables(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_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("transition_tables: must be called as trigger")));
+
+ if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("transition_tables: must be called after the change")));
+
+ if (!TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("transition_tables: must be called for each statement")));
+
+ tupdesc = trigdata->tg_relation->rd_att;
+ slot = MakeSingleTupleTableSlot(tupdesc);
+
+ /*
+ * Since other code may have already used the tuplestore(s), reset to the
+ * start before reading.
+ */
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ {
+ if (trigdata->tg_newdelta == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("NEW TABLE was not specified in the CREATE TRIGGER statement")));
+
+ new_tuplestore = trigdata->tg_newdelta;
+ 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,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("OLD TABLE was not specified in the 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 if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ {
+ if (trigdata->tg_olddelta == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("OLD TABLE was not specified in the CREATE TRIGGER statement")));
+ if (trigdata->tg_newdelta == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("NEW TABLE was not specified in the 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, adding or subtracting
+ * 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);
+ }
+
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("transition_tables: only INSERT, UPDATE, and DELETE triggers may use transition tables")));
+
+ ExecDropSingleTupleTableSlot(slot);
+
+ ereport(NOTICE, (errmsg("Total change: " INT64_FORMAT, delta)));
+
+ return PointerGetDatum(NULL); /* after trigger; value doesn't matter */
+}
diff --git a/contrib/transition_tables/transition_tables.control b/contrib/transition_tables/transition_tables.control
new file mode 100644
index 0000000..486dde7
--- /dev/null
+++ b/contrib/transition_tables/transition_tables.control
@@ -0,0 +1,5 @@
+# transition_tables extension
+comment = 'Sample code for using trigger transition tables'
+default_version = '1.0'
+module_pathname = '$libdir/transition_tables'
+relocatable = true
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
