On Fri, 2009-12-04 at 09:20 +0000, Simon Riggs wrote: > On Tue, 2009-12-01 at 01:43 -0800, Jeff Davis wrote: > > Marking as ready. > > You're saying its ready, yet there are 3 additional suggestions patches > attached here. Please can you resolve these and re-submit a single final > patch from author and reviewer?
My apologies. At the time, I thought a couple days might matter, and the changes are in areas that committers tend to editorialize anyway: docs and a style issue. The only substantial patch was to vacuumdb.c. Complete patch attached including my edits. > * What happens if you issue VACUUM FULL; which we would expect to use > the new method of vacuum on all tables in the database. Won't that just > fail with an error when it comes to catalog tables? Sounds to me like we > should avoid the error and just silently do an INPLACE on catalog > tables. That's how it works. > * Such a pivotal change to Postgres needs more test coverage than a > single line in regression tests. It might have been OK before, but I > think we need a few more combinations here, at least in this release: > with, without indexes, empty table, clustered, non-clustered etc and of > course a full database VACUUM so that we have the catalog table case > covered, plus an explicit catalog table vacuum. It was my impression that the regression tests aren't meant to be exhaustive, but merely exercise a good portion of the code to help detect simple breakage. Also, pg_regress isn't good for detecting a lot of the problems that vacuum might have (how do you even know whether the vacuum happened in-place or not?). We could put a VACUUM FULL; and a VACUUM (FULL INPLACE); somewhere, which will cover a lot of the cases you're talking about. However, that may be a performance consideration especially for people who develop on laptops. In general, though, I think the right place for this is a longer test suite that is meant to be run less frequently. Regards, Jeff Davis
*** a/doc/src/sgml/ref/vacuum.sgml --- b/doc/src/sgml/ref/vacuum.sgml *************** *** 21,27 **** PostgreSQL documentation <refsynopsisdiv> <synopsis> ! VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ <replaceable class="PARAMETER">table</replaceable> [ (<replaceable class="PARAMETER">column</replaceable> [, ...] ) ] ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER">table</replaceable> [ (<replaceable class="PARAMETER">column</replaceable> [, ...] ) ] ] </synopsis> --- 21,27 ---- <refsynopsisdiv> <synopsis> ! VACUUM [ ( { FULL [ INPLACE ] | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ <replaceable class="PARAMETER">table</replaceable> [ (<replaceable class="PARAMETER">column</replaceable> [, ...] ) ] ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER">table</replaceable> [ (<replaceable class="PARAMETER">column</replaceable> [, ...] ) ] ] </synopsis> *************** *** 86,91 **** VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER"> --- 86,112 ---- Selects <quote>full</quote> vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. </para> + <para> + For user tables, all table data and indexes are rewritten. This + method requires extra disk space in which to write the new data, + and is generally useful when a significant amount of space needs + to be reclaimed from within the table. + </para> + <para> + For system tables, all table data and indexes are modified in + place to reclaim space. This method may require less disk space + for the table data than <command>VACUUM FULL</command> on a + comparable user table, but the indexes will grow which may + counteract that benefit. Additionally, the operation is often + slower than <command>VACUUM FULL</command> on a comparable user + table. + </para> + <para> + If <literal>FULL INPLACE</literal> is specified, the space is + reclaimed in the same manner as a system table, even if it is a + user table. Specifying <literal>INPLACE</literal> explicitly is + rarely useful. + </para> </listitem> </varlistentry> *** a/doc/src/sgml/ref/vacuumdb.sgml --- b/doc/src/sgml/ref/vacuumdb.sgml *************** *** 24,29 **** PostgreSQL documentation --- 24,30 ---- <command>vacuumdb</command> <arg rep="repeat"><replaceable>connection-option</replaceable></arg> <group><arg>--full</arg><arg>-f</arg></group> + <group><arg>--inplace</arg><arg>-i</arg></group> <group><arg>--verbose</arg><arg>-v</arg></group> <group><arg>--analyze</arg><arg>-z</arg></group> <group><arg>--freeze</arg><arg>-F</arg></group> *************** *** 36,41 **** PostgreSQL documentation --- 37,43 ---- <arg rep="repeat"><replaceable>connection-options</replaceable></arg> <group><arg>--all</arg><arg>-a</arg></group> <group><arg>--full</arg><arg>-f</arg></group> + <group><arg>--inplace</arg><arg>-i</arg></group> <group><arg>--verbose</arg><arg>-v</arg></group> <group><arg>--analyze</arg><arg>-z</arg></group> <group><arg>--freeze</arg><arg>-F</arg></group> *************** *** 111,117 **** PostgreSQL documentation <term><option>--full</option></term> <listitem> <para> ! Perform <quote>full</quote> vacuuming. </para> </listitem> </varlistentry> --- 113,129 ---- <term><option>--full</option></term> <listitem> <para> ! Perform <quote>full replace</quote> vacuuming. ! </para> ! </listitem> ! </varlistentry> ! ! <varlistentry> ! <term><option>-i</option></term> ! <term><option>--inplace</option></term> ! <listitem> ! <para> ! Perform <quote>full inplace</quote> vacuuming. </para> </listitem> </varlistentry> *** a/src/backend/commands/cluster.c --- b/src/backend/commands/cluster.c *************** *** 61,69 **** typedef struct } RelToCluster; ! static void cluster_rel(RelToCluster *rv, bool recheck, bool verbose); ! static void rebuild_relation(Relation OldHeap, Oid indexOid); ! static TransactionId copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex); static List *get_tables_to_cluster(MemoryContext cluster_context); --- 61,70 ---- } RelToCluster; ! static void rebuild_relation(Relation OldHeap, Oid indexOid, ! int freeze_min_age, int freeze_table_age); ! static TransactionId copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, ! Oid OIDOldIndex, int freeze_min_age, int freeze_table_age); static List *get_tables_to_cluster(MemoryContext cluster_context); *************** *** 101,107 **** cluster(ClusterStmt *stmt, bool isTopLevel) Oid tableOid, indexOid = InvalidOid; Relation rel; - RelToCluster rvtc; /* Find and lock the table */ rel = heap_openrv(stmt->relation, AccessExclusiveLock); --- 102,107 ---- *************** *** 169,183 **** cluster(ClusterStmt *stmt, bool isTopLevel) stmt->indexname, stmt->relation->relname))); } - /* All other checks are done in cluster_rel() */ - rvtc.tableOid = tableOid; - rvtc.indexOid = indexOid; - /* close relation, keep lock till commit */ heap_close(rel, NoLock); /* Do the job */ ! cluster_rel(&rvtc, false, stmt->verbose); } else { --- 169,179 ---- stmt->indexname, stmt->relation->relname))); } /* close relation, keep lock till commit */ heap_close(rel, NoLock); /* Do the job */ ! cluster_rel(tableOid, indexOid, false, stmt->verbose, -1, -1); } else { *************** *** 226,232 **** cluster(ClusterStmt *stmt, bool isTopLevel) StartTransactionCommand(); /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); ! cluster_rel(rvtc, true, stmt->verbose); PopActiveSnapshot(); CommitTransactionCommand(); } --- 222,228 ---- StartTransactionCommand(); /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); ! cluster_rel(rvtc->tableOid, rvtc->indexOid, true, stmt->verbose, -1, -1); PopActiveSnapshot(); CommitTransactionCommand(); } *************** *** 253,260 **** cluster(ClusterStmt *stmt, bool isTopLevel) * the new table, it's better to create the indexes afterwards than to fill * them incrementally while we load the table. */ ! static void ! cluster_rel(RelToCluster *rvtc, bool recheck, bool verbose) { Relation OldHeap; --- 249,257 ---- * the new table, it's better to create the indexes afterwards than to fill * them incrementally while we load the table. */ ! void ! cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose, ! int freeze_min_age, int freeze_table_age) { Relation OldHeap; *************** *** 267,273 **** cluster_rel(RelToCluster *rvtc, bool recheck, bool verbose) * case, since cluster() already did it.) The index lock is taken inside * check_index_is_clusterable. */ ! OldHeap = try_relation_open(rvtc->tableOid, AccessExclusiveLock); /* If the table has gone away, we can skip processing it */ if (!OldHeap) --- 264,270 ---- * case, since cluster() already did it.) The index lock is taken inside * check_index_is_clusterable. */ ! OldHeap = try_relation_open(tableOid, AccessExclusiveLock); /* If the table has gone away, we can skip processing it */ if (!OldHeap) *************** *** 287,293 **** cluster_rel(RelToCluster *rvtc, bool recheck, bool verbose) Form_pg_index indexForm; /* Check that the user still owns the relation */ ! if (!pg_class_ownercheck(rvtc->tableOid, GetUserId())) { relation_close(OldHeap, AccessExclusiveLock); return; --- 284,290 ---- Form_pg_index indexForm; /* Check that the user still owns the relation */ ! if (!pg_class_ownercheck(tableOid, GetUserId())) { relation_close(OldHeap, AccessExclusiveLock); return; *************** *** 308,360 **** cluster_rel(RelToCluster *rvtc, bool recheck, bool verbose) return; } ! /* ! * Check that the index still exists ! */ ! if (!SearchSysCacheExists(RELOID, ! ObjectIdGetDatum(rvtc->indexOid), ! 0, 0, 0)) { ! relation_close(OldHeap, AccessExclusiveLock); ! return; ! } ! /* ! * Check that the index is still the one with indisclustered set. ! */ ! tuple = SearchSysCache(INDEXRELID, ! ObjectIdGetDatum(rvtc->indexOid), ! 0, 0, 0); ! if (!HeapTupleIsValid(tuple)) /* probably can't happen */ ! { ! relation_close(OldHeap, AccessExclusiveLock); ! return; ! } ! indexForm = (Form_pg_index) GETSTRUCT(tuple); ! if (!indexForm->indisclustered) ! { ReleaseSysCache(tuple); - relation_close(OldHeap, AccessExclusiveLock); - return; } - ReleaseSysCache(tuple); } ! /* Check index is valid to cluster on */ ! check_index_is_clusterable(OldHeap, rvtc->indexOid, recheck); /* rebuild_relation does all the dirty work */ ereport(verbose ? INFO : DEBUG2, (errmsg("clustering \"%s.%s\"", get_namespace_name(RelationGetNamespace(OldHeap)), RelationGetRelationName(OldHeap)))); ! rebuild_relation(OldHeap, rvtc->indexOid); /* NB: rebuild_relation does heap_close() on OldHeap */ } /* ! * Verify that the specified index is a legitimate index to cluster on * * Side effect: obtains exclusive lock on the index. The caller should * already have exclusive lock on the table, so the index lock is likely --- 305,360 ---- return; } ! if (OidIsValid(indexOid)) { ! /* ! * Check that the index still exists ! */ ! if (!SearchSysCacheExists(RELOID, ! ObjectIdGetDatum(indexOid), ! 0, 0, 0)) ! { ! relation_close(OldHeap, AccessExclusiveLock); ! return; ! } ! /* ! * Check that the index is still the one with indisclustered set. ! */ ! tuple = SearchSysCache(INDEXRELID, ! ObjectIdGetDatum(indexOid), ! 0, 0, 0); ! if (!HeapTupleIsValid(tuple)) /* probably can't happen */ ! { ! relation_close(OldHeap, AccessExclusiveLock); ! return; ! } ! indexForm = (Form_pg_index) GETSTRUCT(tuple); ! if (!indexForm->indisclustered) ! { ! ReleaseSysCache(tuple); ! relation_close(OldHeap, AccessExclusiveLock); ! return; ! } ReleaseSysCache(tuple); } } ! /* Check heap and index are valid to cluster on */ ! check_index_is_clusterable(OldHeap, indexOid, recheck); /* rebuild_relation does all the dirty work */ ereport(verbose ? INFO : DEBUG2, (errmsg("clustering \"%s.%s\"", get_namespace_name(RelationGetNamespace(OldHeap)), RelationGetRelationName(OldHeap)))); ! rebuild_relation(OldHeap, indexOid, freeze_min_age, freeze_table_age); /* NB: rebuild_relation does heap_close() on OldHeap */ } /* ! * Verify that the specified heap and index are valid to cluster on * * Side effect: obtains exclusive lock on the index. The caller should * already have exclusive lock on the table, so the index lock is likely *************** *** 366,371 **** check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck) --- 366,403 ---- { Relation OldIndex; + /* + * Disallow clustering system relations. This will definitely NOT work + * for shared relations (we have no way to update pg_class rows in other + * databases), nor for nailed-in-cache relations (the relfilenode values + * for those are hardwired, see relcache.c). It might work for other + * system relations, but I ain't gonna risk it. + */ + if (IsSystemRelation(OldHeap)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("\"%s\" is a system catalog", + RelationGetRelationName(OldHeap)))); + + /* + * Don't allow cluster on temp tables of other backends ... their local + * buffer manager is not going to cope. + */ + if (RELATION_IS_OTHER_TEMP(OldHeap)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot cluster temporary tables of other sessions"))); + + /* + * Also check for active uses of the relation in the current transaction, + * including open scans and pending AFTER trigger events. + */ + CheckTableNotInUse(OldHeap, "CLUSTER"); + + /* Skip checks for index if not specified. */ + if (!OidIsValid(indexOid)) + return; + OldIndex = index_open(indexOid, AccessExclusiveLock); /* *************** *** 448,481 **** check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck) errmsg("cannot cluster on invalid index \"%s\"", RelationGetRelationName(OldIndex)))); - /* - * Disallow clustering system relations. This will definitely NOT work - * for shared relations (we have no way to update pg_class rows in other - * databases), nor for nailed-in-cache relations (the relfilenode values - * for those are hardwired, see relcache.c). It might work for other - * system relations, but I ain't gonna risk it. - */ - if (IsSystemRelation(OldHeap)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("\"%s\" is a system catalog", - RelationGetRelationName(OldHeap)))); - - /* - * Don't allow cluster on temp tables of other backends ... their local - * buffer manager is not going to cope. - */ - if (RELATION_IS_OTHER_TEMP(OldHeap)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot cluster temporary tables of other sessions"))); - - /* - * Also check for active uses of the relation in the current transaction, - * including open scans and pending AFTER trigger events. - */ - CheckTableNotInUse(OldHeap, "CLUSTER"); - /* Drop relcache refcnt on OldIndex, but keep lock */ index_close(OldIndex, NoLock); } --- 480,485 ---- *************** *** 565,571 **** mark_index_clustered(Relation rel, Oid indexOid) * NB: this routine closes OldHeap at the right time; caller should not. */ static void ! rebuild_relation(Relation OldHeap, Oid indexOid) { Oid tableOid = RelationGetRelid(OldHeap); Oid tableSpace = OldHeap->rd_rel->reltablespace; --- 569,576 ---- * NB: this routine closes OldHeap at the right time; caller should not. */ static void ! rebuild_relation(Relation OldHeap, Oid indexOid, ! int freeze_min_age, int freeze_table_age) { Oid tableOid = RelationGetRelid(OldHeap); Oid tableSpace = OldHeap->rd_rel->reltablespace; *************** *** 576,582 **** rebuild_relation(Relation OldHeap, Oid indexOid) Relation newrel; /* Mark the correct index as clustered */ ! mark_index_clustered(OldHeap, indexOid); /* Close relcache entry, but keep lock until transaction commit */ heap_close(OldHeap, NoLock); --- 581,588 ---- Relation newrel; /* Mark the correct index as clustered */ ! if (OidIsValid(indexOid)) ! mark_index_clustered(OldHeap, indexOid); /* Close relcache entry, but keep lock until transaction commit */ heap_close(OldHeap, NoLock); *************** *** 599,605 **** rebuild_relation(Relation OldHeap, Oid indexOid) /* * Copy the heap data into the new table in the desired order. */ ! frozenXid = copy_heap_data(OIDNewHeap, tableOid, indexOid); /* To make the new heap's data visible (probably not needed?). */ CommandCounterIncrement(); --- 605,612 ---- /* * Copy the heap data into the new table in the desired order. */ ! frozenXid = copy_heap_data(OIDNewHeap, tableOid, indexOid, ! freeze_min_age, freeze_table_age); /* To make the new heap's data visible (probably not needed?). */ CommandCounterIncrement(); *************** *** 758,764 **** make_new_heap(Oid OIDOldHeap, const char *NewName, Oid NewTableSpace) * freeze cutoff point for the tuples. */ static TransactionId ! copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex) { Relation NewHeap, OldHeap, --- 765,772 ---- * freeze cutoff point for the tuples. */ static TransactionId ! copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, ! int freeze_min_age, int freeze_table_age) { Relation NewHeap, OldHeap, *************** *** 768,775 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex) int natts; Datum *values; bool *isnull; ! IndexScanDesc scan; ! HeapTuple tuple; bool use_wal; TransactionId OldestXmin; TransactionId FreezeXid; --- 776,783 ---- int natts; Datum *values; bool *isnull; ! IndexScanDesc indexScan; ! HeapScanDesc heapScan; bool use_wal; TransactionId OldestXmin; TransactionId FreezeXid; *************** *** 780,786 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex) */ NewHeap = heap_open(OIDNewHeap, AccessExclusiveLock); OldHeap = heap_open(OIDOldHeap, AccessExclusiveLock); ! OldIndex = index_open(OIDOldIndex, AccessExclusiveLock); /* * Their tuple descriptors should be exactly alike, but here we only need --- 788,797 ---- */ NewHeap = heap_open(OIDNewHeap, AccessExclusiveLock); OldHeap = heap_open(OIDOldHeap, AccessExclusiveLock); ! if (OidIsValid(OIDOldIndex)) ! OldIndex = index_open(OIDOldIndex, AccessExclusiveLock); ! else ! OldIndex = NULL; /* * Their tuple descriptors should be exactly alike, but here we only need *************** *** 809,816 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex) * freeze_min_age to avoid having CLUSTER freeze tuples earlier than a * plain VACUUM would. */ ! vacuum_set_xid_limits(-1, -1, OldHeap->rd_rel->relisshared, ! &OldestXmin, &FreezeXid, NULL); /* * FreezeXid will become the table's new relfrozenxid, and that mustn't go --- 820,827 ---- * freeze_min_age to avoid having CLUSTER freeze tuples earlier than a * plain VACUUM would. */ ! vacuum_set_xid_limits(freeze_min_age, freeze_table_age, ! OldHeap->rd_rel->relisshared, &OldestXmin, &FreezeXid, NULL); /* * FreezeXid will become the table's new relfrozenxid, and that mustn't go *************** *** 828,852 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex) * copied, we scan with SnapshotAny and use HeapTupleSatisfiesVacuum for * the visibility test. */ ! scan = index_beginscan(OldHeap, OldIndex, SnapshotAny, 0, (ScanKey) NULL); ! while ((tuple = index_getnext(scan, ForwardScanDirection)) != NULL) { HeapTuple copiedTuple; bool isdead; int i; CHECK_FOR_INTERRUPTS(); ! /* Since we used no scan keys, should never need to recheck */ ! if (scan->xs_recheck) ! elog(ERROR, "CLUSTER does not support lossy index conditions"); ! LockBuffer(scan->xs_cbuf, BUFFER_LOCK_SHARE); ! switch (HeapTupleSatisfiesVacuum(tuple->t_data, OldestXmin, ! scan->xs_cbuf)) { case HEAPTUPLE_DEAD: /* Definitely dead */ --- 839,884 ---- * copied, we scan with SnapshotAny and use HeapTupleSatisfiesVacuum for * the visibility test. */ ! if (OldIndex != NULL) ! indexScan = index_beginscan(OldHeap, OldIndex, SnapshotAny, 0, (ScanKey) NULL); + else + heapScan = heap_beginscan(OldHeap, SnapshotAny, 0, (ScanKey) NULL); ! for (;;) { + HeapTuple tuple; HeapTuple copiedTuple; + Buffer buf; bool isdead; int i; CHECK_FOR_INTERRUPTS(); ! if (OldIndex != NULL) ! { ! tuple = index_getnext(indexScan, ForwardScanDirection); ! if (tuple == NULL) ! break; ! /* Since we used no scan keys, should never need to recheck */ ! if (indexScan->xs_recheck) ! elog(ERROR, "CLUSTER does not support lossy index conditions"); ! buf = indexScan->xs_cbuf; ! } ! else ! { ! tuple = heap_getnext(heapScan, ForwardScanDirection); ! if (tuple == NULL) ! break; ! ! buf = heapScan->rs_cbuf; ! } ! ! LockBuffer(buf, BUFFER_LOCK_SHARE); ! ! switch (HeapTupleSatisfiesVacuum(tuple->t_data, OldestXmin, buf)) { case HEAPTUPLE_DEAD: /* Definitely dead */ *************** *** 888,894 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex) break; } ! LockBuffer(scan->xs_cbuf, BUFFER_LOCK_UNLOCK); if (isdead) { --- 920,926 ---- break; } ! LockBuffer(buf, BUFFER_LOCK_UNLOCK); if (isdead) { *************** *** 932,938 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex) heap_freetuple(copiedTuple); } ! index_endscan(scan); /* Write out any remaining tuples, and fsync if needed */ end_heap_rewrite(rwstate); --- 964,973 ---- heap_freetuple(copiedTuple); } ! if (OldIndex != NULL) ! index_endscan(indexScan); ! else ! heap_endscan(heapScan); /* Write out any remaining tuples, and fsync if needed */ end_heap_rewrite(rwstate); *************** *** 940,946 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex) pfree(values); pfree(isnull); ! index_close(OldIndex, NoLock); heap_close(OldHeap, NoLock); heap_close(NewHeap, NoLock); --- 975,982 ---- pfree(values); pfree(isnull); ! if (OldIndex != NULL) ! index_close(OldIndex, NoLock); heap_close(OldHeap, NoLock); heap_close(NewHeap, NoLock); *** a/src/backend/commands/vacuum.c --- b/src/backend/commands/vacuum.c *************** *** 29,38 **** --- 29,40 ---- #include "access/visibilitymap.h" #include "access/xact.h" #include "access/xlog.h" + #include "catalog/catalog.h" #include "catalog/namespace.h" #include "catalog/pg_database.h" #include "catalog/pg_namespace.h" #include "catalog/storage.h" + #include "commands/cluster.h" #include "commands/dbcommands.h" #include "commands/vacuum.h" #include "executor/executor.h" *************** *** 301,306 **** vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast, --- 303,310 ---- Assert((vacstmt->options & VACOPT_VACUUM) || !(vacstmt->options & (VACOPT_FULL | VACOPT_FREEZE))); Assert((vacstmt->options & VACOPT_ANALYZE) || vacstmt->va_cols == NIL); + Assert((vacstmt->options & VACOPT_FULL) || + !(vacstmt->options & VACOPT_INPLACE)); stmttype = (vacstmt->options & VACOPT_VACUUM) ? "VACUUM" : "ANALYZE"; *************** *** 1192,1209 **** vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound, SetUserIdAndContext(onerel->rd_rel->relowner, true); /* ! * Do the actual work --- either FULL or "lazy" vacuum */ ! if (vacstmt->options & VACOPT_FULL) heldoff = full_vacuum_rel(onerel, vacstmt); else ! heldoff = lazy_vacuum_rel(onerel, vacstmt, vac_strategy, scanned_all); /* Restore userid */ SetUserIdAndContext(save_userid, save_secdefcxt); /* all done with this class, but hold lock until commit */ ! relation_close(onerel, NoLock); /* * Complete the transaction and free all temporary memory used. --- 1196,1226 ---- SetUserIdAndContext(onerel->rd_rel->relowner, true); /* ! * Do the actual work --- either FULL, FULL INPLACE, or "lazy" vacuum. ! * We can only use FULL INPLACE vacuum for system relations. */ ! if (!(vacstmt->options & VACOPT_FULL)) ! heldoff = lazy_vacuum_rel(onerel, vacstmt, vac_strategy, scanned_all); ! else if ((vacstmt->options & VACOPT_INPLACE) || IsSystemRelation(onerel)) heldoff = full_vacuum_rel(onerel, vacstmt); else ! { ! /* close relation before clustering, but hold lock until commit */ ! relation_close(onerel, NoLock); ! onerel = NULL; ! ! cluster_rel(relid, InvalidOid, false, ! (vacstmt->options & VACOPT_VERBOSE) != 0, ! vacstmt->freeze_min_age, vacstmt->freeze_table_age); ! heldoff = false; ! } /* Restore userid */ SetUserIdAndContext(save_userid, save_secdefcxt); /* all done with this class, but hold lock until commit */ ! if (onerel) ! relation_close(onerel, NoLock); /* * Complete the transaction and free all temporary memory used. *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 486,492 **** static TypeName *TableFuncTypeName(List *columns); IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P ! INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION JOIN --- 486,492 ---- IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P ! INNER_P INOUT INPLACE INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION JOIN *************** *** 6708,6713 **** vacuum_option_elem: --- 6708,6714 ---- | VERBOSE { $$ = VACOPT_VERBOSE; } | FREEZE { $$ = VACOPT_FREEZE; } | FULL { $$ = VACOPT_FULL; } + | FULL INPLACE { $$ = VACOPT_FULL | VACOPT_INPLACE; } ; AnalyzeStmt: *************** *** 10649,10654 **** unreserved_keyword: --- 10650,10656 ---- | INHERIT | INHERITS | INLINE_P + | INPLACE | INPUT_P | INSENSITIVE | INSERT *** a/src/bin/scripts/vacuumdb.c --- b/src/bin/scripts/vacuumdb.c *************** *** 14,28 **** #include "common.h" ! static void vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze, ! bool freeze, const char *table, const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo); ! static void vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze, ! const char *host, const char *port, ! const char *username, enum trivalue prompt_password, ! const char *progname, bool echo, bool quiet); static void help(const char *progname); --- 14,29 ---- #include "common.h" ! static void vacuum_one_database(const char *dbname, bool full, bool inplace, ! bool verbose, bool analyze, bool freeze, const char *table, const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo); ! static void vacuum_all_databases(bool full, bool inplace, ! bool verbose, bool analyze, bool freeze, ! const char *host, const char *port, ! const char *username, enum trivalue prompt_password, ! const char *progname, bool echo, bool quiet); static void help(const char *progname); *************** *** 45,50 **** main(int argc, char *argv[]) --- 46,52 ---- {"table", required_argument, NULL, 't'}, {"full", no_argument, NULL, 'f'}, {"verbose", no_argument, NULL, 'v'}, + {"inplace", no_argument, NULL, 'i'}, {NULL, 0, NULL, 0} }; *************** *** 65,77 **** main(int argc, char *argv[]) char *table = NULL; bool full = false; bool verbose = false; progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); handle_help_version_opts(argc, argv, "vacuumdb", help); ! while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zaFt:fv", long_options, &optindex)) != -1) { switch (c) { --- 67,80 ---- char *table = NULL; bool full = false; bool verbose = false; + bool inplace = false; progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); handle_help_version_opts(argc, argv, "vacuumdb", help); ! while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zaFt:fiv", long_options, &optindex)) != -1) { switch (c) { *************** *** 117,122 **** main(int argc, char *argv[]) --- 120,128 ---- case 'v': verbose = true; break; + case 'i': + inplace = true; + break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); *************** *** 137,142 **** main(int argc, char *argv[]) --- 143,156 ---- exit(1); } + /* INPLACE option requires FULL option. */ + if (inplace && !full) + { + fprintf(stderr, _("%s: -i, --inplace option requires -f, --full\n"), + progname); + exit(1); + } + setup_cancel_handler(); if (alldb) *************** *** 154,160 **** main(int argc, char *argv[]) exit(1); } ! vacuum_all_databases(full, verbose, analyze, freeze, host, port, username, prompt_password, progname, echo, quiet); } --- 168,174 ---- exit(1); } ! vacuum_all_databases(full, inplace, verbose, analyze, freeze, host, port, username, prompt_password, progname, echo, quiet); } *************** *** 170,177 **** main(int argc, char *argv[]) dbname = get_user_name(progname); } ! vacuum_one_database(dbname, full, verbose, analyze, freeze, table, ! host, port, username, prompt_password, progname, echo); } --- 184,191 ---- dbname = get_user_name(progname); } ! vacuum_one_database(dbname, full, inplace, verbose, analyze, freeze, ! table, host, port, username, prompt_password, progname, echo); } *************** *** 180,187 **** main(int argc, char *argv[]) static void ! vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze, ! bool freeze, const char *table, const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo) --- 194,201 ---- static void ! vacuum_one_database(const char *dbname, bool full, bool inplace, bool verbose, ! bool analyze, bool freeze, const char *table, const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo) *************** *** 189,211 **** vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze, PQExpBufferData sql; PGconn *conn; initPQExpBuffer(&sql); appendPQExpBuffer(&sql, "VACUUM"); ! if (full) ! appendPQExpBuffer(&sql, " FULL"); ! if (freeze) ! appendPQExpBuffer(&sql, " FREEZE"); ! if (verbose) ! appendPQExpBuffer(&sql, " VERBOSE"); ! if (analyze) ! appendPQExpBuffer(&sql, " ANALYZE"); if (table) appendPQExpBuffer(&sql, " %s", table); appendPQExpBuffer(&sql, ";\n"); - conn = connectDatabase(dbname, host, port, username, prompt_password, progname); if (!executeMaintenanceCommand(conn, sql.data, echo)) { if (table) --- 203,264 ---- PQExpBufferData sql; PGconn *conn; + int version; + bool first_opt = true; initPQExpBuffer(&sql); + conn = connectDatabase(dbname, host, port, username, prompt_password, progname); + version = PQserverVersion(conn); + appendPQExpBuffer(&sql, "VACUUM"); ! ! if (version >= 80500) ! { ! if (full) ! { ! appendPQExpBuffer(&sql, "%sFULL%s", first_opt ? " (" : ", ", ! inplace ? " INPLACE" : ""); ! first_opt = false; ! } ! if (freeze) ! { ! appendPQExpBuffer(&sql, "%sFREEZE", first_opt ? " (" : ", "); ! first_opt = false; ! } ! if (verbose) ! { ! appendPQExpBuffer(&sql, "%sVERBOSE", first_opt ? " (" : ", "); ! first_opt = false; ! } ! if (analyze) ! { ! appendPQExpBuffer(&sql, "%sANALYZE", first_opt ? " (" : ", "); ! first_opt = false; ! } ! if (!first_opt) ! appendPQExpBuffer(&sql, ")"); ! } ! else ! { ! /* ! * On older servers, VACUUM FULL is equivalent to VACUUM (FULL ! * INPLACE) on newer servers, so we can ignore 'inplace'. ! */ ! if (full) ! appendPQExpBuffer(&sql, " FULL"); ! if (freeze) ! appendPQExpBuffer(&sql, " FREEZE"); ! if (verbose) ! appendPQExpBuffer(&sql, " VERBOSE"); ! if (analyze) ! appendPQExpBuffer(&sql, " ANALYZE"); ! } ! if (table) appendPQExpBuffer(&sql, " %s", table); appendPQExpBuffer(&sql, ";\n"); if (!executeMaintenanceCommand(conn, sql.data, echo)) { if (table) *************** *** 223,230 **** vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze, static void ! vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze, ! const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo, bool quiet) { --- 276,283 ---- static void ! vacuum_all_databases(bool full, bool inplace, bool verbose, bool analyze, ! bool freeze, const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo, bool quiet) { *************** *** 246,253 **** vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze, fflush(stdout); } ! vacuum_one_database(dbname, full, verbose, analyze, freeze, NULL, ! host, port, username, prompt_password, progname, echo); } --- 299,306 ---- fflush(stdout); } ! vacuum_one_database(dbname, full, inplace, verbose, analyze, freeze, ! NULL, host, port, username, prompt_password, progname, echo); } *************** *** 267,272 **** help(const char *progname) --- 320,326 ---- printf(_(" -e, --echo show the commands being sent to the server\n")); printf(_(" -f, --full do full vacuuming\n")); printf(_(" -F, --freeze freeze row transaction information\n")); + printf(_(" -i, --inplace do full inplace vacuuming\n")); printf(_(" -q, --quiet don't write any messages\n")); printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table only\n")); printf(_(" -v, --verbose write a lot of output\n")); *** a/src/include/commands/cluster.h --- b/src/include/commands/cluster.h *************** *** 18,24 **** extern void cluster(ClusterStmt *stmt, bool isTopLevel); ! extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck); extern void mark_index_clustered(Relation rel, Oid indexOid); --- 18,25 ---- extern void cluster(ClusterStmt *stmt, bool isTopLevel); ! extern void cluster_rel(Oid tableOid, Oid indexOid, bool recheck, ! bool verbose, int freeze_min_age, int freeze_table_age); extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck); extern void mark_index_clustered(Relation rel, Oid indexOid); *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 2222,2228 **** typedef enum VacuumOption VACOPT_ANALYZE = 1 << 1, /* do ANALYZE */ VACOPT_VERBOSE = 1 << 2, /* print progress info */ VACOPT_FREEZE = 1 << 3, /* FREEZE option */ ! VACOPT_FULL = 1 << 4 /* FULL (non-concurrent) vacuum */ } VacuumOption; typedef struct VacuumStmt --- 2222,2229 ---- VACOPT_ANALYZE = 1 << 1, /* do ANALYZE */ VACOPT_VERBOSE = 1 << 2, /* print progress info */ VACOPT_FREEZE = 1 << 3, /* FREEZE option */ ! VACOPT_FULL = 1 << 4, /* FULL (non-concurrent) vacuum */ ! VACOPT_INPLACE = 1 << 5 /* traditional FULL INPLACE vacuum */ } VacuumOption; typedef struct VacuumStmt *** a/src/include/parser/kwlist.h --- b/src/include/parser/kwlist.h *************** *** 192,197 **** PG_KEYWORD("initially", INITIALLY, RESERVED_KEYWORD) --- 192,198 ---- PG_KEYWORD("inline", INLINE_P, UNRESERVED_KEYWORD) PG_KEYWORD("inner", INNER_P, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("inout", INOUT, COL_NAME_KEYWORD) + PG_KEYWORD("inplace", INPLACE, UNRESERVED_KEYWORD) PG_KEYWORD("input", INPUT_P, UNRESERVED_KEYWORD) PG_KEYWORD("insensitive", INSENSITIVE, UNRESERVED_KEYWORD) PG_KEYWORD("insert", INSERT, UNRESERVED_KEYWORD) *** a/src/test/regress/expected/vacuum.out --- b/src/test/regress/expected/vacuum.out *************** *** 57,60 **** SELECT * FROM vactst; --- 57,61 ---- (0 rows) VACUUM (FULL, FREEZE) vactst; + VACUUM (ANALYZE, FULL INPLACE) vactst; DROP TABLE vactst; *** a/src/test/regress/sql/vacuum.sql --- b/src/test/regress/sql/vacuum.sql *************** *** 40,44 **** DELETE FROM vactst; --- 40,45 ---- SELECT * FROM vactst; VACUUM (FULL, FREEZE) vactst; + VACUUM (ANALYZE, FULL INPLACE) vactst; DROP TABLE vactst;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers