Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1

Hi all,

While I was trying to find the right place to add a new page on the
wiki, I found the document of Simon on partitioning requirements
(http://wiki.postgresql.org/wiki/Image:Partitioning_Requirements.pdf)
referenced from http://wiki.postgresql.org/wiki/Development_projects
I think  this is a good base to start from. Should we convert the doc
into a wiki page or get the source for the doc and go from there?

I attach what I have come up with so far for the C trigger I was talking
about for efficient automatic auto-partitioning of inserts in child tables.

Emmanuel

Robert Haas wrote:
> On Thu, Nov 27, 2008 at 11:09 AM, Emmanuel Cecchet <manu@frogthinker.org> wrote:
>
>> I have been following that discussion very closely but it seems that we are
>> debating solutions without a good specification of the problem/requirements.
>> I would suggest that we collect all the partitioning requirements on a
>> dedicated Wiki page. There might not be a one size fits it all solution for
>> all requirements. We can also look at what other databases are proposing to
>> address these issues.
>> If we can prioritize features, that should also allow us to stage the
>> partitioning implementation.
>>
>
> This might be a good idea.  Want to take a crack at it?
>
>
>> I have a prototype insert trigger in C that directly move inserts in a
>> master table to the appropriate child table (directly moving the tuple). Let
>> me know if anyone is interested.
>>
>
> Can't hurt to post it.
>
> ...Robert
>
>


--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet

### Eclipse Workspace Patch 1.0
#P Postgres-HEAD
Index: src/test/regress/regress.c
===================================================================
RCS file: /root/cvsrepo/pgsql/src/test/regress/regress.c,v
retrieving revision 1.71
diff -u -r1.71 regress.c
--- src/test/regress/regress.c    25 Mar 2008 22:42:46 -0000    1.71
+++ src/test/regress/regress.c    13 Nov 2008 06:11:08 -0000
@@ -10,6 +10,9 @@
 #include "utils/geo_decls.h"    /* includes <math.h> */
 #include "executor/executor.h"    /* For GetAttributeByName */
 #include "commands/sequence.h"    /* for nextval() */
+#include "catalog/namespace.h"
+#include "executor/executor.h"
+#include "executor/tuptable.h"

 #define P_MAXDIG 12
 #define LDELIM            '('
@@ -732,3 +735,90 @@
     *--walk = '\0';
     PG_RETURN_CSTRING(result);
 }
