Re: Partitioning option for COPY - Mailing list pgsql-hackers
| From | Emmanuel Cecchet |
|---|---|
| Subject | Re: Partitioning option for COPY |
| Date | |
| Msg-id | 4B05B7B0.7030205@frogthinker.org Whole thread Raw |
| In response to | Re: Partitioning option for COPY (Jan Urbański <wulczer@wulczer.org>) |
| Responses |
Re: Partitioning option for COPY
|
| List | pgsql-hackers |
Hi Jan, Here is a new version of the patch with the following modifications: - used oid list from pg_list.h - properly handles triggers and generate an error if needed (updated doc as well) - added your test cases + extra bad trigger cases Emmanuel > Hi, > > I'll hopefully look at the next version of the patch tommorrow. > > Emmanuel Cecchet wrote: > >>> o test1.sql always segfaults for me, poking around with gdb suggests >>> it's a case of an uninitialised cache list (another reason to use the >>> builtin one). >>> >>> >> I was never able to reproduce that problem. I don't know where this >> comes from. >> > > >> I have integrated your tests in the regression test suite and I was >> never able to reproduce the segfault you mentioned. What platform are >> you using? >> > > In the meantime I tried the test1.sql file again and it still segfaulted > for me. > I'm using 32bit Linux, PG compiled with: > > $ ./configure CFLAGS=-O0 --enable-cassert --enable-debug --without-perl > --without-python --without-openssl --without-tcl > > and then I start postmaster, fire up psql, attach gdb to the backend, do > \i test1.sql and get: > > Program received signal SIGSEGV, Segmentation fault. > 0x0819368b in route_tuple_to_child (parent_relation=0xb5d93040, > tuple=0x873b08c, hi_options=0, parentResultRelInfo=0x871e204) at copy.c:1821 > 1821 child_relation_id = > child_oid_cell->oid_value; > (gdb) bt > #0 0x0819368b in route_tuple_to_child (parent_relation=0xb5d93040, > tuple=0x873b08c, hi_options=0, parentResultRelInfo=0x871e204) at copy.c:1821 > #1 0x081950e3 in CopyFrom (cstate=0x871e0dc) at copy.c:2480 > #2 0x08192532 in DoCopy (stmt=0x86fb144, queryString=0x86fa73c "copy > parent from stdin with (partitioning);") at copy.c:1227 > > (gdb) p child_oid_cell > $1 = (OidCell *) 0x7f7f7f7f > > (gdb) p child_oid_cell->oid_value > Cannot access memory at address 0x7f7f7f7f > > > That 0x7f7f7f7f looks like clobbered memory, the memory management funcs > do that when cassert is enabled, IIRC. > > Cheers, > Jan > > -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development & Consulting -- Web: http://www.frogthinker.org email: manu@frogthinker.org Skype: emmanuel_cecchet Index: src/backend/commands/trigger.c =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/backend/commands/trigger.c,v retrieving revision 1.256 diff -c -r1.256 trigger.c *** src/backend/commands/trigger.c 27 Oct 2009 20:14:27 -0000 1.256 --- src/backend/commands/trigger.c 19 Nov 2009 21:19:09 -0000 *************** *** 1756,1761 **** --- 1756,1802 ---- return newtuple; } + HeapTuple + ExecARInsertTriggersNow(EState *estate, ResultRelInfo *relinfo, + HeapTuple trigtuple) + { + TriggerDesc *trigdesc = relinfo->ri_TrigDesc; + int ntrigs = trigdesc->n_after_row[TRIGGER_EVENT_INSERT]; + int *tgindx = trigdesc->tg_after_row[TRIGGER_EVENT_INSERT]; + HeapTuple newtuple = trigtuple; + HeapTuple oldtuple; + TriggerData LocTriggerData; + int i; + + LocTriggerData.type = T_TriggerData; + LocTriggerData.tg_event = TRIGGER_EVENT_INSERT | + TRIGGER_EVENT_ROW; + LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_newtuple = NULL; + LocTriggerData.tg_newtuplebuf = InvalidBuffer; + for (i = 0; i < ntrigs; i++) + { + Trigger *trigger = &trigdesc->triggers[tgindx[i]]; + + if (!TriggerEnabled(trigger, LocTriggerData.tg_event, NULL)) + continue; + + LocTriggerData.tg_trigtuple = oldtuple = newtuple; + LocTriggerData.tg_trigtuplebuf = InvalidBuffer; + LocTriggerData.tg_trigger = trigger; + newtuple = ExecCallTriggerFunc(&LocTriggerData, + tgindx[i], + relinfo->ri_TrigFunctions, + relinfo->ri_TrigInstrument, + GetPerTupleMemoryContext(estate)); + if (oldtuple != newtuple && oldtuple != trigtuple) + heap_freetuple(oldtuple); + if (newtuple == NULL) + break; + } + return newtuple; + } + void ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, HeapTuple trigtuple, List *recheckIndexes) Index: src/backend/commands/copy.c =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/backend/commands/copy.c,v retrieving revision 1.317 diff -c -r1.317 copy.c *** src/backend/commands/copy.c 21 Sep 2009 20:10:21 -0000 1.317 --- src/backend/commands/copy.c 19 Nov 2009 21:19:09 -0000 *************** *** 43,48 **** --- 43,56 ---- #include "utils/memutils.h" #include "utils/snapmgr.h" + /* For tuple routing */ + #include "catalog/pg_inherits.h" + #include "catalog/pg_inherits_fn.h" + #include "nodes/makefuncs.h" + #include "nodes/pg_list.h" + #include "utils/fmgroids.h" + #include "utils/relcache.h" + #include "utils/tqual.h" #define ISOCTAL(c) (((c) >= '0') && ((c) <= '7')) #define OCTVALUE(c) ((c) - '0') *************** *** 117,122 **** --- 125,131 ---- char *escape; /* CSV escape char (must be 1 byte) */ bool *force_quote_flags; /* per-column CSV FQ flags */ bool *force_notnull_flags; /* per-column CSV FNN flags */ + bool partitioning; /* tuple routing in table hierarchy */ /* these are just for error messages, see copy_in_error_callback */ const char *cur_relname; /* table name for error messages */ *************** *** 173,178 **** --- 182,194 ---- } DR_copy; + /** + * Size of the LRU list of relations to keep in cache for routing + */ + int partitioningCacheSize; + + List *child_table_lru = NULL; + /* * These macros centralize code used to process line_buf and raw_buf buffers. * They are macros because they often do continue/break control and to avoid *************** *** 839,844 **** --- 855,868 ---- errmsg("argument to option \"%s\" must be a list of column names", defel->defname))); } + else if (strcmp(defel->defname, "partitioning") == 0) + { + if (cstate->partitioning) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + cstate->partitioning = defGetBoolean(defel); + } else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), *************** *** 1662,1667 **** --- 1686,1960 ---- return res; } + /** + * Check that the given tuple matches the constraints of the given child table + * and performs an insert if the constraints are matched. insert_tuple specifies + * if the tuple must be inserted in the table if the constraint is satisfied. + * The method returns true if the constraint is satisfied (and insert was + * performed if insert_tuple is true), false otherwise (constraints not + * satisfied for this tuple on this child table). + */ + static bool + check_tuple_constraints(Relation child_table_relation, HeapTuple tuple, + bool insert_tuple, int hi_options, ResultRelInfo *parentResultRelInfo) + { + /* Check the constraints */ + ResultRelInfo *resultRelInfo; + TupleTableSlot *slot; + EState *estate = CreateExecutorState(); + bool result = false; + + resultRelInfo = makeNode(ResultRelInfo); + resultRelInfo->ri_RangeTableIndex = 1; /* dummy */ + resultRelInfo->ri_RelationDesc = child_table_relation; + resultRelInfo->ri_TrigDesc = CopyTriggerDesc(child_table_relation->trigdesc); + if (resultRelInfo->ri_TrigDesc) + resultRelInfo->ri_TrigFunctions = (FmgrInfo *) + palloc0(resultRelInfo->ri_TrigDesc->numtriggers * sizeof(FmgrInfo)); + resultRelInfo->ri_TrigInstrument = NULL; + + ExecOpenIndices(resultRelInfo); + + 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(child_table_relation->rd_att); + ExecStoreTuple(tuple, slot, InvalidBuffer, false); + + if (ExecRelCheck(resultRelInfo, slot, estate) == NULL) + { + /* Constraints satisfied */ + if (insert_tuple) + { + /* Insert the row in the child table */ + List *recheckIndexes = NIL; + + /* BEFORE ROW INSERT Triggers */ + if (resultRelInfo->ri_TrigDesc && + resultRelInfo->ri_TrigDesc->n_before_row[TRIGGER_EVENT_INSERT] > 0) + { + HeapTuple newtuple; + newtuple = ExecBRInsertTriggers(estate, resultRelInfo, tuple); + + if (newtuple != tuple) + { + /* tuple modified by Trigger(s), check that the constraint is still valid */ + heap_freetuple(tuple); + tuple = newtuple; + ExecStoreTuple(tuple, slot, InvalidBuffer, false); + if (ExecRelCheck(resultRelInfo, slot, estate) != NULL) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("Before row insert trigger on table \"%s\" modified partitioning routing decision.Aborting insert.", + RelationGetRelationName(child_table_relation)))); + } + } + } + + /* OK, store the tuple and create index entries for it */ + heap_insert(child_table_relation, tuple, GetCurrentCommandId(true), + hi_options, NULL); + + /* Update indices */ + if (resultRelInfo->ri_NumIndices > 0) + recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), + estate, false); + + /* AFTER ROW INSERT Triggers */ + if (resultRelInfo->ri_TrigDesc && + resultRelInfo->ri_TrigDesc->n_after_row[TRIGGER_EVENT_INSERT] > 0) + { + HeapTuple newtuple; + newtuple = ExecARInsertTriggersNow(estate, resultRelInfo, tuple); + if (newtuple != tuple) + { + /* tuple modified by Trigger(s), check that the constraint is still valid */ + heap_freetuple(tuple); + tuple = newtuple; + ExecStoreTuple(tuple, slot, InvalidBuffer, false); + if (ExecRelCheck(resultRelInfo, slot, estate) != NULL) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("After row insert trigger on table \"%s\" modified partitioning routingdecision. Aborting insert.", + RelationGetRelationName(child_table_relation)))); + } + } + } + } + result = true; + } + + /* Free resources */ + FreeExecutorState(estate); + ExecDropSingleTupleTableSlot(slot); + ExecCloseIndices(resultRelInfo); + + return result; + } + + + /** + * Route a tuple into a child table that matches the constraints of the tuple + * to be inserted. + * @param parent_relation_id Oid of the parent relation + * @param tuple the tuple to be routed + */ + static bool route_tuple_to_child(Relation parent_relation, HeapTuple tuple, int hi_options, ResultRelInfo *parentResultRelInfo) + { + Relation child_table_relation; + bool result = false; + Relation catalog_relation; + HeapTuple inherits_tuple; + HeapScanDesc scan; + ScanKeyData key[1]; + + /* Try to exploit locality for bulk inserts + * We expect consecutive insert to go to the same child table */ + if (partitioningCacheSize > 0 && child_table_lru != NULL) + { + /* Try the child table LRU */ + ListCell *child_oid_cell; + Oid child_relation_id; + + foreach(child_oid_cell, child_table_lru) + { + child_relation_id = lfirst_oid(child_oid_cell); + child_table_relation = try_relation_open(child_relation_id, + RowExclusiveLock); + + if (child_table_relation == NULL) + { + /* Child table does not exist anymore, purge cache entry */ + child_table_lru = list_delete_oid(child_table_lru, child_relation_id); + if (list_length(child_table_lru) == 0) + break; /* Cache is now empty */ + else + { /* Restart scanning */ + child_oid_cell = list_head(child_table_lru); + continue; + } + } + + if (check_tuple_constraints(child_table_relation, tuple, true, hi_options, parentResultRelInfo)) + { + /* Hit, move in front if not already the head */ + if (lfirst_oid(list_head(child_table_lru)) != child_relation_id) + { + /* The partitioning cache is in the CacheMemoryContext) */ + MemoryContext currentContext = MemoryContextSwitchTo(CacheMemoryContext); + child_table_lru = list_delete_oid(child_table_lru, child_relation_id); + child_table_lru = lcons_oid(child_relation_id, child_table_lru); + MemoryContextSwitchTo(currentContext); + } + + /* Close the relation but keep the lock until the end of + * the transaction */ + relation_close(child_table_relation, NoLock); + + return true; + } + relation_close(child_table_relation, RowExclusiveLock); + } + /* We got a miss */ + } + + /* Looking up child tables */ + ScanKeyInit(&key[0], + Anum_pg_inherits_inhparent, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(parent_relation->rd_id)); + catalog_relation = heap_open(InheritsRelationId, AccessShareLock); + scan = heap_beginscan(catalog_relation, SnapshotNow, 1, key); + while ((inherits_tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + TupleConstr *constr; + Form_pg_inherits inh = (Form_pg_inherits) GETSTRUCT(inherits_tuple); + Oid child_relation_id = inh->inhrelid; + + /* Check if the child table satisfy the constraints, if the relation + * cannot be opened this throws an exception */ + child_table_relation = (Relation) relation_open(child_relation_id, + RowExclusiveLock); + + constr = child_table_relation->rd_att->constr; + if (constr->num_check == 0) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("partition routing found no constraint for relation \"%s\"", + RelationGetRelationName(child_table_relation)))); + } + + if (has_subclass(child_table_relation->rd_id)) + { + /* This is a parent table, check its constraints first */ + if (check_tuple_constraints(child_table_relation, tuple, false, hi_options, parentResultRelInfo)) + { + /* Constraint satisfied, explore the child tables */ + result = route_tuple_to_child(child_table_relation, tuple, hi_options, parentResultRelInfo); + if (result) + { + /* Success, one of our child tables matched. + * Release the lock on this parent relation, we did not use it */ + relation_close(child_table_relation, RowExclusiveLock); + break; + } + else + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("tuple matched constraints of relation \"%s\" but none of " + "its children", + RelationGetRelationName(child_table_relation)))); + } + } + } + else + { + /* Child table, try it */ + result = check_tuple_constraints(child_table_relation, tuple, true, hi_options, parentResultRelInfo); + } + + if (result) + { + /* We found the one, update the LRU and exit the loop! + * + * Close the relation but keep the lock until the end of + * the transaction */ + relation_close(child_table_relation, NoLock); + + if (partitioningCacheSize > 0) + { + /* The partitioning cache is in the CacheMemoryContext) */ + MemoryContext currentContext; + currentContext = MemoryContextSwitchTo(CacheMemoryContext); + + /* Add the new entry in head of the list (also builds the list if needed) */ + child_table_lru = lcons_oid(child_relation_id, child_table_lru); + + /* Adjust list size if needed */ + child_table_lru = list_truncate(child_table_lru, partitioningCacheSize); + + /* Restore memory context */ + MemoryContextSwitchTo(currentContext); + } + break; + } + else + { + /* Release the lock on that relation, we did not use it */ + relation_close(child_table_relation, RowExclusiveLock); + } + } + heap_endscan(scan); + heap_close(catalog_relation, AccessShareLock); + return result; + } + /* * Copy FROM file to relation. */ *************** *** 2149,2178 **** { List *recheckIndexes = NIL; ! /* Place tuple in tuple slot */ ! ExecStoreTuple(tuple, slot, InvalidBuffer, false); ! ! /* Check the constraints of the tuple */ ! if (cstate->rel->rd_att->constr) ! ExecConstraints(resultRelInfo, slot, estate); ! ! /* OK, store the tuple and create index entries for it */ ! heap_insert(cstate->rel, tuple, mycid, hi_options, bistate); ! if (resultRelInfo->ri_NumIndices > 0) ! recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), ! estate, false); ! /* AFTER ROW INSERT Triggers */ ! ExecARInsertTriggers(estate, resultRelInfo, tuple, ! recheckIndexes); ! /* ! * We count only tuples not suppressed by a BEFORE INSERT trigger; ! * this is the same definition used by execMain.c for counting ! * tuples inserted by an INSERT command. ! */ ! cstate->processed++; } } --- 2442,2494 ---- { List *recheckIndexes = NIL; ! /* If routing is enabled and table has child tables, let's try routing */ ! if (cstate->partitioning && has_subclass(cstate->rel->rd_id)) ! { ! if (route_tuple_to_child(cstate->rel, tuple, hi_options, resultRelInfo)) ! { ! /* increase the counter so that we return how many ! * tuples got copied into all tables in total */ ! cstate->processed++; ! } ! else ! { ! ereport(ERROR, ( ! errcode(ERRCODE_BAD_COPY_FILE_FORMAT), ! errmsg("tuple does not satisfy any child table constraint") ! )); ! } ! } ! else ! { ! /* No partitioning, prepare the tuple and ! * check the constraints */ ! /* Place tuple in tuple slot */ ! ExecStoreTuple(tuple, slot, InvalidBuffer, false); ! /* Check the constraints of the tuple */ ! if (cstate->rel->rd_att->constr) ! ExecConstraints(resultRelInfo, slot, estate); ! ! /* OK, store the tuple and create index entries for it */ ! heap_insert(cstate->rel, tuple, mycid, hi_options, bistate); ! ! if (resultRelInfo->ri_NumIndices > 0) ! recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self), ! estate, false); ! ! /* AFTER ROW INSERT Triggers */ ! ExecARInsertTriggers(estate, resultRelInfo, tuple, ! recheckIndexes); ! /* ! * We count only tuples not suppressed by a BEFORE INSERT trigger; ! * this is the same definition used by execMain.c for counting ! * tuples inserted by an INSERT command. ! */ ! cstate->processed++; ! } } } Index: src/include/commands/trigger.h =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/include/commands/trigger.h,v retrieving revision 1.77 diff -c -r1.77 trigger.h *** src/include/commands/trigger.h 26 Oct 2009 02:26:41 -0000 1.77 --- src/include/commands/trigger.h 19 Nov 2009 21:19:09 -0000 *************** *** 130,135 **** --- 130,138 ---- extern HeapTuple ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo, HeapTuple trigtuple); + extern HeapTuple ExecARInsertTriggersNow(EState *estate, + ResultRelInfo *relinfo, + HeapTuple trigtuple); extern void ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, HeapTuple trigtuple, Index: src/include/commands/copy.h =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/include/commands/copy.h,v retrieving revision 1.32 diff -c -r1.32 copy.h *** src/include/commands/copy.h 1 Jan 2009 17:23:58 -0000 1.32 --- src/include/commands/copy.h 19 Nov 2009 21:19:09 -0000 *************** *** 22,25 **** --- 22,30 ---- extern DestReceiver *CreateCopyDestReceiver(void); + /** + * Size of the LRU list of relations to keep in cache for partitioning in COPY + */ + extern int partitioningCacheSize; + #endif /* COPY_H */ Index: src/include/executor/executor.h =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/include/executor/executor.h,v retrieving revision 1.163 diff -c -r1.163 executor.h *** src/include/executor/executor.h 26 Oct 2009 02:26:41 -0000 1.163 --- src/include/executor/executor.h 19 Nov 2009 21:19:09 -0000 *************** *** 166,171 **** --- 166,173 ---- 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, EPQState *epqstate, Relation relation, Index rti, ItemPointer tid, TransactionId priorXmax); Index: src/backend/executor/execMain.c =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/backend/executor/execMain.c,v retrieving revision 1.334 diff -c -r1.334 execMain.c *** src/backend/executor/execMain.c 26 Oct 2009 02:26:29 -0000 1.334 --- src/backend/executor/execMain.c 19 Nov 2009 21:19:09 -0000 *************** *** 1235,1241 **** /* * ExecRelCheck --- check that tuple meets constraints for result relation */ ! static const char * ExecRelCheck(ResultRelInfo *resultRelInfo, TupleTableSlot *slot, EState *estate) { --- 1235,1241 ---- /* * ExecRelCheck --- check that tuple meets constraints for result relation */ ! const char * ExecRelCheck(ResultRelInfo *resultRelInfo, TupleTableSlot *slot, EState *estate) { Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/backend/utils/misc/guc.c,v retrieving revision 1.523 diff -c -r1.523 guc.c *** src/backend/utils/misc/guc.c 21 Oct 2009 20:38:58 -0000 1.523 --- src/backend/utils/misc/guc.c 19 Nov 2009 21:19:09 -0000 *************** *** 32,37 **** --- 32,38 ---- #include "access/xact.h" #include "catalog/namespace.h" #include "commands/async.h" + #include "commands/copy.h" #include "commands/prepare.h" #include "commands/vacuum.h" #include "commands/variable.h" *************** *** 534,539 **** --- 535,542 ---- gettext_noop("Customized Options"), /* DEVELOPER_OPTIONS */ gettext_noop("Developer Options"), + /* COPY_OPTIONS */ + gettext_noop("Copy Options"), /* help_config wants this array to be null-terminated */ NULL }; *************** *** 1955,1960 **** --- 1958,2019 ---- 1024, 100, 102400, NULL, NULL }, + { + { + /* variable name */ + "copy_partitioning_cache_size", + + /* context, we want the user to set it */ + PGC_USERSET, + + /* category for this configuration variable */ + COPY_OPTIONS, + + /* short description */ + gettext_noop("Size of the LRU list of child tables to keep in cache " + " when partitioning tuples in COPY."), + + /* long description */ + gettext_noop("When tuples are automatically routed in COPY, all " + "tables are scanned until the constraints are matched. When " + "a large number of child tables are present the scanning " + "overhead can be large. To reduce that overhead, the routing " + "mechanism keeps a cache of the last child tables in which " + "tuples where inserted and try these tables first before " + "performing a full scan. This variable defines the cache size " + "with 0 meaning no caching, 1 keep the last matching child table" + ", x keep the last x child tables in which tuples were inserted." + " Note that the list is managed with an LRU policy."), + + + /* flags: this option is not in the postgresql.conf.sample + * file and should not be allowed in the config. + * NOTE: this is not currently enforced. + */ + GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE + }, + + /* pointer to the variable, this one is present in + * src/backend/commands/copy.c + */ + &partitioningCacheSize, + + /* default value */ + 2, + + /* min value */ + 0, + + /* max value */ + INT_MAX, + + /* assign hook function */ + NULL, + + /* show hook function */ + NULL + }, + /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL Index: src/test/regress/parallel_schedule =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/parallel_schedule,v retrieving revision 1.57 diff -c -r1.57 parallel_schedule *** src/test/regress/parallel_schedule 24 Aug 2009 03:10:16 -0000 1.57 --- src/test/regress/parallel_schedule 19 Nov 2009 21:19:09 -0000 *************** *** 47,53 **** # execute two copy tests parallel, to check that copy itself # is concurrent safe. # ---------- ! test: copy copyselect # ---------- # Another group of parallel tests --- 47,55 ---- # execute two copy tests parallel, to check that copy itself # is concurrent safe. # ---------- ! test: copy copyselect ! test: copy_partitioning ! test: copy_partitioning_trigger # ---------- # Another group of parallel tests Index: doc/src/sgml/ref/copy.sgml =================================================================== RCS file: /home/manu/cvsrepo/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.92 diff -c -r1.92 copy.sgml *** doc/src/sgml/ref/copy.sgml 21 Sep 2009 20:10:21 -0000 1.92 --- doc/src/sgml/ref/copy.sgml 19 Nov 2009 21:19:09 -0000 *************** *** 41,46 **** --- 41,47 ---- ESCAPE '<replaceable class="parameter">escape_character</replaceable>' FORCE_QUOTE { ( <replaceable class="parameter">column</replaceable> [, ...] ) | * } FORCE_NOT_NULL ( <replaceable class="parameter">column</replaceable> [, ...] ) + PARTITIONING [ <replaceable class="parameter">boolean</replaceable> ] </synopsis> </refsynopsisdiv> *************** *** 282,287 **** --- 283,298 ---- </listitem> </varlistentry> + <varlistentry> + <term><literal>PARTITIONING</></term> + <listitem> + <para> + In <literal>PARTITIONING</> mode, <command>COPY TO</> a parent + table will automatically move each row to the child table that + has the matching constraints. + </para> + </listitem> + </varlistentry> </variablelist> </refsect1> *************** *** 384,389 **** --- 395,419 ---- <command>VACUUM</command> to recover the wasted space. </para> + <para> + <literal>PARTITIONING</> mode scans for each child table constraint in the + hierarchy to find a match. As an optimization, a cache of the last child + tables where tuples have been routed is kept and tried first. The size + of the cache is set by the <literal>copy_partitioning_cache_size</literal> + session variable. It the size is set to 0, the cache is disabled otherwise + the indicated number of child tables is kept in the cache (at most). + </para> + + <para> + <literal>PARTITIONING</> mode assumes that every child table has at least + one constraint defined otherwise an error is thrown. If child tables have + overlapping constraints, the row is inserted in the first child table found + (be it a cached table or the first table to appear in the lookup). + Before of after ROW triggers will generate an error and abort the COPY operation + if they modify the tuple value in a way that violates the constraints of the child + table where the tuple has been routed. + </para> + </refsect1> <refsect1> *************** *** 828,833 **** --- 858,1001 ---- 0000200 M B A B W E 377 377 377 377 377 377 </programlisting> </para> + + <para> + Multiple options are separated by a comma like: + <programlisting> + COPY (SELECT t FROM foo WHERE id = 1) TO STDOUT (FORMAT CSV, HEADER, FORCE_QUOTE (t)); + </programlisting> + </para> + + <refsect2> + <title>Partitioning examples</title> + <para> + Here is an example on how to use partitioning. Let's first create a parent + table and 3 child tables as follows: + <programlisting> + CREATE TABLE y2008 ( + id int not null, + date date not null, + value int + ); + + CREATE TABLE jan2008 ( + CHECK ( date >= DATE '2008-01-01' AND date < DATE '2008-02-01' ) + ) INHERITS (y2008); + + CREATE TABLE feb2008 ( + CHECK ( date >= DATE '2008-02-01' AND date < DATE '2008-03-01' ) + ) INHERITS (y2008); + + CREATE TABLE mar2008 ( + CHECK ( date >= DATE '2008-03-01' AND date < DATE '2008-04-01' ) + ) INHERITS (y2008); + </programlisting> + We prepare the following data file (1 row for each child table): + copy_input.data content: + <programlisting> + 11 '2008-01-10' 11 + 12 '2008-02-15' 12 + 13 '2008-03-15' 13 + 21 '2008-01-10' 11 + 31 '2008-01-10' 11 + 41 '2008-01-10' 11 + 22 '2008-02-15' 12 + 23 '2008-03-15' 13 + 32 '2008-02-15' 12 + 33 '2008-03-15' 13 + 42 '2008-02-15' 12 + 43 '2008-03-15' 13 + </programlisting> + If we COPY the data in the parent table without partitioning enabled, all + rows are inserted in the master table as in this example: + <programlisting> + COPY y2008 FROM 'copy_input.data'; + + SELECT COUNT(*) FROM y2008; + count + ------- + 12 + (1 row) + + SELECT COUNT(*) FROM jan2008; + count + ------- + 0 + (1 row) + + SELECT COUNT(*) FROM feb2008; + count + ------- + 0 + (1 row) + + SELECT COUNT(*) FROM mar2008; + count + ------- + 0 + (1 row) + + DELETE FROM y2008; + </programlisting> + If we execute COPY with partitioning enabled, rows are loaded in the + appropriate child table automatically as in this example: + <programlisting> + COPY y2008 FROM 'copy_input.data' (PARTITIONING); + + SELECT * FROM y2008; + id | date | value + ----+------------+------- + 11 | 01-10-2008 | 11 + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + 12 | 02-15-2008 | 12 + 22 | 02-15-2008 | 12 + 32 | 02-15-2008 | 12 + 42 | 02-15-2008 | 12 + 13 | 03-15-2008 | 13 + 23 | 03-15-2008 | 13 + 33 | 03-15-2008 | 13 + 43 | 03-15-2008 | 13 + (12 rows) + + SELECT * FROM jan2008; + id | date | value + ----+------------+------- + 11 | 01-10-2008 | 11 + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (4 rows) + + SELECT * FROM feb2008; + id | date | value + ----+------------+------- + 12 | 02-15-2008 | 12 + 22 | 02-15-2008 | 12 + 32 | 02-15-2008 | 12 + 42 | 02-15-2008 | 12 + (4 rows) + + SELECT * FROM mar2008; + id | date | value + ----+------------+------- + 13 | 03-15-2008 | 13 + 23 | 03-15-2008 | 13 + 33 | 03-15-2008 | 13 + 43 | 03-15-2008 | 13 + (4 rows) + </programlisting> + The cache size can be tuned using: + <programlisting> + set copy_partitioning_cache_size = 3; + </programlisting> + Repeating the COPY command will now be faster: + <programlisting> + COPY y2008 FROM 'copy_input.data' (PARTITIONING); + </programlisting> + </para> + </refsect2> </refsect1> <refsect1> Index: src/include/utils/guc_tables.h =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/include/utils/guc_tables.h,v retrieving revision 1.46 diff -c -r1.46 guc_tables.h *** src/include/utils/guc_tables.h 11 Jun 2009 14:49:13 -0000 1.46 --- src/include/utils/guc_tables.h 19 Nov 2009 21:19:09 -0000 *************** *** 76,82 **** COMPAT_OPTIONS_CLIENT, PRESET_OPTIONS, CUSTOM_OPTIONS, ! DEVELOPER_OPTIONS }; /* --- 76,83 ---- COMPAT_OPTIONS_CLIENT, PRESET_OPTIONS, CUSTOM_OPTIONS, ! DEVELOPER_OPTIONS, ! COPY_OPTIONS }; /* Index: src/test/regress/input/copy_partitioning_trigger.source =================================================================== RCS file: src/test/regress/input/copy_partitioning_trigger.source diff -N src/test/regress/input/copy_partitioning_trigger.source *** /dev/null 1 Jan 1970 00:00:00 -0000 --- src/test/regress/input/copy_partitioning_trigger.source 1 Jan 1970 00:00:00 -0000 *************** *** 0 **** --- 1,62 ---- + -- Test triggers with partitioning + set copy_partitioning_cache_size = 0; + + create table t(i int); + create table t1 (check (i > 0 and i <= 1)) inherits (t); + create table t2 (check (i > 1 and i <= 2)) inherits (t); + create table t3 (check (i > 2 and i <= 3)) inherits (t); + + create table audit(i int); + + create function audit() returns trigger as $$ begin insert into audit(i) values (new.i); return new; end; $$ language plpgsql; + + create trigger t_a after insert on t for each row execute procedure audit(); + -- the before trigger on the t would get fired + -- create trigger t_a2 before insert on t for each row execute procedure audit(); + create trigger t1_a before insert on t1 for each row execute procedure audit(); + create trigger t1_a2 after insert on t1 for each row execute procedure audit(); + + copy t from stdin with (partitioning); + 1 + 2 + 3 + \. + + -- no rows if trigger does not work + select * from audit; + + drop table t cascade; + drop table audit cascade; + drop function audit(); + + -- Test bad before row trigger + create table t(i int); + create table t1 (check (i > 0 and i <= 1)) inherits (t); + create table t2 (check (i > 1 and i <= 2)) inherits (t); + + create function i2() returns trigger as $$ begin NEW.i := 2; return NEW; end; $$ language plpgsql; + create trigger t1_before before insert on t1 for each row execute procedure i2(); + + -- COPY should fail + copy t from stdin with (partitioning); + 1 + \. + + drop table t cascade; + drop function i2(); + + -- Test bad after row trigger + create table t(i int); + create table t1 (check (i > 0 and i <= 1)) inherits (t); + create table t2 (check (i > 1 and i <= 2)) inherits (t); + + create function i2() returns trigger as $$ begin NEW.i := 2; return NEW; end; $$ language plpgsql; + create trigger t1_after after insert on t1 for each row execute procedure i2(); + + -- COPY should fail + copy t from stdin with (partitioning); + 1 + \. + + drop table t cascade; + drop function i2(); Index: src/test/regress/data/copy_input.data =================================================================== RCS file: src/test/regress/data/copy_input.data diff -N src/test/regress/data/copy_input.data *** /dev/null 1 Jan 1970 00:00:00 -0000 --- src/test/regress/data/copy_input.data 1 Jan 1970 00:00:00 -0000 *************** *** 0 **** --- 1,12 ---- + 11 '2008-01-19' 11 + 12 '2008-02-15' 12 + 13 '2008-03-15' 13 + 21 '2008-01-10' 11 + 31 '2008-01-10' 11 + 41 '2008-01-10' 11 + 22 '2008-02-15' 12 + 23 '2008-03-15' 13 + 32 '2008-02-15' 12 + 33 '2008-03-15' 13 + 42 '2008-02-15' 12 + 43 '2008-03-15' 13 Index: src/test/regress/input/copy_partitioning.source =================================================================== RCS file: src/test/regress/input/copy_partitioning.source diff -N src/test/regress/input/copy_partitioning.source *** /dev/null 1 Jan 1970 00:00:00 -0000 --- src/test/regress/input/copy_partitioning.source 1 Jan 1970 00:00:00 -0000 *************** *** 0 **** --- 1,149 ---- + -- test 1 + create table parent(i int); + create table c1 (check (i > 0 and i <= 1)) inherits (parent); + copy parent from stdin with (partitioning); + 1 + \. + + drop table parent cascade; + + create table parent(i int); + create table c1 (check (i > 0 and i <= 1)) inherits (parent); + copy parent from stdin with (partitioning); + 1 + \. + + drop table parent cascade; + + -- test 2 + set copy_partitioning_cache_size = 0; + create table parent(i int, j int); + create table c1 (check (i > 0 and i <= 1)) inherits (parent); + create table c2 (check (i > 1 and i <= 2)) inherits (parent); + create table c3 (check (i > 2 and i <= 3)) inherits (parent); + + create index c1_idx on c1(j); + copy (select i % 3 + 1, i from generate_series(1, 1000) s(i)) to '/tmp/parent'; + copy parent from '/tmp/parent' with (partitioning); + analyse; + + set enable_seqscan to false; + -- no rows if index was not updated + select * from c1 where j = 3; + + set enable_seqscan to true; + set enable_indexscan to false; + -- 1 row + select * from c1 where j = 3; + drop table parent cascade; + + -- test cache size + CREATE TABLE y2008 ( + id int not null, + date date not null, + value int, + primary key(id) + ); + + CREATE TABLE jan2008 ( + CHECK ( date >= DATE '2008-01-01' AND date < DATE '2008-02-01' ) + ) INHERITS (y2008); + + CREATE TABLE jan2008half1 ( + CHECK ( date >= DATE '2008-01-01' AND date < DATE '2008-01-15' ) + ) INHERITS (jan2008); + + CREATE TABLE jan2008half2 ( + CHECK ( date >= DATE '2008-01-16' AND date < DATE '2008-01-31' ) + ) INHERITS (jan2008); + + CREATE TABLE feb2008 ( + CHECK ( date >= DATE '2008-02-01' AND date < DATE '2008-03-01' ) + ) INHERITS (y2008); + + CREATE TABLE mar2008 ( + CHECK ( date >= DATE '2008-03-01' AND date < DATE '2008-04-01' ) + ) INHERITS (y2008); + + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data'; + + SELECT COUNT(*) FROM y2008; + SELECT COUNT(*) FROM jan2008; + SELECT COUNT(*) FROM jan2008half1; + SELECT COUNT(*) FROM jan2008half2; + SELECT COUNT(*) FROM feb2008; + SELECT COUNT(*) FROM mar2008; + + DELETE FROM y2008; + + set copy_partitioning_cache_size = 0; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + SELECT * FROM jan2008 ORDER BY id; + SELECT * FROM jan2008half1 ORDER BY id; + SELECT * FROM jan2008half2 ORDER BY id; + SELECT * FROM feb2008 ORDER BY id; + SELECT * FROM mar2008 ORDER BY id; + DELETE FROM y2008; + + set copy_partitioning_cache_size = 1; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + SELECT * FROM jan2008 ORDER BY id; + SELECT * FROM jan2008half1 ORDER BY id; + SELECT * FROM jan2008half2 ORDER BY id; + SELECT * FROM feb2008 ORDER BY id; + SELECT * FROM mar2008 ORDER BY id; + DELETE FROM y2008; + + set copy_partitioning_cache_size = 2; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + SELECT * FROM jan2008 ORDER BY id; + SELECT * FROM jan2008half1 ORDER BY id; + SELECT * FROM jan2008half2 ORDER BY id; + SELECT * FROM feb2008 ORDER BY id; + SELECT * FROM mar2008 ORDER BY id; + DELETE FROM y2008; + + set copy_partitioning_cache_size = 3; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + SELECT * FROM jan2008 ORDER BY id; + SELECT * FROM jan2008half1 ORDER BY id; + SELECT * FROM jan2008half2 ORDER BY id; + SELECT * FROM feb2008 ORDER BY id; + SELECT * FROM mar2008 ORDER BY id; + DELETE FROM y2008; + + set copy_partitioning_cache_size = 2; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + SELECT * FROM jan2008 ORDER BY id; + SELECT * FROM jan2008half1 ORDER BY id; + SELECT * FROM jan2008half2 ORDER BY id; + SELECT * FROM feb2008 ORDER BY id; + SELECT * FROM mar2008 ORDER BY id; + DELETE FROM y2008; + + set copy_partitioning_cache_size = 1; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + SELECT * FROM jan2008 ORDER BY id; + SELECT * FROM jan2008half1 ORDER BY id; + SELECT * FROM jan2008half2 ORDER BY id; + SELECT * FROM feb2008 ORDER BY id; + SELECT * FROM mar2008 ORDER BY id; + DELETE FROM y2008; + + set copy_partitioning_cache_size = 0; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + SELECT * FROM jan2008 ORDER BY id; + SELECT * FROM jan2008half1 ORDER BY id; + SELECT * FROM jan2008half2 ORDER BY id; + SELECT * FROM feb2008 ORDER BY id; + SELECT * FROM mar2008 ORDER BY id; + DELETE FROM y2008; + + DROP TABLE y2008 CASCADE; Index: src/test/regress/output/copy_partitioning.source =================================================================== RCS file: src/test/regress/output/copy_partitioning.source diff -N src/test/regress/output/copy_partitioning.source *** /dev/null 1 Jan 1970 00:00:00 -0000 --- src/test/regress/output/copy_partitioning.source 1 Jan 1970 00:00:00 -0000 *************** *** 0 **** --- 1,538 ---- + -- test 1 + create table parent(i int); + create table c1 (check (i > 0 and i <= 1)) inherits (parent); + copy parent from stdin with (partitioning); + drop table parent cascade; + NOTICE: drop cascades to table c1 + create table parent(i int); + create table c1 (check (i > 0 and i <= 1)) inherits (parent); + copy parent from stdin with (partitioning); + drop table parent cascade; + NOTICE: drop cascades to table c1 + -- test 2 + set copy_partitioning_cache_size = 0; + create table parent(i int, j int); + create table c1 (check (i > 0 and i <= 1)) inherits (parent); + create table c2 (check (i > 1 and i <= 2)) inherits (parent); + create table c3 (check (i > 2 and i <= 3)) inherits (parent); + create index c1_idx on c1(j); + copy (select i % 3 + 1, i from generate_series(1, 1000) s(i)) to '/tmp/parent'; + copy parent from '/tmp/parent' with (partitioning); + analyse; + set enable_seqscan to false; + -- no rows if index was not updated + select * from c1 where j = 3; + i | j + ---+--- + 1 | 3 + (1 row) + + set enable_seqscan to true; + set enable_indexscan to false; + -- 1 row + select * from c1 where j = 3; + i | j + ---+--- + 1 | 3 + (1 row) + + drop table parent cascade; + NOTICE: drop cascades to 3 other objects + DETAIL: drop cascades to table c1 + drop cascades to table c2 + drop cascades to table c3 + -- test cache size + CREATE TABLE y2008 ( + id int not null, + date date not null, + value int, + primary key(id) + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "y2008_pkey" for table "y2008" + CREATE TABLE jan2008 ( + CHECK ( date >= DATE '2008-01-01' AND date < DATE '2008-02-01' ) + ) INHERITS (y2008); + CREATE TABLE jan2008half1 ( + CHECK ( date >= DATE '2008-01-01' AND date < DATE '2008-01-15' ) + ) INHERITS (jan2008); + CREATE TABLE jan2008half2 ( + CHECK ( date >= DATE '2008-01-16' AND date < DATE '2008-01-31' ) + ) INHERITS (jan2008); + CREATE TABLE feb2008 ( + CHECK ( date >= DATE '2008-02-01' AND date < DATE '2008-03-01' ) + ) INHERITS (y2008); + CREATE TABLE mar2008 ( + CHECK ( date >= DATE '2008-03-01' AND date < DATE '2008-04-01' ) + ) INHERITS (y2008); + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data'; + SELECT COUNT(*) FROM y2008; + count + ------- + 12 + (1 row) + + SELECT COUNT(*) FROM jan2008; + count + ------- + 0 + (1 row) + + SELECT COUNT(*) FROM jan2008half1; + count + ------- + 0 + (1 row) + + SELECT COUNT(*) FROM jan2008half2; + count + ------- + 0 + (1 row) + + SELECT COUNT(*) FROM feb2008; + count + ------- + 0 + (1 row) + + SELECT COUNT(*) FROM mar2008; + count + ------- + 0 + (1 row) + + DELETE FROM y2008; + set copy_partitioning_cache_size = 0; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 12 | 02-15-2008 | 12 + 13 | 03-15-2008 | 13 + 21 | 01-10-2008 | 11 + 22 | 02-15-2008 | 12 + 23 | 03-15-2008 | 13 + 31 | 01-10-2008 | 11 + 32 | 02-15-2008 | 12 + 33 | 03-15-2008 | 13 + 41 | 01-10-2008 | 11 + 42 | 02-15-2008 | 12 + 43 | 03-15-2008 | 13 + (12 rows) + + SELECT * FROM jan2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (4 rows) + + SELECT * FROM jan2008half1 ORDER BY id; + id | date | value + ----+------------+------- + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (3 rows) + + SELECT * FROM jan2008half2 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + (1 row) + + SELECT * FROM feb2008 ORDER BY id; + id | date | value + ----+------------+------- + 12 | 02-15-2008 | 12 + 22 | 02-15-2008 | 12 + 32 | 02-15-2008 | 12 + 42 | 02-15-2008 | 12 + (4 rows) + + SELECT * FROM mar2008 ORDER BY id; + id | date | value + ----+------------+------- + 13 | 03-15-2008 | 13 + 23 | 03-15-2008 | 13 + 33 | 03-15-2008 | 13 + 43 | 03-15-2008 | 13 + (4 rows) + + DELETE FROM y2008; + set copy_partitioning_cache_size = 1; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 12 | 02-15-2008 | 12 + 13 | 03-15-2008 | 13 + 21 | 01-10-2008 | 11 + 22 | 02-15-2008 | 12 + 23 | 03-15-2008 | 13 + 31 | 01-10-2008 | 11 + 32 | 02-15-2008 | 12 + 33 | 03-15-2008 | 13 + 41 | 01-10-2008 | 11 + 42 | 02-15-2008 | 12 + 43 | 03-15-2008 | 13 + (12 rows) + + SELECT * FROM jan2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (4 rows) + + SELECT * FROM jan2008half1 ORDER BY id; + id | date | value + ----+------------+------- + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (3 rows) + + SELECT * FROM jan2008half2 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + (1 row) + + SELECT * FROM feb2008 ORDER BY id; + id | date | value + ----+------------+------- + 12 | 02-15-2008 | 12 + 22 | 02-15-2008 | 12 + 32 | 02-15-2008 | 12 + 42 | 02-15-2008 | 12 + (4 rows) + + SELECT * FROM mar2008 ORDER BY id; + id | date | value + ----+------------+------- + 13 | 03-15-2008 | 13 + 23 | 03-15-2008 | 13 + 33 | 03-15-2008 | 13 + 43 | 03-15-2008 | 13 + (4 rows) + + DELETE FROM y2008; + set copy_partitioning_cache_size = 2; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 12 | 02-15-2008 | 12 + 13 | 03-15-2008 | 13 + 21 | 01-10-2008 | 11 + 22 | 02-15-2008 | 12 + 23 | 03-15-2008 | 13 + 31 | 01-10-2008 | 11 + 32 | 02-15-2008 | 12 + 33 | 03-15-2008 | 13 + 41 | 01-10-2008 | 11 + 42 | 02-15-2008 | 12 + 43 | 03-15-2008 | 13 + (12 rows) + + SELECT * FROM jan2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (4 rows) + + SELECT * FROM jan2008half1 ORDER BY id; + id | date | value + ----+------------+------- + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (3 rows) + + SELECT * FROM jan2008half2 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + (1 row) + + SELECT * FROM feb2008 ORDER BY id; + id | date | value + ----+------------+------- + 12 | 02-15-2008 | 12 + 22 | 02-15-2008 | 12 + 32 | 02-15-2008 | 12 + 42 | 02-15-2008 | 12 + (4 rows) + + SELECT * FROM mar2008 ORDER BY id; + id | date | value + ----+------------+------- + 13 | 03-15-2008 | 13 + 23 | 03-15-2008 | 13 + 33 | 03-15-2008 | 13 + 43 | 03-15-2008 | 13 + (4 rows) + + DELETE FROM y2008; + set copy_partitioning_cache_size = 3; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 12 | 02-15-2008 | 12 + 13 | 03-15-2008 | 13 + 21 | 01-10-2008 | 11 + 22 | 02-15-2008 | 12 + 23 | 03-15-2008 | 13 + 31 | 01-10-2008 | 11 + 32 | 02-15-2008 | 12 + 33 | 03-15-2008 | 13 + 41 | 01-10-2008 | 11 + 42 | 02-15-2008 | 12 + 43 | 03-15-2008 | 13 + (12 rows) + + SELECT * FROM jan2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (4 rows) + + SELECT * FROM jan2008half1 ORDER BY id; + id | date | value + ----+------------+------- + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (3 rows) + + SELECT * FROM jan2008half2 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + (1 row) + + SELECT * FROM feb2008 ORDER BY id; + id | date | value + ----+------------+------- + 12 | 02-15-2008 | 12 + 22 | 02-15-2008 | 12 + 32 | 02-15-2008 | 12 + 42 | 02-15-2008 | 12 + (4 rows) + + SELECT * FROM mar2008 ORDER BY id; + id | date | value + ----+------------+------- + 13 | 03-15-2008 | 13 + 23 | 03-15-2008 | 13 + 33 | 03-15-2008 | 13 + 43 | 03-15-2008 | 13 + (4 rows) + + DELETE FROM y2008; + set copy_partitioning_cache_size = 2; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 12 | 02-15-2008 | 12 + 13 | 03-15-2008 | 13 + 21 | 01-10-2008 | 11 + 22 | 02-15-2008 | 12 + 23 | 03-15-2008 | 13 + 31 | 01-10-2008 | 11 + 32 | 02-15-2008 | 12 + 33 | 03-15-2008 | 13 + 41 | 01-10-2008 | 11 + 42 | 02-15-2008 | 12 + 43 | 03-15-2008 | 13 + (12 rows) + + SELECT * FROM jan2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (4 rows) + + SELECT * FROM jan2008half1 ORDER BY id; + id | date | value + ----+------------+------- + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (3 rows) + + SELECT * FROM jan2008half2 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + (1 row) + + SELECT * FROM feb2008 ORDER BY id; + id | date | value + ----+------------+------- + 12 | 02-15-2008 | 12 + 22 | 02-15-2008 | 12 + 32 | 02-15-2008 | 12 + 42 | 02-15-2008 | 12 + (4 rows) + + SELECT * FROM mar2008 ORDER BY id; + id | date | value + ----+------------+------- + 13 | 03-15-2008 | 13 + 23 | 03-15-2008 | 13 + 33 | 03-15-2008 | 13 + 43 | 03-15-2008 | 13 + (4 rows) + + DELETE FROM y2008; + set copy_partitioning_cache_size = 1; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 12 | 02-15-2008 | 12 + 13 | 03-15-2008 | 13 + 21 | 01-10-2008 | 11 + 22 | 02-15-2008 | 12 + 23 | 03-15-2008 | 13 + 31 | 01-10-2008 | 11 + 32 | 02-15-2008 | 12 + 33 | 03-15-2008 | 13 + 41 | 01-10-2008 | 11 + 42 | 02-15-2008 | 12 + 43 | 03-15-2008 | 13 + (12 rows) + + SELECT * FROM jan2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (4 rows) + + SELECT * FROM jan2008half1 ORDER BY id; + id | date | value + ----+------------+------- + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (3 rows) + + SELECT * FROM jan2008half2 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + (1 row) + + SELECT * FROM feb2008 ORDER BY id; + id | date | value + ----+------------+------- + 12 | 02-15-2008 | 12 + 22 | 02-15-2008 | 12 + 32 | 02-15-2008 | 12 + 42 | 02-15-2008 | 12 + (4 rows) + + SELECT * FROM mar2008 ORDER BY id; + id | date | value + ----+------------+------- + 13 | 03-15-2008 | 13 + 23 | 03-15-2008 | 13 + 33 | 03-15-2008 | 13 + 43 | 03-15-2008 | 13 + (4 rows) + + DELETE FROM y2008; + set copy_partitioning_cache_size = 0; + COPY y2008 FROM '@abs_srcdir@/data/copy_input.data' (PARTITIONING); + SELECT * FROM y2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 12 | 02-15-2008 | 12 + 13 | 03-15-2008 | 13 + 21 | 01-10-2008 | 11 + 22 | 02-15-2008 | 12 + 23 | 03-15-2008 | 13 + 31 | 01-10-2008 | 11 + 32 | 02-15-2008 | 12 + 33 | 03-15-2008 | 13 + 41 | 01-10-2008 | 11 + 42 | 02-15-2008 | 12 + 43 | 03-15-2008 | 13 + (12 rows) + + SELECT * FROM jan2008 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (4 rows) + + SELECT * FROM jan2008half1 ORDER BY id; + id | date | value + ----+------------+------- + 21 | 01-10-2008 | 11 + 31 | 01-10-2008 | 11 + 41 | 01-10-2008 | 11 + (3 rows) + + SELECT * FROM jan2008half2 ORDER BY id; + id | date | value + ----+------------+------- + 11 | 01-19-2008 | 11 + (1 row) + + SELECT * FROM feb2008 ORDER BY id; + id | date | value + ----+------------+------- + 12 | 02-15-2008 | 12 + 22 | 02-15-2008 | 12 + 32 | 02-15-2008 | 12 + 42 | 02-15-2008 | 12 + (4 rows) + + SELECT * FROM mar2008 ORDER BY id; + id | date | value + ----+------------+------- + 13 | 03-15-2008 | 13 + 23 | 03-15-2008 | 13 + 33 | 03-15-2008 | 13 + 43 | 03-15-2008 | 13 + (4 rows) + + DELETE FROM y2008; + DROP TABLE y2008 CASCADE; + NOTICE: drop cascades to 5 other objects + DETAIL: drop cascades to table jan2008 + drop cascades to table jan2008half1 + drop cascades to table jan2008half2 + drop cascades to table feb2008 + drop cascades to table mar2008 Index: src/test/regress/output/copy_partitioning_trigger.source =================================================================== RCS file: src/test/regress/output/copy_partitioning_trigger.source diff -N src/test/regress/output/copy_partitioning_trigger.source *** /dev/null 1 Jan 1970 00:00:00 -0000 --- src/test/regress/output/copy_partitioning_trigger.source 1 Jan 1970 00:00:00 -0000 *************** *** 0 **** --- 1,59 ---- + -- Test triggers with partitioning + set copy_partitioning_cache_size = 0; + create table t(i int); + create table t1 (check (i > 0 and i <= 1)) inherits (t); + create table t2 (check (i > 1 and i <= 2)) inherits (t); + create table t3 (check (i > 2 and i <= 3)) inherits (t); + create table audit(i int); + create function audit() returns trigger as $$ begin insert into audit(i) values (new.i); return new; end; $$ language plpgsql; + create trigger t_a after insert on t for each row execute procedure audit(); + -- the before trigger on the t would get fired + -- create trigger t_a2 before insert on t for each row execute procedure audit(); + create trigger t1_a before insert on t1 for each row execute procedure audit(); + create trigger t1_a2 after insert on t1 for each row execute procedure audit(); + copy t from stdin with (partitioning); + -- no rows if trigger does not work + select * from audit; + i + --- + 1 + 1 + (2 rows) + + drop table t cascade; + NOTICE: drop cascades to 3 other objects + DETAIL: drop cascades to table t1 + drop cascades to table t2 + drop cascades to table t3 + drop table audit cascade; + drop function audit(); + -- Test bad before row trigger + create table t(i int); + create table t1 (check (i > 0 and i <= 1)) inherits (t); + create table t2 (check (i > 1 and i <= 2)) inherits (t); + create function i2() returns trigger as $$ begin NEW.i := 2; return NEW; end; $$ language plpgsql; + create trigger t1_before before insert on t1 for each row execute procedure i2(); + -- COPY should fail + copy t from stdin with (partitioning); + ERROR: Before row insert trigger on table "t1" modified partitioning routing decision. Aborting insert. + CONTEXT: COPY t, line 1: "1" + drop table t cascade; + NOTICE: drop cascades to 2 other objects + DETAIL: drop cascades to table t1 + drop cascades to table t2 + drop function i2(); + -- Test bad after row trigger + create table t(i int); + create table t1 (check (i > 0 and i <= 1)) inherits (t); + create table t2 (check (i > 1 and i <= 2)) inherits (t); + create function i2() returns trigger as $$ begin NEW.i := 2; return NEW; end; $$ language plpgsql; + create trigger t1_after after insert on t1 for each row execute procedure i2(); + -- COPY should fail + copy t from stdin with (partitioning); + ERROR: After row insert trigger on table "t1" modified partitioning routing decision. Aborting insert. + CONTEXT: COPY t, line 1: "1" + drop table t cascade; + NOTICE: drop cascades to 2 other objects + DETAIL: drop cascades to table t1 + drop cascades to table t2 + drop function i2();
pgsql-hackers by date: