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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
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@/[EMAIL PROTECTED]@'
LANGUAGE C STRICT;
+CREATE FUNCTION partition_insert_trigger ()
+ RETURNS trigger
+ AS '@libdir@/[EMAIL PROTECTED]@'
+ 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@/[EMAIL PROTECTED]@'
LANGUAGE C STRICT;
+CREATE FUNCTION partition_insert_trigger ()
+ RETURNS trigger
+ AS '@libdir@/[EMAIL PROTECTED]@'
+ 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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
