diff -cpr pgsql-orig/doc/src/sgml/config.sgml pgsql/doc/src/sgml/config.sgml *** pgsql-orig/doc/src/sgml/config.sgml 2006-02-06 12:47:39.000000000 +0900 --- pgsql/doc/src/sgml/config.sgml 2006-02-06 12:48:14.000000000 +0900 *************** SELECT * FROM parent WHERE key = 2400; *** 3188,3193 **** --- 3188,3209 ---- + + btree_free_percent (integer) + hash_free_percent (integer) + gist_free_percent (integer) + + btree_free_percent configuration parameter + hash_free_percent configuration parameter + gist_free_percent configuration parameter + + + + This variable specifies the default fill factors of indexes. + + + + check_function_bodies (boolean) diff -cpr pgsql-orig/doc/src/sgml/ref/alter_index.sgml pgsql/doc/src/sgml/ref/alter_index.sgml *** pgsql-orig/doc/src/sgml/ref/alter_index.sgml 2006-02-06 12:47:39.000000000 +0900 --- pgsql/doc/src/sgml/ref/alter_index.sgml 2006-02-06 12:48:14.000000000 +0900 *************** PostgreSQL documentation *** 22,27 **** --- 22,28 ---- ALTER INDEX name RENAME TO new_name ALTER INDEX name SET TABLESPACE tablespace_name + ALTER INDEX name SET PCTFREE percent *************** ALTER INDEX . + + + + diff -cpr pgsql-orig/doc/src/sgml/ref/create_table.sgml pgsql/doc/src/sgml/ref/create_table.sgml *** pgsql-orig/doc/src/sgml/ref/create_table.sgml 2006-02-06 12:47:39.000000000 +0900 --- pgsql/doc/src/sgml/ref/create_table.sgml 2006-02-06 12:48:14.000000000 +0900 *************** where col *** 36,43 **** [ CONSTRAINT constraint_name ] { NOT NULL | NULL | ! UNIQUE [ USING INDEX TABLESPACE tablespace ] | ! PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } --- 36,43 ---- [ CONSTRAINT constraint_name ] { NOT NULL | NULL | ! UNIQUE [PCTFREE percent] [ USING INDEX TABLESPACE tablespace ] | ! PRIMARY KEY [PCTFREE percent] [ USING INDEX TABLESPACE tablespace ] | CHECK (expression) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } *************** where col *** 46,53 **** and table_constraint is: [ CONSTRAINT constraint_name ] ! { UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | ! PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } --- 46,53 ---- and table_constraint is: [ CONSTRAINT constraint_name ] ! { UNIQUE ( column_name [, ... ] ) [PCTFREE percent] [ USING INDEX TABLESPACE tablespace ] | ! PRIMARY KEY ( column_name [, ... ] ) [PCTFREE percent] [ USING INDEX TABLESPACE tablespace ] | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } *************** and table *** 616,621 **** --- 616,634 ---- + PCTFREE percent + + + This clause allows selection of the fill factor on the index + associated with a UNIQUE or PRIMARY + KEY constraint will be created. + If not specified, + is used. + + + + + USING INDEX TABLESPACE tablespace diff -cpr pgsql-orig/doc/src/sgml/ref/reindex.sgml pgsql/doc/src/sgml/ref/reindex.sgml *** pgsql-orig/doc/src/sgml/ref/reindex.sgml 2006-02-06 12:47:39.000000000 +0900 --- pgsql/doc/src/sgml/ref/reindex.sgml 2006-02-06 12:48:14.000000000 +0900 *************** PostgreSQL documentation *** 20,26 **** ! REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ] --- 20,26 ---- ! REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [PCTFREE percent] [ FORCE ] *************** REINDEX { INDEX | TABLE | DATABASE | SYS *** 117,122 **** --- 117,131 ---- + PCTFREE percent + + + This form changes the index's fill factor to the specified percentage. + + + + + FORCE diff -cpr pgsql-orig/src/backend/access/gist/gist.c pgsql/src/backend/access/gist/gist.c *** pgsql-orig/src/backend/access/gist/gist.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/access/gist/gist.c 2006-02-06 12:48:14.000000000 +0900 *************** *** 25,30 **** --- 25,32 ---- const XLogRecPtr XLogRecPtrForTemp = {1, 1}; + int gist_free_percent = 10; + /* Working state for gistbuild and its callback */ typedef struct { *************** static void gistbuildCallback(Relation i *** 44,49 **** --- 46,52 ---- void *state); static void gistdoinsert(Relation r, IndexTuple itup, + Size freespace, GISTSTATE *GISTstate); static void gistfindleaf(GISTInsertState *state, GISTSTATE *giststate); *************** gistbuild(PG_FUNCTION_ARGS) *** 90,95 **** --- 93,105 ---- double reltuples; GISTBuildState buildstate; Buffer buffer; + int save_free_percent = gist_free_percent; + + /* set guc fill factor to specified value by command. */ + if (indexInfo->ii_PctFree != -1) + gist_free_percent = indexInfo->ii_PctFree; + + elog(DEBUG1, "gistbuild (gist_free_percent=%d)", gist_free_percent); /* * We expect to be called exactly once for any index relation. If that's *************** gistbuild(PG_FUNCTION_ARGS) *** 148,153 **** --- 158,167 ---- /* okay, all heap tuples are indexed */ MemoryContextDelete(buildstate.tmpCtx); + /* revert guc fill factor. */ + if (indexInfo->ii_PctFree != -1) + gist_free_percent = save_free_percent; + /* since we just counted the # of tuples, may as well update stats */ IndexCloseAndUpdateStats(heap, reltuples, index, buildstate.indtuples); *************** gistbuildCallback(Relation index, *** 204,210 **** * you're inserting single tups, but not when you're initializing the * whole index at once. */ ! gistdoinsert(index, itup, &buildstate->giststate); buildstate->indtuples += 1; MemoryContextSwitchTo(oldCtx); --- 218,225 ---- * you're inserting single tups, but not when you're initializing the * whole index at once. */ ! gistdoinsert(index, itup, BLCKSZ * gist_free_percent / 100, ! &buildstate->giststate); buildstate->indtuples += 1; MemoryContextSwitchTo(oldCtx); *************** gistinsert(PG_FUNCTION_ARGS) *** 261,267 **** itup = index_form_tuple(giststate.tupdesc, values, isnull); itup->t_tid = *ht_ctid; ! gistdoinsert(r, itup, &giststate); /* cleanup */ freeGISTstate(&giststate); --- 276,282 ---- itup = index_form_tuple(giststate.tupdesc, values, isnull); itup->t_tid = *ht_ctid; ! gistdoinsert(r, itup, 0, &giststate); /* cleanup */ freeGISTstate(&giststate); *************** gistinsert(PG_FUNCTION_ARGS) *** 278,284 **** * so it does not bother releasing palloc'd allocations. */ static void ! gistdoinsert(Relation r, IndexTuple itup, GISTSTATE *giststate) { GISTInsertState state; --- 293,299 ---- * so it does not bother releasing palloc'd allocations. */ static void ! gistdoinsert(Relation r, IndexTuple itup, Size freespace, GISTSTATE *giststate) { GISTInsertState state; *************** gistdoinsert(Relation r, IndexTuple itup *** 288,293 **** --- 303,309 ---- state.itup[0] = (IndexTuple) palloc(IndexTupleSize(itup)); memcpy(state.itup[0], itup, IndexTupleSize(itup)); state.ituplen = 1; + state.freespace = freespace; state.r = r; state.key = itup->t_tid; state.needInsertComplete = true; *************** gistplacetopage(GISTInsertState *state, *** 316,322 **** PageIndexTupleDelete(state->stack->page, state->stack->childoffnum); ! if (gistnospace(state->stack->page, state->itup, state->ituplen)) { /* no space for insertion */ IndexTuple *itvec, --- 332,342 ---- PageIndexTupleDelete(state->stack->page, state->stack->childoffnum); ! /* ! * XXX: If fill factors for leaf and node should be different, use below or so. ! * freespace = (is_leaf ? state->leaf_freespace : state->node_freespace) ! */ ! if (gistnospace(state->stack->page, state->itup, state->ituplen, state->freespace)) { /* no space for insertion */ IndexTuple *itvec, *************** gistSplit(Relation r, *** 1035,1041 **** for (i = 1; i < entryvec->n; i++) cleaneditup[i - 1] = itup[realoffset[i] - 1]; ! if (gistnospace(left, cleaneditup, lencleaneditup)) { /* no space on left to put all good tuples, so picksplit */ gistUserPicksplit(r, entryvec, &v, cleaneditup, lencleaneditup, giststate); --- 1055,1061 ---- for (i = 1; i < entryvec->n; i++) cleaneditup[i - 1] = itup[realoffset[i] - 1]; ! if (gistnospace(left, cleaneditup, lencleaneditup, 0)) { /* no space on left to put all good tuples, so picksplit */ gistUserPicksplit(r, entryvec, &v, cleaneditup, lencleaneditup, giststate); *************** gistSplit(Relation r, *** 1108,1114 **** } /* write on disk (may need another split) */ ! if (gistnospace(right, rvectup, v.spl_nright)) { nlen = v.spl_nright; newtup = gistSplit(r, rightbuf, rvectup, &nlen, dist, giststate); --- 1128,1134 ---- } /* write on disk (may need another split) */ ! if (gistnospace(right, rvectup, v.spl_nright, 0)) { nlen = v.spl_nright; newtup = gistSplit(r, rightbuf, rvectup, &nlen, dist, giststate); *************** gistSplit(Relation r, *** 1140,1146 **** ItemPointerSetBlockNumber(&(newtup[0]->t_tid), rbknum); } ! if (gistnospace(left, lvectup, v.spl_nleft)) { int llen = v.spl_nleft; IndexTuple *lntup; --- 1160,1166 ---- ItemPointerSetBlockNumber(&(newtup[0]->t_tid), rbknum); } ! if (gistnospace(left, lvectup, v.spl_nleft, 0)) { int llen = v.spl_nleft; IndexTuple *lntup; diff -cpr pgsql-orig/src/backend/access/gist/gistutil.c pgsql/src/backend/access/gist/gistutil.c *** pgsql-orig/src/backend/access/gist/gistutil.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/access/gist/gistutil.c 2006-02-06 12:48:14.000000000 +0900 *************** gistfillbuffer(Relation r, Page page, In *** 81,90 **** * Check space for itup vector on page */ bool ! gistnospace(Page page, IndexTuple *itvec, int len) { ! unsigned int size = 0; ! int i; for (i = 0; i < len; i++) size += IndexTupleSize(itvec[i]) + sizeof(ItemIdData); --- 81,90 ---- * Check space for itup vector on page */ bool ! gistnospace(Page page, IndexTuple *itvec, int len, Size freespace) { ! Size size = freespace; ! int i; for (i = 0; i < len; i++) size += IndexTupleSize(itvec[i]) + sizeof(ItemIdData); diff -cpr pgsql-orig/src/backend/access/gist/gistvacuum.c pgsql/src/backend/access/gist/gistvacuum.c *** pgsql-orig/src/backend/access/gist/gistvacuum.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/access/gist/gistvacuum.c 2006-02-06 12:48:14.000000000 +0900 *************** gistVacuumUpdate(GistVacuum *gv, BlockNu *** 149,155 **** if (curlenaddon) { /* insert updated tuples */ ! if (gistnospace(page, addon, curlenaddon)) { /* there is no space on page to insert tuples */ IndexTuple *vec; --- 149,155 ---- if (curlenaddon) { /* insert updated tuples */ ! if (gistnospace(page, addon, curlenaddon, 0 /* XXX: Should pctfree be used here? */)) { /* there is no space on page to insert tuples */ IndexTuple *vec; diff -cpr pgsql-orig/src/backend/access/gist/gistxlog.c pgsql/src/backend/access/gist/gistxlog.c *** pgsql-orig/src/backend/access/gist/gistxlog.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/access/gist/gistxlog.c 2006-02-06 12:48:14.000000000 +0900 *************** gistContinueInsert(gistIncompleteInsert *** 690,696 **** } } ! if (gistnospace(pages[numbuffer - 1], itup, lenitup)) { /* no space left on page, so we should split */ buffers[numbuffer] = XLogReadBuffer(true, index, P_NEW); --- 690,696 ---- } } ! if (gistnospace(pages[numbuffer - 1], itup, lenitup, 0)) { /* no space left on page, so we should split */ buffers[numbuffer] = XLogReadBuffer(true, index, P_NEW); diff -cpr pgsql-orig/src/backend/access/hash/hash.c pgsql/src/backend/access/hash/hash.c *** pgsql-orig/src/backend/access/hash/hash.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/access/hash/hash.c 2006-02-06 12:48:14.000000000 +0900 *************** hashbuild(PG_FUNCTION_ARGS) *** 53,58 **** --- 53,65 ---- IndexInfo *indexInfo = (IndexInfo *) PG_GETARG_POINTER(2); double reltuples; HashBuildState buildstate; + int save_free_percent = hash_free_percent; + + /* set guc fill factor to specified value by command. */ + if (indexInfo->ii_PctFree != -1) + hash_free_percent = indexInfo->ii_PctFree; + + elog(DEBUG1, "hashbuild (hash_free_percent=%d)", hash_free_percent); /* * We expect to be called exactly once for any index relation. If that's *************** hashbuild(PG_FUNCTION_ARGS) *** 72,77 **** --- 79,88 ---- reltuples = IndexBuildHeapScan(heap, index, indexInfo, hashbuildCallback, (void *) &buildstate); + /* revert guc fill factor. */ + if (indexInfo->ii_PctFree != -1) + hash_free_percent = save_free_percent; + /* since we just counted the # of tuples, may as well update stats */ IndexCloseAndUpdateStats(heap, reltuples, index, buildstate.indtuples); diff -cpr pgsql-orig/src/backend/access/hash/hashpage.c pgsql/src/backend/access/hash/hashpage.c *** pgsql-orig/src/backend/access/hash/hashpage.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/access/hash/hashpage.c 2006-02-06 12:48:14.000000000 +0900 *************** static void _hash_splitbucket(Relation r *** 42,47 **** --- 42,48 ---- uint32 maxbucket, uint32 highmask, uint32 lowmask); + int hash_free_percent = 25; /* * We use high-concurrency locking on hash indexes (see README for an overview *************** _hash_metapinit(Relation rel) *** 251,257 **** RelationGetDescr(rel)->attrs[0]->atttypmod); item_width = MAXALIGN(sizeof(IndexTupleData)) + MAXALIGN(data_width) + sizeof(ItemIdData); /* include the line pointer */ ! ffactor = (BLCKSZ * 3 / 4) / item_width; /* keep to a sane range */ if (ffactor < 10) ffactor = 10; --- 252,258 ---- RelationGetDescr(rel)->attrs[0]->atttypmod); item_width = MAXALIGN(sizeof(IndexTupleData)) + MAXALIGN(data_width) + sizeof(ItemIdData); /* include the line pointer */ ! ffactor = BLCKSZ * (100 - hash_free_percent) / 100 / item_width; /* keep to a sane range */ if (ffactor < 10) ffactor = 10; diff -cpr pgsql-orig/src/backend/access/nbtree/nbtree.c pgsql/src/backend/access/nbtree/nbtree.c *** pgsql-orig/src/backend/access/nbtree/nbtree.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/access/nbtree/nbtree.c 2006-02-06 13:25:06.000000000 +0900 *************** btbuild(PG_FUNCTION_ARGS) *** 70,75 **** --- 70,82 ---- IndexInfo *indexInfo = (IndexInfo *) PG_GETARG_POINTER(2); double reltuples; BTBuildState buildstate; + int save_free_percent = btree_free_percent; + + /* set guc fill factor to specified value by command. */ + if (indexInfo->ii_PctFree != -1) + btree_free_percent = indexInfo->ii_PctFree; + + elog(DEBUG1, "btbuild (btree_free_percent=%d)", btree_free_percent); /* * bootstrap processing does something strange, so don't use sort/build *************** btbuild(PG_FUNCTION_ARGS) *** 148,153 **** --- 155,164 ---- } #endif /* BTREE_BUILD_STATS */ + /* revert guc fill factor. */ + if (indexInfo->ii_PctFree != -1) + btree_free_percent = save_free_percent; + /* since we just counted the # of tuples, may as well update stats */ IndexCloseAndUpdateStats(heap, reltuples, index, buildstate.indtuples); diff -cpr pgsql-orig/src/backend/access/nbtree/nbtsort.c pgsql/src/backend/access/nbtree/nbtsort.c *** pgsql-orig/src/backend/access/nbtree/nbtsort.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/access/nbtree/nbtsort.c 2006-02-06 12:48:14.000000000 +0900 *************** typedef struct BTWriteState *** 120,125 **** --- 120,126 ---- static Page _bt_blnewpage(uint32 level); + static Size _bt_full_threshold(Size pagesize, bool leaf); static BTPageState *_bt_pagestate(BTWriteState *wstate, uint32 level); static void _bt_slideleft(Page page); static void _bt_sortaddtup(Page page, Size itemsize, *************** static void _bt_uppershutdown(BTWriteSta *** 130,135 **** --- 131,137 ---- static void _bt_load(BTWriteState *wstate, BTSpool *btspool, BTSpool *btspool2); + int btree_free_percent = 10; /* * Interface routines *************** _bt_blwritepage(BTWriteState *wstate, Pa *** 327,332 **** --- 329,349 ---- } /* + * The steady-state load factor for btrees is usually estimated at 70%. + * We choose to pack leaf pages to 90% and upper pages to 70% as defaults. + */ + static Size + _bt_full_threshold(Size pagesize, bool leaf) + { + int pctfree; + if (leaf) + pctfree = btree_free_percent; + else /* node */ + pctfree = Min(30, 3 * btree_free_percent); + return pagesize * pctfree / 100; + } + + /* * allocate and initialize a new BTPageState. the returned structure * is suitable for immediate use by _bt_buildadd. */ *************** _bt_pagestate(BTWriteState *wstate, uint *** 346,355 **** state->btps_lastoff = P_HIKEY; state->btps_level = level; /* set "full" threshold based on level. See notes at head of file. */ ! if (level > 0) ! state->btps_full = (PageGetPageSize(state->btps_page) * 3) / 10; ! else ! state->btps_full = PageGetPageSize(state->btps_page) / 10; /* no parent level, yet */ state->btps_next = NULL; --- 363,370 ---- state->btps_lastoff = P_HIKEY; state->btps_level = level; /* set "full" threshold based on level. See notes at head of file. */ ! state->btps_full = _bt_full_threshold( ! PageGetPageSize(state->btps_page), level == 0); /* no parent level, yet */ state->btps_next = NULL; diff -cpr pgsql-orig/src/backend/bootstrap/bootparse.y pgsql/src/backend/bootstrap/bootparse.y *** pgsql-orig/src/backend/bootstrap/bootparse.y 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/bootstrap/bootparse.y 2006-02-06 12:48:14.000000000 +0900 *************** Boot_DeclareIndexStmt: *** 251,257 **** NULL, $10, NULL, NIL, ! false, false, false, false, false, true, false); do_end(); } --- 251,257 ---- NULL, $10, NULL, NIL, ! false, false, false, -1, false, false, true, false); do_end(); } *************** Boot_DeclareUniqueIndexStmt: *** 269,275 **** NULL, $11, NULL, NIL, ! true, false, false, false, false, true, false); do_end(); } --- 269,275 ---- NULL, $11, NULL, NIL, ! true, false, false, -1, false, false, true, false); do_end(); } diff -cpr pgsql-orig/src/backend/catalog/index.c pgsql/src/backend/catalog/index.c *** pgsql-orig/src/backend/catalog/index.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/catalog/index.c 2006-02-06 12:48:14.000000000 +0900 *************** UpdateIndexRelation(Oid indexoid, *** 410,415 **** --- 410,416 ---- values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique); values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary); values[Anum_pg_index_indisclustered - 1] = BoolGetDatum(false); + values[Anum_pg_index_indpctfree - 1] = Int16GetDatum(indexInfo->ii_PctFree); values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey); values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass); values[Anum_pg_index_indexprs - 1] = exprsDatum; *************** BuildIndexInfo(Relation index) *** 902,907 **** --- 903,909 ---- /* other info */ ii->ii_Unique = indexStruct->indisunique; + ii->ii_PctFree = indexStruct->indpctfree; return ii; } *************** index_build(Relation heapRelation, *** 1343,1348 **** --- 1345,1352 ---- */ Assert(RelationIsValid(indexRelation)); Assert(PointerIsValid(indexRelation->rd_am)); + Assert(indexInfo->ii_PctFree >= -1); + Assert(indexInfo->ii_PctFree <= MAX_PCTFREE); procedure = indexRelation->rd_am->ambuild; Assert(RegProcedureIsValid(procedure)); *************** IndexGetRelation(Oid indexId) *** 1616,1622 **** * reindex_index - This routine is used to recreate a single index */ void ! reindex_index(Oid indexId) { Relation iRel, heapRelation; --- 1620,1626 ---- * reindex_index - This routine is used to recreate a single index */ void ! reindex_index(Oid indexId, int pctfree) { Relation iRel, heapRelation; *************** reindex_index(Oid indexId) *** 1663,1668 **** --- 1667,1678 ---- /* Fetch info needed for index_build */ indexInfo = BuildIndexInfo(iRel); + if (pctfree != -1) + { + /* PCTFREE is specified, so update catalog. */ + CatalogSetIndexPctfree(iRel, pctfree); + indexInfo->ii_PctFree = pctfree; + } if (inplace) { *************** reindex_index(Oid indexId) *** 1703,1709 **** * CommandCounterIncrement will occur after each index rebuild. */ bool ! reindex_relation(Oid relid, bool toast_too) { Relation rel; Oid toast_relid; --- 1713,1719 ---- * CommandCounterIncrement will occur after each index rebuild. */ bool ! reindex_relation(Oid relid, bool toast_too, int pctfree) { Relation rel; Oid toast_relid; *************** reindex_relation(Oid relid, bool toast_t *** 1761,1767 **** if (is_pg_class) RelationSetIndexList(rel, doneIndexes, InvalidOid); ! reindex_index(indexOid); CommandCounterIncrement(); --- 1771,1777 ---- if (is_pg_class) RelationSetIndexList(rel, doneIndexes, InvalidOid); ! reindex_index(indexOid, pctfree); CommandCounterIncrement(); *************** reindex_relation(Oid relid, bool toast_t *** 1784,1790 **** * still hold the lock on the master table. */ if (toast_too && OidIsValid(toast_relid)) ! result |= reindex_relation(toast_relid, false); return result; } --- 1794,1800 ---- * still hold the lock on the master table. */ if (toast_too && OidIsValid(toast_relid)) ! result |= reindex_relation(toast_relid, false, pctfree); return result; } diff -cpr pgsql-orig/src/backend/catalog/indexing.c pgsql/src/backend/catalog/indexing.c *** pgsql-orig/src/backend/catalog/indexing.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/catalog/indexing.c 2006-02-06 12:48:14.000000000 +0900 *************** *** 16,24 **** --- 16,26 ---- #include "postgres.h" #include "access/genam.h" + #include "access/heapam.h" #include "catalog/index.h" #include "catalog/indexing.h" #include "executor/executor.h" + #include "utils/syscache.h" /* *************** CatalogUpdateIndexes(Relation heapRel, H *** 149,151 **** --- 151,196 ---- CatalogIndexInsert(indstate, heapTuple); CatalogCloseIndexes(indstate); } + + void + CatalogSetIndexPctfree(Relation index, int pctfree) + { + Relation indexes; + HeapTuple tuple; + Form_pg_index indexDesc; + Oid indexOid = RelationGetRelid(index); + + /* + * Limit target to a sane range + */ + if (pctfree < -1 || pctfree > MAX_PCTFREE) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("PCTFREE %d must be between 0 and %d", + pctfree, MAX_PCTFREE))); + + indexes = heap_open(IndexRelationId, RowExclusiveLock); + + tuple = SearchSysCacheCopy(INDEXRELID, + ObjectIdGetDatum(indexOid), + 0, 0, 0); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for index %u", indexOid); + + indexDesc = (Form_pg_index) GETSTRUCT(tuple); + + /* + * XXX: Is it legal to overwrite index->rd_index->indpctfree? + * There was a bug that REINDEX just after ALTER INDEX SET PCTFREE + * didn't use the specified value. This is intended to fix it, but + * invalidating cache of index->rd_index might be better. + */ + index->rd_index->indpctfree = pctfree; + + indexDesc->indpctfree = pctfree; + simple_heap_update(indexes, &tuple->t_self, tuple); + CatalogUpdateIndexes(indexes, tuple); + + heap_freetuple(tuple); + heap_close(indexes, RowExclusiveLock); + } diff -cpr pgsql-orig/src/backend/commands/cluster.c pgsql/src/backend/commands/cluster.c *** pgsql-orig/src/backend/commands/cluster.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/commands/cluster.c 2006-02-06 12:48:14.000000000 +0900 *************** rebuild_relation(Relation OldHeap, Oid i *** 547,553 **** * all-new at this point). We do not need CommandCounterIncrement() * because reindex_relation does it. */ ! reindex_relation(tableOid, false); } /* --- 547,553 ---- * all-new at this point). We do not need CommandCounterIncrement() * because reindex_relation does it. */ ! reindex_relation(tableOid, false, /* use last pctfree */ -1); } /* diff -cpr pgsql-orig/src/backend/commands/indexcmds.c pgsql/src/backend/commands/indexcmds.c *** pgsql-orig/src/backend/commands/indexcmds.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/commands/indexcmds.c 2006-02-06 12:48:14.000000000 +0900 *************** static bool relationHasPrimaryKey(Relati *** 79,84 **** --- 79,85 ---- * 'isconstraint': index is for a PRIMARY KEY or UNIQUE constraint, * so build a pg_constraint entry for it. * 'is_alter_table': this is due to an ALTER rather than a CREATE operation. + * 'pctfree': fill factor. * 'check_rights': check for CREATE rights in the namespace. (This should * be true except when ALTER is deleting/recreating an index.) * 'skip_build': make the catalog entries but leave the index file empty; *************** DefineIndex(RangeVar *heapRelation, *** 97,102 **** --- 98,104 ---- bool unique, bool primary, bool isconstraint, + int pctfree, bool is_alter_table, bool check_rights, bool skip_build, *************** DefineIndex(RangeVar *heapRelation, *** 378,383 **** --- 380,386 ---- indexInfo->ii_Predicate = make_ands_implicit(predicate); indexInfo->ii_PredicateState = NIL; indexInfo->ii_Unique = unique; + indexInfo->ii_PctFree = pctfree; classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); ComputeIndexAttrs(indexInfo, classObjectId, attributeList, *************** RemoveIndex(RangeVar *relation, DropBeha *** 914,920 **** * Recreate a specific index. */ void ! ReindexIndex(RangeVar *indexRelation) { Oid indOid; HeapTuple tuple; --- 917,923 ---- * Recreate a specific index. */ void ! ReindexIndex(RangeVar *indexRelation, int pctfree) { Oid indOid; HeapTuple tuple; *************** ReindexIndex(RangeVar *indexRelation) *** 939,945 **** ReleaseSysCache(tuple); ! reindex_index(indOid); } /* --- 942,948 ---- ReleaseSysCache(tuple); ! reindex_index(indOid, pctfree); } /* *************** ReindexIndex(RangeVar *indexRelation) *** 947,953 **** * Recreate all indexes of a table (and of its toast table, if any) */ void ! ReindexTable(RangeVar *relation) { Oid heapOid; HeapTuple tuple; --- 950,956 ---- * Recreate all indexes of a table (and of its toast table, if any) */ void ! ReindexTable(RangeVar *relation, int pctfree) { Oid heapOid; HeapTuple tuple; *************** ReindexTable(RangeVar *relation) *** 980,986 **** ReleaseSysCache(tuple); ! if (!reindex_relation(heapOid, true)) ereport(NOTICE, (errmsg("table \"%s\" has no indexes", relation->relname))); --- 983,989 ---- ReleaseSysCache(tuple); ! if (!reindex_relation(heapOid, true, pctfree)) ereport(NOTICE, (errmsg("table \"%s\" has no indexes", relation->relname))); *************** ReindexTable(RangeVar *relation) *** 994,1000 **** * separate transaction, so we can release the lock on it right away. */ void ! ReindexDatabase(const char *databaseName, bool do_system, bool do_user) { Relation relationRelation; HeapScanDesc scan; --- 997,1003 ---- * separate transaction, so we can release the lock on it right away. */ void ! ReindexDatabase(const char *databaseName, int pctfree, bool do_system, bool do_user) { Relation relationRelation; HeapScanDesc scan; *************** ReindexDatabase(const char *databaseName *** 1099,1105 **** StartTransactionCommand(); /* functions in indexes may want a snapshot set */ ActiveSnapshot = CopySnapshot(GetTransactionSnapshot()); ! if (reindex_relation(relid, true)) ereport(NOTICE, (errmsg("table \"%s\" was reindexed", get_rel_name(relid)))); --- 1102,1108 ---- StartTransactionCommand(); /* functions in indexes may want a snapshot set */ ActiveSnapshot = CopySnapshot(GetTransactionSnapshot()); ! if (reindex_relation(relid, true, pctfree)) ereport(NOTICE, (errmsg("table \"%s\" was reindexed", get_rel_name(relid)))); diff -cpr pgsql-orig/src/backend/commands/tablecmds.c pgsql/src/backend/commands/tablecmds.c *** pgsql-orig/src/backend/commands/tablecmds.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/commands/tablecmds.c 2006-02-06 12:48:14.000000000 +0900 *************** static void ATPrepSetTableSpace(AlteredT *** 245,250 **** --- 245,252 ---- static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace); static void ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable, bool skip_system); + static void ATPrepSetPctfree(Relation rel); + static void ATExecSetPctfree(Relation rel, Node *newValue); static void copy_relation_data(Relation rel, SMgrRelation dst); static void update_ri_trigger_args(Oid relid, const char *oldname, *************** ExecuteTruncate(List *relations) *** 629,635 **** /* * Reconstruct the indexes to match, and we're done. */ ! reindex_relation(heap_relid, true); } } --- 631,637 ---- /* * Reconstruct the indexes to match, and we're done. */ ! reindex_relation(heap_relid, true, /* use last pctfree */ -1); } } *************** ATPrepCmd(List **wqueue, Relation rel, A *** 2008,2013 **** --- 2010,2019 ---- /* No command-specific prep needed */ pass = AT_PASS_MISC; break; + case AT_SetPctfree: + ATPrepSetPctfree(rel); + pass = AT_PASS_ADD_CONSTR; + break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); *************** ATExecCmd(AlteredTableInfo *tab, Relatio *** 2183,2188 **** --- 2189,2197 ---- case AT_DisableTrigUser: /* DISABLE TRIGGER USER */ ATExecEnableDisableTrigger(rel, NULL, false, true); break; + case AT_SetPctfree: /* SET PCTFREE */ + ATExecSetPctfree(rel, cmd->def); + break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); *************** ATRewriteTables(List **wqueue) *** 2297,2303 **** * which is all-new anyway). We do not need * CommandCounterIncrement() because reindex_relation does it. */ ! reindex_relation(tab->relid, false); } else { --- 2306,2312 ---- * which is all-new anyway). We do not need * CommandCounterIncrement() because reindex_relation does it. */ ! reindex_relation(tab->relid, false, /* use last pctfree */ -1); } else { *************** ATExecAddIndex(AlteredTableInfo *tab, Re *** 3697,3702 **** --- 3706,3712 ---- stmt->unique, stmt->primary, stmt->isconstraint, + stmt->pctfree, true, /* is_alter_table */ check_rights, skip_build, *************** ATExecAddConstraint(AlteredTableInfo *ta *** 3797,3802 **** --- 3807,3847 ---- } /* + * ALTER IONDEX SET PCTFREE + */ + static void + ATPrepSetPctfree(Relation rel) + { + if (rel->rd_rel->relkind != RELKIND_INDEX) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a index", + RelationGetRelationName(rel)))); + + /* Permissions checks */ + if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, + RelationGetRelationName(rel)); + } + + static void + ATExecSetPctfree(Relation rel, Node *newValue) + { + int pctfree; + + Assert(rel->rd_rel->relkind == RELKIND_INDEX); + Assert(IsA(newValue, Integer)); + + pctfree = intVal(newValue); + CatalogSetIndexPctfree(rel, pctfree); + CommandCounterIncrement(); + + /* Only catalog was changed. Actual change delays until next reindex. */ + elog(NOTICE, "PCTFREE for %s has changed to %d. Do REINDEX to reorganize.", + RelationGetRelationName(rel), pctfree); + } + + /* * Add a foreign-key constraint to a single table * * Subroutine for ATExecAddConstraint. Must already hold exclusive *************** AlterTableCreateToastTable(Oid relOid, b *** 5991,5996 **** --- 6036,6042 ---- indexInfo->ii_Predicate = NIL; indexInfo->ii_PredicateState = NIL; indexInfo->ii_Unique = true; + indexInfo->ii_PctFree = -1; classObjectId[0] = OID_BTREE_OPS_OID; classObjectId[1] = INT4_BTREE_OPS_OID; diff -cpr pgsql-orig/src/backend/nodes/copyfuncs.c pgsql/src/backend/nodes/copyfuncs.c *** pgsql-orig/src/backend/nodes/copyfuncs.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/nodes/copyfuncs.c 2006-02-06 12:48:14.000000000 +0900 *************** _copyConstraint(Constraint *from) *** 1622,1627 **** --- 1622,1628 ---- COPY_NODE_FIELD(raw_expr); COPY_STRING_FIELD(cooked_expr); COPY_NODE_FIELD(keys); + COPY_SCALAR_FIELD(pctfree); COPY_STRING_FIELD(indexspace); return newnode; *************** _copyIndexStmt(IndexStmt *from) *** 2002,2007 **** --- 2003,2009 ---- COPY_SCALAR_FIELD(unique); COPY_SCALAR_FIELD(primary); COPY_SCALAR_FIELD(isconstraint); + COPY_SCALAR_FIELD(pctfree); return newnode; } *************** _copyReindexStmt(ReindexStmt *from) *** 2554,2559 **** --- 2556,2562 ---- COPY_SCALAR_FIELD(kind); COPY_NODE_FIELD(relation); COPY_STRING_FIELD(name); + COPY_SCALAR_FIELD(pctfree); COPY_SCALAR_FIELD(do_system); COPY_SCALAR_FIELD(do_user); diff -cpr pgsql-orig/src/backend/nodes/equalfuncs.c pgsql/src/backend/nodes/equalfuncs.c *** pgsql-orig/src/backend/nodes/equalfuncs.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/nodes/equalfuncs.c 2006-02-06 12:48:14.000000000 +0900 *************** _equalIndexStmt(IndexStmt *a, IndexStmt *** 965,970 **** --- 965,971 ---- COMPARE_SCALAR_FIELD(unique); COMPARE_SCALAR_FIELD(primary); COMPARE_SCALAR_FIELD(isconstraint); + COMPARE_SCALAR_FIELD(pctfree); return true; } *************** _equalReindexStmt(ReindexStmt *a, Reinde *** 1428,1433 **** --- 1429,1435 ---- COMPARE_SCALAR_FIELD(kind); COMPARE_NODE_FIELD(relation); COMPARE_STRING_FIELD(name); + COMPARE_SCALAR_FIELD(pctfree); COMPARE_SCALAR_FIELD(do_system); COMPARE_SCALAR_FIELD(do_user); *************** _equalConstraint(Constraint *a, Constrai *** 1686,1691 **** --- 1688,1694 ---- COMPARE_NODE_FIELD(raw_expr); COMPARE_STRING_FIELD(cooked_expr); COMPARE_NODE_FIELD(keys); + COMPARE_SCALAR_FIELD(pctfree); COMPARE_STRING_FIELD(indexspace); return true; diff -cpr pgsql-orig/src/backend/nodes/outfuncs.c pgsql/src/backend/nodes/outfuncs.c *** pgsql-orig/src/backend/nodes/outfuncs.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/nodes/outfuncs.c 2006-02-06 12:48:14.000000000 +0900 *************** _outIndexStmt(StringInfo str, IndexStmt *** 1345,1350 **** --- 1345,1351 ---- WRITE_BOOL_FIELD(unique); WRITE_BOOL_FIELD(primary); WRITE_BOOL_FIELD(isconstraint); + WRITE_INT_FIELD(pctfree); } static void *************** _outConstraint(StringInfo str, Constrain *** 1743,1754 **** --- 1744,1757 ---- case CONSTR_PRIMARY: appendStringInfo(str, "PRIMARY_KEY"); WRITE_NODE_FIELD(keys); + WRITE_INT_FIELD(pctfree); WRITE_STRING_FIELD(indexspace); break; case CONSTR_UNIQUE: appendStringInfo(str, "UNIQUE"); WRITE_NODE_FIELD(keys); + WRITE_INT_FIELD(pctfree); WRITE_STRING_FIELD(indexspace); break; diff -cpr pgsql-orig/src/backend/parser/analyze.c pgsql/src/backend/parser/analyze.c *** pgsql-orig/src/backend/parser/analyze.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/parser/analyze.c 2006-02-06 12:48:14.000000000 +0900 *************** transformIndexConstraints(ParseState *ps *** 1219,1224 **** --- 1219,1225 ---- index->relation = cxt->relation; index->accessMethod = DEFAULT_INDEX_TYPE; + index->pctfree = constraint->pctfree; index->tableSpace = constraint->indexspace; index->indexParams = NIL; index->whereClause = NULL; diff -cpr pgsql-orig/src/backend/parser/gram.y pgsql/src/backend/parser/gram.y *** pgsql-orig/src/backend/parser/gram.y 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/parser/gram.y 2006-02-06 12:48:14.000000000 +0900 *************** static void doNegateFloat(Value *v); *** 161,168 **** %type alter_column_default opclass_item alter_using %type add_drop ! %type alter_table_cmd alter_rel_cmd ! %type alter_table_cmds alter_rel_cmds %type opt_drop_behavior --- 161,168 ---- %type alter_column_default opclass_item alter_using %type add_drop ! %type alter_table_cmd alter_index_cmd alter_rel_cmd ! %type alter_table_cmds alter_index_cmds %type opt_drop_behavior *************** static void doNegateFloat(Value *v); *** 250,256 **** %type extract_list overlay_list position_list %type substr_list trim_list ! %type opt_interval %type overlay_placing substr_from substr_for %type opt_instead opt_analyze --- 250,256 ---- %type extract_list overlay_list position_list %type substr_list trim_list ! %type opt_interval opt_pctfree %type overlay_placing substr_from substr_for %type opt_instead opt_analyze *************** static void doNegateFloat(Value *v); *** 386,392 **** OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER ! PARTIAL PASSWORD PLACING POSITION PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE --- 386,392 ---- OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER ! PARTIAL PASSWORD PLACING PCTFREE POSITION PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE *************** AlterTableStmt: *** 1313,1319 **** n->relkind = OBJECT_TABLE; $$ = (Node *)n; } ! | ALTER INDEX relation_expr alter_rel_cmds { AlterTableStmt *n = makeNode(AlterTableStmt); n->relation = $3; --- 1313,1319 ---- n->relkind = OBJECT_TABLE; $$ = (Node *)n; } ! | ALTER INDEX relation_expr alter_index_cmds { AlterTableStmt *n = makeNode(AlterTableStmt); n->relation = $3; *************** alter_table_cmds: *** 1328,1333 **** --- 1328,1338 ---- | alter_table_cmds ',' alter_table_cmd { $$ = lappend($1, $3); } ; + alter_index_cmds: + alter_index_cmd { $$ = list_make1($1); } + | alter_index_cmds ',' alter_index_cmd { $$ = lappend($1, $3); } + ; + /* Subcommands that are for ALTER TABLE only */ alter_table_cmd: /* ALTER TABLE ADD [COLUMN] */ *************** alter_table_cmd: *** 1500,1508 **** } ; ! alter_rel_cmds: ! alter_rel_cmd { $$ = list_make1($1); } ! | alter_rel_cmds ',' alter_rel_cmd { $$ = lappend($1, $3); } ; /* Subcommands that are for ALTER TABLE or ALTER INDEX */ --- 1505,1525 ---- } ; ! /* Subcommands that are for ALTER INDEX only */ ! alter_index_cmd: ! /* ALTER INDEX SET PCTFREE */ ! /* XXX: Need revert command? (ex. SET PCTFREE DEFAULT) */ ! SET PCTFREE IntegerOnly ! { ! AlterTableCmd *n = makeNode(AlterTableCmd); ! n->subtype = AT_SetPctfree; ! n->def = (Node *) $3; ! $$ = (Node *)n; ! } ! | alter_rel_cmd ! { ! $$ = $1; ! } ; /* Subcommands that are for ALTER TABLE or ALTER INDEX */ *************** ColConstraintElem: *** 1844,1849 **** --- 1861,1867 ---- n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = NULL; + n->pctfree = -1; n->indexspace = NULL; $$ = (Node *)n; } *************** ColConstraintElem: *** 1855,1864 **** n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = NULL; n->indexspace = NULL; $$ = (Node *)n; } ! | UNIQUE OptConsTableSpace { Constraint *n = makeNode(Constraint); n->contype = CONSTR_UNIQUE; --- 1873,1883 ---- n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = NULL; + n->pctfree = -1; n->indexspace = NULL; $$ = (Node *)n; } ! | UNIQUE opt_pctfree OptConsTableSpace { Constraint *n = makeNode(Constraint); n->contype = CONSTR_UNIQUE; *************** ColConstraintElem: *** 1866,1875 **** n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = NULL; ! n->indexspace = $2; $$ = (Node *)n; } ! | PRIMARY KEY OptConsTableSpace { Constraint *n = makeNode(Constraint); n->contype = CONSTR_PRIMARY; --- 1885,1895 ---- n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = NULL; ! n->pctfree = $2; ! n->indexspace = $3; $$ = (Node *)n; } ! | PRIMARY KEY opt_pctfree OptConsTableSpace { Constraint *n = makeNode(Constraint); n->contype = CONSTR_PRIMARY; *************** ColConstraintElem: *** 1877,1883 **** n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = NULL; ! n->indexspace = $3; $$ = (Node *)n; } | CHECK '(' a_expr ')' --- 1897,1904 ---- n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = NULL; ! n->pctfree = $3; ! n->indexspace = $4; $$ = (Node *)n; } | CHECK '(' a_expr ')' *************** ColConstraintElem: *** 1888,1893 **** --- 1909,1915 ---- n->raw_expr = $3; n->cooked_expr = NULL; n->keys = NULL; + n->pctfree = -1; n->indexspace = NULL; $$ = (Node *)n; } *************** ColConstraintElem: *** 1907,1912 **** --- 1929,1935 ---- } n->cooked_expr = NULL; n->keys = NULL; + n->pctfree = -1; n->indexspace = NULL; $$ = (Node *)n; } *************** ConstraintElem: *** 2028,2037 **** n->name = NULL; n->raw_expr = $3; n->cooked_expr = NULL; n->indexspace = NULL; $$ = (Node *)n; } ! | UNIQUE '(' columnList ')' OptConsTableSpace { Constraint *n = makeNode(Constraint); n->contype = CONSTR_UNIQUE; --- 2051,2061 ---- n->name = NULL; n->raw_expr = $3; n->cooked_expr = NULL; + n->pctfree = -1; n->indexspace = NULL; $$ = (Node *)n; } ! | UNIQUE '(' columnList ')' opt_pctfree OptConsTableSpace { Constraint *n = makeNode(Constraint); n->contype = CONSTR_UNIQUE; *************** ConstraintElem: *** 2039,2048 **** n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = $3; ! n->indexspace = $5; $$ = (Node *)n; } ! | PRIMARY KEY '(' columnList ')' OptConsTableSpace { Constraint *n = makeNode(Constraint); n->contype = CONSTR_PRIMARY; --- 2063,2073 ---- n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = $3; ! n->pctfree = $5; ! n->indexspace = $6; $$ = (Node *)n; } ! | PRIMARY KEY '(' columnList ')' opt_pctfree OptConsTableSpace { Constraint *n = makeNode(Constraint); n->contype = CONSTR_PRIMARY; *************** ConstraintElem: *** 2050,2056 **** n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = $4; ! n->indexspace = $6; $$ = (Node *)n; } | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name --- 2075,2082 ---- n->raw_expr = NULL; n->cooked_expr = NULL; n->keys = $4; ! n->pctfree = $6; ! n->indexspace = $7; $$ = (Node *)n; } | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name *************** opt_granted_by: GRANTED BY RoleId { *** 3505,3511 **** *****************************************************************************/ IndexStmt: CREATE index_opt_unique INDEX index_name ON qualified_name ! access_method_clause '(' index_params ')' OptTableSpace where_clause { IndexStmt *n = makeNode(IndexStmt); n->unique = $2; --- 3531,3537 ---- *****************************************************************************/ IndexStmt: CREATE index_opt_unique INDEX index_name ON qualified_name ! access_method_clause '(' index_params ')' opt_pctfree OptTableSpace where_clause { IndexStmt *n = makeNode(IndexStmt); n->unique = $2; *************** IndexStmt: CREATE index_opt_unique INDEX *** 3513,3520 **** n->relation = $6; n->accessMethod = $7; n->indexParams = $9; ! n->tableSpace = $11; ! n->whereClause = $12; $$ = (Node *)n; } ; --- 3539,3547 ---- n->relation = $6; n->accessMethod = $7; n->indexParams = $9; ! n->pctfree = $11; ! n->tableSpace = $12; ! n->whereClause = $13; $$ = (Node *)n; } ; *************** DropCastStmt: DROP CAST '(' Typename AS *** 3956,3985 **** *****************************************************************************/ ReindexStmt: ! REINDEX reindex_type qualified_name opt_force { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $2; n->relation = $3; n->name = NULL; $$ = (Node *)n; } ! | REINDEX SYSTEM_P name opt_force { ReindexStmt *n = makeNode(ReindexStmt); n->kind = OBJECT_DATABASE; n->name = $3; n->relation = NULL; n->do_system = true; n->do_user = false; $$ = (Node *)n; } ! | REINDEX DATABASE name opt_force { ReindexStmt *n = makeNode(ReindexStmt); n->kind = OBJECT_DATABASE; n->name = $3; n->relation = NULL; n->do_system = true; n->do_user = true; $$ = (Node *)n; --- 3983,4015 ---- *****************************************************************************/ ReindexStmt: ! REINDEX reindex_type qualified_name opt_pctfree opt_force { ReindexStmt *n = makeNode(ReindexStmt); n->kind = $2; n->relation = $3; + n->pctfree = $4; n->name = NULL; $$ = (Node *)n; } ! | REINDEX SYSTEM_P name opt_pctfree opt_force { ReindexStmt *n = makeNode(ReindexStmt); n->kind = OBJECT_DATABASE; n->name = $3; + n->pctfree = $4; n->relation = NULL; n->do_system = true; n->do_user = false; $$ = (Node *)n; } ! | REINDEX DATABASE name opt_pctfree opt_force { ReindexStmt *n = makeNode(ReindexStmt); n->kind = OBJECT_DATABASE; n->name = $3; n->relation = NULL; + n->pctfree = $4; n->do_system = true; n->do_user = true; $$ = (Node *)n; *************** reindex_type: *** 3991,3996 **** --- 4021,4043 ---- | TABLE { $$ = OBJECT_TABLE; } ; + opt_pctfree: + PCTFREE Iconst + { + if ($2 < 0 || $2 > MAX_PCTFREE) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("PCTFREE %d must be between 0 and %d", + $2, MAX_PCTFREE))); + $$ = $2; + } + | /*EMPTY*/ + { + /* Insert "-1" meaning "use default" */ + $$ = -1; + } + ; + opt_force: FORCE { $$ = TRUE; } | /* EMPTY */ { $$ = FALSE; } ; diff -cpr pgsql-orig/src/backend/parser/keywords.c pgsql/src/backend/parser/keywords.c *** pgsql-orig/src/backend/parser/keywords.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/parser/keywords.c 2006-02-06 12:48:14.000000000 +0900 *************** static const ScanKeyword ScanKeywords[] *** 256,261 **** --- 256,262 ---- {"owner", OWNER}, {"partial", PARTIAL}, {"password", PASSWORD}, + {"pctfree", PCTFREE}, {"placing", PLACING}, {"position", POSITION}, {"precision", PRECISION}, diff -cpr pgsql-orig/src/backend/tcop/utility.c pgsql/src/backend/tcop/utility.c *** pgsql-orig/src/backend/tcop/utility.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/tcop/utility.c 2006-02-06 12:48:14.000000000 +0900 *************** ProcessUtility(Node *parsetree, *** 794,799 **** --- 794,800 ---- stmt->unique, stmt->primary, stmt->isconstraint, + stmt->pctfree, false, /* is_alter_table */ true, /* check_rights */ false, /* skip_build */ *************** ProcessUtility(Node *parsetree, *** 1052,1064 **** switch (stmt->kind) { case OBJECT_INDEX: ! ReindexIndex(stmt->relation); break; case OBJECT_TABLE: ! ReindexTable(stmt->relation); break; case OBJECT_DATABASE: ! ReindexDatabase(stmt->name, stmt->do_system, stmt->do_user); break; default: --- 1053,1065 ---- switch (stmt->kind) { case OBJECT_INDEX: ! ReindexIndex(stmt->relation, stmt->pctfree); break; case OBJECT_TABLE: ! ReindexTable(stmt->relation, stmt->pctfree); break; case OBJECT_DATABASE: ! ReindexDatabase(stmt->name, stmt->pctfree, stmt->do_system, stmt->do_user); break; default: diff -cpr pgsql-orig/src/backend/utils/misc/guc.c pgsql/src/backend/utils/misc/guc.c *** pgsql-orig/src/backend/utils/misc/guc.c 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/utils/misc/guc.c 2006-02-06 12:48:14.000000000 +0900 *************** *** 29,34 **** --- 29,35 ---- #include "utils/guc_tables.h" #include "access/twophase.h" + #include "catalog/index.h" #include "catalog/namespace.h" #include "catalog/pg_type.h" #include "commands/async.h" *************** static struct config_int ConfigureNamesI *** 1570,1575 **** --- 1571,1603 ---- 0, 0, INT_MAX, assign_tcp_keepalives_count, show_tcp_keepalives_count }, + { + {"btree_free_percent", PGC_SUSET, RESOURCES, + gettext_noop("Reserved free space for btree pages during index creation."), + NULL + }, + &btree_free_percent, + 10, 0, MAX_PCTFREE, NULL, NULL + }, + + { + {"hash_free_percent", PGC_SUSET, RESOURCES, + gettext_noop("Reserved free space for hash pages during index creation."), + NULL + }, + &hash_free_percent, + 25, 0, MAX_PCTFREE, NULL, NULL + }, + + { + {"gist_free_percent", PGC_SUSET, RESOURCES, + gettext_noop("Reserved free space for gist pages during index creation."), + NULL + }, + &gist_free_percent, + 10, 0, MAX_PCTFREE, NULL, NULL + }, + /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL diff -cpr pgsql-orig/src/backend/utils/misc/postgresql.conf.sample pgsql/src/backend/utils/misc/postgresql.conf.sample *** pgsql-orig/src/backend/utils/misc/postgresql.conf.sample 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/backend/utils/misc/postgresql.conf.sample 2006-02-06 12:48:14.000000000 +0900 *************** *** 371,376 **** --- 371,379 ---- #default_transaction_isolation = 'read committed' #default_transaction_read_only = off #statement_timeout = 0 # 0 is disabled, in milliseconds + #btree_free_percent = 10 # 0-50% of free space + #hash_free_percent = 25 # 0-50% of free space + #gist_free_percent = 10 # 0-50% of free space # - Locale and Formatting - diff -cpr pgsql-orig/src/bin/psql/tab-complete.c pgsql/src/bin/psql/tab-complete.c *** pgsql-orig/src/bin/psql/tab-complete.c 2006-02-06 12:47:39.000000000 +0900 --- pgsql/src/bin/psql/tab-complete.c 2006-02-06 12:48:14.000000000 +0900 *************** psql_completion(char *text, int start, i *** 632,638 **** pg_strcasecmp(prev2_wd, "INDEX") == 0) { static const char *const list_ALTERINDEX[] = ! {"SET TABLESPACE", "OWNER TO", "RENAME TO", NULL}; COMPLETE_WITH_LIST(list_ALTERINDEX); } --- 632,638 ---- pg_strcasecmp(prev2_wd, "INDEX") == 0) { static const char *const list_ALTERINDEX[] = ! {"SET TABLESPACE", "SET PCTFREE", "OWNER TO", "RENAME TO", NULL}; COMPLETE_WITH_LIST(list_ALTERINDEX); } diff -cpr pgsql-orig/src/include/access/gist_private.h pgsql/src/include/access/gist_private.h *** pgsql-orig/src/include/access/gist_private.h 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/include/access/gist_private.h 2006-02-06 12:48:14.000000000 +0900 *************** typedef struct *** 181,186 **** --- 181,187 ---- Relation r; IndexTuple *itup; /* in/out, points to compressed entry */ int ituplen; /* length of itup */ + Size freespace; /* free space to be left */ GISTInsertStack *stack; bool needInsertComplete; *************** extern void gistcheckpage(Relation rel, *** 267,273 **** extern Buffer gistNewBuffer(Relation r); extern OffsetNumber gistfillbuffer(Relation r, Page page, IndexTuple *itup, int len, OffsetNumber off); ! extern bool gistnospace(Page page, IndexTuple *itvec, int len); extern IndexTuple *gistextractbuffer(Buffer buffer, int *len /* out */ ); extern IndexTuple *gistjoinvector( IndexTuple *itvec, int *len, --- 268,274 ---- extern Buffer gistNewBuffer(Relation r); extern OffsetNumber gistfillbuffer(Relation r, Page page, IndexTuple *itup, int len, OffsetNumber off); ! extern bool gistnospace(Page page, IndexTuple *itvec, int len, Size freespace); extern IndexTuple *gistextractbuffer(Buffer buffer, int *len /* out */ ); extern IndexTuple *gistjoinvector( IndexTuple *itvec, int *len, diff -cpr pgsql-orig/src/include/catalog/index.h pgsql/src/include/catalog/index.h *** pgsql-orig/src/include/catalog/index.h 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/include/catalog/index.h 2006-02-06 12:48:14.000000000 +0900 *************** *** 21,26 **** --- 21,34 ---- #define DEFAULT_INDEX_TYPE "btree" + /* + * Knobs for fill factor control during index building. + */ + #define MAX_PCTFREE 50 + extern int btree_free_percent; + extern int hash_free_percent; + extern int gist_free_percent; + /* Typedef for callback function for IndexBuildHeapScan */ typedef void (*IndexBuildCallback) (Relation index, HeapTuple htup, *************** extern double IndexBuildHeapScan(Relatio *** 69,75 **** IndexBuildCallback callback, void *callback_state); ! extern void reindex_index(Oid indexId); ! extern bool reindex_relation(Oid relid, bool toast_too); #endif /* INDEX_H */ --- 77,83 ---- IndexBuildCallback callback, void *callback_state); ! extern void reindex_index(Oid indexId, int pctfree); ! extern bool reindex_relation(Oid relid, bool toast_too, int pctfree); #endif /* INDEX_H */ diff -cpr pgsql-orig/src/include/catalog/indexing.h pgsql/src/include/catalog/indexing.h *** pgsql-orig/src/include/catalog/indexing.h 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/include/catalog/indexing.h 2006-02-06 12:48:14.000000000 +0900 *************** extern void CatalogCloseIndexes(CatalogI *** 33,38 **** --- 33,39 ---- extern void CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple); extern void CatalogUpdateIndexes(Relation heapRel, HeapTuple heapTuple); + extern void CatalogSetIndexPctfree(Relation index, int pctfree); /* diff -cpr pgsql-orig/src/include/catalog/pg_attribute.h pgsql/src/include/catalog/pg_attribute.h *** pgsql-orig/src/include/catalog/pg_attribute.h 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/include/catalog/pg_attribute.h 2006-02-06 12:48:14.000000000 +0900 *************** DATA(insert ( 1259 tableoid 26 0 4 - *** 454,462 **** { 0, {"indisunique"}, 16, -1, 1, 4, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \ { 0, {"indisprimary"}, 16, -1, 1, 5, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \ { 0, {"indisclustered"}, 16, -1, 1, 6, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \ ! { 0, {"indkey"}, 22, -1, -1, 7, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ ! { 0, {"indclass"}, 30, -1, -1, 8, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ ! { 0, {"indexprs"}, 25, -1, -1, 9, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \ ! { 0, {"indpred"}, 25, -1, -1, 10, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 } #endif /* PG_ATTRIBUTE_H */ --- 454,463 ---- { 0, {"indisunique"}, 16, -1, 1, 4, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \ { 0, {"indisprimary"}, 16, -1, 1, 5, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \ { 0, {"indisclustered"}, 16, -1, 1, 6, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \ ! { 0, {"indpctfree"}, 21, -1, 2, 7, 0, -1, -1, true, 'p', 's', true, false, false, true, 0 }, \ ! { 0, {"indkey"}, 22, -1, -1, 8, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ ! { 0, {"indclass"}, 30, -1, -1, 9, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ ! { 0, {"indexprs"}, 25, -1, -1, 10, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \ ! { 0, {"indpred"}, 25, -1, -1, 11, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 } #endif /* PG_ATTRIBUTE_H */ diff -cpr pgsql-orig/src/include/catalog/pg_index.h pgsql/src/include/catalog/pg_index.h *** pgsql-orig/src/include/catalog/pg_index.h 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/include/catalog/pg_index.h 2006-02-06 12:48:14.000000000 +0900 *************** CATALOG(pg_index,2610) BKI_WITHOUT_OIDS *** 41,46 **** --- 41,47 ---- bool indisunique; /* is this a unique index? */ bool indisprimary; /* is this index for primary key? */ bool indisclustered; /* is this the index last clustered by? */ + int2 indpctfree; /* fill factor */ /* VARIABLE LENGTH FIELDS: */ int2vector indkey; /* column numbers of indexed cols, or 0 */ *************** typedef FormData_pg_index *Form_pg_index *** 63,78 **** * compiler constants for pg_index * ---------------- */ ! #define Natts_pg_index 10 #define Anum_pg_index_indexrelid 1 #define Anum_pg_index_indrelid 2 #define Anum_pg_index_indnatts 3 #define Anum_pg_index_indisunique 4 #define Anum_pg_index_indisprimary 5 #define Anum_pg_index_indisclustered 6 ! #define Anum_pg_index_indkey 7 ! #define Anum_pg_index_indclass 8 ! #define Anum_pg_index_indexprs 9 ! #define Anum_pg_index_indpred 10 #endif /* PG_INDEX_H */ --- 64,80 ---- * compiler constants for pg_index * ---------------- */ ! #define Natts_pg_index 11 #define Anum_pg_index_indexrelid 1 #define Anum_pg_index_indrelid 2 #define Anum_pg_index_indnatts 3 #define Anum_pg_index_indisunique 4 #define Anum_pg_index_indisprimary 5 #define Anum_pg_index_indisclustered 6 ! #define Anum_pg_index_indpctfree 7 ! #define Anum_pg_index_indkey 8 ! #define Anum_pg_index_indclass 9 ! #define Anum_pg_index_indexprs 10 ! #define Anum_pg_index_indpred 11 #endif /* PG_INDEX_H */ diff -cpr pgsql-orig/src/include/commands/defrem.h pgsql/src/include/commands/defrem.h *** pgsql-orig/src/include/commands/defrem.h 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/include/commands/defrem.h 2006-02-06 12:48:14.000000000 +0900 *************** extern void DefineIndex(RangeVar *heapRe *** 29,43 **** bool unique, bool primary, bool isconstraint, bool is_alter_table, bool check_rights, bool skip_build, bool quiet); extern void RemoveIndex(RangeVar *relation, DropBehavior behavior); ! extern void ReindexIndex(RangeVar *indexRelation); ! extern void ReindexTable(RangeVar *relation); extern void ReindexDatabase(const char *databaseName, ! bool do_system, bool do_user); extern char *makeObjectName(const char *name1, const char *name2, const char *label); extern char *ChooseRelationName(const char *name1, const char *name2, --- 29,44 ---- bool unique, bool primary, bool isconstraint, + int pctfree, bool is_alter_table, bool check_rights, bool skip_build, bool quiet); extern void RemoveIndex(RangeVar *relation, DropBehavior behavior); ! extern void ReindexIndex(RangeVar *indexRelation, int pctfree); ! extern void ReindexTable(RangeVar *relation, int pctfree); extern void ReindexDatabase(const char *databaseName, ! int pctfree, bool do_system, bool do_user); extern char *makeObjectName(const char *name1, const char *name2, const char *label); extern char *ChooseRelationName(const char *name1, const char *name2, diff -cpr pgsql-orig/src/include/nodes/execnodes.h pgsql/src/include/nodes/execnodes.h *** pgsql-orig/src/include/nodes/execnodes.h 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/include/nodes/execnodes.h 2006-02-06 12:48:14.000000000 +0900 *************** *** 40,45 **** --- 40,46 ---- * Predicate partial-index predicate, or NIL if none * PredicateState exec state for predicate, or NIL if none * Unique is it a unique index? + * PctFree fill factor * ---------------- */ typedef struct IndexInfo *************** typedef struct IndexInfo *** 52,57 **** --- 53,59 ---- List *ii_Predicate; /* list of Expr */ List *ii_PredicateState; /* list of ExprState */ bool ii_Unique; + int ii_PctFree; } IndexInfo; /* ---------------- diff -cpr pgsql-orig/src/include/nodes/parsenodes.h pgsql/src/include/nodes/parsenodes.h *** pgsql-orig/src/include/nodes/parsenodes.h 2006-02-06 12:47:40.000000000 +0900 --- pgsql/src/include/nodes/parsenodes.h 2006-02-06 12:48:14.000000000 +0900 *************** typedef enum AlterTableType *** 837,843 **** AT_EnableTrigAll, /* ENABLE TRIGGER ALL */ AT_DisableTrigAll, /* DISABLE TRIGGER ALL */ AT_EnableTrigUser, /* ENABLE TRIGGER USER */ ! AT_DisableTrigUser /* DISABLE TRIGGER USER */ } AlterTableType; typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ --- 837,844 ---- AT_EnableTrigAll, /* ENABLE TRIGGER ALL */ AT_DisableTrigAll, /* DISABLE TRIGGER ALL */ AT_EnableTrigUser, /* ENABLE TRIGGER USER */ ! AT_DisableTrigUser, /* DISABLE TRIGGER USER */ ! AT_SetPctfree, /* SET PCTFREE */ } AlterTableType; typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ *************** typedef struct Constraint *** 1042,1047 **** --- 1043,1050 ---- Node *raw_expr; /* expr, as untransformed parse tree */ char *cooked_expr; /* expr, as nodeToString representation */ List *keys; /* String nodes naming referenced column(s) */ + int pctfree; /* index tablespace for PKEY/UNIQUE + * constraints; -1 for default */ char *indexspace; /* index tablespace for PKEY/UNIQUE * constraints; NULL for default */ } Constraint; *************** typedef struct IndexStmt *** 1395,1400 **** --- 1398,1404 ---- bool unique; /* is index unique? */ bool primary; /* is index on primary key? */ bool isconstraint; /* is it from a CONSTRAINT clause? */ + int pctfree; /* fill factor */ } IndexStmt; /* ---------------------- *************** typedef struct ReindexStmt *** 1797,1802 **** --- 1801,1807 ---- ObjectType kind; /* OBJECT_INDEX, OBJECT_TABLE, OBJECT_DATABASE */ RangeVar *relation; /* Table or index to reindex */ const char *name; /* name of database to reindex */ + int pctfree; /* index fill factor */ bool do_system; /* include system tables in database case */ bool do_user; /* include user tables in database case */ } ReindexStmt;