diff -cprN head/src/backend/catalog/Makefile work/src/backend/catalog/Makefile *** head/src/backend/catalog/Makefile 2009-10-08 07:14:16.000000000 +0900 --- work/src/backend/catalog/Makefile 2009-11-05 11:55:00.573160135 +0900 *************** POSTGRES_BKI_SRCS = $(addprefix $(top_sr *** 37,43 **** pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \ pg_ts_parser.h pg_ts_template.h \ pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \ ! pg_default_acl.h \ toasting.h indexing.h \ ) --- 37,43 ---- pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \ pg_ts_parser.h pg_ts_template.h \ pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \ ! pg_default_acl.h pg_partition.h \ toasting.h indexing.h \ ) diff -cprN head/src/backend/catalog/dependency.c work/src/backend/catalog/dependency.c *** head/src/backend/catalog/dependency.c 2009-10-06 04:24:35.000000000 +0900 --- work/src/backend/catalog/dependency.c 2009-11-05 11:55:00.573886532 +0900 *************** *** 41,46 **** --- 41,47 ---- #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_opfamily.h" + #include "catalog/pg_partition.h" #include "catalog/pg_proc.h" #include "catalog/pg_rewrite.h" #include "catalog/pg_tablespace.h" *************** static const Oid object_classes[MAX_OCLA *** 149,155 **** ForeignDataWrapperRelationId, /* OCLASS_FDW */ ForeignServerRelationId, /* OCLASS_FOREIGN_SERVER */ UserMappingRelationId, /* OCLASS_USER_MAPPING */ ! DefaultAclRelationId /* OCLASS_DEFACL */ }; --- 150,157 ---- ForeignDataWrapperRelationId, /* OCLASS_FDW */ ForeignServerRelationId, /* OCLASS_FOREIGN_SERVER */ UserMappingRelationId, /* OCLASS_USER_MAPPING */ ! DefaultAclRelationId, /* OCLASS_DEFACL */ ! PartitionRelationId /* OCLASS_PARTITION */ }; *************** doDeletion(const ObjectAddress *object) *** 1143,1148 **** --- 1145,1154 ---- RemoveDefaultACLById(object->objectId); break; + case OCLASS_PARTITION: + RemovePartitionById(object->objectId); + break; + default: elog(ERROR, "unrecognized object class: %u", object->classId); *************** getObjectClass(const ObjectAddress *obje *** 2066,2071 **** --- 2072,2081 ---- case DefaultAclRelationId: Assert(object->objectSubId == 0); return OCLASS_DEFACL; + + case PartitionRelationId: + Assert(object->objectSubId == 0); + return OCLASS_PARTITION; } /* shouldn't get here */ *************** getObjectDescription(const ObjectAddress *** 2671,2676 **** --- 2681,2730 ---- break; } + case OCLASS_PARTITION: + { + Relation rel; + ScanKeyData skey[1]; + SysScanDesc rcscan; + HeapTuple tup; + Form_pg_partition partition; + + rel = heap_open(PartitionRelationId, AccessShareLock); + + ScanKeyInit(&skey[0], + Anum_pg_partition_partrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(object->objectId)); + + rcscan = systable_beginscan(rel, PartitionRelidIndexId, + true, SnapshotNow, 1, skey); + + tup = systable_getnext(rcscan); + + if (!HeapTupleIsValid(tup)) + elog(ERROR, "could not find tuple for partition %u", + object->objectId); + + partition = (Form_pg_partition) GETSTRUCT(tup); + + switch (partition->partkind) + { + case PARTITION_BY_RANGE: + appendStringInfo(&buffer, _("range partition")); + break; + case PARTITION_BY_LIST: + appendStringInfo(&buffer, _("list partition")); + break; + default: + appendStringInfo(&buffer, _("partition")); + break; + } + + systable_endscan(rcscan); + heap_close(rel, AccessShareLock); + break; + } + default: appendStringInfo(&buffer, "unrecognized object %u %u %d", object->classId, diff -cprN head/src/backend/catalog/heap.c work/src/backend/catalog/heap.c *** head/src/backend/catalog/heap.c 2009-10-06 04:24:35.000000000 +0900 --- work/src/backend/catalog/heap.c 2009-11-05 11:55:00.574839091 +0900 *************** *** 43,48 **** --- 43,49 ---- #include "catalog/pg_constraint.h" #include "catalog/pg_inherits.h" #include "catalog/pg_namespace.h" + #include "catalog/pg_partition.h" #include "catalog/pg_statistic.h" #include "catalog/pg_tablespace.h" #include "catalog/pg_type.h" *************** RemoveStatistics(Oid relid, AttrNumber a *** 2361,2366 **** --- 2362,2392 ---- /* + * Remove a pg_partition entry + */ + void + RemovePartitionById(Oid relid) + { + Relation rel; + HeapTuple tup; + + rel = heap_open(PartitionRelationId, RowExclusiveLock); + + tup = SearchSysCache(PARTITIONKEY, + ObjectIdGetDatum(relid), + 0, 0, 0); + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for partition %u", relid); + + simple_heap_delete(rel, &tup->t_self); + + ReleaseSysCache(tup); + + heap_close(rel, RowExclusiveLock); + } + + + /* * RelationTruncateIndexes - truncate all indexes associated * with the heap relation to zero tuples. * diff -cprN head/src/backend/commands/tablecmds.c work/src/backend/commands/tablecmds.c *** head/src/backend/commands/tablecmds.c 2009-10-15 07:14:21.000000000 +0900 --- work/src/backend/commands/tablecmds.c 2009-11-05 20:07:25.137312000 +0900 *************** *** 32,37 **** --- 32,39 ---- #include "catalog/pg_inherits_fn.h" #include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" + #include "catalog/pg_operator.h" + #include "catalog/pg_partition.h" #include "catalog/pg_tablespace.h" #include "catalog/pg_trigger.h" #include "catalog/pg_type.h" *************** *** 65,72 **** --- 67,76 ---- #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/smgr.h" + #include "tcop/utility.h" #include "utils/acl.h" #include "utils/builtins.h" + #include "utils/datum.h" #include "utils/fmgroids.h" #include "utils/inval.h" #include "utils/lsyscache.h" *************** typedef struct NewColumnValue *** 174,179 **** --- 178,193 ---- } NewColumnValue; /* + * PartitionValues - store partition values. + */ + typedef struct PartitionValues + { + Oid relid; + int nvalues; + Datum *values; + } PartitionValues; + + /* * Error-reporting support for RemoveRelations */ struct dropmsgstrings *************** static void MergeAttributesIntoExisting( *** 230,236 **** static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel); static void StoreCatalogInheritance(Oid relationId, List *supers); static void StoreCatalogInheritance1(Oid relationId, Oid parentOid, ! int16 seqNumber, Relation inhRelation); static int findAttrByName(const char *attributeName, List *schema); static void setRelhassubclassInRelation(Oid relationId, bool relhassubclass); static void AlterIndexNamespaces(Relation classRel, Relation rel, --- 244,250 ---- static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel); static void StoreCatalogInheritance(Oid relationId, List *supers); static void StoreCatalogInheritance1(Oid relationId, Oid parentOid, ! int16 seqNumber, ArrayType *values, Relation inhRelation); static int findAttrByName(const char *attributeName, List *schema); static void setRelhassubclassInRelation(Oid relationId, bool relhassubclass); static void AlterIndexNamespaces(Relation classRel, Relation rel, *************** static void ATExecEnableDisableTrigger(R *** 329,339 **** char fires_when, bool skip_system); static void ATExecEnableDisableRule(Relation rel, char *rulename, char fires_when); ! static void ATExecAddInherit(Relation rel, RangeVar *parent); static void ATExecDropInherit(Relation rel, RangeVar *parent); static void copy_relation_data(SMgrRelation rel, SMgrRelation dst, ForkNumber forkNum, bool istemp); static const char *storage_name(char c); /* ---------------------------------------------------------------- --- 343,358 ---- char fires_when, bool skip_system); static void ATExecEnableDisableRule(Relation rel, char *rulename, char fires_when); ! static void ATExecAddInherit(List **wqueue, AlteredTableInfo *tab, Relation rel, AddInherit *def); static void ATExecDropInherit(Relation rel, RangeVar *parent); + static void ATExecPartitionBy(Relation rel, PartitionBy *defs); static void copy_relation_data(SMgrRelation rel, SMgrRelation dst, ForkNumber forkNum, bool istemp); static const char *storage_name(char c); + static ArrayType *AddPartitionConstraint(List **wqueue, AlteredTableInfo *tab, + AddInherit *def, Relation inhrel, Relation parent, Relation child); + static Datum *evaluate_values(Oid typid, int typlen, bool typbyval, + List *values, int *length); /* ---------------------------------------------------------------- *************** StoreCatalogInheritance(Oid relationId, *** 1767,1773 **** { Oid parentOid = lfirst_oid(entry); ! StoreCatalogInheritance1(relationId, parentOid, seqNumber, relation); seqNumber++; } --- 1786,1792 ---- { Oid parentOid = lfirst_oid(entry); ! StoreCatalogInheritance1(relationId, parentOid, seqNumber, NULL, relation); seqNumber++; } *************** StoreCatalogInheritance(Oid relationId, *** 1779,1786 **** * of parentOid. inhRelation is the already-opened pg_inherits catalog. */ static void ! StoreCatalogInheritance1(Oid relationId, Oid parentOid, ! int16 seqNumber, Relation inhRelation) { TupleDesc desc = RelationGetDescr(inhRelation); Datum datum[Natts_pg_inherits]; --- 1798,1805 ---- * of parentOid. inhRelation is the already-opened pg_inherits catalog. */ static void ! StoreCatalogInheritance1(Oid relationId, Oid parentOid, int16 seqNumber, ! ArrayType *values, Relation inhRelation) { TupleDesc desc = RelationGetDescr(inhRelation); Datum datum[Natts_pg_inherits]; *************** StoreCatalogInheritance1(Oid relationId, *** 1795,1804 **** --- 1814,1825 ---- datum[0] = ObjectIdGetDatum(relationId); /* inhrelid */ datum[1] = ObjectIdGetDatum(parentOid); /* inhparent */ datum[2] = Int16GetDatum(seqNumber); /* inhseqno */ + datum[3] = PointerGetDatum(values); /* inhvalues */ nullarr[0] = false; nullarr[1] = false; nullarr[2] = false; + nullarr[3] = (values == NULL); tuple = heap_form_tuple(desc, datum, nullarr); *************** RenameRelationInternal(Oid myrelid, cons *** 2234,2239 **** --- 2255,2279 ---- } /* + * CREATE PARTITION partition ON table + */ + void + CreatePartition(CreatePartitionStmt *stmt, const char *queryString) + { + List *stmts; + ListCell *cell; + + stmts = transformCreatePartition(stmt->def, stmt->parent); + + foreach(cell, stmts) + { + ProcessUtility((Node *) lfirst(cell), + queryString, NULL, false, NULL, NULL); + CommandCounterIncrement(); + } + } + + /* * Disallow ALTER TABLE (and similar commands) when the current backend has * any open reference to the target table besides the one just acquired by * the calling command; this implies there's an open cursor or active plan. *************** ATPrepCmd(List **wqueue, Relation rel, A *** 2579,2584 **** --- 2619,2628 ---- /* No command-specific prep needed */ pass = AT_PASS_MISC; break; + case AT_AddInherit: /* INHERIT [AS PARTITION] */ + ATSimplePermissions(rel, false); + pass = AT_PASS_ADD_CONSTR; + break; case AT_EnableTrig: /* ENABLE TRIGGER variants */ case AT_EnableAlwaysTrig: case AT_EnableReplicaTrig: *************** ATPrepCmd(List **wqueue, Relation rel, A *** 2591,2598 **** case AT_EnableAlwaysRule: case AT_EnableReplicaRule: case AT_DisableRule: ! case AT_AddInherit: /* INHERIT / NO INHERIT */ ! case AT_DropInherit: ATSimplePermissions(rel, false); /* These commands never recurse */ /* No command-specific prep needed */ --- 2635,2642 ---- case AT_EnableAlwaysRule: case AT_EnableReplicaRule: case AT_DisableRule: ! case AT_DropInherit: /* NO INHERIT */ ! case AT_PartitionBy: /* PARTITION BY / NO PARTITION */ ATSimplePermissions(rel, false); /* These commands never recurse */ /* No command-specific prep needed */ *************** ATExecCmd(List **wqueue, AlteredTableInf *** 2833,2843 **** break; case AT_AddInherit: ! ATExecAddInherit(rel, (RangeVar *) cmd->def); break; case AT_DropInherit: ATExecDropInherit(rel, (RangeVar *) cmd->def); break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); --- 2877,2890 ---- break; case AT_AddInherit: ! ATExecAddInherit(wqueue, tab, rel, (AddInherit *) cmd->def); break; case AT_DropInherit: ATExecDropInherit(rel, (RangeVar *) cmd->def); break; + case AT_PartitionBy: /* PARTITION BY / NO PARTITION */ + ATExecPartitionBy(rel, (PartitionBy *) cmd->def); + break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); *************** ATExecAlterColumnType(AlteredTableInfo * *** 6164,6169 **** --- 6211,6221 ---- getObjectDescription(&foundObject)); break; + case OCLASS_PARTITION: + /* TODO: recreate all partitions */ + elog(ERROR, "cannot change partition key"); + break; + default: elog(ERROR, "unrecognized object class: %u", foundObject.classId); *************** ATExecEnableDisableRule(Relation rel, ch *** 7138,7145 **** * same data types and expressions. */ static void ! ATExecAddInherit(Relation child_rel, RangeVar *parent) { Relation parent_rel, catalogRelation; SysScanDesc scan; --- 7190,7199 ---- * same data types and expressions. */ static void ! ATExecAddInherit(List **wqueue, AlteredTableInfo *tab, ! Relation child_rel, AddInherit *def) { + RangeVar *parent = def->parent; Relation parent_rel, catalogRelation; SysScanDesc scan; *************** ATExecAddInherit(Relation child_rel, Ran *** 7147,7152 **** --- 7201,7207 ---- HeapTuple inheritsTuple; int32 inhseqno; List *children; + ArrayType *inhvalues; /* * AccessShareLock on the parent is what's obtained during normal CREATE *************** ATExecAddInherit(Relation child_rel, Ran *** 7238,7251 **** --- 7293,7331 ---- /* Match up the constraints and bump coninhcount as needed */ MergeConstraintsIntoExisting(child_rel, parent_rel); + /* Add CHECK constraint for partition */ + if (def->kind) + inhvalues = AddPartitionConstraint(wqueue, tab, def, + catalogRelation, parent_rel, child_rel); + else + inhvalues = NULL; + /* * OK, it looks valid. Make the catalog entries that show inheritance. */ StoreCatalogInheritance1(RelationGetRelid(child_rel), RelationGetRelid(parent_rel), inhseqno + 1, + inhvalues, catalogRelation); + #ifdef NOT_USED + /* XXX: Do we need to add depencency to inherit object? */ + if (def->kind) + { + ObjectAddress partitionObj; + ObjectAddress inheritsObj; + + partitionObj.classId = PartitionRelationId; + partitionObj.objectId = RelationGetRelid(child_rel); + partitionObj.objectSubId = 0; + inheritsObj.classId = InheritsRelationId; + inheritsObj.objectId = RelationGetRelid(parent_rel); + inheritsObj.objectSubId = RelationGetRelid(child_rel); + recordDependencyOn(&childObj, &partitionObj, DEPENDENCY_NORMAL); + } + #endif + /* Now we're done with pg_inherits */ heap_close(catalogRelation, RowExclusiveLock); *************** ATExecAddInherit(Relation child_rel, Ran *** 7254,7259 **** --- 7334,7649 ---- } /* + * AddPartitionConstraint - Add check constraint for partition. + */ + static ArrayType * + AddPartitionConstraint(List **wqueue, AlteredTableInfo *tab, AddInherit *def, + Relation inhrel, Relation parent, Relation child) + { + SysScanDesc scan; + ScanKeyData key; + HeapTuple tp; + Oid opno; + Oid oprcode; + Form_pg_operator optup; + char kind; + bool isNull; + Datum datum; + Node *partkey; + List *partopr; + FmgrInfo opfn; + Oid elmtype; + int16 elmlen; + bool elmbyval; + char elmalign; + Node *check_expr; + Datum *values; + int nvalues; + List *partitions = NIL; + + /* Get partition key and operator. */ + tp = SearchSysCache(PARTITIONKEY, + ObjectIdGetDatum(RelationGetRelid(parent)), + 0, 0, 0); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "relation \"%s.%s\" has no partition key", + get_namespace_name(RelationGetNamespace(parent)), + RelationGetRelationName(parent)); + + kind = DatumGetChar(SysCacheGetAttr(PARTITIONKEY, tp, + Anum_pg_partition_partkind, &isNull)); + opno = DatumGetObjectId(SysCacheGetAttr(PARTITIONKEY, tp, + Anum_pg_partition_partopr, &isNull)); + datum = SysCacheGetAttr(PARTITIONKEY, tp, + Anum_pg_partition_partkey, &isNull); + ReleaseSysCache(tp); + + if (kind != def->kind) + elog(ERROR, "cannot add partition '%c' to relation \"%s.%s\" partitioned with '%c'", + def->kind, + get_namespace_name(RelationGetNamespace(parent)), + RelationGetRelationName(parent), + kind); + + /* Get partition key and operator. */ + tp = SearchSysCache(OPEROID, + ObjectIdGetDatum(opno), + 0, 0, 0); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for operator %u", opno); + optup = (Form_pg_operator) GETSTRUCT(tp); + elmtype = optup->oprleft; + oprcode = optup->oprcode; + partopr = list_make2(makeString(get_namespace_name(optup->oprnamespace)), + makeString(pstrdup(NameStr(optup->oprname)))); + ReleaseSysCache(tp); + + /* Get partition key and operator. */ + partkey = stringToNode(TextDatumGetCString(datum)); + fmgr_info(oprcode, &opfn); + get_typlenbyvalalign(elmtype, &elmlen, &elmbyval, &elmalign); + + /* Gather values from existing paritition. */ + ScanKeyInit(&key, + Anum_pg_inherits_inhparent, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(parent))); + scan = systable_beginscan(inhrel, InvalidOid, false, SnapshotNow, 1, &key); + + while (HeapTupleIsValid(tp = systable_getnext(scan))) + { + Form_pg_inherits inh = (Form_pg_inherits) GETSTRUCT(tp); + + /* gather existing partition values */ + Datum datum; + bool isnull; + + datum = heap_getattr(tp, Anum_pg_inherits_inhvalues, + RelationGetDescr(inhrel), &isnull); + if (isnull) + { + if (def->values == NIL) + elog(ERROR, "duplicated overflow partition"); + else if (def->kind == PARTITION_BY_LIST) + elog(ERROR, "cannot add list partition to relation that has overflow partition"); + } + else + { + ArrayType *array; + PartitionValues *part; + + array = DatumGetArrayTypePCopy(datum); + + part = (PartitionValues *) palloc(sizeof(PartitionValues)); + part->relid = inh->inhrelid; + deconstruct_array(array, elmtype, elmlen, elmbyval, elmalign, + &part->values, NULL, &part->nvalues); + + partitions = lappend(partitions, part); + } + } + systable_endscan(scan); + + /* Convert expressions to datum array. */ + values = evaluate_values(elmtype, elmlen, elmbyval, def->values, &nvalues); + + /* ALTER TABLE ... ADD CHECK */ + switch (def->kind) + { + case PARTITION_BY_RANGE: + { + PartitionValues *largest = NULL; + Node *lt_expr = NULL; + Node *ge_expr = NULL; + ListCell *cell; + + /* Find partition with largest upper value. */ + foreach(cell, partitions) + { + PartitionValues *part = (PartitionValues *) lfirst(cell); + + /* overflow partition is the largest */ + if (largest == NULL || part->nvalues < 0 || + DatumGetBool(FunctionCall2(&opfn, largest->values[0], part->values[0]))) + largest = part; + } + + if (largest) + { + /* TODO: support spliting existing partition */ + if (nvalues > 0 && + (largest->nvalues < 0 || !DatumGetBool(FunctionCall2( + &opfn, largest->values[0], values[0])))) + elog(ERROR, "cannot split existing partition"); + + /* NOT (key < lower) */ + ge_expr = + (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL, + (Node *) makeA_Expr(AEXPR_OP, partopr, partkey, + (Node *) makeConst(elmtype, -1, elmlen, + largest->values[0], false, elmbyval), -1), -1); + } + + /* key < upper */ + if (nvalues > 0) + lt_expr = (Node *) makeA_Expr( + AEXPR_OP, partopr, partkey, linitial(def->values), -1); + + if (ge_expr == NULL) /* key < upper */ + check_expr = lt_expr; + else if (lt_expr == NULL) /* NOT (key < lower) */ + check_expr = ge_expr; + else /* NOT (key < lower) AND key < upper */ + check_expr = (Node *) makeA_Expr( + AEXPR_AND, NIL, ge_expr, lt_expr, -1); + break; + } + case PARTITION_BY_LIST: + { + ListCell *cell; + int i; + int j; + + if (nvalues > 0) + { + /* Check for conflicted value. */ + foreach(cell, partitions) + { + PartitionValues *part = (PartitionValues *) lfirst(cell); + + for (i = 0; i < part->nvalues; i++) + { + for (j = 0; j < nvalues; j++) + { + if (DatumGetBool(FunctionCall2(&opfn, + part->values[i], values[j]))) + { + elog(ERROR, "conflicted list partition values"); + } + } + } + } + + /* CHECK ( key = ANY ( values ) ) */ + check_expr = + (Node *) makeA_Expr(AEXPR_IN, partopr, partkey, + (Node *) def->values, -1); + } + else + { + List *all_values = NIL; + + /* Gather all values */ + foreach(cell, partitions) + { + PartitionValues *part = (PartitionValues *) lfirst(cell); + + for (i = 0; i < part->nvalues; i++) + { + Const *value = makeConst(elmtype, -1, elmlen, + part->values[i], false, elmbyval); + all_values = lappend(all_values, value); + } + } + + /* CHECK ( NOT (key = ANY ( values ) ) ) */ + if (all_values == NIL) + check_expr = NULL; + else + check_expr = + (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL, + (Node *) makeA_Expr(AEXPR_IN, partopr, partkey, + (Node *) all_values, -1), -1); + } + + break; + } + default: + elog(ERROR, "unknown partition kind: %d", def->kind); + check_expr = NULL; /* keep compiler quiet */ + } + + /* Add partition constraint if needed */ + if (check_expr != NULL) + { + Constraint *check; + + check = makeNode(Constraint); + check->contype = CONSTR_CHECK; + check->location = -1; + check->raw_expr = check_expr; + check->cooked_expr = NULL; + ATAddCheckConstraint(wqueue, tab, child, check, false, false); + } + + if (nvalues > 0) + return construct_array(values, nvalues, + elmtype, elmlen, elmbyval, elmalign); + else + return NULL; + } + + /* + * evaluate_values - evaluate a list of expressions to build a datum array. + */ + static Datum * + evaluate_values(Oid typid, int typlen, bool typbyval, List *values, int *length) + { + ListCell *cell; + ParseState *pstate; + EState *estate; + ExprContext *econtext; + int i; + Datum *datum; + + *length = list_length(values); + if (*length < 1) + return NULL; + + datum = (Datum *) palloc(*length * sizeof(Datum)); + pstate = make_parsestate(NULL); + estate = CreateExecutorState(); + econtext = GetPerTupleExprContext(estate); + + i = 0; + foreach(cell, values) + { + Node *value = (Node *) lfirst(cell); + bool isnull; + ExprState *expr; + MemoryContext oldcxt; + + oldcxt = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate)); + + value = transformExpr(pstate, value); + value = coerce_to_specific_type(pstate, value, typid, "PARTITION"); + expr = ExecPrepareExpr((Expr *) value, estate); + + datum[i] = ExecEvalExpr(expr, econtext, &isnull, NULL); + if (isnull) + elog(ERROR, "partition key must not be NULL"); + + MemoryContextSwitchTo(oldcxt); + + if (!typbyval) + { + if (typlen == -1) + datum[i] = PointerGetDatum(PG_DETOAST_DATUM_COPY(datum[i])); + else + datum[i] = datumCopy(datum[i], false, typlen); + } + + ResetPerTupleExprContext(estate); + i++; + } + + FreeExecutorState(estate); + free_parsestate(pstate); + + return datum; + } + + /* * Obtain the source-text form of the constraint expression for a check * constraint, given its pg_constraint tuple */ *************** ATExecDropInherit(Relation rel, RangeVar *** 7717,7722 **** --- 8107,8185 ---- /* + * ALTER TABLE PARTITION BY / NO PARTITION + */ + static void + ATExecPartitionBy(Relation rel, PartitionBy *defs) + { + Oid relid = RelationGetRelid(rel); + ObjectAddress myself; + + myself.classId = PartitionRelationId; + myself.objectId = relid; + myself.objectSubId = 0; + + if (defs == NULL) + { + /* ALTER TABLE NO PARTITION */ + performDeletion(&myself, DROP_RESTRICT); + } + else + { + Relation catalogRel; + TupleDesc catalogDesc; + Datum datum[Natts_pg_partition]; + bool nulls[Natts_pg_partition]; + HeapTuple tuple; + Node *partkey; + Oid keytype; + Oid partopr; + ParseState *pstate; + RangeTblEntry *rte; + + Assert(defs->key != NULL); + Assert(defs->opr != NIL); + + /* Transform the expression of partition key. */ + pstate = make_parsestate(NULL); + rte = addRangeTableEntryForRelation(pstate, rel, NULL, false, true); + addRTEtoQuery(pstate, rte, true, true, true); + partkey = transformExpr(pstate, defs->key); + free_parsestate(pstate); + + /* Extract operator oid to compare partition keys */ + keytype = exprType(partkey); + partopr = compatible_oper_opid(defs->opr, keytype, keytype, false); + + /* + * Make the pg_partition entry + */ + memset(nulls, 0, sizeof(nulls)); + datum[0] = ObjectIdGetDatum(relid); /* partrelid */ + datum[1] = ObjectIdGetDatum(partopr); /* partopr */ + datum[2] = CharGetDatum(defs->kind); /* partkind */ + datum[3] = CStringGetTextDatum(nodeToString(partkey)); /* partkey */ + + catalogRel = heap_open(PartitionRelationId, RowExclusiveLock); + catalogDesc = RelationGetDescr(catalogRel); + + tuple = heap_form_tuple(catalogDesc, datum, nulls); + simple_heap_insert(catalogRel, tuple); + CatalogUpdateIndexes(catalogRel, tuple); + heap_freetuple(tuple); + + /* Store a dependency */ + recordDependencyOnSingleRelExpr(&myself, partkey, relid, + DEPENDENCY_NORMAL, DEPENDENCY_AUTO); + + heap_close(catalogRel, RowExclusiveLock); + + /* Caller will create partition tables */ + } + } + + + /* * Execute ALTER TABLE SET SCHEMA * * Note: caller must have checked ownership of the relation already diff -cprN head/src/backend/nodes/copyfuncs.c work/src/backend/nodes/copyfuncs.c *** head/src/backend/nodes/copyfuncs.c 2009-10-28 23:55:38.000000000 +0900 --- work/src/backend/nodes/copyfuncs.c 2009-11-05 12:11:45.983845439 +0900 *************** _copyCreateStmt(CreateStmt *from) *** 2503,2508 **** --- 2503,2509 ---- COPY_NODE_FIELD(options); COPY_SCALAR_FIELD(oncommit); COPY_STRING_FIELD(tablespacename); + COPY_NODE_FIELD(partitions); return newnode; } *************** _copyAlterTSConfigurationStmt(AlterTSCon *** 3450,3455 **** --- 3451,3506 ---- return newnode; } + static Partition * + _copyPartition(Partition *from) + { + Partition *newnode = makeNode(Partition); + + COPY_SCALAR_FIELD(kind); + COPY_NODE_FIELD(name); + COPY_NODE_FIELD(values); + COPY_NODE_FIELD(options); + COPY_STRING_FIELD(tablespacename); + + return newnode; + } + + static PartitionBy * + _copyPartitionBy(PartitionBy *from) + { + PartitionBy *newnode = makeNode(PartitionBy); + + COPY_SCALAR_FIELD(kind); + COPY_NODE_FIELD(key); + COPY_NODE_FIELD(opr); + COPY_NODE_FIELD(defs); + + return newnode; + } + + static CreatePartitionStmt * + _copyCreatePartitionStmt(CreatePartitionStmt *from) + { + CreatePartitionStmt *newnode = makeNode(CreatePartitionStmt); + + COPY_NODE_FIELD(parent); + COPY_NODE_FIELD(def); + + return newnode; + } + + static AddInherit * + _copyAddInherit(AddInherit *from) + { + AddInherit *newnode = makeNode(AddInherit); + + COPY_NODE_FIELD(parent); + COPY_SCALAR_FIELD(kind); + COPY_NODE_FIELD(values); + + return newnode; + } + /* **************************************************************** * pg_list.h copy functions * **************************************************************** *************** copyObject(void *from) *** 4117,4122 **** --- 4168,4185 ---- case T_AlterTSConfigurationStmt: retval = _copyAlterTSConfigurationStmt(from); break; + case T_Partition: + retval = _copyPartition(from); + break; + case T_PartitionBy: + retval = _copyPartitionBy(from); + break; + case T_CreatePartitionStmt: + retval = _copyCreatePartitionStmt(from); + break; + case T_AddInherit: + retval = _copyAddInherit(from); + break; case T_A_Expr: retval = _copyAExpr(from); diff -cprN head/src/backend/nodes/equalfuncs.c work/src/backend/nodes/equalfuncs.c *** head/src/backend/nodes/equalfuncs.c 2009-10-28 23:55:38.000000000 +0900 --- work/src/backend/nodes/equalfuncs.c 2009-11-05 12:11:46.040814773 +0900 *************** _equalCreateStmt(CreateStmt *a, CreateSt *** 1101,1106 **** --- 1101,1107 ---- COMPARE_NODE_FIELD(options); COMPARE_SCALAR_FIELD(oncommit); COMPARE_STRING_FIELD(tablespacename); + COMPARE_NODE_FIELD(partitions); return true; } *************** _equalAlterTSConfigurationStmt(AlterTSCo *** 1900,1905 **** --- 1901,1948 ---- } static bool + _equalPartition(Partition *a, Partition *b) + { + COMPARE_SCALAR_FIELD(kind); + COMPARE_NODE_FIELD(name); + COMPARE_NODE_FIELD(values); + COMPARE_NODE_FIELD(options); + COMPARE_STRING_FIELD(tablespacename); + + return true; + } + + static bool + _equalPartitionBy(PartitionBy *a, PartitionBy *b) + { + COMPARE_SCALAR_FIELD(kind); + COMPARE_NODE_FIELD(key); + COMPARE_NODE_FIELD(opr); + COMPARE_NODE_FIELD(defs); + + return true; + } + + static bool + _equalCreatePartitionStmt(CreatePartitionStmt *a, CreatePartitionStmt *b) + { + COMPARE_NODE_FIELD(parent); + COMPARE_NODE_FIELD(def); + + return true; + } + + static bool + _equalAddInherit(AddInherit *a, AddInherit *b) + { + COMPARE_NODE_FIELD(parent); + COMPARE_SCALAR_FIELD(kind); + COMPARE_NODE_FIELD(values); + + return true; + } + + static bool _equalAExpr(A_Expr *a, A_Expr *b) { COMPARE_SCALAR_FIELD(kind); *************** equal(void *a, void *b) *** 2810,2815 **** --- 2853,2870 ---- case T_AlterTSConfigurationStmt: retval = _equalAlterTSConfigurationStmt(a, b); break; + case T_Partition: + retval = _equalPartition(a, b); + break; + case T_PartitionBy: + retval = _equalPartitionBy(a, b); + break; + case T_CreatePartitionStmt: + retval = _equalCreatePartitionStmt(a, b); + break; + case T_AddInherit: + retval = _equalAddInherit(a, b); + break; case T_A_Expr: retval = _equalAExpr(a, b); diff -cprN head/src/backend/nodes/outfuncs.c work/src/backend/nodes/outfuncs.c *** head/src/backend/nodes/outfuncs.c 2009-10-28 23:55:38.000000000 +0900 --- work/src/backend/nodes/outfuncs.c 2009-11-05 12:22:51.764905902 +0900 *************** _outCreateStmt(StringInfo str, CreateStm *** 1782,1787 **** --- 1782,1788 ---- WRITE_NODE_FIELD(options); WRITE_ENUM_FIELD(oncommit, OnCommitAction); WRITE_STRING_FIELD(tablespacename); + WRITE_NODE_FIELD(partitions); } static void *************** _outConstraint(StringInfo str, Constrain *** 2419,2424 **** --- 2420,2448 ---- } } + static void + _outPartition(StringInfo str, Partition *node) + { + WRITE_NODE_TYPE("PARTITION"); + + WRITE_CHAR_FIELD(kind); + WRITE_NODE_FIELD(name); + WRITE_NODE_FIELD(values); + WRITE_NODE_FIELD(options); + WRITE_STRING_FIELD(tablespacename); + } + + static void + _outPartitionBy(StringInfo str, PartitionBy *node) + { + WRITE_NODE_TYPE("PARTITIONBY"); + + WRITE_CHAR_FIELD(kind); + WRITE_NODE_FIELD(key); + WRITE_NODE_FIELD(opr); + WRITE_NODE_FIELD(defs); + } + /* * _outNode - *************** _outNode(StringInfo str, void *obj) *** 2872,2877 **** --- 2896,2907 ---- case T_XmlSerialize: _outXmlSerialize(str, obj); break; + case T_Partition: + _outPartition(str, obj); + break; + case T_PartitionBy: + _outPartitionBy(str, obj); + break; default: diff -cprN head/src/backend/parser/gram.y work/src/backend/parser/gram.y *** head/src/backend/parser/gram.y 2009-10-15 07:14:22.000000000 +0900 --- work/src/backend/parser/gram.y 2009-11-05 18:27:12.662798025 +0900 *************** static TypeName *TableFuncTypeName(List *** 183,188 **** --- 183,191 ---- InsertStmt *istmt; VariableSetStmt *vsetstmt; + AddInherit *addinherit; + Partition *partition; + PartitionBy *partitionby; } %type stmt schema_stmt *************** static TypeName *TableFuncTypeName(List *** 198,204 **** CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateAssertStmt CreateTrigStmt ! CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt DropGroupStmt DropOpClassStmt DropOpFamilyStmt DropPLangStmt DropStmt DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt --- 201,207 ---- CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateAssertStmt CreateTrigStmt ! CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePartitionStmt CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt DropGroupStmt DropOpClassStmt DropOpFamilyStmt DropPLangStmt DropStmt DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt *************** static TypeName *TableFuncTypeName(List *** 435,440 **** --- 438,448 ---- %type opt_existing_window_name %type opt_frame_clause frame_extent frame_bound + %type InheritAsPartition + %type RangePartition ListPartition + %type PartitionBy OptPartition + %type OptUsingOp OptRangePartitions RangePartitions RangeUpper + OptListPartitions ListPartitions ListValues ConstValues /* * Non-keyword token types. These are hard-wired into the "flex" lexer. *************** static TypeName *TableFuncTypeName(List *** 497,503 **** KEY LANGUAGE LARGE_P LAST_P LC_COLLATE_P LC_CTYPE_P LEADING ! LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOGIN_P MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE --- 505,511 ---- KEY LANGUAGE LARGE_P LAST_P LC_COLLATE_P LC_CTYPE_P LEADING ! LEAST LEFT LESS LEVEL LIKE LIMIT LIST LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOGIN_P MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE *************** static TypeName *TableFuncTypeName(List *** 525,531 **** STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P SYMMETRIC SYSID SYSTEM_P ! TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P TRUNCATE TRUSTED TYPE_P --- 533,539 ---- STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P SYMMETRIC SYSID SYSTEM_P ! TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THAN THEN TIME TIMESTAMP TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P TRUNCATE TRUSTED TYPE_P *************** stmt : *** 678,683 **** --- 686,692 ---- | CreateRoleStmt | CreateUserStmt | CreateUserMappingStmt + | CreatePartitionStmt | CreatedbStmt | DeallocateStmt | DeclareCursorStmt *************** schema_stmt: *** 1136,1141 **** --- 1145,1151 ---- | CreateTrigStmt | GrantStmt | ViewStmt + | CreatePartitionStmt ; *************** AlterTableStmt: *** 1561,1566 **** --- 1571,1584 ---- n->relkind = OBJECT_TABLE; $$ = (Node *)n; } + | ALTER PARTITION relation_expr alter_table_cmds + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $3; + n->cmds = $4; + n->relkind = OBJECT_TABLE; + $$ = (Node *)n; + } | ALTER INDEX qualified_name alter_table_cmds { AlterTableStmt *n = makeNode(AlterTableStmt); *************** alter_table_cmd: *** 1836,1847 **** n->name = $3; $$ = (Node *)n; } ! /* ALTER TABLE INHERIT */ ! | INHERIT qualified_name { AlterTableCmd *n = makeNode(AlterTableCmd); n->subtype = AT_AddInherit; ! n->def = (Node *) $2; $$ = (Node *)n; } /* ALTER TABLE NO INHERIT */ --- 1854,1867 ---- n->name = $3; $$ = (Node *)n; } ! /* ALTER TABLE INHERIT [ AS PARTITION ] */ ! | INHERIT qualified_name InheritAsPartition { + AddInherit *inh = $3; AlterTableCmd *n = makeNode(AlterTableCmd); + inh->parent = $2; n->subtype = AT_AddInherit; ! n->def = (Node *) inh; $$ = (Node *)n; } /* ALTER TABLE NO INHERIT */ *************** alter_table_cmd: *** 1884,1889 **** --- 1904,1925 ---- n->def = (Node *)$2; $$ = (Node *)n; } + /* ALTER TABLE PARTITION BY ... */ + | PartitionBy + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_PartitionBy; + n->def = (Node *)$1; + $$ = (Node *)n; + } + /* ALTER TABLE NO PARTITION */ + | NO PARTITION + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_PartitionBy; + n->def = NULL; + $$ = (Node *)n; + } ; alter_column_default: *************** copy_generic_opt_arg_list_item: *** 2171,2177 **** *****************************************************************************/ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')' ! OptInherit OptWith OnCommitOption OptTableSpace { CreateStmt *n = makeNode(CreateStmt); $4->istemp = $2; --- 2207,2213 ---- *****************************************************************************/ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')' ! OptInherit OptWith OnCommitOption OptTableSpace OptPartition { CreateStmt *n = makeNode(CreateStmt); $4->istemp = $2; *************** CreateStmt: CREATE OptTemp TABLE qualifi *** 2182,2191 **** n->options = $9; n->oncommit = $10; n->tablespacename = $11; $$ = (Node *)n; } | CREATE OptTemp TABLE qualified_name OF qualified_name ! '(' OptTableElementList ')' OptWith OnCommitOption OptTableSpace { /* SQL99 CREATE TABLE OF (cols) seems to be satisfied * by our inheritance capabilities. Let's try it... --- 2218,2228 ---- n->options = $9; n->oncommit = $10; n->tablespacename = $11; + n->partitions = $12; $$ = (Node *)n; } | CREATE OptTemp TABLE qualified_name OF qualified_name ! '(' OptTableElementList ')' OptWith OnCommitOption OptTableSpace OptPartition { /* SQL99 CREATE TABLE OF (cols) seems to be satisfied * by our inheritance capabilities. Let's try it... *************** CreateStmt: CREATE OptTemp TABLE qualifi *** 2199,2208 **** --- 2236,2367 ---- n->options = $10; n->oncommit = $11; n->tablespacename = $12; + n->partitions = $13; $$ = (Node *)n; } ; + OptPartition: + PartitionBy { $$ = $1; } + | /*EMPTY*/ { $$ = NULL; } + ; + + PartitionBy: + PARTITION BY RANGE '(' a_expr OptUsingOp ')' OptRangePartitions + { + PartitionBy *n = makeNode(PartitionBy); + + n->kind = PARTITION_BY_RANGE; + n->key = $5; + n->opr = ($6 != NIL ? $6 : list_make1(makeString("<"))); + n->defs = $8; + $$ = n; + } + | PARTITION BY LIST '(' a_expr OptUsingOp ')' OptListPartitions + { + PartitionBy *n = makeNode(PartitionBy); + n->kind = PARTITION_BY_LIST; + n->key = $5; + n->opr = ($6 != NIL ? $6 : list_make1(makeString("="))); + n->defs = $8; + $$ = n; + } + ; + + OptUsingOp: + USING qual_all_Op { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } + ; + + OptRangePartitions: + '(' RangePartitions ')' { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } + ; + + RangePartitions: + RangePartition { $$ = list_make1($1); } + | RangePartitions ',' RangePartition { $$ = lappend($1, $3); } + ; + + RangePartition: + PARTITION qualified_name VALUES LESS THAN RangeUpper OptWith OptTableSpace + { + Partition *n = makeNode(Partition); + n->kind = PARTITION_BY_RANGE; + n->name = $2; + n->values = $6; + n->options = $7; + n->tablespacename = $8; + $$ = n; + } + ; + + RangeUpper: + AexprConst { $$ = list_make1($1); } + | '(' AexprConst ')' { $$ = list_make1($2); } + | MAXVALUE { $$ = NIL; } + | '(' MAXVALUE ')' { $$ = NIL; } + ; + + OptListPartitions: + '(' ListPartitions ')' { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } + ; + + ListPartitions: + ListPartition { $$ = list_make1($1); } + | ListPartitions ',' ListPartition { $$ = lappend($1, $3); } + ; + + ListPartition: + PARTITION qualified_name VALUES opt_in ListValues OptWith OptTableSpace + { + Partition *n = makeNode(Partition); + n->kind = PARTITION_BY_LIST; + n->name = $2; + n->values = $5; + n->options = $6; + n->tablespacename = $7; + $$ = n; + } + ; + + opt_in: IN_P {} + | /*EMPTY*/ {} + ; + + ListValues: + DEFAULT { $$ = NIL; } + | '(' DEFAULT ')' { $$ = NIL; } + | '(' ConstValues ')' { $$ = $2; } + ; + + ConstValues: + AexprConst { $$ = list_make1($1); } + | ConstValues ',' AexprConst { $$ = lappend($1, $3); } + ; + + InheritAsPartition: + AS PARTITION VALUES LESS THAN RangeUpper + { + AddInherit *n = makeNode(AddInherit); + n->kind = PARTITION_BY_RANGE; + n->values = $6; + $$ = n; + } + | AS PARTITION VALUES opt_in ListValues + { + AddInherit *n = makeNode(AddInherit); + n->kind = PARTITION_BY_LIST; + n->values = $5; + $$ = n; + } + | /* EMPTY */ + { + $$ = makeNode(AddInherit); + } + ; + /* * Redundancy here is needed to avoid shift/reduce conflicts, * since TEMP is not a reserved word. See also OptTempTableName. *************** opt_with_data: *** 2684,2689 **** --- 2843,2884 ---- /***************************************************************************** * * QUERY : + * CREATE PARTITION name + * + *****************************************************************************/ + + CreatePartitionStmt: + CREATE PARTITION qualified_name ON qualified_name + VALUES LESS THAN RangeUpper OptWith OptTableSpace + { + CreatePartitionStmt *n = makeNode(CreatePartitionStmt); + n->parent = $5; + n->def = makeNode(Partition); + n->def->kind = PARTITION_BY_RANGE; + n->def->name = $3; + n->def->values = $9; + n->def->options = $10; + n->def->tablespacename = $11; + $$ = (Node *)n; + } + | CREATE PARTITION qualified_name ON qualified_name + VALUES opt_in ListValues OptWith OptTableSpace + { + CreatePartitionStmt *n = makeNode(CreatePartitionStmt); + n->parent = $5; + n->def = makeNode(Partition); + n->def->kind = PARTITION_BY_LIST; + n->def->name = $3; + n->def->values = $8; + n->def->options = $9; + n->def->tablespacename = $10; + $$ = (Node *)n; + } + ; + + /***************************************************************************** + * + * QUERY : * CREATE SEQUENCE seqname * ALTER SEQUENCE seqname * *************** DropStmt: DROP drop_type IF_P EXISTS any *** 3930,3935 **** --- 4125,4131 ---- drop_type: TABLE { $$ = OBJECT_TABLE; } + | PARTITION { $$ = OBJECT_TABLE; } | SEQUENCE { $$ = OBJECT_SEQUENCE; } | VIEW { $$ = OBJECT_VIEW; } | INDEX { $$ = OBJECT_INDEX; } *************** RenameStmt: ALTER AGGREGATE func_name ag *** 5551,5556 **** --- 5747,5761 ---- n->newname = $6; $$ = (Node *)n; } + | ALTER PARTITION relation_expr RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_TABLE; + n->relation = $3; + n->subname = NULL; + n->newname = $6; + $$ = (Node *)n; + } | ALTER SEQUENCE qualified_name RENAME TO name { RenameStmt *n = makeNode(RenameStmt); *************** AlterObjectSchemaStmt: *** 5711,5716 **** --- 5916,5929 ---- n->newschema = $6; $$ = (Node *)n; } + | ALTER PARTITION relation_expr SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_TABLE; + n->relation = $3; + n->newschema = $6; + $$ = (Node *)n; + } | ALTER SEQUENCE qualified_name SET SCHEMA name { AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); *************** unreserved_keyword: *** 10628,10634 **** --- 10841,10849 ---- | LAST_P | LC_COLLATE_P | LC_CTYPE_P + | LESS | LEVEL + | LIST | LISTEN | LOAD | LOCAL *************** unreserved_keyword: *** 10734,10739 **** --- 10949,10955 ---- | TEMPLATE | TEMPORARY | TEXT_P + | THAN | TRANSACTION | TRIGGER | TRUNCATE diff -cprN head/src/backend/parser/parse_utilcmd.c work/src/backend/parser/parse_utilcmd.c *** head/src/backend/parser/parse_utilcmd.c 2009-10-13 09:53:08.000000000 +0900 --- work/src/backend/parser/parse_utilcmd.c 2009-11-05 20:30:38.516702000 +0900 *************** static void transformFKConstraints(Parse *** 114,119 **** --- 114,120 ---- CreateStmtContext *cxt, bool skipValidation, bool isAddConstraint); + static void transformPartitionBy(ParseState *pstate, CreateStmtContext *cxt, PartitionBy *defs); static void transformConstraintAttrs(ParseState *pstate, List *constraintList); static void transformColumnType(ParseState *pstate, ColumnDef *column); static void setSchemaName(char *context_schema, char **stmt_schema_name); *************** transformCreateStmt(CreateStmt *stmt, co *** 233,238 **** --- 234,257 ---- transformFKConstraints(pstate, &cxt, true, false); /* + * Postprocess partition related information. + */ + if (stmt->partitions) + { + AlterTableStmt *alterstmt = makeNode(AlterTableStmt); + AlterTableCmd *cmd = makeNode(AlterTableCmd); + + cmd->subtype = AT_PartitionBy; + cmd->def = (Node *) stmt->partitions; + + alterstmt->relation = cxt.relation; + alterstmt->relkind = OBJECT_TABLE; + alterstmt->cmds = list_make1(cmd); + + cxt.alist = lappend(cxt.alist, alterstmt); + } + + /* * Output results. */ stmt->tableElts = cxt.columns; *************** transformFKConstraints(ParseState *pstat *** 1421,1426 **** --- 1440,1512 ---- } } + /* CREATE PARTITION */ + List * + transformCreatePartition(Partition *def, RangeVar *parent) + { + CreateStmt *create; + InhRelation *like; + AlterTableStmt *alter; + AlterTableCmd *inhcmd; + AddInherit *inh; + + /* Use the same schema as the parent if not specified. */ + if (def->name->schemaname == NULL) + def->name->schemaname = parent->schemaname; + def->name->istemp = parent->istemp; + + /* CREATE TABLE partition (LIKE parent INCLUDING ALL) */ + like = makeNode(InhRelation); + like->relation = parent; + like->options = CREATE_TABLE_LIKE_ALL; + create = makeNode(CreateStmt); + create->relation = def->name; + create->tableElts = list_make1(like); + create->inhRelations = NIL; + create->constraints = NIL; + create->options = def->options; + create->oncommit = ONCOMMIT_NOOP; + create->tablespacename = def->tablespacename; + create->partitions = NULL; + + /* ALTER TABLE partition INHERIT parent AS PARTITION ... */ + inh = makeNode(AddInherit); + inh->parent = parent; + inh->kind = def->kind; + inh->values = def->values; + inhcmd = makeNode(AlterTableCmd); + inhcmd->subtype = AT_AddInherit; + inhcmd->def = (Node *) inh; + alter = makeNode(AlterTableStmt); + alter->relation = def->name; + alter->cmds = list_make1(inhcmd); + alter->relkind = OBJECT_TABLE; + + return list_make2(create, alter); + } + + /* PARTITION BY / NO PARTITION */ + static void + transformPartitionBy(ParseState *pstate, CreateStmtContext *cxt, + PartitionBy *partitions) + { + ListCell *cell; + + if (partitions == NULL) + return; + + Assert(partitions->opr != NIL); + + /* Expand partition clauses to CREATE TABLE. */ + foreach (cell, partitions->defs) + { + Partition *def = (Partition *) lfirst(cell); + + cxt->alist = list_concat(cxt->alist, + transformCreatePartition(def, cxt->relation)); + } + } + /* * transformIndexStmt - parse analysis for CREATE INDEX * *************** transformAlterTableStmt(AlterTableStmt * *** 1905,1910 **** --- 1991,2001 ---- newcmds = lappend(newcmds, cmd); break; + case AT_PartitionBy: + newcmds = lappend(newcmds, cmd); + transformPartitionBy(pstate, &cxt, (PartitionBy *) cmd->def); + break; + default: newcmds = lappend(newcmds, cmd); break; diff -cprN head/src/backend/tcop/utility.c work/src/backend/tcop/utility.c *** head/src/backend/tcop/utility.c 2009-10-26 11:26:40.000000000 +0900 --- work/src/backend/tcop/utility.c 2009-11-05 11:55:00.584915998 +0900 *************** check_xact_readonly(Node *parsetree) *** 214,219 **** --- 214,220 ---- case T_CreateUserMappingStmt: case T_AlterUserMappingStmt: case T_DropUserMappingStmt: + case T_CreatePartitionStmt: ereport(ERROR, (errcode(ERRCODE_READ_ONLY_SQL_TRANSACTION), errmsg("transaction is read-only"))); *************** ProcessUtility(Node *parsetree, *** 516,521 **** --- 517,526 ---- RemoveUserMapping((DropUserMappingStmt *) parsetree); break; + case T_CreatePartitionStmt: + CreatePartition((CreatePartitionStmt *) parsetree, queryString); + break; + case T_DropStmt: { DropStmt *stmt = (DropStmt *) parsetree; *************** CreateCommandTag(Node *parsetree) *** 1422,1427 **** --- 1427,1436 ---- tag = "DROP USER MAPPING"; break; + case T_CreatePartitionStmt: + tag = "CREATE PARTITION"; + break; + case T_DropStmt: switch (((DropStmt *) parsetree)->removeType) { *************** GetCommandLogLevel(Node *parsetree) *** 2174,2179 **** --- 2183,2189 ---- case T_CreateUserMappingStmt: case T_AlterUserMappingStmt: case T_DropUserMappingStmt: + case T_CreatePartitionStmt: lev = LOGSTMT_DDL; break; diff -cprN head/src/backend/utils/cache/lsyscache.c work/src/backend/utils/cache/lsyscache.c *** head/src/backend/utils/cache/lsyscache.c 2009-08-10 14:46:50.000000000 +0900 --- work/src/backend/utils/cache/lsyscache.c 2009-11-05 15:22:24.876794653 +0900 *************** *** 24,29 **** --- 24,30 ---- #include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" + #include "catalog/pg_partition.h" #include "catalog/pg_proc.h" #include "catalog/pg_statistic.h" #include "catalog/pg_type.h" diff -cprN head/src/backend/utils/cache/syscache.c work/src/backend/utils/cache/syscache.c *** head/src/backend/utils/cache/syscache.c 2009-10-06 04:24:45.000000000 +0900 --- work/src/backend/utils/cache/syscache.c 2009-11-05 11:55:00.586837752 +0900 *************** *** 40,45 **** --- 40,46 ---- #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_opfamily.h" + #include "catalog/pg_partition.h" #include "catalog/pg_proc.h" #include "catalog/pg_rewrite.h" #include "catalog/pg_statistic.h" *************** static const struct cachedesc cacheinfo[ *** 764,774 **** 0 }, 128 } }; ! static CatCache *SysCache[ ! lengthof(cacheinfo)]; static int SysCacheSize = lengthof(cacheinfo); static bool CacheInitialized = false; --- 765,786 ---- 0 }, 128 + }, + {PartitionRelationId, /* PARTITIONKEY */ + PartitionRelidIndexId, + Anum_pg_partition_partrelid, + 1, + { + Anum_pg_partition_partrelid, + 0, + 0, + 0 + }, + 64 } }; ! static CatCache *SysCache[lengthof(cacheinfo)]; static int SysCacheSize = lengthof(cacheinfo); static bool CacheInitialized = false; diff -cprN head/src/include/catalog/dependency.h work/src/include/catalog/dependency.h *** head/src/include/catalog/dependency.h 2009-10-08 07:14:24.000000000 +0900 --- work/src/include/catalog/dependency.h 2009-11-05 11:55:00.586837752 +0900 *************** typedef enum ObjectClass *** 147,152 **** --- 147,153 ---- OCLASS_FOREIGN_SERVER, /* pg_foreign_server */ OCLASS_USER_MAPPING, /* pg_user_mapping */ OCLASS_DEFACL, /* pg_default_acl */ + OCLASS_PARTITION, /* pg_partition */ MAX_OCLASS /* MUST BE LAST */ } ObjectClass; diff -cprN head/src/include/catalog/heap.h work/src/include/catalog/heap.h *** head/src/include/catalog/heap.h 2009-10-06 04:24:48.000000000 +0900 --- work/src/include/catalog/heap.h 2009-11-05 11:55:00.586837752 +0900 *************** extern void RemoveAttrDefault(Oid relid, *** 101,106 **** --- 101,107 ---- DropBehavior behavior, bool complain); extern void RemoveAttrDefaultById(Oid attrdefId); extern void RemoveStatistics(Oid relid, AttrNumber attnum); + extern void RemovePartitionById(Oid relid); extern Form_pg_attribute SystemAttributeDefinition(AttrNumber attno, bool relhasoids); diff -cprN head/src/include/catalog/indexing.h work/src/include/catalog/indexing.h *** head/src/include/catalog/indexing.h 2009-10-08 07:14:25.000000000 +0900 --- work/src/include/catalog/indexing.h 2009-11-05 11:55:00.586837752 +0900 *************** DECLARE_UNIQUE_INDEX(pg_default_acl_oid_ *** 275,280 **** --- 275,283 ---- DECLARE_UNIQUE_INDEX(pg_db_role_setting_databaseid_rol_index, 2965, on pg_db_role_setting using btree(setdatabase oid_ops, setrole oid_ops)); #define DbRoleSettingDatidRolidIndexId 2965 + DECLARE_UNIQUE_INDEX(pg_partition_relid_index, 2337, on pg_partition using btree(partrelid oid_ops)); + #define PartitionRelidIndexId 2337 + /* last step of initialization script: build the indexes declared above */ BUILD_INDICES diff -cprN head/src/include/catalog/pg_inherits.h work/src/include/catalog/pg_inherits.h *** head/src/include/catalog/pg_inherits.h 2009-05-12 12:11:02.000000000 +0900 --- work/src/include/catalog/pg_inherits.h 2009-11-05 11:55:00.586837752 +0900 *************** *** 21,26 **** --- 21,29 ---- #include "catalog/genbki.h" + /* See comments in pg_statistic.h. */ + #define anyarray int + /* ---------------- * pg_inherits definition. cpp turns this into * typedef struct FormData_pg_inherits *************** CATALOG(pg_inherits,2611) BKI_WITHOUT_OI *** 33,38 **** --- 36,42 ---- Oid inhrelid; Oid inhparent; int4 inhseqno; + anyarray inhvalues; /* values for partition */ } FormData_pg_inherits; /* ---------------- *************** typedef FormData_pg_inherits *Form_pg_in *** 46,55 **** * compiler constants for pg_inherits * ---------------- */ ! #define Natts_pg_inherits 3 #define Anum_pg_inherits_inhrelid 1 #define Anum_pg_inherits_inhparent 2 #define Anum_pg_inherits_inhseqno 3 /* ---------------- * pg_inherits has no initial contents --- 50,60 ---- * compiler constants for pg_inherits * ---------------- */ ! #define Natts_pg_inherits 4 #define Anum_pg_inherits_inhrelid 1 #define Anum_pg_inherits_inhparent 2 #define Anum_pg_inherits_inhseqno 3 + #define Anum_pg_inherits_inhvalues 4 /* ---------------- * pg_inherits has no initial contents diff -cprN head/src/include/catalog/pg_partition.h work/src/include/catalog/pg_partition.h *** head/src/include/catalog/pg_partition.h 1970-01-01 09:00:00.000000000 +0900 --- work/src/include/catalog/pg_partition.h 2009-11-05 19:50:02.193881328 +0900 *************** *** 0 **** --- 1,55 ---- + /*------------------------------------------------------------------------- + * + * pg_partition.h + * definition of the system "partition" relation (pg_partition) + * along with the relation's initial contents. + * + * + * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group + * + * $PostgreSQL: pgsql/src/include/catalog/pg_partition.h $ + * + * NOTES + * the genbki.sh script reads this file and generates .bki + * information from the DATA() statements. + * + *------------------------------------------------------------------------- + */ + #ifndef PG_PARTITION_H + #define PG_PARTITION_H + + #include "catalog/genbki.h" + + /* ---------------- + * pg_partition definition. cpp turns this into + * typedef struct FormData_pg_partitions + * ---------------- + */ + #define PartitionRelationId 2336 + + CATALOG(pg_partition,2336) BKI_WITHOUT_OIDS + { + Oid partrelid; /* partitioned table oid */ + Oid partopr; /* operator to compare keys */ + char partkind; /* kind of partition: RANGE or LIST */ + text partkey; /* partition key expression */ + } FormData_pg_partition; + + /* ---------------- + * Form_pg_partitions corresponds to a pointer to a tuple with + * the format of pg_partitions relation. + * ---------------- + */ + typedef FormData_pg_partition *Form_pg_partition; + + /* ---------------- + * compiler constants for pg_partitions + * ---------------- + */ + #define Natts_pg_partition 4 + #define Anum_pg_partition_partrelid 1 + #define Anum_pg_partition_partopr 2 + #define Anum_pg_partition_partkind 3 + #define Anum_pg_partition_partkey 4 + + #endif /* PG_PARTITIONS_H */ diff -cprN head/src/include/commands/tablecmds.h work/src/include/commands/tablecmds.h *** head/src/include/commands/tablecmds.h 2009-07-16 15:33:45.000000000 +0900 --- work/src/include/commands/tablecmds.h 2009-11-05 11:55:00.587788494 +0900 *************** extern void RenameRelationInternal(Oid m *** 53,58 **** --- 53,60 ---- const char *newrelname, Oid namespaceId); + extern void CreatePartition(CreatePartitionStmt *stmt, const char *queryString); + extern void find_composite_type_dependencies(Oid typeOid, const char *origTblName, const char *origTypeName); diff -cprN head/src/include/nodes/nodes.h work/src/include/nodes/nodes.h *** head/src/include/nodes/nodes.h 2009-10-26 11:26:41.000000000 +0900 --- work/src/include/nodes/nodes.h 2009-11-05 11:55:00.587788494 +0900 *************** typedef enum NodeTag *** 346,351 **** --- 346,352 ---- T_CreateUserMappingStmt, T_AlterUserMappingStmt, T_DropUserMappingStmt, + T_CreatePartitionStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) *************** typedef enum NodeTag *** 384,389 **** --- 385,393 ---- T_XmlSerialize, T_WithClause, T_CommonTableExpr, + T_Partition, + T_PartitionBy, + T_AddInherit, /* * TAGS FOR RANDOM OTHER STUFF diff -cprN head/src/include/nodes/parsenodes.h work/src/include/nodes/parsenodes.h *** head/src/include/nodes/parsenodes.h 2009-10-28 23:55:46.000000000 +0900 --- work/src/include/nodes/parsenodes.h 2009-11-05 15:10:10.350806060 +0900 *************** typedef enum AlterTableType *** 1132,1138 **** AT_EnableReplicaRule, /* ENABLE REPLICA RULE name */ AT_DisableRule, /* DISABLE RULE name */ AT_AddInherit, /* INHERIT parent */ ! AT_DropInherit /* NO INHERIT parent */ } AlterTableType; typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ --- 1132,1139 ---- AT_EnableReplicaRule, /* ENABLE REPLICA RULE name */ AT_DisableRule, /* DISABLE RULE name */ AT_AddInherit, /* INHERIT parent */ ! AT_DropInherit, /* NO INHERIT parent */ ! AT_PartitionBy /* PARTITION BY / NO PARTITION */ } AlterTableType; typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ *************** typedef struct VariableShowStmt *** 1333,1338 **** --- 1334,1381 ---- char *name; } VariableShowStmt; + /* ---------- + * Partitioning definitions + * ---------- + */ + + #define PARTITION_BY_RANGE 'R' + #define PARTITION_BY_LIST 'L' + + typedef struct Partition + { + NodeTag type; + char kind; /* PARTITION_BY_xxx */ + RangeVar *name; /* name of partition */ + List *values; /* for RANGE and LIST */ + List *options; /* options from WITH clause */ + char *tablespacename; /* table space to use, or NULL */ + } Partition; + + typedef struct PartitionBy + { + NodeTag type; + char kind; /* PARTITION_BY_xxx */ + Node *key; /* partition key expr */ + List *opr; /* partition key operator */ + List *defs; /* list of Partition */ + } PartitionBy; + + typedef struct CreatePartitionStmt + { + NodeTag type; + RangeVar *parent; /* parent of the partition */ + Partition *def; + } CreatePartitionStmt; + + typedef struct AddInherit + { + NodeTag type; + RangeVar *parent; /* parent of the partition */ + char kind; /* PARTITION_BY_xxx */ + List *values; /* for RANGE and LIST */ + } AddInherit; + /* ---------------------- * Create Table Statement * *************** typedef struct CreateStmt *** 1355,1360 **** --- 1398,1404 ---- List *options; /* options from WITH clause */ OnCommitAction oncommit; /* what do we do at COMMIT? */ char *tablespacename; /* table space to use, or NULL */ + PartitionBy *partitions; /* partitioning definition, or NULL */ } CreateStmt; /* ---------- diff -cprN head/src/include/parser/kwlist.h work/src/include/parser/kwlist.h *** head/src/include/parser/kwlist.h 2009-10-13 05:39:42.000000000 +0900 --- work/src/include/parser/kwlist.h 2009-11-05 11:55:00.588781605 +0900 *************** PG_KEYWORD("lc_ctype", LC_CTYPE_P, UNRES *** 215,223 **** --- 215,225 ---- PG_KEYWORD("leading", LEADING, RESERVED_KEYWORD) PG_KEYWORD("least", LEAST, COL_NAME_KEYWORD) PG_KEYWORD("left", LEFT, TYPE_FUNC_NAME_KEYWORD) + PG_KEYWORD("less", LESS, UNRESERVED_KEYWORD) PG_KEYWORD("level", LEVEL, UNRESERVED_KEYWORD) PG_KEYWORD("like", LIKE, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("limit", LIMIT, RESERVED_KEYWORD) + PG_KEYWORD("list", LIST, UNRESERVED_KEYWORD) PG_KEYWORD("listen", LISTEN, UNRESERVED_KEYWORD) PG_KEYWORD("load", LOAD, UNRESERVED_KEYWORD) PG_KEYWORD("local", LOCAL, UNRESERVED_KEYWORD) *************** PG_KEYWORD("temp", TEMP, UNRESERVED_KEYW *** 365,370 **** --- 367,373 ---- PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD) PG_KEYWORD("temporary", TEMPORARY, UNRESERVED_KEYWORD) PG_KEYWORD("text", TEXT_P, UNRESERVED_KEYWORD) + PG_KEYWORD("than", THAN, UNRESERVED_KEYWORD) PG_KEYWORD("then", THEN, RESERVED_KEYWORD) PG_KEYWORD("time", TIME, COL_NAME_KEYWORD) PG_KEYWORD("timestamp", TIMESTAMP, COL_NAME_KEYWORD) diff -cprN head/src/include/parser/parse_utilcmd.h work/src/include/parser/parse_utilcmd.h *** head/src/include/parser/parse_utilcmd.h 2009-01-02 02:24:00.000000000 +0900 --- work/src/include/parser/parse_utilcmd.h 2009-11-05 13:09:27.304150702 +0900 *************** extern IndexStmt *transformIndexStmt(Ind *** 24,28 **** --- 24,29 ---- extern void transformRuleStmt(RuleStmt *stmt, const char *queryString, List **actions, Node **whereClause); extern List *transformCreateSchemaStmt(CreateSchemaStmt *stmt); + extern List *transformCreatePartition(Partition *def, RangeVar *parent); #endif /* PARSE_UTILCMD_H */ diff -cprN head/src/include/utils/syscache.h work/src/include/utils/syscache.h *** head/src/include/utils/syscache.h 2009-10-06 04:24:49.000000000 +0900 --- work/src/include/utils/syscache.h 2009-11-05 11:55:00.588781605 +0900 *************** enum SysCacheIdentifier *** 83,89 **** TYPENAMENSP, TYPEOID, USERMAPPINGOID, ! USERMAPPINGUSERSERVER }; extern void InitCatalogCache(void); --- 83,90 ---- TYPENAMENSP, TYPEOID, USERMAPPINGOID, ! USERMAPPINGUSERSERVER, ! PARTITIONKEY }; extern void InitCatalogCache(void); diff -cprN head/src/test/regress/expected/partition.out work/src/test/regress/expected/partition.out *** head/src/test/regress/expected/partition.out 1970-01-01 09:00:00.000000000 +0900 --- work/src/test/regress/expected/partition.out 2009-11-05 19:43:20.188969000 +0900 *************** *** 0 **** --- 1,604 ---- + -- + -- RANGE PARTITIONING + -- + CREATE TABLE sales_range ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ) + PARTITION BY RANGE ( sales_date ) + ( + PARTITION sales_2006 VALUES LESS THAN ('2007-01-01'), + PARTITION sales_2007 VALUES LESS THAN ('2008-01-01'), + PARTITION sales_2008 VALUES LESS THAN ('2009-01-01'), + PARTITION sales_max VALUES LESS THAN (MAXVALUE) + ); + \d+ sales_range + Table "public.sales_range" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Child tables: sales_2006, + sales_2007, + sales_2008, + sales_max + Has OIDs: no + + \d+ sales_2006 + Table "public.sales_2006" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2006_sales_date_check" CHECK (sales_date < 'Mon Jan 01 00:00:00 2007'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_2007 + Table "public.sales_2007" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2007_sales_date_check" CHECK (NOT sales_date < 'Mon Jan 01 00:00:00 2007'::timestamp without time zone AND sales_date < 'Tue Jan 01 00:00:00 2008'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_2008 + Table "public.sales_2008" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2008_sales_date_check" CHECK (NOT sales_date < 'Tue Jan 01 00:00:00 2008'::timestamp without time zone AND sales_date < 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_max + Table "public.sales_max" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_max_sales_date_check" CHECK (NOT sales_date < 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_range'::regclass ORDER BY 1; + inhrelid | inhseqno | inhvalues + ------------+----------+------------------------------ + sales_2006 | 1 | {"Mon Jan 01 00:00:00 2007"} + sales_2007 | 1 | {"Tue Jan 01 00:00:00 2008"} + sales_2008 | 1 | {"Thu Jan 01 00:00:00 2009"} + sales_max | 1 | + (4 rows) + + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + partrelid | partopr | partkind | pg_get_expr + -------------+------------------------------------------------------------+----------+------------- + sales_range | <(timestamp without time zone,timestamp without time zone) | R | sales_date + (1 row) + + DROP TABLE IF EXISTS sales_range CASCADE; + NOTICE: drop cascades to 4 other objects + DETAIL: drop cascades to table sales_2006 + drop cascades to table sales_2007 + drop cascades to table sales_2008 + drop cascades to table sales_max + CREATE TABLE sales_range ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ); + ALTER TABLE sales_range PARTITION BY RANGE ( sales_date USING < ) + ( + PARTITION sales_2006 VALUES LESS THAN '2007-01-01', + PARTITION sales_2007 VALUES LESS THAN '2008-01-01', + PARTITION sales_2008 VALUES LESS THAN '2009-01-01', + PARTITION sales_max VALUES LESS THAN MAXVALUE + ); + \d+ sales_range + Table "public.sales_range" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Child tables: sales_2006, + sales_2007, + sales_2008, + sales_max + Has OIDs: no + + \d+ sales_2006 + Table "public.sales_2006" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2006_sales_date_check" CHECK (sales_date < 'Mon Jan 01 00:00:00 2007'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_2007 + Table "public.sales_2007" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2007_sales_date_check" CHECK (NOT sales_date < 'Mon Jan 01 00:00:00 2007'::timestamp without time zone AND sales_date < 'Tue Jan 01 00:00:00 2008'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_2008 + Table "public.sales_2008" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2008_sales_date_check" CHECK (NOT sales_date < 'Tue Jan 01 00:00:00 2008'::timestamp without time zone AND sales_date < 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_max + Table "public.sales_max" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_max_sales_date_check" CHECK (NOT sales_date < 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_range'::regclass ORDER BY 1; + inhrelid | inhseqno | inhvalues + ------------+----------+------------------------------ + sales_2006 | 1 | {"Mon Jan 01 00:00:00 2007"} + sales_2007 | 1 | {"Tue Jan 01 00:00:00 2008"} + sales_2008 | 1 | {"Thu Jan 01 00:00:00 2009"} + sales_max | 1 | + (4 rows) + + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + partrelid | partopr | partkind | pg_get_expr + -------------+------------------------------------------------------------+----------+------------- + sales_range | <(timestamp without time zone,timestamp without time zone) | R | sales_date + (1 row) + + DROP TABLE IF EXISTS sales_range CASCADE; + NOTICE: drop cascades to 4 other objects + DETAIL: drop cascades to table sales_2006 + drop cascades to table sales_2007 + drop cascades to table sales_2008 + drop cascades to table sales_max + CREATE TABLE sales_range ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ); + ALTER TABLE sales_range PARTITION BY RANGE ( sales_date ); + CREATE PARTITION sales_2006 ON sales_range VALUES LESS THAN '2007-01-01'; + CREATE PARTITION sales_2007 ON sales_range VALUES LESS THAN '2008-01-01'; + CREATE PARTITION sales_2008 ON sales_range VALUES LESS THAN '2009-01-01'; + CREATE PARTITION sales_max ON sales_range VALUES LESS THAN MAXVALUE; + \d+ sales_range + Table "public.sales_range" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Child tables: sales_2006, + sales_2007, + sales_2008, + sales_max + Has OIDs: no + + \d+ sales_2006 + Table "public.sales_2006" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2006_sales_date_check" CHECK (sales_date < 'Mon Jan 01 00:00:00 2007'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_2007 + Table "public.sales_2007" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2007_sales_date_check" CHECK (NOT sales_date < 'Mon Jan 01 00:00:00 2007'::timestamp without time zone AND sales_date < 'Tue Jan 01 00:00:00 2008'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_2008 + Table "public.sales_2008" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2008_sales_date_check" CHECK (NOT sales_date < 'Tue Jan 01 00:00:00 2008'::timestamp without time zone AND sales_date < 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_max + Table "public.sales_max" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_max_sales_date_check" CHECK (NOT sales_date < 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_range'::regclass ORDER BY 1; + inhrelid | inhseqno | inhvalues + ------------+----------+------------------------------ + sales_2006 | 1 | {"Mon Jan 01 00:00:00 2007"} + sales_2007 | 1 | {"Tue Jan 01 00:00:00 2008"} + sales_2008 | 1 | {"Thu Jan 01 00:00:00 2009"} + sales_max | 1 | + (4 rows) + + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + partrelid | partopr | partkind | pg_get_expr + -------------+------------------------------------------------------------+----------+------------- + sales_range | <(timestamp without time zone,timestamp without time zone) | R | sales_date + (1 row) + + DROP TABLE IF EXISTS sales_range CASCADE; + NOTICE: drop cascades to 4 other objects + DETAIL: drop cascades to table sales_2006 + drop cascades to table sales_2007 + drop cascades to table sales_2008 + drop cascades to table sales_max + CREATE TABLE sales_range ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ); + ALTER TABLE sales_range PARTITION BY RANGE ( sales_date ); + CREATE TABLE sales_2006 (LIKE sales_range INCLUDING ALL); + CREATE TABLE sales_2007 (LIKE sales_range INCLUDING ALL); + CREATE TABLE sales_2008 (LIKE sales_range INCLUDING ALL); + CREATE TABLE sales_max (LIKE sales_range INCLUDING ALL); + ALTER TABLE sales_2006 INHERIT sales_range AS PARTITION VALUES LESS THAN '2007-01-01'; + ALTER TABLE sales_2007 INHERIT sales_range AS PARTITION VALUES LESS THAN '2008-01-01'; + ALTER TABLE sales_2008 INHERIT sales_range AS PARTITION VALUES LESS THAN '2009-01-01'; + ALTER TABLE sales_max INHERIT sales_range AS PARTITION VALUES LESS THAN MAXVALUE; + \d+ sales_range + Table "public.sales_range" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Child tables: sales_2006, + sales_2007, + sales_2008, + sales_max + Has OIDs: no + + \d+ sales_2006 + Table "public.sales_2006" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2006_sales_date_check" CHECK (sales_date < 'Mon Jan 01 00:00:00 2007'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_2007 + Table "public.sales_2007" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2007_sales_date_check" CHECK (NOT sales_date < 'Mon Jan 01 00:00:00 2007'::timestamp without time zone AND sales_date < 'Tue Jan 01 00:00:00 2008'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_2008 + Table "public.sales_2008" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_2008_sales_date_check" CHECK (NOT sales_date < 'Tue Jan 01 00:00:00 2008'::timestamp without time zone AND sales_date < 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + \d+ sales_max + Table "public.sales_max" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_max_sales_date_check" CHECK (NOT sales_date < 'Thu Jan 01 00:00:00 2009'::timestamp without time zone) + Inherits: sales_range + Has OIDs: no + + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_range'::regclass ORDER BY 1; + inhrelid | inhseqno | inhvalues + ------------+----------+------------------------------ + sales_2006 | 1 | {"Mon Jan 01 00:00:00 2007"} + sales_2007 | 1 | {"Tue Jan 01 00:00:00 2008"} + sales_2008 | 1 | {"Thu Jan 01 00:00:00 2009"} + sales_max | 1 | + (4 rows) + + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + partrelid | partopr | partkind | pg_get_expr + -------------+------------------------------------------------------------+----------+------------- + sales_range | <(timestamp without time zone,timestamp without time zone) | R | sales_date + (1 row) + + DROP TABLE IF EXISTS sales_range CASCADE; + NOTICE: drop cascades to 4 other objects + DETAIL: drop cascades to table sales_2006 + drop cascades to table sales_2007 + drop cascades to table sales_2008 + drop cascades to table sales_max + -- + -- LIST PARTITIONING + -- + CREATE TABLE sales_list ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ) + PARTITION BY LIST ( sales_state ) + ( + PARTITION sales_asia VALUES ('asia'), + PARTITION sales_euro VALUES ('eu'), + PARTITION sales_us VALUES ('usa', 'canada'), + PARTITION sales_other VALUES (DEFAULT) + ); + \d+ sales_list + Table "public.sales_list" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Child tables: sales_asia, + sales_euro, + sales_other, + sales_us + Has OIDs: no + + \d+ sales_asia + Table "public.sales_asia" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_asia_sales_state_check" CHECK (sales_state::text = 'asia'::text) + Inherits: sales_list + Has OIDs: no + + \d+ sales_euro + Table "public.sales_euro" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_euro_sales_state_check" CHECK (sales_state::text = 'eu'::text) + Inherits: sales_list + Has OIDs: no + + \d+ sales_us + Table "public.sales_us" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_us_sales_state_check" CHECK (sales_state::text = ANY (ARRAY['usa'::character varying, 'canada'::character varying]::text[])) + Inherits: sales_list + Has OIDs: no + + \d+ sales_other + Table "public.sales_other" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_other_sales_state_check" CHECK (NOT (sales_state::text = ANY (ARRAY['asia'::text::character varying, 'eu'::text::character varying, 'usa'::text::character varying, 'canada'::text::character varying]::text[]))) + Inherits: sales_list + Has OIDs: no + + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_list'::regclass ORDER BY 1; + inhrelid | inhseqno | inhvalues + -------------+----------+-------------- + sales_asia | 1 | {asia} + sales_euro | 1 | {eu} + sales_us | 1 | {usa,canada} + sales_other | 1 | + (4 rows) + + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + partrelid | partopr | partkind | pg_get_expr + ------------+--------------+----------+------------- + sales_list | =(text,text) | L | sales_state + (1 row) + + DROP TABLE IF EXISTS sales_list CASCADE; + NOTICE: drop cascades to 4 other objects + DETAIL: drop cascades to table sales_asia + drop cascades to table sales_euro + drop cascades to table sales_us + drop cascades to table sales_other + CREATE TABLE sales_list ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ); + ALTER TABLE sales_list PARTITION BY LIST ( sales_state USING = ); + CREATE PARTITION sales_asia ON sales_list VALUES IN ('asia'); + CREATE PARTITION sales_euro ON sales_list VALUES IN ('eu'); + CREATE PARTITION sales_us ON sales_list VALUES IN ('usa', 'canada'); + CREATE PARTITION sales_other ON sales_list VALUES DEFAULT; + \d+ sales_list + Table "public.sales_list" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Child tables: sales_asia, + sales_euro, + sales_other, + sales_us + Has OIDs: no + + \d+ sales_asia + Table "public.sales_asia" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_asia_sales_state_check" CHECK (sales_state::text = 'asia'::text) + Inherits: sales_list + Has OIDs: no + + \d+ sales_euro + Table "public.sales_euro" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_euro_sales_state_check" CHECK (sales_state::text = 'eu'::text) + Inherits: sales_list + Has OIDs: no + + \d+ sales_us + Table "public.sales_us" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_us_sales_state_check" CHECK (sales_state::text = ANY (ARRAY['usa'::character varying, 'canada'::character varying]::text[])) + Inherits: sales_list + Has OIDs: no + + \d+ sales_other + Table "public.sales_other" + Column | Type | Modifiers | Storage | Description + ---------------+-----------------------------+-----------+----------+------------- + salesman_id | numeric(5,0) | | main | + salesman_name | character varying(30) | | extended | + sales_state | character varying(20) | | extended | + sales_date | timestamp without time zone | | plain | + Check constraints: + "sales_other_sales_state_check" CHECK (NOT (sales_state::text = ANY (ARRAY['asia'::text::character varying, 'eu'::text::character varying, 'usa'::text::character varying, 'canada'::text::character varying]::text[]))) + Inherits: sales_list + Has OIDs: no + + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_list'::regclass ORDER BY 1; + inhrelid | inhseqno | inhvalues + -------------+----------+-------------- + sales_asia | 1 | {asia} + sales_euro | 1 | {eu} + sales_us | 1 | {usa,canada} + sales_other | 1 | + (4 rows) + + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + partrelid | partopr | partkind | pg_get_expr + ------------+--------------+----------+------------- + sales_list | =(text,text) | L | sales_state + (1 row) + + DROP TABLE IF EXISTS sales_list CASCADE; + NOTICE: drop cascades to 4 other objects + DETAIL: drop cascades to table sales_asia + drop cascades to table sales_euro + drop cascades to table sales_us + drop cascades to table sales_other diff -cprN head/src/test/regress/expected/sanity_check.out work/src/test/regress/expected/sanity_check.out *** head/src/test/regress/expected/sanity_check.out 2009-10-08 07:14:26.000000000 +0900 --- work/src/test/regress/expected/sanity_check.out 2009-11-05 11:55:00.589937195 +0900 *************** SELECT relname, relhasindex *** 111,116 **** --- 111,117 ---- pg_opclass | t pg_operator | t pg_opfamily | t + pg_partition | t pg_pltemplate | t pg_proc | t pg_rewrite | t *************** SELECT relname, relhasindex *** 153,159 **** timetz_tbl | f tinterval_tbl | f varchar_tbl | f ! (142 rows) -- -- another sanity check: every system catalog that has OIDs should have --- 154,160 ---- timetz_tbl | f tinterval_tbl | f varchar_tbl | f ! (143 rows) -- -- another sanity check: every system catalog that has OIDs should have diff -cprN head/src/test/regress/parallel_schedule work/src/test/regress/parallel_schedule *** head/src/test/regress/parallel_schedule 2009-08-24 12:10:16.000000000 +0900 --- work/src/test/regress/parallel_schedule 2009-11-05 11:55:00.589937195 +0900 *************** test: copy copyselect *** 52,58 **** # ---------- # Another group of parallel tests # ---------- ! test: constraints triggers create_misc create_aggregate create_operator inherit vacuum drop_if_exists create_cast # Depends on the above test: create_index create_view --- 52,58 ---- # ---------- # Another group of parallel tests # ---------- ! test: constraints triggers create_misc create_aggregate create_operator inherit partition vacuum drop_if_exists create_cast # Depends on the above test: create_index create_view diff -cprN head/src/test/regress/serial_schedule work/src/test/regress/serial_schedule *** head/src/test/regress/serial_schedule 2009-08-24 12:10:16.000000000 +0900 --- work/src/test/regress/serial_schedule 2009-11-05 11:55:00.589937195 +0900 *************** test: create_operator *** 60,65 **** --- 60,66 ---- test: create_index test: drop_if_exists test: inherit + test: partition test: vacuum test: create_view test: sanity_check diff -cprN head/src/test/regress/sql/partition.sql work/src/test/regress/sql/partition.sql *** head/src/test/regress/sql/partition.sql 1970-01-01 09:00:00.000000000 +0900 --- work/src/test/regress/sql/partition.sql 2009-11-05 19:43:01.804620000 +0900 *************** *** 0 **** --- 1,135 ---- + -- + -- RANGE PARTITIONING + -- + CREATE TABLE sales_range ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ) + PARTITION BY RANGE ( sales_date ) + ( + PARTITION sales_2006 VALUES LESS THAN ('2007-01-01'), + PARTITION sales_2007 VALUES LESS THAN ('2008-01-01'), + PARTITION sales_2008 VALUES LESS THAN ('2009-01-01'), + PARTITION sales_max VALUES LESS THAN (MAXVALUE) + ); + \d+ sales_range + \d+ sales_2006 + \d+ sales_2007 + \d+ sales_2008 + \d+ sales_max + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_range'::regclass ORDER BY 1; + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + DROP TABLE IF EXISTS sales_range CASCADE; + + CREATE TABLE sales_range ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ); + ALTER TABLE sales_range PARTITION BY RANGE ( sales_date USING < ) + ( + PARTITION sales_2006 VALUES LESS THAN '2007-01-01', + PARTITION sales_2007 VALUES LESS THAN '2008-01-01', + PARTITION sales_2008 VALUES LESS THAN '2009-01-01', + PARTITION sales_max VALUES LESS THAN MAXVALUE + ); + \d+ sales_range + \d+ sales_2006 + \d+ sales_2007 + \d+ sales_2008 + \d+ sales_max + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_range'::regclass ORDER BY 1; + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + DROP TABLE IF EXISTS sales_range CASCADE; + + CREATE TABLE sales_range ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ); + ALTER TABLE sales_range PARTITION BY RANGE ( sales_date ); + CREATE PARTITION sales_2006 ON sales_range VALUES LESS THAN '2007-01-01'; + CREATE PARTITION sales_2007 ON sales_range VALUES LESS THAN '2008-01-01'; + CREATE PARTITION sales_2008 ON sales_range VALUES LESS THAN '2009-01-01'; + CREATE PARTITION sales_max ON sales_range VALUES LESS THAN MAXVALUE; + \d+ sales_range + \d+ sales_2006 + \d+ sales_2007 + \d+ sales_2008 + \d+ sales_max + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_range'::regclass ORDER BY 1; + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + DROP TABLE IF EXISTS sales_range CASCADE; + + CREATE TABLE sales_range ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ); + ALTER TABLE sales_range PARTITION BY RANGE ( sales_date ); + CREATE TABLE sales_2006 (LIKE sales_range INCLUDING ALL); + CREATE TABLE sales_2007 (LIKE sales_range INCLUDING ALL); + CREATE TABLE sales_2008 (LIKE sales_range INCLUDING ALL); + CREATE TABLE sales_max (LIKE sales_range INCLUDING ALL); + ALTER TABLE sales_2006 INHERIT sales_range AS PARTITION VALUES LESS THAN '2007-01-01'; + ALTER TABLE sales_2007 INHERIT sales_range AS PARTITION VALUES LESS THAN '2008-01-01'; + ALTER TABLE sales_2008 INHERIT sales_range AS PARTITION VALUES LESS THAN '2009-01-01'; + ALTER TABLE sales_max INHERIT sales_range AS PARTITION VALUES LESS THAN MAXVALUE; + \d+ sales_range + \d+ sales_2006 + \d+ sales_2007 + \d+ sales_2008 + \d+ sales_max + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_range'::regclass ORDER BY 1; + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + DROP TABLE IF EXISTS sales_range CASCADE; + + -- + -- LIST PARTITIONING + -- + CREATE TABLE sales_list ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ) + PARTITION BY LIST ( sales_state ) + ( + PARTITION sales_asia VALUES ('asia'), + PARTITION sales_euro VALUES ('eu'), + PARTITION sales_us VALUES ('usa', 'canada'), + PARTITION sales_other VALUES (DEFAULT) + ); + \d+ sales_list + \d+ sales_asia + \d+ sales_euro + \d+ sales_us + \d+ sales_other + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_list'::regclass ORDER BY 1; + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + DROP TABLE IF EXISTS sales_list CASCADE; + + CREATE TABLE sales_list ( + salesman_id numeric(5), + salesman_name varchar(30), + sales_state varchar(20), + sales_date timestamp + ); + ALTER TABLE sales_list PARTITION BY LIST ( sales_state USING = ); + CREATE PARTITION sales_asia ON sales_list VALUES IN ('asia'); + CREATE PARTITION sales_euro ON sales_list VALUES IN ('eu'); + CREATE PARTITION sales_us ON sales_list VALUES IN ('usa', 'canada'); + CREATE PARTITION sales_other ON sales_list VALUES DEFAULT; + \d+ sales_list + \d+ sales_asia + \d+ sales_euro + \d+ sales_us + \d+ sales_other + SELECT inhrelid::regclass, inhseqno, inhvalues FROM pg_inherits WHERE inhparent = 'sales_list'::regclass ORDER BY 1; + SELECT partrelid::regclass, partopr::regoperator, partkind, pg_get_expr(partkey, partrelid) FROM pg_partition; + DROP TABLE IF EXISTS sales_list CASCADE;