+
+
+/*
+ * Partition trigger test
+ *
+ * The trigger should be used this way:
+ * CREATE TRIGGER child_table_name
+    BEFORE INSERT ON master_table
+    FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
+ */
+
+extern Datum partition_insert_trigger(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(partition_insert_trigger);
+
+Datum
+partition_insert_trigger(PG_FUNCTION_ARGS)
+{
+    TriggerData *trigdata = (TriggerData *) fcinfo->context;
+    HeapTuple    trigtuple= trigdata->tg_trigtuple;
+    char        *child_table_name;
+    Relation child_table_relation;
+    Oid relation_id;
+
+    /* make sure it's called as a trigger at all */
+    if (!CALLED_AS_TRIGGER(fcinfo))
+        elog(ERROR, "partition_insert_trigger: not called by trigger manager");
+
+    /* Sanity checks */
+    if (!TRIGGER_FIRED_BY_INSERT(trigdata->tg_event) || !TRIGGER_FIRED_BEFORE(trigdata->tg_event))
+        elog(ERROR, "partition_insert_trigger: not called on insert before");
+
+    // Child table name is either given as the unique parameter or it is the name of the trigger
+    if (trigdata->tg_trigger->tgnargs == 1)
+        child_table_name = trigdata->tg_trigger->tgargs[0];
+    else
+        child_table_name = trigdata->tg_trigger->tgname;
+
+    // Lookup the child relation
+    relation_id = RelnameGetRelid(child_table_name);
+    if (relation_id == InvalidOid)
+        elog(ERROR, "partition_insert_trigger: Invalid child table %s", child_table_name);
+    child_table_relation = RelationIdGetRelation(relation_id);
+    if (child_table_relation == NULL)
+        elog(ERROR, "partition_insert_trigger: Failed to locate relation for child table %s", child_table_name);
+
+    { // Check the constraints
+        TupleConstr *constr = child_table_relation->rd_att->constr;
+
+        if (constr->num_check > 0)
+        {
+            ResultRelInfo *resultRelInfo;
+            TupleTableSlot *slot;
+            EState *estate= CreateExecutorState();
+
+            resultRelInfo = makeNode(ResultRelInfo);
+            resultRelInfo->ri_RangeTableIndex = 1;        /* dummy */
+            resultRelInfo->ri_RelationDesc = child_table_relation;
+
+            estate->es_result_relations = resultRelInfo;
+            estate->es_num_result_relations = 1;
+            estate->es_result_relation_info = resultRelInfo;
+
+            /* Set up a tuple slot too */
+            slot = MakeSingleTupleTableSlot(trigdata->tg_relation->rd_att);
+            ExecStoreTuple(trigtuple, slot, InvalidBuffer, false);
+
+            if (ExecRelCheck(resultRelInfo, slot, estate) == NULL)
+            { // Constraints satisfied, insert the row in the child table
+                bool use_wal = true;
+                bool use_fsm=true;
+
+                heap_insert(child_table_relation, trigtuple, GetCurrentCommandId(true), use_wal, use_fsm);
+                RelationClose(child_table_relation);
+                ExecDropSingleTupleTableSlot(slot);
+                return PointerGetDatum(NULL);
+            }
+            ExecDropSingleTupleTableSlot(slot);
+        }
+        else
+            elog(ERROR, "partition_insert_trigger: No constraint found for child table %s", child_table_name);
+    }
+    RelationClose(child_table_relation);
+
+    return PointerGetDatum(trigdata->tg_trigtuple);
+}
+
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /root/cvsrepo/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.314
diff -u -r1.314 execMain.c
--- src/backend/executor/execMain.c    31 Oct 2008 21:07:54 -0000    1.314
+++ src/backend/executor/execMain.c    13 Nov 2008 06:11:08 -0000
@@ -1947,7 +1947,7 @@
 /*
  * ExecRelCheck --- check that tuple meets constraints for result relation
  */
-static const char *
+const char *
 ExecRelCheck(ResultRelInfo *resultRelInfo,
              TupleTableSlot *slot, EState *estate)
 {
Index: src/test/regress/output/create_function_1.source
===================================================================
RCS file: /root/cvsrepo/pgsql/src/test/regress/output/create_function_1.source,v
retrieving revision 1.34
diff -u -r1.34 create_function_1.source
--- src/test/regress/output/create_function_1.source    31 Oct 2008 19:37:56 -0000    1.34
+++ src/test/regress/output/create_function_1.source    13 Nov 2008 06:11:08 -0000
@@ -47,6 +47,10 @@
         RETURNS int4
         AS '@libdir@/regress@DLSUFFIX@'
         LANGUAGE C STRICT;
+CREATE FUNCTION partition_insert_trigger ()
+        RETURNS trigger
+        AS '@libdir@/regress@DLSUFFIX@'
+        LANGUAGE C STRICT;
 -- Things that shouldn't work:
 CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
     AS 'SELECT ''not an integer'';';
@@ -80,3 +84,70 @@
 CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal
     AS 'nosuch';
 ERROR:  there is no built-in function named "nosuch"
+-- Partitioning trigger test
+CREATE TABLE master (
+  id     int not null,
+  date   date not null,
+  value  int
+);
+CREATE TABLE child_y2008m01 (
+    CHECK ( date >= DATE '2008-01-01' AND date < DATE '2008-02-01' )
+) INHERITS (master);
+CREATE TABLE child_y2008m02 (
+    CHECK ( date >= DATE '2008-02-01' AND date < DATE '2008-03-01' )
+) INHERITS (master);
+CREATE TABLE child_y2008m03 (
+    CHECK ( date >= DATE '2008-03-01' AND date < DATE '2008-04-01' )
+) INHERITS (master);
+CREATE TRIGGER insert_child_y2008m01
+    BEFORE INSERT ON master
+    FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger('child_y2008m01');
+CREATE TRIGGER child_y2008m02
+    BEFORE INSERT ON master
+    FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
+CREATE TRIGGER child_y2008m03
+    BEFORE INSERT ON master
+    FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
+INSERT INTO master VALUES (1, '2008-01-15', 1);
+INSERT INTO master VALUES (2, '2008-02-15', 2);
+INSERT INTO master VALUES (3, '2008-03-15', 3);
+INSERT INTO master VALUES (4, '2008-04-15', 4);
+COPY master FROM '/root/pg_partitions/copy_input.txt';
+select * from master;
+ id |    date    | value
+----+------------+-------
+  4 | 04-15-2008 |     4
+  1 | 01-15-2008 |     1
+ 11 | 01-10-2008 |    11
+  2 | 02-15-2008 |     2
+ 12 | 02-15-2008 |    12
+  3 | 03-15-2008 |     3
+ 13 | 03-15-2008 |    13
+(7 rows)
+
+ select * from child_y2008m01;
+ id |    date    | value
+----+------------+-------
+  1 | 01-15-2008 |     1
+ 11 | 01-10-2008 |    11
+(2 rows)
+
+  select * from child_y2008m02;
+ id |    date    | value
+----+------------+-------
+  2 | 02-15-2008 |     2
+ 12 | 02-15-2008 |    12
+(2 rows)
+
+  select * from child_y2008m03;
+ id |    date    | value
+----+------------+-------
+  3 | 03-15-2008 |     3
+ 13 | 03-15-2008 |    13
+(2 rows)
+
+DROP TABLE master CASCADE;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to table child_y2008m01
+drop cascades to table child_y2008m02
+drop cascades to table child_y2008m03
Index: src/include/executor/executor.h
===================================================================
RCS file: /root/cvsrepo/pgsql/src/include/executor/executor.h,v
retrieving revision 1.152
diff -u -r1.152 executor.h
--- src/include/executor/executor.h    31 Oct 2008 21:07:55 -0000    1.152
+++ src/include/executor/executor.h    13 Nov 2008 06:11:08 -0000
@@ -155,6 +155,8 @@
 extern bool ExecContextForcesOids(PlanState *planstate, bool *hasoids);
 extern void ExecConstraints(ResultRelInfo *resultRelInfo,
                 TupleTableSlot *slot, EState *estate);
+extern const char *ExecRelCheck(ResultRelInfo *resultRelInfo,
+             TupleTableSlot *slot, EState *estate);
 extern TupleTableSlot *EvalPlanQual(EState *estate, Index rti,
              ItemPointer tid, TransactionId priorXmax);
 extern PlanState *ExecGetActivePlanTree(QueryDesc *queryDesc);
Index: src/test/regress/input/create_function_1.source
===================================================================
RCS file: /root/cvsrepo/pgsql/src/test/regress/input/create_function_1.source,v
retrieving revision 1.19
diff -u -r1.19 create_function_1.source
--- src/test/regress/input/create_function_1.source    1 Oct 2008 22:38:57 -0000    1.19
+++ src/test/regress/input/create_function_1.source    13 Nov 2008 06:11:08 -0000
@@ -52,6 +52,12 @@
         AS '@libdir@/regress@DLSUFFIX@'
         LANGUAGE C STRICT;

+CREATE FUNCTION partition_insert_trigger ()
+        RETURNS trigger
+        AS '@libdir@/regress@DLSUFFIX@'
+        LANGUAGE C STRICT;
+
+
 -- Things that shouldn't work:

 CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
@@ -77,3 +83,48 @@

 CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal
     AS 'nosuch';
+
+-- Partitioning trigger test
+
+CREATE TABLE master (
+  id     int not null,
+  date   date not null,
+  value  int
+);
+
+CREATE TABLE child_y2008m01 (
+    CHECK ( date >= DATE '2008-01-01' AND date < DATE '2008-02-01' )
+) INHERITS (master);
+
+CREATE TABLE child_y2008m02 (
+    CHECK ( date >= DATE '2008-02-01' AND date < DATE '2008-03-01' )
+) INHERITS (master);
+
+CREATE TABLE child_y2008m03 (
+    CHECK ( date >= DATE '2008-03-01' AND date < DATE '2008-04-01' )
+) INHERITS (master);
+
+CREATE TRIGGER insert_child_y2008m01
+    BEFORE INSERT ON master
+    FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger('child_y2008m01');
+
+CREATE TRIGGER child_y2008m02
+    BEFORE INSERT ON master
+    FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
+
+CREATE TRIGGER child_y2008m03
+    BEFORE INSERT ON master
+    FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
+
+INSERT INTO master VALUES (1, '2008-01-15', 1);
+INSERT INTO master VALUES (2, '2008-02-15', 2);
+INSERT INTO master VALUES (3, '2008-03-15', 3);
+INSERT INTO master VALUES (4, '2008-04-15', 4);
+COPY master FROM '/root/pg_partitions/copy_input.txt';
+
+select * from master;
+select * from child_y2008m01;
+select * from child_y2008m02;
+select * from child_y2008m03;
+
+DROP TABLE master CASCADE;