From: Emmanuel Cecchet <manu@frogthinker.org>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jaime Casanova <jcasanov@systemguards.com.ec>
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Nikhil Sontakke <nikhil.sontakke@enterprisedb.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
Date: Tue, 09 Dec 2008 19:51:05 -0500
Message-ID: <493F1279.8010804@frogthinker.org> (raw)
In-Reply-To: <603c8f070812021452m65ed412ckc416e14b8e8f3f77@mail.gmail.com>
References: <3073cc9b0810220744y987173avd560a33fcb58ede5@mail.gmail.com>
<603c8f070811262009v459d0701w151c16533d4ab947@mail.gmail.com>
<a301bfd90811270106k39d60318s7fc871a477a2b58d@mail.gmail.com>
<20081127120402.GC4586@alvh.no-ip.org>
<603c8f070811270507i36e18e0bu5d461a4f957e24d3@mail.gmail.com>
<871vwxpa7b.fsf@oxford.xeocode.com>
<603c8f070811270641u55bbf340p3383dcc6a6f1f6e8@mail.gmail.com>
<3073cc9b0811270710p3a2e5ae9s10f66b529550c03e@mail.gmail.com>
<3073cc9b0811270712l6384c28awac4f0318c704df99@mail.gmail.com>
<492EC647.4090706@frogthinker.org>
<603c8f070812021452m65ed412ckc416e14b8e8f3f77@mail.gmail.com>
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
Attachments:patch-trigger-autopartition-C.txt (text/plain, 10.2K) download
### 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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: manu@frogthinker.org, robertmhaas@gmail.com, jcasanov@systemguards.com.ec, stark@enterprisedb.com, alvherre@commandprompt.com, nikhil.sontakke@enterprisedb.com, pgsql-hackers@postgresql.org
Subject: Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
In-Reply-To: <493F1279.8010804@frogthinker.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox