Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A964164FE1D for ; Tue, 9 Dec 2008 20:57:51 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 10481-03 for ; Tue, 9 Dec 2008 20:57:48 -0400 (AST) X-Greylist: delayed 00:06:40.248863 by SQLgrey-1.7.6 Received: from outbound-mail-249.bluehost.com (unknown [66.147.249.10]) by mail.postgresql.org (Postfix) with SMTP id AD317650123 for ; Tue, 9 Dec 2008 20:57:47 -0400 (AST) Received: (qmail 17357 invoked by uid 0); 10 Dec 2008 00:50:29 -0000 Received: from unknown (HELO fast22.fastdomain.com) (74.220.199.22) by outboundproxy7.bluehost.com.bluehost.com with SMTP; 10 Dec 2008 00:50:29 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=frogthinker.org; h=Received:Message-ID:Date:From:Organization:User-Agent:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:X-Identified-User; b=PpTyE89cgIpwYuEQLef+cZhiVhg+QcX5ps+r5aPd2PsiUpLrwQkDnoyOYxJTdbOqttefn4p4KsP7JQGyNJzCluo5uhXDYoE0ETUhaNTWX7vaaxWk88cO8mdahPYnh301; Received: from [74.85.100.72] (helo=[131.106.61.15]) by fast22.fastdomain.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1LADI8-0005SB-Ge; Tue, 09 Dec 2008 17:51:05 -0700 Message-ID: <493F1279.8010804@frogthinker.org> Date: Tue, 09 Dec 2008 19:51:05 -0500 From: Emmanuel Cecchet Organization: Frog Thinker User-Agent: Thunderbird 2.0.0.18 (Windows/20081105) MIME-Version: 1.0 To: Robert Haas CC: Jaime Casanova , Gregory Stark , Alvaro Herrera , Nikhil Sontakke , PostgreSQL-development Subject: Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1 References: <3073cc9b0810220744y987173avd560a33fcb58ede5@mail.gmail.com> <603c8f070811262009v459d0701w151c16533d4ab947@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> In-Reply-To: <603c8f070812021452m65ed412ckc416e14b8e8f3f77@mail.gmail.com> Content-Type: multipart/mixed; boundary="------------040705020606080509000406" X-Identified-User: {2846:fast22.fastdomain.com:frogthin:frogthinker.org} {sentby:smtp auth 74.85.100.72 authed with manu+frogthinker.org} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.51 tagged_above=0 required=5 tests=RDNS_NONE=0.1, SUBJECT_FUZZY_TION=0.41 X-Spam-Level: X-Archive-Number: 200812/596 X-Sequence-Number: 129225 This is a multi-part message in MIME format. --------------040705020606080509000406 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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 --------------040705020606080509000406 Content-Type: text/plain; name="patch-trigger-autopartition-C.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="patch-trigger-autopartition-C.txt" ### 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 */ #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; --------------040705020606080509000406--