($INBOX_DIR/description missing)help / color / mirror / Atom feed
[PATCH 2/3] Fix WAL skipping feature 3+ messages / 3 participants [nested] [flat]
* [PATCH 2/3] Fix WAL skipping feature @ 2019-05-27 07:06 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Kyotaro Horiguchi @ 2019-05-27 07:06 UTC (permalink / raw) WAL-skipping operations mixed with WAL-logged operations can lead to database corruption after a crash. This patch changes the WAL-skipping feature so that no data modifcation is WAL-logged at all then sync such relations at commit. --- src/backend/access/heap/heapam.c | 4 +- src/backend/access/heap/heapam_handler.c | 22 +---------- src/backend/access/heap/rewriteheap.c | 13 ++----- src/backend/catalog/storage.c | 64 +++++++++++++++++++++++++------- src/backend/commands/cluster.c | 24 ++++++++++++ src/backend/commands/copy.c | 39 ++++--------------- src/backend/commands/createas.c | 5 +-- src/backend/commands/matview.c | 4 -- src/backend/commands/tablecmds.c | 10 ++--- src/backend/storage/buffer/bufmgr.c | 33 +++++++++++----- src/backend/utils/cache/relcache.c | 16 ++++++-- src/include/access/heapam.h | 1 - src/include/access/rewriteheap.h | 2 +- src/include/access/tableam.h | 41 ++------------------ src/include/storage/bufmgr.h | 1 + src/include/utils/rel.h | 17 ++++++++- 16 files changed, 148 insertions(+), 148 deletions(-) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index d768b9b061..eca98fb063 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -1941,7 +1941,7 @@ heap_insert(Relation relation, HeapTuple tup, CommandId cid, MarkBufferDirty(buffer); /* XLOG stuff */ - if (!(options & HEAP_INSERT_SKIP_WAL) && RelationNeedsWAL(relation)) + if (RelationNeedsWAL(relation)) { xl_heap_insert xlrec; xl_heap_header xlhdr; @@ -2124,7 +2124,7 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples, /* currently not needed (thus unsupported) for heap_multi_insert() */ AssertArg(!(options & HEAP_INSERT_NO_LOGICAL)); - needwal = !(options & HEAP_INSERT_SKIP_WAL) && RelationNeedsWAL(relation); + needwal = RelationNeedsWAL(relation); saveFreeSpace = RelationGetTargetPageFreeSpace(relation, HEAP_DEFAULT_FILLFACTOR); diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index 09bc6fe98a..b9554f6064 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -556,18 +556,6 @@ tuple_lock_retry: return result; } -static void -heapam_finish_bulk_insert(Relation relation, int options) -{ - /* - * If we skipped writing WAL, then we need to sync the heap (but not - * indexes since those use WAL anyway / don't go through tableam) - */ - if (options & HEAP_INSERT_SKIP_WAL) - heap_sync(relation); -} - - /* ------------------------------------------------------------------------ * DDL related callbacks for heap AM. * ------------------------------------------------------------------------ @@ -699,7 +687,6 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, IndexScanDesc indexScan; TableScanDesc tableScan; HeapScanDesc heapScan; - bool use_wal; bool is_system_catalog; Tuplesortstate *tuplesort; TupleDesc oldTupDesc = RelationGetDescr(OldHeap); @@ -713,12 +700,6 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, /* Remember if it's a system catalog */ is_system_catalog = IsSystemRelation(OldHeap); - /* - * We need to log the copied data in WAL iff WAL archiving/streaming is - * enabled AND it's a WAL-logged rel. - */ - use_wal = XLogIsNeeded() && RelationNeedsWAL(NewHeap); - /* use_wal off requires smgr_targblock be initially invalid */ Assert(RelationGetTargetBlock(NewHeap) == InvalidBlockNumber); @@ -729,7 +710,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, /* Initialize the rewrite operation */ rwstate = begin_heap_rewrite(OldHeap, NewHeap, OldestXmin, *xid_cutoff, - *multi_cutoff, use_wal); + *multi_cutoff); /* Set up sorting if wanted */ @@ -2517,7 +2498,6 @@ static const TableAmRoutine heapam_methods = { .tuple_delete = heapam_tuple_delete, .tuple_update = heapam_tuple_update, .tuple_lock = heapam_tuple_lock, - .finish_bulk_insert = heapam_finish_bulk_insert, .tuple_fetch_row_version = heapam_fetch_row_version, .tuple_get_latest_tid = heap_get_latest_tid, diff --git a/src/backend/access/heap/rewriteheap.c b/src/backend/access/heap/rewriteheap.c index 72a448ad31..992d4b9880 100644 --- a/src/backend/access/heap/rewriteheap.c +++ b/src/backend/access/heap/rewriteheap.c @@ -144,7 +144,6 @@ typedef struct RewriteStateData Page rs_buffer; /* page currently being built */ BlockNumber rs_blockno; /* block where page will go */ bool rs_buffer_valid; /* T if any tuples in buffer */ - bool rs_use_wal; /* must we WAL-log inserts? */ bool rs_logical_rewrite; /* do we need to do logical rewriting */ TransactionId rs_oldest_xmin; /* oldest xmin used by caller to determine * tuple visibility */ @@ -238,15 +237,13 @@ static void logical_end_heap_rewrite(RewriteState state); * oldest_xmin xid used by the caller to determine which tuples are dead * freeze_xid xid before which tuples will be frozen * min_multi multixact before which multis will be removed - * use_wal should the inserts to the new heap be WAL-logged? * * Returns an opaque RewriteState, allocated in current memory context, * to be used in subsequent calls to the other functions. */ RewriteState begin_heap_rewrite(Relation old_heap, Relation new_heap, TransactionId oldest_xmin, - TransactionId freeze_xid, MultiXactId cutoff_multi, - bool use_wal) + TransactionId freeze_xid, MultiXactId cutoff_multi) { RewriteState state; MemoryContext rw_cxt; @@ -271,7 +268,6 @@ begin_heap_rewrite(Relation old_heap, Relation new_heap, TransactionId oldest_xm /* new_heap needn't be empty, just locked */ state->rs_blockno = RelationGetNumberOfBlocks(new_heap); state->rs_buffer_valid = false; - state->rs_use_wal = use_wal; state->rs_oldest_xmin = oldest_xmin; state->rs_freeze_xid = freeze_xid; state->rs_cutoff_multi = cutoff_multi; @@ -330,7 +326,7 @@ end_heap_rewrite(RewriteState state) /* Write the last page, if any */ if (state->rs_buffer_valid) { - if (state->rs_use_wal) + if (RelationNeedsWAL(state->rs_new_rel)) log_newpage(&state->rs_new_rel->rd_node, MAIN_FORKNUM, state->rs_blockno, @@ -654,9 +650,6 @@ raw_heap_insert(RewriteState state, HeapTuple tup) { int options = HEAP_INSERT_SKIP_FSM; - if (!state->rs_use_wal) - options |= HEAP_INSERT_SKIP_WAL; - /* * While rewriting the heap for VACUUM FULL / CLUSTER, make sure data * for the TOAST table are not logically decoded. The main heap is @@ -695,7 +688,7 @@ raw_heap_insert(RewriteState state, HeapTuple tup) /* Doesn't fit, so write out the existing page */ /* XLOG stuff */ - if (state->rs_use_wal) + if (RelationNeedsWAL(state->rs_new_rel)) log_newpage(&state->rs_new_rel->rd_node, MAIN_FORKNUM, state->rs_blockno, diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index 3cc886f7fe..e4bcdc390f 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -57,7 +57,8 @@ typedef struct PendingRelDelete { RelFileNode relnode; /* relation that may need to be deleted */ BackendId backend; /* InvalidBackendId if not a temp rel */ - bool atCommit; /* T=delete at commit; F=delete at abort */ + bool atCommit; /* T=work at commit; F=work at abort */ + bool dosync; /* T=work is sync; F=work is delete */ int nestLevel; /* xact nesting level of request */ struct PendingRelDelete *next; /* linked-list link */ } PendingRelDelete; @@ -114,10 +115,29 @@ RelationCreateStorage(RelFileNode rnode, char relpersistence) pending->relnode = rnode; pending->backend = backend; pending->atCommit = false; /* delete if abort */ + pending->dosync = false; pending->nestLevel = GetCurrentTransactionNestLevel(); pending->next = pendingDeletes; pendingDeletes = pending; + /* + * We are going to skip WAL-logging for storage of persistent relations + * created in the current transaction when wal_level = minimal. The + * relation needs to be synced at commit. + */ + if (relpersistence == RELPERSISTENCE_PERMANENT && !XLogIsNeeded()) + { + pending = (PendingRelDelete *) + MemoryContextAlloc(TopMemoryContext, sizeof(PendingRelDelete)); + pending->relnode = rnode; + pending->backend = backend; + pending->atCommit = true; + pending->dosync = true; + pending->nestLevel = GetCurrentTransactionNestLevel(); + pending->next = pendingDeletes; + pendingDeletes = pending; + } + return srel; } @@ -155,6 +175,7 @@ RelationDropStorage(Relation rel) pending->relnode = rel->rd_node; pending->backend = rel->rd_backend; pending->atCommit = true; /* delete if commit */ + pending->dosync = false; pending->nestLevel = GetCurrentTransactionNestLevel(); pending->next = pendingDeletes; pendingDeletes = pending; @@ -428,21 +449,34 @@ smgrDoPendingDeletes(bool isCommit) { SMgrRelation srel; - srel = smgropen(pending->relnode, pending->backend); - - /* allocate the initial array, or extend it, if needed */ - if (maxrels == 0) + if (pending->dosync) { - maxrels = 8; - srels = palloc(sizeof(SMgrRelation) * maxrels); + /* Perform pending sync of WAL-skipped relation */ + FlushRelationBuffersWithoutRelcache(pending->relnode, + false); + srel = smgropen(pending->relnode, pending->backend); + smgrimmedsync(srel, MAIN_FORKNUM); + smgrclose(srel); } - else if (maxrels <= nrels) + else { - maxrels *= 2; - srels = repalloc(srels, sizeof(SMgrRelation) * maxrels); - } + /* Collect pending deletions */ + srel = smgropen(pending->relnode, pending->backend); - srels[nrels++] = srel; + /* allocate the initial array, or extend it, if needed */ + if (maxrels == 0) + { + maxrels = 8; + srels = palloc(sizeof(SMgrRelation) * maxrels); + } + else if (maxrels <= nrels) + { + maxrels *= 2; + srels = repalloc(srels, sizeof(SMgrRelation) * maxrels); + } + + srels[nrels++] = srel; + } } /* must explicitly free the list entry */ pfree(pending); @@ -489,8 +523,9 @@ smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr) nrels = 0; for (pending = pendingDeletes; pending != NULL; pending = pending->next) { + /* Pending syncs are excluded */ if (pending->nestLevel >= nestLevel && pending->atCommit == forCommit - && pending->backend == InvalidBackendId) + && pending->backend == InvalidBackendId && !pending->dosync) nrels++; } if (nrels == 0) @@ -502,8 +537,9 @@ smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr) *ptr = rptr; for (pending = pendingDeletes; pending != NULL; pending = pending->next) { + /* Pending syncs are excluded */ if (pending->nestLevel >= nestLevel && pending->atCommit == forCommit - && pending->backend == InvalidBackendId) + && pending->backend == InvalidBackendId && !pending->dosync) { *rptr = pending->relnode; rptr++; diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index ebaec4f8dd..6fc9d7d64e 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -1034,12 +1034,36 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class, if (OidIsValid(relfilenode1) && OidIsValid(relfilenode2)) { + Relation rel1; + Relation rel2; + /* * Normal non-mapped relations: swap relfilenodes, reltablespaces, * relpersistence */ Assert(!target_is_pg_class); + /* Update creation subid hints of relcache */ + rel1 = relation_open(r1, ExclusiveLock); + rel2 = relation_open(r2, ExclusiveLock); + + /* + * New relation's relfilenode is created in the current transaction + * and used as old ralation's new relfilenode. So its + * newRelfilenodeSubid as new relation's createSubid. We don't fix + * rel2 since it would be deleted soon. + */ + Assert(rel2->rd_createSubid != InvalidSubTransactionId); + rel1->rd_newRelfilenodeSubid = rel2->rd_createSubid; + + /* record the first relfilenode change in the current transaction */ + if (rel1->rd_firstRelfilenodeSubid == InvalidSubTransactionId) + rel1->rd_firstRelfilenodeSubid = GetCurrentSubTransactionId(); + + relation_close(rel1, ExclusiveLock); + relation_close(rel2, ExclusiveLock); + + /* swap relfilenodes, reltablespaces, relpersistence */ swaptemp = relform1->relfilenode; relform1->relfilenode = relform2->relfilenode; relform2->relfilenode = swaptemp; diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 4f04d122c3..f02efd59fc 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -2535,9 +2535,6 @@ CopyMultiInsertBufferCleanup(CopyMultiInsertInfo *miinfo, for (i = 0; i < MAX_BUFFERED_TUPLES && buffer->slots[i] != NULL; i++) ExecDropSingleTupleTableSlot(buffer->slots[i]); - table_finish_bulk_insert(buffer->resultRelInfo->ri_RelationDesc, - miinfo->ti_options); - pfree(buffer); } @@ -2726,28 +2723,9 @@ CopyFrom(CopyState cstate) * If it does commit, we'll have done the table_finish_bulk_insert() at * the bottom of this routine first. * - * As mentioned in comments in utils/rel.h, the in-same-transaction test - * is not always set correctly, since in rare cases rd_newRelfilenodeSubid - * can be cleared before the end of the transaction. The exact case is - * when a relation sets a new relfilenode twice in same transaction, yet - * the second one fails in an aborted subtransaction, e.g. - * - * BEGIN; - * TRUNCATE t; - * SAVEPOINT save; - * TRUNCATE t; - * ROLLBACK TO save; - * COPY ... - * - * Also, if the target file is new-in-transaction, we assume that checking - * FSM for free space is a waste of time, even if we must use WAL because - * of archiving. This could possibly be wrong, but it's unlikely. - * - * The comments for table_tuple_insert and RelationGetBufferForTuple - * specify that skipping WAL logging is only safe if we ensure that our - * tuples do not go into pages containing tuples from any other - * transactions --- but this must be the case if we have a new table or - * new relfilenode, so we need no additional work to enforce that. + * If the target file is new-in-transaction, we assume that checking FSM + * for free space is a waste of time, even if we must use WAL because of + * archiving. This could possibly be wrong, but it's unlikely. * * We currently don't support this optimization if the COPY target is a * partitioned table as we currently only lazily initialize partition @@ -2763,15 +2741,14 @@ CopyFrom(CopyState cstate) * are not supported as per the description above. *---------- */ - /* createSubid is creation check, newRelfilenodeSubid is truncation check */ + /* + * createSubid is creation check, firstRelfilenodeSubid is truncation and + * cluster check. Partitioned table doesn't have storage. + */ if (RELKIND_HAS_STORAGE(cstate->rel->rd_rel->relkind) && (cstate->rel->rd_createSubid != InvalidSubTransactionId || - cstate->rel->rd_newRelfilenodeSubid != InvalidSubTransactionId)) - { + cstate->rel->rd_firstRelfilenodeSubid != InvalidSubTransactionId)) ti_options |= TABLE_INSERT_SKIP_FSM; - if (!XLogIsNeeded()) - ti_options |= TABLE_INSERT_SKIP_WAL; - } /* * Optimize if new relfilenode was created in this subxact or one of its diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 4c1d909d38..39ebd73691 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -558,8 +558,7 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) * We can skip WAL-logging the insertions, unless PITR or streaming * replication is in use. We can skip the FSM in any case. */ - myState->ti_options = TABLE_INSERT_SKIP_FSM | - (XLogIsNeeded() ? 0 : TABLE_INSERT_SKIP_WAL); + myState->ti_options = TABLE_INSERT_SKIP_FSM; myState->bistate = GetBulkInsertState(); /* Not using WAL requires smgr_targblock be initially invalid */ @@ -604,8 +603,6 @@ intorel_shutdown(DestReceiver *self) FreeBulkInsertState(myState->bistate); - table_finish_bulk_insert(myState->rel, myState->ti_options); - /* close rel, but keep lock until commit */ table_close(myState->rel, NoLock); myState->rel = NULL; diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 537d0e8cef..1c854dcebf 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -463,8 +463,6 @@ transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) * replication is in use. We can skip the FSM in any case. */ myState->ti_options = TABLE_INSERT_SKIP_FSM | TABLE_INSERT_FROZEN; - if (!XLogIsNeeded()) - myState->ti_options |= TABLE_INSERT_SKIP_WAL; myState->bistate = GetBulkInsertState(); /* Not using WAL requires smgr_targblock be initially invalid */ @@ -509,8 +507,6 @@ transientrel_shutdown(DestReceiver *self) FreeBulkInsertState(myState->bistate); - table_finish_bulk_insert(myState->transientrel, myState->ti_options); - /* close transientrel, but keep lock until commit */ table_close(myState->transientrel, NoLock); myState->transientrel = NULL; diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 0f1a9f0e54..ac7336ef58 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -4761,9 +4761,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) /* * Prepare a BulkInsertState and options for table_tuple_insert. Because - * we're building a new heap, we can skip WAL-logging and fsync it to disk - * at the end instead (unless WAL-logging is required for archiving or - * streaming replication). The FSM is empty too, so don't bother using it. + * we're building a new heap, the underlying table AM can skip WAL-logging + * and fsync the relation to disk at the end of the current transaction + * instead. The FSM is empty too, so don't bother using it. */ if (newrel) { @@ -4771,8 +4771,6 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) bistate = GetBulkInsertState(); ti_options = TABLE_INSERT_SKIP_FSM; - if (!XLogIsNeeded()) - ti_options |= TABLE_INSERT_SKIP_WAL; } else { @@ -5057,8 +5055,6 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) { FreeBulkInsertState(bistate); - table_finish_bulk_insert(newrel, ti_options); - table_close(newrel, NoLock); } } diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 7332e6b590..280fdf8080 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -171,6 +171,7 @@ static HTAB *PrivateRefCountHash = NULL; static int32 PrivateRefCountOverflowed = 0; static uint32 PrivateRefCountClock = 0; static PrivateRefCountEntry *ReservedRefCountEntry = NULL; +static void FlushRelationBuffers_common(SMgrRelation smgr, bool islocal); static void ReservePrivateRefCountEntry(void); static PrivateRefCountEntry *NewPrivateRefCountEntry(Buffer buffer); @@ -3190,20 +3191,32 @@ PrintPinnedBufs(void) void FlushRelationBuffers(Relation rel) { - int i; - BufferDesc *bufHdr; - - /* Open rel at the smgr level if not already done */ RelationOpenSmgr(rel); - if (RelationUsesLocalBuffers(rel)) + FlushRelationBuffers_common(rel->rd_smgr, RelationUsesLocalBuffers(rel)); +} + +void +FlushRelationBuffersWithoutRelcache(RelFileNode rnode, bool islocal) +{ + FlushRelationBuffers_common(smgropen(rnode, InvalidBackendId), islocal); +} + +static void +FlushRelationBuffers_common(SMgrRelation smgr, bool islocal) +{ + RelFileNode rnode = smgr->smgr_rnode.node; + int i; + BufferDesc *bufHdr; + + if (islocal) { for (i = 0; i < NLocBuffer; i++) { uint32 buf_state; bufHdr = GetLocalBufferDescriptor(i); - if (RelFileNodeEquals(bufHdr->tag.rnode, rel->rd_node) && + if (RelFileNodeEquals(bufHdr->tag.rnode, rnode) && ((buf_state = pg_atomic_read_u32(&bufHdr->state)) & (BM_VALID | BM_DIRTY)) == (BM_VALID | BM_DIRTY)) { @@ -3220,7 +3233,7 @@ FlushRelationBuffers(Relation rel) PageSetChecksumInplace(localpage, bufHdr->tag.blockNum); - smgrwrite(rel->rd_smgr, + smgrwrite(smgr, bufHdr->tag.forkNum, bufHdr->tag.blockNum, localpage, @@ -3250,18 +3263,18 @@ FlushRelationBuffers(Relation rel) * As in DropRelFileNodeBuffers, an unlocked precheck should be safe * and saves some cycles. */ - if (!RelFileNodeEquals(bufHdr->tag.rnode, rel->rd_node)) + if (!RelFileNodeEquals(bufHdr->tag.rnode, rnode)) continue; ReservePrivateRefCountEntry(); buf_state = LockBufHdr(bufHdr); - if (RelFileNodeEquals(bufHdr->tag.rnode, rel->rd_node) && + if (RelFileNodeEquals(bufHdr->tag.rnode, rnode) && (buf_state & (BM_VALID | BM_DIRTY)) == (BM_VALID | BM_DIRTY)) { PinBuffer_Locked(bufHdr); LWLockAcquire(BufferDescriptorGetContentLock(bufHdr), LW_SHARED); - FlushBuffer(bufHdr, rel->rd_smgr); + FlushBuffer(bufHdr, smgr); LWLockRelease(BufferDescriptorGetContentLock(bufHdr)); UnpinBuffer(bufHdr, true); } diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 2b992d7832..812bfadb40 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -2661,7 +2661,7 @@ static void RelationFlushRelation(Relation relation) { if (relation->rd_createSubid != InvalidSubTransactionId || - relation->rd_newRelfilenodeSubid != InvalidSubTransactionId) + relation->rd_firstRelfilenodeSubid != InvalidSubTransactionId) { /* * New relcache entries are always rebuilt, not flushed; else we'd @@ -2801,7 +2801,7 @@ RelationCacheInvalidate(void) * pending invalidations. */ if (relation->rd_createSubid != InvalidSubTransactionId || - relation->rd_newRelfilenodeSubid != InvalidSubTransactionId) + relation->rd_firstRelfilenodeSubid != InvalidSubTransactionId) continue; relcacheInvalsReceived++; @@ -3058,6 +3058,7 @@ AtEOXact_cleanup(Relation relation, bool isCommit) * Likewise, reset the hint about the relfilenode being new. */ relation->rd_newRelfilenodeSubid = InvalidSubTransactionId; + relation->rd_firstRelfilenodeSubid = InvalidSubTransactionId; } /* @@ -3149,7 +3150,7 @@ AtEOSubXact_cleanup(Relation relation, bool isCommit, } /* - * Likewise, update or drop any new-relfilenode-in-subtransaction hint. + * Likewise, update or drop any new-relfilenode-in-subtransaction hints. */ if (relation->rd_newRelfilenodeSubid == mySubid) { @@ -3158,6 +3159,15 @@ AtEOSubXact_cleanup(Relation relation, bool isCommit, else relation->rd_newRelfilenodeSubid = InvalidSubTransactionId; } + + + if (relation->rd_firstRelfilenodeSubid == mySubid) + { + if (isCommit) + relation->rd_firstRelfilenodeSubid = parentSubid; + else + relation->rd_firstRelfilenodeSubid = InvalidSubTransactionId; + } } diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index 858bcb6bc9..80c2e1bafc 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -29,7 +29,6 @@ /* "options" flag bits for heap_insert */ -#define HEAP_INSERT_SKIP_WAL TABLE_INSERT_SKIP_WAL #define HEAP_INSERT_SKIP_FSM TABLE_INSERT_SKIP_FSM #define HEAP_INSERT_FROZEN TABLE_INSERT_FROZEN #define HEAP_INSERT_NO_LOGICAL TABLE_INSERT_NO_LOGICAL diff --git a/src/include/access/rewriteheap.h b/src/include/access/rewriteheap.h index 8056253916..7f9736e294 100644 --- a/src/include/access/rewriteheap.h +++ b/src/include/access/rewriteheap.h @@ -23,7 +23,7 @@ typedef struct RewriteStateData *RewriteState; extern RewriteState begin_heap_rewrite(Relation OldHeap, Relation NewHeap, TransactionId OldestXmin, TransactionId FreezeXid, - MultiXactId MultiXactCutoff, bool use_wal); + MultiXactId MultiXactCutoff); extern void end_heap_rewrite(RewriteState state); extern void rewrite_heap_tuple(RewriteState state, HeapTuple oldTuple, HeapTuple newTuple); diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h index c2b0481e7e..ac0e981acb 100644 --- a/src/include/access/tableam.h +++ b/src/include/access/tableam.h @@ -407,22 +407,6 @@ typedef struct TableAmRoutine uint8 flags, TM_FailureData *tmfd); - /* - * Perform operations necessary to complete insertions made via - * tuple_insert and multi_insert with a BulkInsertState specified. This - * may for example be used to flush the relation, when the - * TABLE_INSERT_SKIP_WAL option was used. - * - * Typically callers of tuple_insert and multi_insert will just pass all - * the flags that apply to them, and each AM has to decide which of them - * make sense for it, and then only take actions in finish_bulk_insert for - * those flags, and ignore others. - * - * Optional callback. - */ - void (*finish_bulk_insert) (Relation rel, int options); - - /* ------------------------------------------------------------------------ * DDL related functionality. * ------------------------------------------------------------------------ @@ -1088,10 +1072,6 @@ table_compute_xid_horizon_for_tuples(Relation rel, * behaviour of the AM. Several options might be ignored by AMs not supporting * them. * - * If the TABLE_INSERT_SKIP_WAL option is specified, the new tuple doesn't - * need to be logged to WAL, even for a non-temp relation. It is the AMs - * choice whether this optimization is supported. - * * If the TABLE_INSERT_SKIP_FSM option is specified, AMs are free to not reuse * free space in the relation. This can save some cycles when we know the * relation is new and doesn't contain useful amounts of free space. It's @@ -1111,10 +1091,8 @@ table_compute_xid_horizon_for_tuples(Relation rel, * Note that most of these options will be applied when inserting into the * heap's TOAST table, too, if the tuple requires any out-of-line data. * - * * The BulkInsertState object (if any; bistate can be NULL for default - * behavior) is also just passed through to RelationGetBufferForTuple. If - * `bistate` is provided, table_finish_bulk_insert() needs to be called. + * behavior) is also just passed through to RelationGetBufferForTuple. * * On return the slot's tts_tid and tts_tableOid are updated to reflect the * insertion. But note that any toasting of fields within the slot is NOT @@ -1249,6 +1227,8 @@ table_tuple_delete(Relation rel, ItemPointer tid, CommandId cid, * update was done. However, any TOAST changes in the new tuple's * data are not reflected into *newtup. * + * See table_insert about skipping WAL-logging feature. + * * In the failure cases, the routine fills *tmfd with the tuple's t_ctid, * t_xmax, and, if possible, t_cmax. See comments for struct TM_FailureData * for additional info. @@ -1309,21 +1289,6 @@ table_tuple_lock(Relation rel, ItemPointer tid, Snapshot snapshot, flags, tmfd); } -/* - * Perform operations necessary to complete insertions made via - * tuple_insert and multi_insert with a BulkInsertState specified. This - * e.g. may e.g. used to flush the relation when inserting with - * TABLE_INSERT_SKIP_WAL specified. - */ -static inline void -table_finish_bulk_insert(Relation rel, int options) -{ - /* optional callback */ - if (rel->rd_tableam && rel->rd_tableam->finish_bulk_insert) - rel->rd_tableam->finish_bulk_insert(rel, options); -} - - /* ------------------------------------------------------------------------ * DDL related functionality. * ------------------------------------------------------------------------ diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 509f4b7ef1..ace5f5a2ae 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -189,6 +189,7 @@ extern BlockNumber RelationGetNumberOfBlocksInFork(Relation relation, ForkNumber forkNum); extern void FlushOneBuffer(Buffer buffer); extern void FlushRelationBuffers(Relation rel); +extern void FlushRelationBuffersWithoutRelcache(RelFileNode rnode, bool islocal); extern void FlushDatabaseBuffers(Oid dbid); extern void DropRelFileNodeBuffers(RelFileNodeBackend rnode, ForkNumber forkNum, BlockNumber firstDelBlock); diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index d35b4a5061..5cbb5a7b27 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -76,10 +76,17 @@ typedef struct RelationData * transaction, with one of them occurring in a subsequently aborted * subtransaction, e.g. BEGIN; TRUNCATE t; SAVEPOINT save; TRUNCATE t; * ROLLBACK TO save; -- rd_newRelfilenode is now forgotten + * rd_firstRelfilenodeSubid is the ID of the hightest subtransaction the + * relfilenode change has took place first in the current + * transaction. This won't be forgotten as newRelfilenodeSubid is. A valid + * OID means that the currently active relfilenode is transaction-local + * and no-need for WAL-logging. */ SubTransactionId rd_createSubid; /* rel was created in current xact */ SubTransactionId rd_newRelfilenodeSubid; /* new relfilenode assigned in * current xact */ + SubTransactionId rd_firstRelfilenodeSubid; /* new relfilenode assigned + * first in current xact */ Form_pg_class rd_rel; /* RELATION tuple */ TupleDesc rd_att; /* tuple descriptor */ @@ -512,9 +519,15 @@ typedef struct ViewOptions /* * RelationNeedsWAL * True if relation needs WAL. + * + * Returns false if wal_level = minimal and this relation is created or + * truncated in the current transaction. */ -#define RelationNeedsWAL(relation) \ - ((relation)->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT) +#define RelationNeedsWAL(relation) \ + ((relation)->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT && \ + (XLogIsNeeded() || \ + (relation->rd_createSubid == InvalidSubTransactionId && \ + relation->rd_firstRelfilenodeSubid == InvalidSubTransactionId))) /* * RelationUsesLocalBuffers -- 2.16.3 ----Next_Part(Fri_Jul_12_17_37_25_2019_721)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v17-0003-Rename-smgrDoPendingDeletes-to-smgrDoPendingOperatio.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH v4 4/7] Row pattern recognition patch (executor). @ 2023-08-09 07:56 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Tatsuo Ishii @ 2023-08-09 07:56 UTC (permalink / raw) --- src/backend/executor/nodeWindowAgg.c | 674 ++++++++++++++++++++++++++- src/backend/utils/adt/windowfuncs.c | 38 +- src/include/catalog/pg_proc.dat | 6 + src/include/nodes/execnodes.h | 19 + src/include/windowapi.h | 8 + 5 files changed, 732 insertions(+), 13 deletions(-) diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index 310ac23e3a..5354e47045 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -36,6 +36,7 @@ #include "access/htup_details.h" #include "catalog/objectaccess.h" #include "catalog/pg_aggregate.h" +#include "catalog/pg_collation_d.h" #include "catalog/pg_proc.h" #include "executor/executor.h" #include "executor/nodeWindowAgg.h" @@ -48,6 +49,7 @@ #include "utils/acl.h" #include "utils/builtins.h" #include "utils/datum.h" +#include "utils/fmgroids.h" #include "utils/expandeddatum.h" #include "utils/lsyscache.h" #include "utils/memutils.h" @@ -182,8 +184,9 @@ static void begin_partition(WindowAggState *winstate); static void spool_tuples(WindowAggState *winstate, int64 pos); static void release_partition(WindowAggState *winstate); -static int row_is_in_frame(WindowAggState *winstate, int64 pos, +static int row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot); + static void update_frameheadpos(WindowAggState *winstate); static void update_frametailpos(WindowAggState *winstate); static void update_grouptailpos(WindowAggState *winstate); @@ -195,9 +198,20 @@ static Datum GetAggInitVal(Datum textInitVal, Oid transtype); static bool are_peers(WindowAggState *winstate, TupleTableSlot *slot1, TupleTableSlot *slot2); -static bool window_gettupleslot(WindowObject winobj, int64 pos, - TupleTableSlot *slot); +static void attno_map(Node *node); +static bool attno_map_walker(Node *node, void *context); +static int row_is_in_reduced_frame(WindowObject winobj, int64 pos); + +static int64 evaluate_pattern(WindowObject winobj, int64 current_pos, + char *vname, StringInfo encoded_str, bool *result); + +static bool get_slots(WindowObject winobj, int64 current_pos); + +static int search_str_set(char *pattern, StringInfo *str_set, int set_size); +static void search_str_set_recurse(char *pattern, StringInfo *str_set, int set_size, int set_index, + char *encoded_str, int *resultlen); +static char pattern_initial(WindowAggState *winstate, char *vname); /* * initialize_windowaggregate @@ -673,6 +687,9 @@ eval_windowaggregates(WindowAggState *winstate) WindowObject agg_winobj; TupleTableSlot *agg_row_slot; TupleTableSlot *temp_slot; + bool reduced_frame_set; + bool check_reduced_frame; + int num_rows_in_reduced_frame; numaggs = winstate->numaggs; if (numaggs == 0) @@ -790,6 +807,7 @@ eval_windowaggregates(WindowAggState *winstate) (winstate->frameOptions & FRAMEOPTION_EXCLUSION) || winstate->aggregatedupto <= winstate->frameheadpos) { + elog(DEBUG1, "peraggstate->restart is set"); peraggstate->restart = true; numaggs_restart++; } @@ -861,8 +879,10 @@ eval_windowaggregates(WindowAggState *winstate) * If we created a mark pointer for aggregates, keep it pushed up to frame * head, so that tuplestore can discard unnecessary rows. */ +#ifdef NOT_USED if (agg_winobj->markptr >= 0) WinSetMarkPosition(agg_winobj, winstate->frameheadpos); +#endif /* * Now restart the aggregates that require it. @@ -919,6 +939,10 @@ eval_windowaggregates(WindowAggState *winstate) ExecClearTuple(agg_row_slot); } + reduced_frame_set = false; + check_reduced_frame = false; + num_rows_in_reduced_frame = 0; + /* * Advance until we reach a row not in frame (or end of partition). * @@ -930,12 +954,18 @@ eval_windowaggregates(WindowAggState *winstate) { int ret; + elog(DEBUG1, "===== loop in frame starts: " INT64_FORMAT, winstate->aggregatedupto); + /* Fetch next row if we didn't already */ if (TupIsNull(agg_row_slot)) { if (!window_gettupleslot(agg_winobj, winstate->aggregatedupto, agg_row_slot)) + { + if (check_reduced_frame) + winstate->aggregatedupto--; break; /* must be end of partition */ + } } /* @@ -944,10 +974,47 @@ eval_windowaggregates(WindowAggState *winstate) */ ret = row_is_in_frame(winstate, winstate->aggregatedupto, agg_row_slot); if (ret < 0) + { + if (winstate->patternVariableList != NIL && check_reduced_frame) + winstate->aggregatedupto--; break; + } if (ret == 0) goto next_tuple; + if (winstate->patternVariableList != NIL) + { + if (!reduced_frame_set) + { + num_rows_in_reduced_frame = row_is_in_reduced_frame(winstate->agg_winobj, winstate->aggregatedupto); + reduced_frame_set = true; + elog(DEBUG1, "set num_rows_in_reduced_frame: %d pos: " INT64_FORMAT, + num_rows_in_reduced_frame, winstate->aggregatedupto); + + if (num_rows_in_reduced_frame <= 0) + break; + + else if (num_rows_in_reduced_frame > 0) + check_reduced_frame = true; + } + + if (check_reduced_frame) + { + elog(DEBUG1, "decrease num_rows_in_reduced_frame: %d pos: " INT64_FORMAT, + num_rows_in_reduced_frame, winstate->aggregatedupto); + num_rows_in_reduced_frame--; + if (num_rows_in_reduced_frame < 0) + { + /* + * No more rows remain in the reduced frame. Finish + * accumulating row into the aggregates. + */ + winstate->aggregatedupto--; + break; + } + } + } + /* Set tuple context for evaluation of aggregate arguments */ winstate->tmpcontext->ecxt_outertuple = agg_row_slot; @@ -976,6 +1043,8 @@ next_tuple: ExecClearTuple(agg_row_slot); } + elog(DEBUG1, "===== break loop in frame starts: " INT64_FORMAT, winstate->aggregatedupto); + /* The frame's end is not supposed to move backwards, ever */ Assert(aggregatedupto_nonrestarted <= winstate->aggregatedupto); @@ -2053,6 +2122,8 @@ ExecWindowAgg(PlanState *pstate) CHECK_FOR_INTERRUPTS(); + elog(DEBUG1, "ExecWindowAgg called. pos: " INT64_FORMAT , winstate->currentpos); + if (winstate->status == WINDOWAGG_DONE) return NULL; @@ -2388,6 +2459,9 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) TupleDesc scanDesc; ListCell *l; + TargetEntry *te; + Expr *expr; + /* check for unsupported flags */ Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK))); @@ -2483,6 +2557,16 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) winstate->temp_slot_2 = ExecInitExtraTupleSlot(estate, scanDesc, &TTSOpsMinimalTuple); + winstate->prev_slot = ExecInitExtraTupleSlot(estate, scanDesc, + &TTSOpsMinimalTuple); + + winstate->next_slot = ExecInitExtraTupleSlot(estate, scanDesc, + &TTSOpsMinimalTuple); + + winstate->null_slot = ExecInitExtraTupleSlot(estate, scanDesc, + &TTSOpsMinimalTuple); + winstate->null_slot = ExecStoreAllNullTuple(winstate->null_slot); + /* * create frame head and tail slots only if needed (must create slots in * exactly the same cases that update_frameheadpos and update_frametailpos @@ -2667,6 +2751,39 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) winstate->inRangeAsc = node->inRangeAsc; winstate->inRangeNullsFirst = node->inRangeNullsFirst; + /* Set up SKIP TO type */ + winstate->rpSkipTo = node->rpSkipTo; + /* Set up row pattern recognition PATTERN clause */ + winstate->patternVariableList = node->patternVariable; + winstate->patternRegexpList = node->patternRegexp; + + /* Set up row pattern recognition DEFINE clause */ + winstate->defineInitial = node->defineInitial; + winstate->defineVariableList = NIL; + winstate->defineClauseList = NIL; + if (node->defineClause != NIL) + { + /* + * Tweak arg var of PREV/NEXT so that it refers to scan/inner slot. + */ + foreach(l, node->defineClause) + { + char *name; + ExprState *exps; + + te = lfirst(l); + name = te->resname; + expr = te->expr; + + elog(DEBUG1, "defineVariable name: %s", name); + winstate->defineVariableList = lappend(winstate->defineVariableList, + makeString(pstrdup(name))); + attno_map((Node *)expr); + exps = ExecInitExpr(expr, (PlanState *) winstate); + winstate->defineClauseList = lappend(winstate->defineClauseList, exps); + } + } + winstate->all_first = true; winstate->partition_spooled = false; winstate->more_partitions = false; @@ -2674,6 +2791,57 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) return winstate; } +/* + * Rewrite varno of Var node that is the argument of PREV/NET so that it sees + * scan tuple (PREV) or inner tuple (NEXT). + */ +static void +attno_map(Node *node) +{ + (void) expression_tree_walker(node, attno_map_walker, NULL); +} + +static bool +attno_map_walker(Node *node, void *context) +{ + FuncExpr *func; + int nargs; + Expr *expr; + Var *var; + + if (node == NULL) + return false; + + if (IsA(node, FuncExpr)) + { + func = (FuncExpr *)node; + + if (func->funcid == F_PREV || func->funcid == F_NEXT) + { + /* sanity check */ + nargs = list_length(func->args); + if (list_length(func->args) != 1) + elog(ERROR, "PREV/NEXT must have 1 argument but function %d has %d args", func->funcid, nargs); + + expr = (Expr *) lfirst(list_head(func->args)); + if (!IsA(expr, Var)) + elog(ERROR, "PREV/NEXT's arg is not Var"); /* XXX: is it possible that arg type is Const? */ + var = (Var *)expr; + + if (func->funcid == F_PREV) + /* + * Rewrite varno from OUTER_VAR to regular var no so that the + * var references scan tuple. + */ + var->varno = var->varnosyn; + else + var->varno = INNER_VAR; + elog(DEBUG1, "PREV/NEXT's varno is rewritten to: %d", var->varno); + } + } + return expression_tree_walker(node, attno_map_walker, NULL); +} + /* ----------------- * ExecEndWindowAgg * ----------------- @@ -2691,6 +2859,8 @@ ExecEndWindowAgg(WindowAggState *node) ExecClearTuple(node->agg_row_slot); ExecClearTuple(node->temp_slot_1); ExecClearTuple(node->temp_slot_2); + ExecClearTuple(node->prev_slot); + ExecClearTuple(node->next_slot); if (node->framehead_slot) ExecClearTuple(node->framehead_slot); if (node->frametail_slot) @@ -2740,6 +2910,8 @@ ExecReScanWindowAgg(WindowAggState *node) ExecClearTuple(node->agg_row_slot); ExecClearTuple(node->temp_slot_1); ExecClearTuple(node->temp_slot_2); + ExecClearTuple(node->prev_slot); + ExecClearTuple(node->next_slot); if (node->framehead_slot) ExecClearTuple(node->framehead_slot); if (node->frametail_slot) @@ -3080,7 +3252,7 @@ are_peers(WindowAggState *winstate, TupleTableSlot *slot1, * * Returns true if successful, false if no such row */ -static bool +bool window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot) { WindowAggState *winstate = winobj->winstate; @@ -3100,7 +3272,7 @@ window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot) return false; if (pos < winobj->markpos) - elog(ERROR, "cannot fetch row before WindowObject's mark position"); + elog(ERROR, "cannot fetch row: " INT64_FORMAT " before WindowObject's mark position: " INT64_FORMAT, pos, winobj->markpos ); oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory); @@ -3420,14 +3592,54 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno, WindowAggState *winstate; ExprContext *econtext; TupleTableSlot *slot; - int64 abs_pos; - int64 mark_pos; Assert(WindowObjectIsValid(winobj)); winstate = winobj->winstate; econtext = winstate->ss.ps.ps_ExprContext; slot = winstate->temp_slot_1; + if (WinGetSlotInFrame(winobj, slot, + relpos, seektype, set_mark, + isnull, isout) == 0) + { + econtext->ecxt_outertuple = slot; + return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), + econtext, isnull); + } + + if (isout) + *isout = true; + *isnull = true; + return (Datum) 0; +} + +/* + * WinGetSlotInFrame + * slot: TupleTableSlot to store the result + * relpos: signed rowcount offset from the seek position + * seektype: WINDOW_SEEK_HEAD or WINDOW_SEEK_TAIL + * set_mark: If the row is found/in frame and set_mark is true, the mark is + * moved to the row as a side-effect. + * isnull: output argument, receives isnull status of result + * isout: output argument, set to indicate whether target row position + * is out of frame (can pass NULL if caller doesn't care about this) + * + * Returns 0 if we successfullt got the slot. false if out of frame. + * (also isout is set) + */ +int +WinGetSlotInFrame(WindowObject winobj, TupleTableSlot *slot, + int relpos, int seektype, bool set_mark, + bool *isnull, bool *isout) +{ + WindowAggState *winstate; + int64 abs_pos; + int64 mark_pos; + int num_reduced_frame; + + Assert(WindowObjectIsValid(winobj)); + winstate = winobj->winstate; + switch (seektype) { case WINDOW_SEEK_CURRENT: @@ -3494,6 +3706,12 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno, winstate->frameOptions); break; } + num_reduced_frame = row_is_in_reduced_frame(winobj, winstate->frameheadpos); + if (num_reduced_frame < 0) + goto out_of_frame; + else if (num_reduced_frame > 0) + if (relpos >= num_reduced_frame) + goto out_of_frame; break; case WINDOW_SEEK_TAIL: /* rejecting relpos > 0 is easy and simplifies code below */ @@ -3565,6 +3783,12 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno, mark_pos = 0; /* keep compiler quiet */ break; } + + num_reduced_frame = row_is_in_reduced_frame(winobj, winstate->frameheadpos + relpos); + if (num_reduced_frame < 0) + goto out_of_frame; + else if (num_reduced_frame > 0) + abs_pos = winstate->frameheadpos + relpos + num_reduced_frame - 1; break; default: elog(ERROR, "unrecognized window seek type: %d", seektype); @@ -3583,15 +3807,13 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno, *isout = false; if (set_mark) WinSetMarkPosition(winobj, mark_pos); - econtext->ecxt_outertuple = slot; - return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), - econtext, isnull); + return 0; out_of_frame: if (isout) *isout = true; *isnull = true; - return (Datum) 0; + return -1; } /* @@ -3622,3 +3844,433 @@ WinGetFuncArgCurrent(WindowObject winobj, int argno, bool *isnull) return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), econtext, isnull); } + +WindowAggState * +WinGetAggState(WindowObject winobj) +{ + return winobj->winstate; +} + +/* + * row_is_in_reduced_frame + * Determine whether a row is in the current row's reduced window frame according + * to row pattern matching + * + * The row must has been already determined that it is in a full window frame + * and fetched it into slot. + * + * Returns: + * = 0, RPR is not defined. + * >0, if the row is the first in the reduced frame. Return the number of rows in the reduced frame. + * -1, if the row is unmatched row + * -2, if the row is in the reduced frame but needed to be skipped because of + * AFTER MATCH SKIP PAST LAST ROW + */ +static +int row_is_in_reduced_frame(WindowObject winobj, int64 pos) +{ + WindowAggState *winstate = winobj->winstate; + ListCell *lc1, *lc2; + bool expression_result; + int num_matched_rows; + int64 original_pos; + bool anymatch; + StringInfo encoded_str; + StringInfo pattern_str = makeStringInfo(); + + /* + * Array of pattern variables evaluted to true. + * Each character corresponds to pattern variable. + * Example: + * str_set[0] = "AB"; + * str_set[1] = "AC"; + * In this case at row 0 A and B are true, and A and C are true in row 1. + */ + #define ENCODED_STR_ARRAY_ALLOC_SIZE 128 + StringInfo *str_set = NULL; + int str_set_index; + int str_set_size; + + if (winstate->patternVariableList == NIL) + { + /* + * RPR is not defined. Assume that we are always in the the reduced + * window frame. + */ + return 0; + } + + /* save original pos */ + original_pos = pos; + + /* + * Check whether the row speicied by pos is in the reduced frame. The + * second and subsequent rows need to be recognized as "unmatched" rows if + * AFTER MATCH SKIP PAST LAST ROW is defined. + */ + if (winstate->rpSkipTo == ST_PAST_LAST_ROW && + pos > winstate->headpos_in_reduced_frame && + pos < (winstate->headpos_in_reduced_frame + winstate->num_rows_in_reduced_frame)) + return -2; + + /* + * Loop over until none of pattern matches or encounters end of frame. + */ + for (;;) + { + int64 result_pos = -1; + + /* + * Loop over each PATTERN variable. + */ + anymatch = false; + encoded_str = makeStringInfo(); + + forboth(lc1, winstate->patternVariableList, lc2, winstate->patternRegexpList) + { + char *vname = strVal(lfirst(lc1)); + char *quantifier = strVal(lfirst(lc2)); + + elog(DEBUG1, "pos: " INT64_FORMAT " pattern vname: %s quantifier: %s", pos, vname, quantifier); + + expression_result = false; + + /* evaluate row pattern against current row */ + result_pos = evaluate_pattern(winobj, pos, vname, encoded_str, &expression_result); + if (expression_result) + { + elog(DEBUG1, "expression result is true"); + anymatch = true; + } + + /* + * If out of frame, we are done. + */ + if (result_pos < 0) + break; + } + + if (!anymatch) + { + /* none of patterns matched. */ + break; + } + + /* build encoded string array */ + if (str_set == NULL) + { + str_set_index = 0; + str_set_size = ENCODED_STR_ARRAY_ALLOC_SIZE * sizeof(StringInfo); + str_set = palloc(str_set_size); + } + + str_set[str_set_index++] = encoded_str; + + elog(DEBUG1, "pos: " INT64_FORMAT " str_set_index: %d encoded_str: %s", pos, str_set_index, encoded_str->data); + + if (str_set_index >= str_set_size) + { + str_set_size *= 2; + str_set = repalloc(str_set, str_set_size); + } + + /* move to next row */ + pos++; + + if (result_pos < 0) + { + /* out of frame */ + break; + } + } + + if (str_set == NULL) + { + /* no matches found in the first row */ + return -1; + } + + elog(DEBUG1, "pos: " INT64_FORMAT " encoded_str: %s", pos, encoded_str->data); + + /* build regular expression */ + pattern_str = makeStringInfo(); + appendStringInfoChar(pattern_str, '^'); + forboth (lc1, winstate->patternVariableList, lc2, winstate->patternRegexpList) + { + char *vname = strVal(lfirst(lc1)); + char *quantifier = strVal(lfirst(lc2)); + char initial; + + initial = pattern_initial(winstate, vname); + Assert(initial != 0); + appendStringInfoChar(pattern_str, initial); + if (quantifier[0]) + appendStringInfoChar(pattern_str, quantifier[0]); + elog(DEBUG1, "vname: %s initial: %c quantifier: %s", vname, initial, quantifier); + } + + elog(DEBUG1, "pos: " INT64_FORMAT " pattern: %s", pos, pattern_str->data); + + /* look for matching pattern variable sequence */ + num_matched_rows = search_str_set(pattern_str->data, str_set, str_set_index); + if (num_matched_rows <= 0) + return -1; + + /* + * We are at the first row in the reduced frame. Save the number of + * matched rows as the number of rows in the reduced frame. + */ + winstate->headpos_in_reduced_frame = original_pos; + winstate->num_rows_in_reduced_frame = num_matched_rows; + + return num_matched_rows; +} + +/* + * search set of encode_str. + * set_size: size of set_str array. + */ +static +int search_str_set(char *pattern, StringInfo *str_set, int set_size) +{ + char *encoded_str = palloc0(set_size+1); + int resultlen = 0; + + search_str_set_recurse(pattern, str_set, set_size, 0, encoded_str, &resultlen); + elog(DEBUG1, "search_str_set returns %d", resultlen); + return resultlen; +} + +static +void search_str_set_recurse(char *pattern, StringInfo *str_set, + int set_size, int set_index, char *encoded_str, int *resultlen) +{ + char *p; + + if (set_index >= set_size) + { + Datum d; + text *res; + char *substr; + + /* + * We first perform pattern matching using regexp_instr, then call + * textregexsubstr to get matched substring to know how log the + * matched string is. That is the number of rows in the reduced window + * frame. The reason why we can't call textregexsubstr is, it error + * out if pattern is not match. + */ + if (DatumGetInt32(DirectFunctionCall2Coll(regexp_instr, DEFAULT_COLLATION_OID, + PointerGetDatum(cstring_to_text(encoded_str)), + PointerGetDatum(cstring_to_text(pattern)))) > 0) + { + d = DirectFunctionCall2Coll(textregexsubstr, + DEFAULT_COLLATION_OID, + PointerGetDatum(cstring_to_text(encoded_str)), + PointerGetDatum(cstring_to_text(pattern))); + if (d != 0) + { + int len; + + res = DatumGetTextPP(d); + substr = text_to_cstring(res); + len = strlen(substr); + if (len > *resultlen) + /* remember the longest match */ + *resultlen = len; + } + } + return; + } + + p = str_set[set_index]->data; + while (*p) + { + encoded_str[set_index] = *p; + p++; + search_str_set_recurse(pattern, str_set, set_size, set_index + 1, encoded_str, resultlen); + } +} + + +/* + * Evaluate expression associated with PATTERN variable vname. + * relpos is relative row position in a frame (starting from 0). + * "quantifier" is the quatifier part of the PATTERN regular expression. + * Currently only '+' is allowed. + * result is out paramater representing the expression evaluation result + * is true of false. + * Return values are: + * >=0: the last match absolute row position + * other wise out of frame. + */ +static +int64 evaluate_pattern(WindowObject winobj, int64 current_pos, + char *vname, StringInfo encoded_str, bool *result) +{ + WindowAggState *winstate = winobj->winstate; + ExprContext *econtext = winstate->ss.ps.ps_ExprContext; + ListCell *lc1, *lc2, *lc3; + ExprState *pat; + Datum eval_result; + bool out_of_frame = false; + bool isnull; + + forthree (lc1, winstate->defineVariableList, lc2, winstate->defineClauseList, lc3, winstate->defineInitial) + { + char initial; + char *name = strVal(lfirst(lc1)); + + elog(DEBUG1, "evaluate_pattern: define variable: %s, pattern variable: %s", name, vname); + + if (strcmp(vname, name)) + continue; + + initial = *(strVal(lfirst(lc3))); + + /* set expression to evaluate */ + pat = lfirst(lc2); + + /* get current, previous and next tuples */ + if (!get_slots(winobj, current_pos)) + { + out_of_frame = true; + } + else + { + /* evaluate the expression */ + eval_result = ExecEvalExpr(pat, econtext, &isnull); + if (isnull) + { + /* expression is NULL */ + elog(DEBUG1, "expression for %s is NULL at row: " INT64_FORMAT, vname, current_pos); + *result = false; + } + else + { + if (!DatumGetBool(eval_result)) + { + /* expression is false */ + elog(DEBUG1, "expression for %s is false at row: " INT64_FORMAT, vname, current_pos); + *result = false; + } + else + { + /* expression is true */ + elog(DEBUG1, "expression for %s is true at row: " INT64_FORMAT, vname, current_pos); + appendStringInfoChar(encoded_str, initial); + *result = true; + } + } + break; + } + + if (out_of_frame) + { + *result = false; + return -1; + } + } + return current_pos; +} + +/* + * Get current, previous and next tuples. + * Returns false if current row is out of partition/full frame. + */ +static +bool get_slots(WindowObject winobj, int64 current_pos) +{ + WindowAggState *winstate = winobj->winstate; + TupleTableSlot *slot; + int ret; + ExprContext *econtext; + + econtext = winstate->ss.ps.ps_ExprContext; + + /* set up current row tuple slot */ + slot = winstate->temp_slot_1; + if (!window_gettupleslot(winobj, current_pos, slot)) + { + elog(DEBUG1, "current row is out of partition at:" INT64_FORMAT, current_pos); + return false; + + ret = row_is_in_frame(winstate, current_pos, slot); + if (ret <= 0) + { + elog(DEBUG1, "current row is out of frame at: " INT64_FORMAT, current_pos); + return false; + } + } + econtext->ecxt_outertuple = slot; + + /* for PREV */ + if (current_pos > 0) + { + slot = winstate->prev_slot; + if (!window_gettupleslot(winobj, current_pos - 1, slot)) + { + elog(DEBUG1, "previous row is out of partition at: " INT64_FORMAT, current_pos - 1); + econtext->ecxt_scantuple = winstate->null_slot; + } + else + { + ret = row_is_in_frame(winstate, current_pos - 1, slot); + if (ret <= 0) + { + elog(DEBUG1, "previous row is out of frame at: " INT64_FORMAT, current_pos - 1); + econtext->ecxt_scantuple = winstate->null_slot; + } + else + { + econtext->ecxt_scantuple = slot; + } + } + } + else + econtext->ecxt_scantuple = winstate->null_slot; + + /* for NEXT */ + slot = winstate->next_slot; + if (!window_gettupleslot(winobj, current_pos + 1, slot)) + { + elog(DEBUG1, "next row is out of partiton at: " INT64_FORMAT, current_pos + 1); + econtext->ecxt_innertuple = winstate->null_slot; + } + else + { + ret = row_is_in_frame(winstate, current_pos + 1, slot); + if (ret <= 0) + { + elog(DEBUG1, "next row is out of frame at: " INT64_FORMAT, current_pos + 1); + econtext->ecxt_innertuple = winstate->null_slot; + } + else + econtext->ecxt_innertuple = slot; + } + return true; +} + +/* + * Return pattern variable initial character + * matching with pattern variable name vname. + * If not found, return 0. + */ +static +char pattern_initial(WindowAggState *winstate, char *vname) +{ + char initial; + char *name; + ListCell *lc1, *lc2; + + forboth (lc1, winstate->defineVariableList, lc2, winstate->defineInitial) + { + name = strVal(lfirst(lc1)); /* DEFINE variable name */ + initial = *(strVal(lfirst(lc2))); /* DEFINE variable initial */ + + + if (!strcmp(name, vname)) + return initial; /* found */ + } + return 0; +} diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c index b87a624fb2..e4cab36ec9 100644 --- a/src/backend/utils/adt/windowfuncs.c +++ b/src/backend/utils/adt/windowfuncs.c @@ -13,6 +13,9 @@ */ #include "postgres.h" +#include "catalog/pg_collation_d.h" +#include "executor/executor.h" +#include "nodes/execnodes.h" #include "nodes/supportnodes.h" #include "utils/builtins.h" #include "windowapi.h" @@ -36,11 +39,19 @@ typedef struct int64 remainder; /* (total rows) % (bucket num) */ } ntile_context; +/* + * rpr process information. + * Used for AFTER MATCH SKIP PAST LAST ROW + */ +typedef struct SkipContext +{ + int64 pos; /* last row absolute position */ +} SkipContext; + static bool rank_up(WindowObject winobj); static Datum leadlag_common(FunctionCallInfo fcinfo, bool forward, bool withoffset, bool withdefault); - /* * utility routine for *_rank functions. */ @@ -673,7 +684,7 @@ window_last_value(PG_FUNCTION_ARGS) bool isnull; result = WinGetFuncArgInFrame(winobj, 0, - 0, WINDOW_SEEK_TAIL, true, + 0, WINDOW_SEEK_TAIL, false, &isnull, NULL); if (isnull) PG_RETURN_NULL(); @@ -713,3 +724,26 @@ window_nth_value(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } + +/* + * prev + * Dummy function to invoke RPR's navigation operator "PREV". + * This is *not* a window function. + */ +Datum +window_prev(PG_FUNCTION_ARGS) +{ + PG_RETURN_DATUM(PG_GETARG_DATUM(0)); +} + +/* + * next + * Dummy function to invoke RPR's navigation operation "NEXT". + * This is *not* a window function. + */ +Datum +window_next(PG_FUNCTION_ARGS) +{ + PG_RETURN_DATUM(PG_GETARG_DATUM(0)); +} + diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 6996073989..fa100b2665 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10397,6 +10397,12 @@ { oid => '3114', descr => 'fetch the Nth row value', proname => 'nth_value', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement int4', prosrc => 'window_nth_value' }, +{ oid => '6122', descr => 'previous value', + proname => 'prev', provolatile => 's', prorettype => 'anyelement', + proargtypes => 'anyelement', prosrc => 'window_prev' }, +{ oid => '6123', descr => 'next value', + proname => 'next', provolatile => 's', prorettype => 'anyelement', + proargtypes => 'anyelement', prosrc => 'window_next' }, # functions for range types { oid => '3832', descr => 'I/O', diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index cb714f4a19..2bd6fcb5e1 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -2519,6 +2519,15 @@ typedef struct WindowAggState int64 groupheadpos; /* current row's peer group head position */ int64 grouptailpos; /* " " " " tail position (group end+1) */ + /* these fields are used in Row pattern recognition: */ + RPSkipTo rpSkipTo; /* Row Pattern Skip To type */ + List *patternVariableList; /* list of row pattern variables names (list of String) */ + List *patternRegexpList; /* list of row pattern regular expressions ('+' or ''. list of String) */ + List *defineVariableList; /* list of row pattern definition variables (list of String) */ + List *defineClauseList; /* expression for row pattern definition + * search conditions ExprState list */ + List *defineInitial; /* list of row pattern definition variable initials (list of String) */ + MemoryContext partcontext; /* context for partition-lifespan data */ MemoryContext aggcontext; /* shared context for aggregate working data */ MemoryContext curaggcontext; /* current aggregate's working data */ @@ -2555,6 +2564,16 @@ typedef struct WindowAggState TupleTableSlot *agg_row_slot; TupleTableSlot *temp_slot_1; TupleTableSlot *temp_slot_2; + + /* temporary slots for RPR */ + TupleTableSlot *prev_slot; /* PREV row navigation operator */ + TupleTableSlot *next_slot; /* NEXT row navigation operator */ + TupleTableSlot *null_slot; /* all NULL slot */ + + /* head of the reduced window frame */ + int64 headpos_in_reduced_frame; + /* number of rows in the reduced window frame */ + int64 num_rows_in_reduced_frame; } WindowAggState; /* ---------------- diff --git a/src/include/windowapi.h b/src/include/windowapi.h index b8c2c565d1..1e292648e9 100644 --- a/src/include/windowapi.h +++ b/src/include/windowapi.h @@ -58,7 +58,15 @@ extern Datum WinGetFuncArgInFrame(WindowObject winobj, int argno, int relpos, int seektype, bool set_mark, bool *isnull, bool *isout); +extern int WinGetSlotInFrame(WindowObject winobj, TupleTableSlot *slot, + int relpos, int seektype, bool set_mark, + bool *isnull, bool *isout); + extern Datum WinGetFuncArgCurrent(WindowObject winobj, int argno, bool *isnull); +extern WindowAggState *WinGetAggState(WindowObject winobj); + +extern bool window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot); + #endif /* WINDOWAPI_H */ -- 2.25.1 ----Next_Part(Wed_Aug__9_17_41_12_2023_134)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v4-0005-Row-pattern-recognition-patch-docs.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH 1/6] Add REPACK command @ 2026-01-12 16:30 Antonin Houska <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Antonin Houska @ 2026-01-12 16:30 UTC (permalink / raw) REPACK absorbs the functionality of VACUUM FULL and CLUSTER in a single command. Because this functionality is completely different from regular VACUUM, having it separate from VACUUM makes it easier for users to understand; as for CLUSTER, the term is heavily overloaded in the IT world and even in Postgres itself, so it's good that we can avoid it. This also adds pg_repackdb, a new utility that can invoke the new commands. This is heavily based on vacuumdb. Author: Antonin Houska <[email protected]> Co-authored-by: =C3=81lvaro Herrera <[email protected]> Reviewed-by: Mihail Nikalayeu <[email protected]> Reviewed-by: Robert Treat <[email protected]> Reviewed-by: Euler Taveira <[email protected]> Reviewed-by: Matheus Alcantara <[email protected]> Reviewed-by: Junwang Zhao <[email protected]> Reviewed-by: jian he <[email protected]> Discussion: https://postgr.es/m/82651.1720540558@antos Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/monitoring.sgml | 223 +++++- doc/src/sgml/ref/allfiles.sgml | 2 + doc/src/sgml/ref/cluster.sgml | 97 +-- doc/src/sgml/ref/clusterdb.sgml | 5 + doc/src/sgml/ref/pg_repackdb.sgml | 488 +++++++++++++ doc/src/sgml/ref/repack.sgml | 328 +++++++++ doc/src/sgml/ref/vacuum.sgml | 33 +- doc/src/sgml/reference.sgml | 2 + src/backend/access/heap/heapam_handler.c | 32 +- src/backend/catalog/index.c | 2 +- src/backend/catalog/system_views.sql | 29 +- src/backend/commands/cluster.c | 842 +++++++++++++++-------- src/backend/commands/vacuum.c | 6 +- src/backend/parser/gram.y | 86 ++- src/backend/tcop/utility.c | 23 +- src/backend/utils/adt/pgstatfuncs.c | 4 +- src/bin/psql/tab-complete.in.c | 42 +- src/bin/scripts/Makefile | 3 + src/bin/scripts/meson.build | 2 + src/bin/scripts/pg_repackdb.c | 240 +++++++ src/bin/scripts/t/103_repackdb.pl | 47 ++ src/bin/scripts/vacuuming.c | 102 ++- src/bin/scripts/vacuuming.h | 3 + src/include/commands/cluster.h | 8 +- src/include/commands/progress.h | 50 +- src/include/nodes/parsenodes.h | 35 +- src/include/parser/kwlist.h | 1 + src/include/tcop/cmdtaglist.h | 1 + src/include/utils/backend_progress.h | 2 +- src/test/regress/expected/cluster.out | 134 +++- src/test/regress/expected/rules.out | 72 +- src/test/regress/sql/cluster.sql | 70 +- src/tools/pgindent/typedefs.list | 2 + 33 files changed, 2480 insertions(+), 536 deletions(-) create mode 100644 doc/src/sgml/ref/pg_repackdb.sgml create mode 100644 doc/src/sgml/ref/repack.sgml create mode 100644 src/bin/scripts/pg_repackdb.c create mode 100644 src/bin/scripts/t/103_repackdb.pl diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 817fd9f4ca7..b07fe3294cd 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -405,6 +405,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss = 11:34 0:00 postgres: ser </entry> </row> =20 + <row> + <entry><structname>pg_stat_progress_repack</structname><indexterm><p= rimary>pg_stat_progress_repack</primary></indexterm></entry> + <entry>One row for each backend running + <command>REPACK</command>, showing current progress. See + <xref linkend=3D"repack-progress-reporting"/>. + </entry> + </row> + <row> <entry><structname>pg_stat_progress_basebackup</structname><indexter= m><primary>pg_stat_progress_basebackup</primary></indexterm></entry> <entry>One row for each WAL sender process streaming a base backup, @@ -5609,7 +5617,8 @@ FROM pg_stat_get_backend_idset() AS backendid; certain commands during command execution. Currently, the only commands which support progress reporting are <command>ANALYZE</command>, <command>CLUSTER</command>, - <command>CREATE INDEX</command>, <command>VACUUM</command>, + <command>CREATE INDEX</command>, <command>REPACK</command>, + <command>VACUUM</command>, <command>COPY</command>, and <xref linkend=3D"protocol-replication-base-backup"/> (i.e., replica= tion command that <xref linkend=3D"app-pgbasebackup"/> issues to take @@ -6093,6 +6102,218 @@ FROM pg_stat_get_backend_idset() AS backendid; </table> </sect2> =20 + <sect2 id=3D"repack-progress-reporting"> + <title>REPACK Progress Reporting</title> + + <indexterm> + <primary>pg_stat_progress_repack</primary> + </indexterm> + + <para> + Whenever <command>REPACK</command> is running, + the <structname>pg_stat_progress_repack</structname> view will contain a + row for each backend that is currently running the command. The tables + below describe the information that will be reported and provide + information about how to interpret it. + </para> + + <table id=3D"pg-stat-progress-repack-view" xreflabel=3D"pg_stat_progress= _repack"> + <title><structname>pg_stat_progress_repack</structname> View</title> + <tgroup cols=3D"1"> + <thead> + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>pid</structfield> <type>integer</type> + </para> + <para> + Process ID of backend. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>datid</structfield> <type>oid</type> + </para> + <para> + OID of the database to which this backend is connected. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>datname</structfield> <type>name</type> + </para> + <para> + Name of the database to which this backend is connected. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>relid</structfield> <type>oid</type> + </para> + <para> + OID of the table being repacked. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>phase</structfield> <type>text</type> + </para> + <para> + Current processing phase. See <xref linkend=3D"repack-phases"/>. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>repack_index_relid</structfield> <type>oid</type> + </para> + <para> + If the table is being scanned using an index, this is the OID of the + index being used; otherwise, it is zero. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>heap_tuples_scanned</structfield> <type>bigint</type> + </para> + <para> + Number of heap tuples scanned. + This counter only advances when the phase is + <literal>seq scanning heap</literal>, + <literal>index scanning heap</literal> + or <literal>writing new heap</literal>. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>heap_tuples_written</structfield> <type>bigint</type> + </para> + <para> + Number of heap tuples written. + This counter only advances when the phase is + <literal>seq scanning heap</literal>, + <literal>index scanning heap</literal> + or <literal>writing new heap</literal>. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>heap_blks_total</structfield> <type>bigint</type> + </para> + <para> + Total number of heap blocks in the table. This number is reported + as of the beginning of <literal>seq scanning heap</literal>. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>heap_blks_scanned</structfield> <type>bigint</type> + </para> + <para> + Number of heap blocks scanned. This counter only advances when the + phase is <literal>seq scanning heap</literal>. + </para></entry> + </row> + + <row> + <entry role=3D"catalog_table_entry"><para role=3D"column_definition"> + <structfield>index_rebuild_count</structfield> <type>bigint</type> + </para> + <para> + Number of indexes rebuilt. This counter only advances when the pha= se + is <literal>rebuilding index</literal>. + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + <table id=3D"repack-phases"> + <title>REPACK Phases</title> + <tgroup cols=3D"2"> + <colspec colname=3D"col1" colwidth=3D"1*"/> + <colspec colname=3D"col2" colwidth=3D"2*"/> + <thead> + <row> + <entry>Phase</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>initializing</literal></entry> + <entry> + The command is preparing to begin scanning the heap. This phase is + expected to be very brief. + </entry> + </row> + <row> + <entry><literal>seq scanning heap</literal></entry> + <entry> + The command is currently scanning the table using a sequential scan. + </entry> + </row> + <row> + <entry><literal>index scanning heap</literal></entry> + <entry> + <command>REPACK</command> is currently scanning the table using an = index scan. + </entry> + </row> + <row> + <entry><literal>sorting tuples</literal></entry> + <entry> + <command>REPACK</command> is currently sorting tuples. + </entry> + </row> + <row> + <entry><literal>writing new heap</literal></entry> + <entry> + <command>REPACK</command> is currently writing the new heap. + </entry> + </row> + <row> + <entry><literal>swapping relation files</literal></entry> + <entry> + The command is currently swapping newly-built files into place. + </entry> + </row> + <row> + <entry><literal>rebuilding index</literal></entry> + <entry> + The command is currently rebuilding an index. + </entry> + </row> + <row> + <entry><literal>performing final cleanup</literal></entry> + <entry> + The command is performing final cleanup. When this phase is + completed, <command>REPACK</command> will end. + </entry> + </row> + </tbody> + </tgroup> + </table> + </sect2> + <sect2 id=3D"copy-progress-reporting"> <title>COPY Progress Reporting</title> =20 diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index e167406c744..5df944d13ca 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -167,6 +167,7 @@ Complete list of usable sgml source files in this direc= tory. <!ENTITY refreshMaterializedView SYSTEM "refresh_materialized_view.sgml"> <!ENTITY reindex SYSTEM "reindex.sgml"> <!ENTITY releaseSavepoint SYSTEM "release_savepoint.sgml"> +<!ENTITY repack SYSTEM "repack.sgml"> <!ENTITY reset SYSTEM "reset.sgml"> <!ENTITY revoke SYSTEM "revoke.sgml"> <!ENTITY rollback SYSTEM "rollback.sgml"> @@ -213,6 +214,7 @@ Complete list of usable sgml source files in this direc= tory. <!ENTITY pgIsready SYSTEM "pg_isready.sgml"> <!ENTITY pgReceivewal SYSTEM "pg_receivewal.sgml"> <!ENTITY pgRecvlogical SYSTEM "pg_recvlogical.sgml"> +<!ENTITY pgRepackdb SYSTEM "pg_repackdb.sgml"> <!ENTITY pgResetwal SYSTEM "pg_resetwal.sgml"> <!ENTITY pgRestore SYSTEM "pg_restore.sgml"> <!ENTITY pgRewind SYSTEM "pg_rewind.sgml"> diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index 0b47460080b..2cda711bc9f 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -33,51 +33,13 @@ CLUSTER [ ( <replaceable class=3D"parameter">option</re= placeable> [, ...] ) ] [ <r <title>Description</title> =20 <para> - <command>CLUSTER</command> instructs <productname>PostgreSQL</productna= me> - to cluster the table specified - by <replaceable class=3D"parameter">table_name</replaceable> - based on the index specified by - <replaceable class=3D"parameter">index_name</replaceable>. The index mu= st - already have been defined on - <replaceable class=3D"parameter">table_name</replaceable>. + The <command>CLUSTER</command> command is equivalent to + <xref linkend=3D"sql-repack"/> with an <literal>USING INDEX</literal> + clause. See there for more details. </para> =20 - <para> - When a table is clustered, it is physically reordered - based on the index information. Clustering is a one-time operation: - when the table is subsequently updated, the changes are - not clustered. That is, no attempt is made to store new or - updated rows according to their index order. (If one wishes, one can - periodically recluster by issuing the command again. Also, setting - the table's <literal>fillfactor</literal> storage parameter to less than - 100% can aid in preserving cluster ordering during updates, since updat= ed - rows are kept on the same page if enough space is available there.) - </para> - - <para> - When a table is clustered, <productname>PostgreSQL</productname> - remembers which index it was clustered by. The form - <command>CLUSTER <replaceable class=3D"parameter">table_name</replaceab= le></command> - reclusters the table using the same index as before. You can also - use the <literal>CLUSTER</literal> or <literal>SET WITHOUT CLUSTER</lit= eral> - forms of <link linkend=3D"sql-altertable"><command>ALTER TABLE</command= ></link> to set the index to be used for - future cluster operations, or to clear any previous setting. - </para> - - <para> - <command>CLUSTER</command> without a - <replaceable class=3D"parameter">table_name</replaceable> reclusters al= l the - previously-clustered tables in the current database that the calling us= er - has privileges for. This form of <command>CLUSTER</command> cannot be - executed inside a transaction block. - </para> +<!-- Do we need to describe exactly which options map to what? They seem = obvious to me. --> =20 - <para> - When a table is being clustered, an <literal>ACCESS - EXCLUSIVE</literal> lock is acquired on it. This prevents any other - database operations (both reads and writes) from operating on the - table until the <command>CLUSTER</command> is finished. - </para> </refsect1> =20 <refsect1> @@ -136,63 +98,12 @@ CLUSTER [ ( <replaceable class=3D"parameter">option</r= eplaceable> [, ...] ) ] [ <r on the table. </para> =20 - <para> - In cases where you are accessing single rows randomly - within a table, the actual order of the data in the - table is unimportant. However, if you tend to access some - data more than others, and there is an index that groups - them together, you will benefit from using <command>CLUSTER</command>. - If you are requesting a range of indexed values from a table, or a - single indexed value that has multiple rows that match, - <command>CLUSTER</command> will help because once the index identifies= the - table page for the first row that matches, all other rows - that match are probably already on the same table page, - and so you save disk accesses and speed up the query. - </para> - - <para> - <command>CLUSTER</command> can re-sort the table using either an index= scan - on the specified index, or (if the index is a b-tree) a sequential - scan followed by sorting. It will attempt to choose the method that - will be faster, based on planner cost parameters and available statist= ical - information. - </para> - <para> While <command>CLUSTER</command> is running, the <xref linkend=3D"guc-search-path"/> is temporarily changed to <literal>pg_ca= talog, pg_temp</literal>. </para> =20 - <para> - When an index scan is used, a temporary copy of the table is created t= hat - contains the table data in the index order. Temporary copies of each - index on the table are created as well. Therefore, you need free spac= e on - disk at least equal to the sum of the table size and the index sizes. - </para> - - <para> - When a sequential scan and sort is used, a temporary sort file is - also created, so that the peak temporary space requirement is as much - as double the table size, plus the index sizes. This method is often - faster than the index scan method, but if the disk space requirement is - intolerable, you can disable this choice by temporarily setting <xref - linkend=3D"guc-enable-sort"/> to <literal>off</literal>. - </para> - - <para> - It is advisable to set <xref linkend=3D"guc-maintenance-work-mem"/> to - a reasonably large value (but not more than the amount of RAM you can - dedicate to the <command>CLUSTER</command> operation) before clusterin= g. - </para> - - <para> - Because the planner records statistics about the ordering of - tables, it is advisable to run <link linkend=3D"sql-analyze"><command>= ANALYZE</command></link> - on the newly clustered table. - Otherwise, the planner might make poor choices of query plans. - </para> - <para> Because <command>CLUSTER</command> remembers which indexes are cluster= ed, one can cluster the tables one wants clustered manually the first time, diff --git a/doc/src/sgml/ref/clusterdb.sgml b/doc/src/sgml/ref/clusterdb.s= gml index 0d2051bf6f1..b50c9581a98 100644 --- a/doc/src/sgml/ref/clusterdb.sgml +++ b/doc/src/sgml/ref/clusterdb.sgml @@ -64,6 +64,11 @@ PostgreSQL documentation this utility and via other methods for accessing the server. </para> =20 + <para> + <application>clusterdb</application> has been superseded by + <application>pg_repackdb</application>. + </para> + </refsect1> =20 =20 diff --git a/doc/src/sgml/ref/pg_repackdb.sgml b/doc/src/sgml/ref/pg_repack= db.sgml new file mode 100644 index 00000000000..a5ea513c6b1 --- /dev/null +++ b/doc/src/sgml/ref/pg_repackdb.sgml @@ -0,0 +1,488 @@ +<!-- +doc/src/sgml/ref/pg_repackdb.sgml +PostgreSQL documentation +--> + +<refentry id=3D"app-pgrepackdb"> + <indexterm zone=3D"app-pgrepackdb"> + <primary>pg_repackdb</primary> + </indexterm> + + <refmeta> + <refentrytitle><application>pg_repackdb</application></refentrytitle> + <manvolnum>1</manvolnum> + <refmiscinfo>Application</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>pg_repackdb</refname> + <refpurpose>repack and analyze a <productname>PostgreSQL</productname> + database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <cmdsynopsis> + <command>pg_repackdb</command> + <arg rep=3D"repeat"><replaceable>connection-option</replaceable></arg> + <arg rep=3D"repeat"><replaceable>option</replaceable></arg> + + <arg choice=3D"plain" rep=3D"repeat"> + <arg choice=3D"opt"> + <group choice=3D"plain"> + <arg choice=3D"plain"><option>-t</option></arg> + <arg choice=3D"plain"><option>--table</option></arg> + </group> + <replaceable>table</replaceable> + <arg choice=3D"opt">( <replaceable class=3D"parameter">column</replac= eable> [,...] )</arg> + </arg> + </arg> + + <arg choice=3D"opt"> + <group choice=3D"plain"> + <arg choice=3D"plain"><replaceable>dbname</replaceable></arg> + <arg choice=3D"plain"><option>-a</option></arg> + <arg choice=3D"plain"><option>--all</option></arg> + </group> + </arg> + </cmdsynopsis> + + <cmdsynopsis> + <command>pg_repackdb</command> + <arg rep=3D"repeat"><replaceable>connection-option</replaceable></arg> + <arg rep=3D"repeat"><replaceable>option</replaceable></arg> + + <arg choice=3D"plain" rep=3D"repeat"> + <arg choice=3D"opt"> + <group choice=3D"plain"> + <arg choice=3D"plain"><option>-n</option></arg> + <arg choice=3D"plain"><option>--schema</option></arg> + </group> + <replaceable>schema</replaceable> + </arg> + </arg> + + <arg choice=3D"opt"> + <group choice=3D"plain"> + <arg choice=3D"plain"><replaceable>dbname</replaceable></arg> + <arg choice=3D"plain"><option>-a</option></arg> + <arg choice=3D"plain"><option>--all</option></arg> + </group> + </arg> + </cmdsynopsis> + + <cmdsynopsis> + <command>pg_repackdb</command> + <arg rep=3D"repeat"><replaceable>connection-option</replaceable></arg> + <arg rep=3D"repeat"><replaceable>option</replaceable></arg> + + <arg choice=3D"plain" rep=3D"repeat"> + <arg choice=3D"opt"> + <group choice=3D"plain"> + <arg choice=3D"plain"><option>-N</option></arg> + <arg choice=3D"plain"><option>--exclude-schema</option></arg> + </group> + <replaceable>schema</replaceable> + </arg> + </arg> + + <arg choice=3D"opt"> + <group choice=3D"plain"> + <arg choice=3D"plain"><replaceable>dbname</replaceable></arg> + <arg choice=3D"plain"><option>-a</option></arg> + <arg choice=3D"plain"><option>--all</option></arg> + </group> + </arg> + </cmdsynopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <application>pg_repackdb</application> is a utility for repacking a + <productname>PostgreSQL</productname> database. + <application>pg_repackdb</application> will also generate internal + statistics used by the <productname>PostgreSQL</productname> query + optimizer. + </para> + + <para> + <application>pg_repackdb</application> is a wrapper around the SQL + command <link linkend=3D"sql-repack"><command>REPACK</command></link>. = There + is no effective difference between repacking and analyzing databases via + this utility and via other methods for accessing the server. + </para> + </refsect1> + + <refsect1> + <title>Options</title> + + <para> + <application>pg_repackdb</application> accepts the following command-l= ine arguments: + <variablelist> + <varlistentry> + <term><option>-a</option></term> + <term><option>--all</option></term> + <listitem> + <para> + Repack all databases. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option><optional>-d</optional> <replaceable class=3D"paramete= r">dbname</replaceable></option></term> + <term><option><optional>--dbname=3D</optional><replaceable class=3D"= parameter">dbname</replaceable></option></term> + <listitem> + <para> + Specifies the name of the database to be repacked or analyzed, + when <option>-a</option>/<option>--all</option> is not used. If t= his + is not specified, the database name is read from the environment + variable <envar>PGDATABASE</envar>. If that is not set, the user = name + specified for the connection is used. + The <replaceable>dbname</replaceable> can be + a <link linkend=3D"libpq-connstring">connection string</link>. If= so, + connection string parameters will override any conflicting command + line options. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-e</option></term> + <term><option>--echo</option></term> + <listitem> + <para> + Echo the commands that <application>pg_repackdb</application> + generates and sends to the server. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--index<optional>=3D<replaceable class=3D"parameter">i= ndex_name</replaceable></optional></option></term> + <listitem> + <para> + Pass the <literal>USING INDEX</literal> clause to <literal>REPACK<= /literal>, + and optionally the index name to specify. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-j <replaceable class=3D"parameter">njobs</replaceable= ></option></term> + <term><option>--jobs=3D<replaceable class=3D"parameter">njobs</repla= ceable></option></term> + <listitem> + <para> + Execute the repack or analyze commands in parallel by running + <replaceable class=3D"parameter">njobs</replaceable> + commands simultaneously. This option may reduce the processing ti= me + but it also increases the load on the database server. + </para> + <para> + <application>pg_repackdb</application> will open + <replaceable class=3D"parameter">njobs</replaceable> connections t= o the + database, so make sure your <xref linkend=3D"guc-max-connections"/> + setting is high enough to accommodate all connections. + </para> + <para> + Note that using this mode might cause deadlock failures if certain + system catalogs are processed in parallel. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-n <replaceable class=3D"parameter">schema</replaceabl= e></option></term> + <term><option>--schema=3D<replaceable class=3D"parameter">schema</re= placeable></option></term> + <listitem> + <para> + Repack or analyze all tables in + <replaceable class=3D"parameter">schema</replaceable> only. Multi= ple + schemas can be repacked by writing multiple <option>-n</option> + switches. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-N <replaceable class=3D"parameter">schema</replaceabl= e></option></term> + <term><option>--exclude-schema=3D<replaceable class=3D"parameter">sc= hema</replaceable></option></term> + <listitem> + <para> + Do not repack or analyze any tables in + <replaceable class=3D"parameter">schema</replaceable>. Multiple s= chemas + can be excluded by writing multiple <option>-N</option> switches. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-q</option></term> + <term><option>--quiet</option></term> + <listitem> + <para> + Do not display progress messages. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-t <replaceable class=3D"parameter">table</replaceable= > [ (<replaceable class=3D"parameter">column</replaceable> [,...]) ]</optio= n></term> + <term><option>--table=3D<replaceable class=3D"parameter">table</repl= aceable> [ (<replaceable class=3D"parameter">column</replaceable> [,...]) ]= </option></term> + <listitem> + <para> + Repack or analyze <replaceable class=3D"parameter">table</replacea= ble> + only. Column names can be specified only in conjunction with + the <option>--analyze</option> option. Multiple tables can be + repacked by writing multiple + <option>-t</option> switches. + </para> + <tip> + <para> + If you specify columns, you probably have to escape the parenthes= es + from the shell. (See examples below.) + </para> + </tip> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-v</option></term> + <term><option>--verbose</option></term> + <listitem> + <para> + Print detailed information during processing. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-V</option></term> + <term><option>--version</option></term> + <listitem> + <para> + Print the <application>pg_repackdb</application> version and exit. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-z</option></term> + <term><option>--analyze</option></term> + <listitem> + <para> + Also calculate statistics for use by the optimizer. If a column n= ame + list is given, only compute statistics for those columns. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-?</option></term> + <term><option>--help</option></term> + <listitem> + <para> + Show help about <application>pg_repackdb</application> command line + arguments, and exit. + </para> + </listitem> + </varlistentry> + + </variablelist> + </para> + + <para> + <application>pg_repackdb</application> also accepts + the following command-line arguments for connection parameters: + <variablelist> + <varlistentry> + <term><option>-h <replaceable class=3D"parameter">host</replaceable>= </option></term> + <term><option>--host=3D<replaceable class=3D"parameter">host</replac= eable></option></term> + <listitem> + <para> + Specifies the host name of the machine on which the server + is running. If the value begins with a slash, it is used + as the directory for the Unix domain socket. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-p <replaceable class=3D"parameter">port</replaceable>= </option></term> + <term><option>--port=3D<replaceable class=3D"parameter">port</replac= eable></option></term> + <listitem> + <para> + Specifies the TCP port or local Unix domain socket file + extension on which the server + is listening for connections. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-U <replaceable class=3D"parameter">username</replacea= ble></option></term> + <term><option>--username=3D<replaceable class=3D"parameter">username= </replaceable></option></term> + <listitem> + <para> + User name to connect as. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-w</option></term> + <term><option>--no-password</option></term> + <listitem> + <para> + Never issue a password prompt. If the server requires + password authentication and a password is not available by + other means such as a <filename>.pgpass</filename> file, the + connection attempt will fail. This option can be useful in + batch jobs and scripts where no user is present to enter a + password. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-W</option></term> + <term><option>--password</option></term> + <listitem> + <para> + Force <application>pg_repackdb</application> to prompt for a + password before connecting to a database. + </para> + + <para> + This option is never essential, since + <application>pg_repackdb</application> will automatically prompt + for a password if the server demands password authentication. + However, <application>pg_repackdb</application> will waste a + connection attempt finding out that the server wants a password. + In some cases it is worth typing <option>-W</option> to avoid the = extra + connection attempt. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--maintenance-db=3D<replaceable class=3D"parameter">db= name</replaceable></option></term> + <listitem> + <para> + When the <option>-a</option>/<option>--all</option> is used, conne= ct + to this database to gather the list of databases to repack. + If not specified, the <literal>postgres</literal> database will be= used, + or if that does not exist, <literal>template1</literal> will be us= ed. + This can be a <link linkend=3D"libpq-connstring">connection + string</link>. If so, connection string parameters will override = any + conflicting command line options. Also, connection string paramet= ers + other than the database name itself will be re-used when connecting + to other databases. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </refsect1> + + + <refsect1> + <title>Environment</title> + + <variablelist> + <varlistentry> + <term><envar>PGDATABASE</envar></term> + <term><envar>PGHOST</envar></term> + <term><envar>PGPORT</envar></term> + <term><envar>PGUSER</envar></term> + + <listitem> + <para> + Default connection parameters + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><envar>PG_COLOR</envar></term> + <listitem> + <para> + Specifies whether to use color in diagnostic messages. Possible valu= es + are <literal>always</literal>, <literal>auto</literal> and + <literal>never</literal>. + </para> + </listitem> + </varlistentry> + </variablelist> + + <para> + This utility, like most other <productname>PostgreSQL</productname> uti= lities, + also uses the environment variables supported by <application>libpq</ap= plication> + (see <xref linkend=3D"libpq-envars"/>). + </para> + + </refsect1> + + + <refsect1> + <title>Diagnostics</title> + + <para> + In case of difficulty, see + <xref linkend=3D"sql-repack"/> and <xref linkend=3D"app-psql"/> for + discussions of potential problems and error messages. + The database server must be running at the + targeted host. Also, any default connection settings and environment + variables used by the <application>libpq</application> front-end + library will apply. + </para> + + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + To repack the database <literal>test</literal>: +<screen> +<prompt>$ </prompt><userinput>pg_repackdb test</userinput> +</screen> + </para> + + <para> + To repack and analyze for the optimizer a database named + <literal>bigdb</literal>: +<screen> +<prompt>$ </prompt><userinput>pg_repackdb --analyze bigdb</userinput> +</screen> + </para> + + <para> + To repack a single table + <literal>foo</literal> in a database named + <literal>xyzzy</literal>, and analyze a single column + <literal>bar</literal> of the table for the optimizer: +<screen> +<prompt>$ </prompt><userinput>pg_repackdb --analyze --verbose --table=3D'f= oo(bar)' xyzzy</userinput> +</screen></para> + + <para> + To repack all tables in the <literal>foo</literal> and <literal>bar</l= iteral> schemas + in a database named <literal>xyzzy</literal>: +<screen> +<prompt>$ </prompt><userinput>pg_repackdb --schema=3D'foo' --schema=3D'bar= ' xyzzy</userinput> +</screen></para> + + + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type=3D"inline"> + <member><xref linkend=3D"sql-repack"/></member> + </simplelist> + </refsect1> + +</refentry> diff --git a/doc/src/sgml/ref/repack.sgml b/doc/src/sgml/ref/repack.sgml new file mode 100644 index 00000000000..61d5c2cdef1 --- /dev/null +++ b/doc/src/sgml/ref/repack.sgml @@ -0,0 +1,328 @@ +<!-- +doc/src/sgml/ref/repack.sgml +PostgreSQL documentation +--> + +<refentry id=3D"sql-repack"> + <indexterm zone=3D"sql-repack"> + <primary>REPACK</primary> + </indexterm> + + <refmeta> + <refentrytitle>REPACK</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>REPACK</refname> + <refpurpose>rewrite a table to reclaim disk space</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +REPACK [ ( <replaceable class=3D"parameter">option</replaceable> [, ...] )= ] [ <replaceable class=3D"parameter">table_and_columns</replaceable> [ USI= NG INDEX [ <replaceable class=3D"parameter">index_name</replaceable> ] ] ] +REPACK [ ( <replaceable class=3D"parameter">option</replaceable> [, ...] )= ] USING INDEX + +<phrase>where <replaceable class=3D"parameter">option</replaceable> can be= one of:</phrase> + + VERBOSE [ <replaceable class=3D"parameter">boolean</replaceable> ] + ANALYZE [ <replaceable class=3D"parameter">boolean</replaceable> ] + +<phrase>and <replaceable class=3D"parameter">table_and_columns</replaceabl= e> is:</phrase> + + <replaceable class=3D"parameter">table_name</replaceable> [ ( <replace= able class=3D"parameter">column_name</replaceable> [, ...] ) ] +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>REPACK</command> reclaims storage occupied by dead + tuples. Unlike <command>VACUUM</command>, it does so by rewriting the + entire contents of the table specified + by <replaceable class=3D"parameter">table_name</replaceable> into a new= disk + file with no extra space (except for the space guaranteed by + the <literal>fillfactor</literal> storage parameter), allowing unused s= pace + to be returned to the operating system. + </para> + + <para> + Without + a <replaceable class=3D"parameter">table_name</replaceable>, <command>R= EPACK</command> + processes every table and materialized view in the current database that + the current user has the <literal>MAINTAIN</literal> privilege on. This + form of <command>REPACK</command> cannot be executed inside a transacti= on + block. + </para> + + <para> + If a <literal>USING INDEX</literal> clause is specified, the rows are + physically reordered based on information from an index. Please see the + notes on clustering below. + </para> + + <para> + When a table is being repacked, an <literal>ACCESS EXCLUSIVE</literal> = lock + is acquired on it. This prevents any other database operations (both re= ads + and writes) from operating on the table until the <command>REPACK</comm= and> + is finished. + </para> + + <refsect2 id=3D"sql-repack-notes-on-clustering" xreflabel=3D"Notes on Cl= ustering"> + <title>Notes on Clustering</title> + + <para> + If the <literal>USING INDEX</literal> clause is specified, the rows in + the table are physically reordered following an index: if an index name + is specified in the command, then that index is used; if no index name + is specified, then the index that has been configured as the index to + cluster on. If no index has been configured in this way, an error is + thrown. The index given in the <literal>USING INDEX</literal> clause + is configured as the index to cluster on, as well as an index given + to the <command>CLUSTER</command> command. An index can be set + manually using <command>ALTER TABLE ... CLUSTER ON</command>, and reset + with <command>ALTER TABLE ... SET WITHOUT CLUSTER</command>. + </para> + + <para> + If no table name is specified in <command>REPACK USING INDEX</command>, + all tables which have a clustering index defined and which the calling + user has privileges for are processed. + </para> + + <para> + Clustering is a one-time operation: when the table is + subsequently updated, the changes are not clustered. That is, no atte= mpt + is made to store new or updated rows according to their index order. = (If + one wishes, one can periodically recluster by issuing the command agai= n. + Also, setting the table's <literal>fillfactor</literal> storage parame= ter + to less than 100% can aid in preserving cluster ordering during update= s, + since updated rows are kept on the same page if enough space is availa= ble + there.) + </para> + + <para> + In cases where you are accessing single rows randomly within a table, = the + actual order of the data in the table is unimportant. However, if you = tend + to access some data more than others, and there is an index that groups + them together, you will benefit from using clustering. If + you are requesting a range of indexed values from a table, or a single + indexed value that has multiple rows that match, + <command>REPACK</command> will help because once the index identifies = the + table page for the first row that matches, all other rows that match a= re + probably already on the same table page, and so you save disk accesses= and + speed up the query. + </para> + + <para> + <command>REPACK</command> can re-sort the table using either an index = scan + on the specified index (if the index is a b-tree), or a sequential scan + followed by sorting. It will attempt to choose the method that will be + faster, based on planner cost parameters and available statistical + information. + </para> + + <para> + Because the planner records statistics about the ordering of tables, i= t is + advisable to + run <link linkend=3D"sql-analyze"><command>ANALYZE</command></link> on= the + newly repacked table. Otherwise, the planner might make poor choices = of + query plans. + </para> + </refsect2> + + <refsect2 id=3D"sql-repack-notes-on-resources" xreflabel=3D"Notes on Res= ources"> + <title>Notes on Resources</title> + + <para> + When an index scan or a sequential scan without sort is used, a tempor= ary + copy of the table is created that contains the table data in the index + order. Temporary copies of each index on the table are created as wel= l. + Therefore, you need free space on disk at least equal to the sum of the + table size and the index sizes. + </para> + + <para> + When a sequential scan and sort is used, a temporary sort file is also + created, so that the peak temporary space requirement is as much as do= uble + the table size, plus the index sizes. This method is often faster than + the index scan method, but if the disk space requirement is intolerabl= e, + you can disable this choice by temporarily setting + <xref linkend=3D"guc-enable-sort"/> to <literal>off</literal>. + </para> + + <para> + It is advisable to set <xref linkend=3D"guc-maintenance-work-mem"/> to= a + reasonably large value (but not more than the amount of RAM you can + dedicate to the <command>REPACK</command> operation) before repacking. + </para> + </refsect2> + + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class=3D"parameter">table_name</replaceable></term> + <listitem> + <para> + The name (possibly schema-qualified) of a table. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class=3D"parameter">column_name</replaceable></term> + <listitem> + <para> + The name of a specific column to analyze. Defaults to all columns. + If a column list is specific, <literal>ANALYZE</literal> must also + be specified. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class=3D"parameter">index_name</replaceable></term> + <listitem> + <para> + The name of an index. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>VERBOSE</literal></term> + <listitem> + <para> + Prints a progress report as each table is repacked + at <literal>INFO</literal> level. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ANALYZE</literal></term> + <term><literal>ANALYSE</literal></term> + <listitem> + <para> + Applies <xref linkend=3D"sql-analyze"/> on the table after repacking= . This is + currently only supported when a single (non-partitioned) table is sp= ecified. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class=3D"parameter">boolean</replaceable></term> + <listitem> + <para> + Specifies whether the selected option should be turned on or off. + You can write <literal>TRUE</literal>, <literal>ON</literal>, or + <literal>1</literal> to enable the option, and <literal>FALSE</liter= al>, + <literal>OFF</literal>, or <literal>0</literal> to disable it. The + <replaceable class=3D"parameter">boolean</replaceable> value can also + be omitted, in which case <literal>TRUE</literal> is assumed. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + To repack a table, one must have the <literal>MAINTAIN</literal> privi= lege + on the table. + </para> + + <para> + While <command>REPACK</command> is running, the <xref + linkend=3D"guc-search-path"/> is temporarily changed to <literal>pg_ca= talog, + pg_temp</literal>. + </para> + + <para> + Each backend running <command>REPACK</command> will report its progress + in the <structname>pg_stat_progress_repack</structname> view. See + <xref linkend=3D"repack-progress-reporting"/> for details. + </para> + + <para> + Repacking a partitioned table repacks each of its partitions. If an in= dex + is specified, each partition is repacked using the partition of that + index. <command>REPACK</command> on a partitioned table cannot be exec= uted + inside a transaction block. + </para> + + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + Repack the table <literal>employees</literal>: +<programlisting> +REPACK employees; +</programlisting> + </para> + + <para> + Repack the table <literal>employees</literal> on the basis of its + index <literal>employees_ind</literal> (Since index is used here, this = is + effectively clustering): +<programlisting> +REPACK employees USING INDEX employees_ind; +</programlisting> + </para> + + <para> + Repack the table <literal>cases</literal> on physical ordering, + running an <command>ANALYZE</command> on the given columns once + repacking is done, showing informational messages: +<programlisting> +REPACK (ANALYZE, VERBOSE) cases (district, case_nr); +</programlisting> + </para> + + <para> + Repack all tables in the database on which you have + the <literal>MAINTAIN</literal> privilege: +<programlisting> +REPACK; +</programlisting> + </para> + + <para> + Repack all tables for which a clustering index has previously been + configured on which you have the <literal>MAINTAIN</literal> privilege, + showing informational messages: +<programlisting> +REPACK (VERBOSE) USING INDEX; +</programlisting> + </para> + + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + There is no <command>REPACK</command> statement in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type=3D"inline"> + <member><xref linkend=3D"app-pgrepackdb"/></member> + <member><xref linkend=3D"repack-progress-reporting"/></member> + </simplelist> + </refsect1> + +</refentry> diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 6d0fdd43cfb..ac5d083d468 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -25,7 +25,6 @@ VACUUM [ ( <replaceable class=3D"parameter">option</repla= ceable> [, ...] ) ] [ <re =20 <phrase>where <replaceable class=3D"parameter">option</replaceable> can be= one of:</phrase> =20 - FULL [ <replaceable class=3D"parameter">boolean</replaceable> ] FREEZE [ <replaceable class=3D"parameter">boolean</replaceable> ] VERBOSE [ <replaceable class=3D"parameter">boolean</replaceable> ] ANALYZE [ <replaceable class=3D"parameter">boolean</replaceable> ] @@ -39,6 +38,7 @@ VACUUM [ ( <replaceable class=3D"parameter">option</repla= ceable> [, ...] ) ] [ <re SKIP_DATABASE_STATS [ <replaceable class=3D"parameter">boolean</replac= eable> ] ONLY_DATABASE_STATS [ <replaceable class=3D"parameter">boolean</replac= eable> ] BUFFER_USAGE_LIMIT <replaceable class=3D"parameter">size</replaceable> + FULL [ <replaceable class=3D"parameter">boolean</replaceable> ] =20 <phrase>and <replaceable class=3D"parameter">table_and_columns</replaceabl= e> is:</phrase> =20 @@ -95,20 +95,6 @@ VACUUM [ ( <replaceable class=3D"parameter">option</repl= aceable> [, ...] ) ] [ <re <title>Parameters</title> =20 <variablelist> - <varlistentry> - <term><literal>FULL</literal></term> - <listitem> - <para> - Selects <quote>full</quote> vacuum, which can reclaim more - space, but takes much longer and exclusively locks the table. - This method also requires extra disk space, since it writes a - new copy of the table and doesn't release the old copy until - the operation is complete. Usually this should only be used when a - significant amount of space needs to be reclaimed from within the ta= ble. - </para> - </listitem> - </varlistentry> - <varlistentry> <term><literal>FREEZE</literal></term> <listitem> @@ -362,6 +348,23 @@ VACUUM [ ( <replaceable class=3D"parameter">option</re= placeable> [, ...] ) ] [ <re </listitem> </varlistentry> =20 + <varlistentry> + <term><literal>FULL</literal></term> + <listitem> + <para> + This option, which is deprecated, makes <command>VACUUM</command> + behave like <command>REPACK</command> without a + <literal>USING INDEX</literal> clause. + This method of compacting the table takes much longer than + <command>VACUUM</command> and exclusively locks the table. + This method also requires extra disk space, since it writes a + new copy of the table and doesn't release the old copy until + the operation is complete. Usually this should only be used when a + significant amount of space needs to be reclaimed from within the ta= ble. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><replaceable class=3D"parameter">boolean</replaceable></term> <listitem> diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 2cf02c37b17..5d9a8a25a02 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -195,6 +195,7 @@ &refreshMaterializedView; &reindex; &releaseSavepoint; + &repack; &reset; &revoke; &rollback; @@ -258,6 +259,7 @@ &pgIsready; &pgReceivewal; &pgRecvlogical; + &pgRepackdb; &pgRestore; &pgVerifyBackup; &psqlRef; diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/= heap/heapam_handler.c index 09a456e9966..778377b9866 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -741,13 +741,13 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Re= lation NewHeap, if (OldIndex !=3D NULL && !use_sort) { const int ci_index[] =3D { - PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_INDEX_RELID + PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_INDEX_RELID }; int64 ci_val[2]; =20 /* Set phase and OIDOldIndex to columns */ - ci_val[0] =3D PROGRESS_CLUSTER_PHASE_INDEX_SCAN_HEAP; + ci_val[0] =3D PROGRESS_REPACK_PHASE_INDEX_SCAN_HEAP; ci_val[1] =3D RelationGetRelid(OldIndex); pgstat_progress_update_multi_param(2, ci_index, ci_val); =20 @@ -759,15 +759,15 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Re= lation NewHeap, else { /* In scan-and-sort mode and also VACUUM FULL, set phase */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_SEQ_SCAN_HEAP); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_SEQ_SCAN_HEAP); =20 tableScan =3D table_beginscan(OldHeap, SnapshotAny, 0, (ScanKey) NULL); heapScan =3D (HeapScanDesc) tableScan; indexScan =3D NULL; =20 /* Set total heap blocks */ - pgstat_progress_update_param(PROGRESS_CLUSTER_TOTAL_HEAP_BLKS, + pgstat_progress_update_param(PROGRESS_REPACK_TOTAL_HEAP_BLKS, heapScan->rs_nblocks); } =20 @@ -809,7 +809,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Rela= tion NewHeap, * is manually updated to the correct value when the table * scan finishes. */ - pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_BLKS_SCANNED, + pgstat_progress_update_param(PROGRESS_REPACK_HEAP_BLKS_SCANNED, heapScan->rs_nblocks); break; } @@ -825,7 +825,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Rela= tion NewHeap, */ if (prev_cblock !=3D heapScan->rs_cblock) { - pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_BLKS_SCANNED, + pgstat_progress_update_param(PROGRESS_REPACK_HEAP_BLKS_SCANNED, (heapScan->rs_cblock + heapScan->rs_nblocks - heapScan->rs_startblock @@ -912,14 +912,14 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Re= lation NewHeap, * In scan-and-sort mode, report increase in number of tuples * scanned */ - pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED, + pgstat_progress_update_param(PROGRESS_REPACK_HEAP_TUPLES_SCANNED, *num_tuples); } else { const int ct_index[] =3D { - PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED, - PROGRESS_CLUSTER_HEAP_TUPLES_WRITTEN + PROGRESS_REPACK_HEAP_TUPLES_SCANNED, + PROGRESS_REPACK_HEAP_TUPLES_WRITTEN }; int64 ct_val[2]; =20 @@ -952,14 +952,14 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Re= lation NewHeap, double n_tuples =3D 0; =20 /* Report that we are now sorting tuples */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_SORT_TUPLES); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_SORT_TUPLES); =20 tuplesort_performsort(tuplesort); =20 /* Report that we are now writing new heap */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_WRITE_NEW_HEAP); =20 for (;;) { @@ -977,7 +977,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Rela= tion NewHeap, values, isnull, rwstate); /* Report n_tuples */ - pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_WRITTEN, + pgstat_progress_update_param(PROGRESS_REPACK_HEAP_TUPLES_WRITTEN, n_tuples); } =20 diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 43de42ce39e..5ee6389d39c 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -4077,7 +4077,7 @@ reindex_relation(const ReindexStmt *stmt, Oid relid, = int flags, Assert(!ReindexIsProcessingIndex(indexOid)); =20 /* Set index rebuild count */ - pgstat_progress_update_param(PROGRESS_CLUSTER_INDEX_REBUILD_COUNT, + pgstat_progress_update_param(PROGRESS_REPACK_INDEX_REBUILD_COUNT, i); i++; } diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/sys= tem_views.sql index 7553f31fef0..3f05ba3083a 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1283,14 +1283,15 @@ CREATE VIEW pg_stat_progress_vacuum AS FROM pg_stat_get_progress_info('VACUUM') AS S LEFT JOIN pg_database D ON S.datid =3D D.oid; =20 -CREATE VIEW pg_stat_progress_cluster AS +CREATE VIEW pg_stat_progress_repack AS SELECT S.pid AS pid, S.datid AS datid, D.datname AS datname, S.relid AS relid, CASE S.param1 WHEN 1 THEN 'CLUSTER' - WHEN 2 THEN 'VACUUM FULL' + WHEN 2 THEN 'REPACK' + WHEN 3 THEN 'VACUUM FULL' END AS command, CASE S.param2 WHEN 0 THEN 'initializing' WHEN 1 THEN 'seq scanning heap' @@ -1301,15 +1302,35 @@ CREATE VIEW pg_stat_progress_cluster AS WHEN 6 THEN 'rebuilding index' WHEN 7 THEN 'performing final cleanup' END AS phase, - CAST(S.param3 AS oid) AS cluster_index_relid, + CAST(S.param3 AS oid) AS repack_index_relid, S.param4 AS heap_tuples_scanned, S.param5 AS heap_tuples_written, S.param6 AS heap_blks_total, S.param7 AS heap_blks_scanned, S.param8 AS index_rebuild_count - FROM pg_stat_get_progress_info('CLUSTER') AS S + FROM pg_stat_get_progress_info('REPACK') AS S LEFT JOIN pg_database D ON S.datid =3D D.oid; =20 +-- This view is as the one above, except for renaming a column and avoiding +-- 'REPACK' as a command name to report. +CREATE VIEW pg_stat_progress_cluster AS + SELECT + pid, + datid, + datname, + relid, + CASE WHEN command IN ('CLUSTER', 'VACUUM FULL') THEN command + WHEN repack_index_relid =3D 0 THEN 'VACUUM FULL' + ELSE 'CLUSTER' END AS command, + phase, + repack_index_relid AS cluster_index_relid, + heap_tuples_scanned, + heap_tuples_written, + heap_blks_total, + heap_blks_scanned, + index_rebuild_count + FROM pg_stat_progress_repack; + CREATE VIEW pg_stat_progress_create_index AS SELECT S.pid AS pid, S.datid AS datid, D.datname AS datname, diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 60a4617a585..e19675a6d05 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -1,7 +1,8 @@ /*------------------------------------------------------------------------- * * cluster.c - * CLUSTER a table on an index. This is now also used for VACUUM FULL. + * CLUSTER a table on an index. This is now also used for VACUUM FULL a= nd + * REPACK. * * There is hardly anything left of Paul Brown's original implementation... * @@ -67,27 +68,35 @@ typedef struct Oid indexOid; } RelToCluster; =20 - -static void cluster_multiple_rels(List *rtcs, ClusterParams *params); +static bool cluster_rel_recheck(RepackCommand cmd, Relation OldHeap, + Oid indexOid, Oid userid, int options); static void rebuild_relation(Relation OldHeap, Relation index, bool verbos= e); static void copy_table_data(Relation NewHeap, Relation OldHeap, Relation O= ldIndex, bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti); -static List *get_tables_to_cluster(MemoryContext cluster_context); -static List *get_tables_to_cluster_partitioned(MemoryContext cluster_conte= xt, - Oid indexOid); -static bool cluster_is_permitted_for_relation(Oid relid, Oid userid); +static List *get_tables_to_repack(RepackCommand cmd, bool usingindex, + MemoryContext permcxt); +static List *get_tables_to_repack_partitioned(RepackCommand cmd, + Oid relid, bool rel_is_index, + MemoryContext permcxt); +static bool cluster_is_permitted_for_relation(RepackCommand cmd, + Oid relid, Oid userid); +static Relation process_single_relation(RepackStmt *stmt, + ClusterParams *params); +static Oid determine_clustered_index(Relation rel, bool usingindex, + const char *indexname); +static const char *RepackCommandAsString(RepackCommand cmd); =20 =20 -/*------------------------------------------------------------------------= --- - * This cluster code allows for clustering multiple tables at once. Because +/* + * The repack code allows for processing multiple tables at once. Because * of this, we cannot just run everything on a single transaction, or we * would be forced to acquire exclusive locks on all the tables being * clustered, simultaneously --- very likely leading to deadlock. * - * To solve this we follow a similar strategy to VACUUM code, - * clustering each relation in a separate transaction. For this to work, - * we need to: + * To solve this we follow a similar strategy to VACUUM code, processing e= ach + * relation in a separate transaction. For this to work, we need to: + * * - provide a separate memory context so that we can pass information in * a way that survives across transactions * - start a new transaction every time a new relation is clustered @@ -98,197 +107,166 @@ static bool cluster_is_permitted_for_relation(Oid re= lid, Oid userid); * * The single-relation case does not have any such overhead. * - * We also allow a relation to be specified without index. In that case, - * the indisclustered bit will be looked up, and an ERROR will be thrown - * if there is no index with the bit set. - *------------------------------------------------------------------------= --- + * We also allow a relation to be repacked following an index, but without + * naming a specific one. In that case, the indisclustered bit will be + * looked up, and an ERROR will be thrown if no so-marked index is found. */ void -cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel) +ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel) { - ListCell *lc; ClusterParams params =3D {0}; - bool verbose =3D false; Relation rel =3D NULL; - Oid indexOid =3D InvalidOid; - MemoryContext cluster_context; + MemoryContext repack_context; List *rtcs; =20 /* Parse option list */ - foreach(lc, stmt->params) + foreach_node(DefElem, opt, stmt->params) { - DefElem *opt =3D (DefElem *) lfirst(lc); - if (strcmp(opt->defname, "verbose") =3D=3D 0) - verbose =3D defGetBoolean(opt); + params.options |=3D defGetBoolean(opt) ? CLUOPT_VERBOSE : 0; + else if (strcmp(opt->defname, "analyze") =3D=3D 0 || + strcmp(opt->defname, "analyse") =3D=3D 0) + params.options |=3D defGetBoolean(opt) ? CLUOPT_ANALYZE : 0; else ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("unrecognized %s option \"%s\"", - "CLUSTER", opt->defname), - parser_errposition(pstate, opt->location))); + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("unrecognized %s option \"%s\"", + RepackCommandAsString(stmt->command), + opt->defname), + parser_errposition(pstate, opt->location)); } =20 - params.options =3D (verbose ? CLUOPT_VERBOSE : 0); - + /* + * If a single relation is specified, process it and we're done ... unless + * the relation is a partitioned table, in which case we fall through. + */ if (stmt->relation !=3D NULL) { - /* This is the single-relation case. */ - Oid tableOid; - - /* - * Find, lock, and check permissions on the table. We obtain - * AccessExclusiveLock right away to avoid lock-upgrade hazard in the - * single-transaction case. - */ - tableOid =3D RangeVarGetRelidExtended(stmt->relation, - AccessExclusiveLock, - 0, - RangeVarCallbackMaintainsTable, - NULL); - rel =3D table_open(tableOid, NoLock); - - /* - * Reject clustering a remote temp table ... their local buffer - * manager is not going to cope. - */ - if (RELATION_IS_OTHER_TEMP(rel)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot cluster temporary tables of other sessions"))); - - if (stmt->indexname =3D=3D NULL) - { - ListCell *index; - - /* We need to find the index that has indisclustered set. */ - foreach(index, RelationGetIndexList(rel)) - { - indexOid =3D lfirst_oid(index); - if (get_index_isclustered(indexOid)) - break; - indexOid =3D InvalidOid; - } - - if (!OidIsValid(indexOid)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("there is no previously clustered index for table \"%s\"", - stmt->relation->relname))); - } - else - { - /* - * The index is expected to be in the same namespace as the - * relation. - */ - indexOid =3D get_relname_relid(stmt->indexname, - rel->rd_rel->relnamespace); - if (!OidIsValid(indexOid)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("index \"%s\" for table \"%s\" does not exist", - stmt->indexname, stmt->relation->relname))); - } - - /* For non-partitioned tables, do what we came here to do. */ - if (rel->rd_rel->relkind !=3D RELKIND_PARTITIONED_TABLE) - { - cluster_rel(rel, indexOid, ¶ms); - /* cluster_rel closes the relation, but keeps lock */ - - return; - } + rel =3D process_single_relation(stmt, ¶ms); + if (rel =3D=3D NULL) + return; /* all done */ } =20 + /* + * Don't allow ANALYZE in the multiple-relation case for now. Maybe we + * can add support for this later. + */ + if (params.options & CLUOPT_ANALYZE) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot %s multiple tables", "REPACK (ANALYZE)")); + /* * By here, we know we are in a multi-table situation. In order to avoid * holding locks for too long, we want to process each table in its own * transaction. This forces us to disallow running inside a user * transaction block. */ - PreventInTransactionBlock(isTopLevel, "CLUSTER"); + PreventInTransactionBlock(isTopLevel, RepackCommandAsString(stmt->command= )); =20 /* Also, we need a memory context to hold our list of relations */ - cluster_context =3D AllocSetContextCreate(PortalContext, - "Cluster", - ALLOCSET_DEFAULT_SIZES); + repack_context =3D AllocSetContextCreate(PortalContext, + "Repack", + ALLOCSET_DEFAULT_SIZES); + + params.options |=3D CLUOPT_RECHECK; =20 /* - * Either we're processing a partitioned table, or we were not given any - * table name at all. In either case, obtain a list of relations to - * process. - * - * In the former case, an index name must have been given, so we don't - * need to recheck its "indisclustered" bit, but we have to check that it - * is an index that we can cluster on. In the latter case, we set the - * option bit to have indisclustered verified. - * - * Rechecking the relation itself is necessary here in all cases. + * If we don't have a relation yet, determine a relation list. If we do, + * then it must be a partitioned table, and we want to process its + * partitions. */ - params.options |=3D CLUOPT_RECHECK; - if (rel !=3D NULL) + if (rel =3D=3D NULL) { - Assert(rel->rd_rel->relkind =3D=3D RELKIND_PARTITIONED_TABLE); - check_index_is_clusterable(rel, indexOid, AccessShareLock); - rtcs =3D get_tables_to_cluster_partitioned(cluster_context, indexOid); - - /* close relation, releasing lock on parent table */ - table_close(rel, AccessExclusiveLock); + Assert(stmt->indexname =3D=3D NULL); + rtcs =3D get_tables_to_repack(stmt->command, stmt->usingindex, + repack_context); + params.options |=3D CLUOPT_RECHECK_ISCLUSTERED; } else { - rtcs =3D get_tables_to_cluster(cluster_context); - params.options |=3D CLUOPT_RECHECK_ISCLUSTERED; - } + Oid relid; + bool rel_is_index; =20 - /* Do the job. */ - cluster_multiple_rels(rtcs, ¶ms); + Assert(rel->rd_rel->relkind =3D=3D RELKIND_PARTITIONED_TABLE); =20 - /* Start a new transaction for the cleanup work. */ - StartTransactionCommand(); + /* + * If USING INDEX was specified, resolve the index name now and pass + * it down. + */ + if (stmt->usingindex) + { + /* + * If no index name was specified when repacking a partitioned + * table, punt for now. Maybe we can improve this later. + */ + if (!stmt->indexname) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("there is no previously clustered index for table \"%s\"", + RelationGetRelationName(rel))); + + relid =3D determine_clustered_index(rel, stmt->usingindex, + stmt->indexname); + if (!OidIsValid(relid)) + elog(ERROR, "unable to determine index to cluster on"); + /* XXX is this the right place for this check? */ + check_index_is_clusterable(rel, relid, AccessExclusiveLock); + rel_is_index =3D true; + } + else + { + relid =3D RelationGetRelid(rel); + rel_is_index =3D false; + } =20 - /* Clean up working storage */ - MemoryContextDelete(cluster_context); -} + rtcs =3D get_tables_to_repack_partitioned(stmt->command, + relid, rel_is_index, + repack_context); =20 -/* - * Given a list of relations to cluster, process each of them in a separate - * transaction. - * - * We expect to be in a transaction at start, but there isn't one when we - * return. - */ -static void -cluster_multiple_rels(List *rtcs, ClusterParams *params) -{ - ListCell *lc; + /* close parent relation, releasing lock on it */ + table_close(rel, AccessExclusiveLock); + rel =3D NULL; + } =20 /* Commit to get out of starting transaction */ PopActiveSnapshot(); CommitTransactionCommand(); =20 /* Cluster the tables, each in a separate transaction */ - foreach(lc, rtcs) + Assert(rel =3D=3D NULL); + foreach_ptr(RelToCluster, rtc, rtcs) { - RelToCluster *rtc =3D (RelToCluster *) lfirst(lc); - Relation rel; - /* Start a new transaction for each relation. */ StartTransactionCommand(); =20 + /* + * Open the target table, coping with the case where it has been + * dropped. + */ + rel =3D try_table_open(rtc->tableOid, AccessExclusiveLock); + if (rel =3D=3D NULL) + { + CommitTransactionCommand(); + continue; + } + /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); =20 - rel =3D table_open(rtc->tableOid, AccessExclusiveLock); - /* Process this table */ - cluster_rel(rel, rtc->indexOid, params); + cluster_rel(stmt->command, rel, rtc->indexOid, ¶ms); /* cluster_rel closes the relation, but keeps lock */ =20 PopActiveSnapshot(); CommitTransactionCommand(); } + + /* Start a new transaction for the cleanup work. */ + StartTransactionCommand(); + + /* Clean up working storage */ + MemoryContextDelete(repack_context); } =20 /* @@ -304,11 +282,14 @@ cluster_multiple_rels(List *rtcs, ClusterParams *para= ms) * them incrementally while we load the table. * * If indexOid is InvalidOid, the table will be rewritten in physical order - * instead of index order. This is the new implementation of VACUUM FULL, - * and error messages should refer to the operation as VACUUM not CLUSTER. + * instead of index order. + * + * 'cmd' indicates which command is being executed, to be used for error + * messages. */ void -cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params) +cluster_rel(RepackCommand cmd, Relation OldHeap, Oid indexOid, + ClusterParams *params) { Oid tableOid =3D RelationGetRelid(OldHeap); Oid save_userid; @@ -323,13 +304,8 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterPar= ams *params) /* Check for user-requested abort. */ CHECK_FOR_INTERRUPTS(); =20 - pgstat_progress_start_command(PROGRESS_COMMAND_CLUSTER, tableOid); - if (OidIsValid(indexOid)) - pgstat_progress_update_param(PROGRESS_CLUSTER_COMMAND, - PROGRESS_CLUSTER_COMMAND_CLUSTER); - else - pgstat_progress_update_param(PROGRESS_CLUSTER_COMMAND, - PROGRESS_CLUSTER_COMMAND_VACUUM_FULL); + pgstat_progress_start_command(PROGRESS_COMMAND_REPACK, tableOid); + pgstat_progress_update_param(PROGRESS_REPACK_COMMAND, cmd); =20 /* * Switch to the table owner's userid, so that any index functions are run @@ -350,86 +326,38 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterPa= rams *params) * *must* skip the one on indisclustered since it would reject an attempt * to cluster a not-previously-clustered index. */ - if (recheck) - { - /* Check that the user still has privileges for the relation */ - if (!cluster_is_permitted_for_relation(tableOid, save_userid)) - { - relation_close(OldHeap, AccessExclusiveLock); - goto out; - } - - /* - * Silently skip a temp table for a remote session. Only doing this - * check in the "recheck" case is appropriate (which currently means - * somebody is executing a database-wide CLUSTER or on a partitioned - * table), because there is another check in cluster() which will stop - * any attempt to cluster remote temp tables by name. There is - * another check in cluster_rel which is redundant, but we leave it - * for extra safety. - */ - if (RELATION_IS_OTHER_TEMP(OldHeap)) - { - relation_close(OldHeap, AccessExclusiveLock); - goto out; - } - - if (OidIsValid(indexOid)) - { - /* - * Check that the index still exists - */ - if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(indexOid))) - { - relation_close(OldHeap, AccessExclusiveLock); - goto out; - } - - /* - * Check that the index is still the one with indisclustered set, - * if needed. - */ - if ((params->options & CLUOPT_RECHECK_ISCLUSTERED) !=3D 0 && - !get_index_isclustered(indexOid)) - { - relation_close(OldHeap, AccessExclusiveLock); - goto out; - } - } - } + if (recheck && + !cluster_rel_recheck(cmd, OldHeap, indexOid, save_userid, + params->options)) + goto out; =20 /* - * We allow VACUUM FULL, but not CLUSTER, on shared catalogs. CLUSTER - * would work in most respects, but the index would only get marked as - * indisclustered in the current database, leading to unexpected behavior - * if CLUSTER were later invoked in another database. + * We allow repacking shared catalogs only when not using an index. It + * would work to use an index in most respects, but the index would only + * get marked as indisclustered in the current database, leading to + * unexpected behavior if CLUSTER were later invoked in another database. */ if (OidIsValid(indexOid) && OldHeap->rd_rel->relisshared) ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot cluster a shared catalog"))); + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot run %s on a shared catalog", + RepackCommandAsString(cmd))); =20 /* * Don't process temp tables of other backends ... their local buffer * manager is not going to cope. */ if (RELATION_IS_OTHER_TEMP(OldHeap)) - { - if (OidIsValid(indexOid)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot cluster temporary tables of other sessions"))); - else - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot vacuum temporary tables of other sessions"))); - } + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot run %s on temporary tables of other sessions", + RepackCommandAsString(cmd))); =20 /* * Also check for active uses of the relation in the current transaction, * including open scans and pending AFTER trigger events. */ - CheckTableNotInUse(OldHeap, OidIsValid(indexOid) ? "CLUSTER" : "VACUUM"); + CheckTableNotInUse(OldHeap, RepackCommandAsString(cmd)); =20 /* Check heap and index are valid to cluster on */ if (OidIsValid(indexOid)) @@ -442,6 +370,24 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterPar= ams *params) else index =3D NULL; =20 + /* + * When allow_system_table_mods is turned off, we disallow repacking a + * catalog on a particular index unless that's already the clustered index + * for that catalog. + * + * XXX We don't check for this in CLUSTER, because it's historically been + * allowed. + */ + if (cmd !=3D REPACK_COMMAND_CLUSTER && + !allowSystemTableMods && OidIsValid(indexOid) && + IsCatalogRelation(OldHeap) && !index->rd_index->indisclustered) + ereport(ERROR, + errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + RelationGetRelationName(OldHeap)), + errdetail("System catalogs can only be clustered by the index they're = already clustered on, if any, unless \"%s\" is enabled.", + "allow_system_table_mods")); + /* * Quietly ignore the request if this is a materialized view which has not * been populated from its query. No harm is done because there is no data @@ -482,6 +428,63 @@ out: pgstat_progress_end_command(); } =20 +/* + * Check if the table (and its index) still meets the requirements of + * cluster_rel(). + */ +static bool +cluster_rel_recheck(RepackCommand cmd, Relation OldHeap, Oid indexOid, + Oid userid, int options) +{ + Oid tableOid =3D RelationGetRelid(OldHeap); + + /* Check that the user still has privileges for the relation */ + if (!cluster_is_permitted_for_relation(cmd, tableOid, userid)) + { + relation_close(OldHeap, AccessExclusiveLock); + return false; + } + + /* + * Silently skip a temp table for a remote session. Only doing this check + * in the "recheck" case is appropriate (which currently means somebody is + * executing a database-wide CLUSTER or on a partitioned table), because + * there is another check in cluster() which will stop any attempt to + * cluster remote temp tables by name. There is another check in + * cluster_rel which is redundant, but we leave it for extra safety. + */ + if (RELATION_IS_OTHER_TEMP(OldHeap)) + { + relation_close(OldHeap, AccessExclusiveLock); + return false; + } + + if (OidIsValid(indexOid)) + { + /* + * Check that the index still exists + */ + if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(indexOid))) + { + relation_close(OldHeap, AccessExclusiveLock); + return false; + } + + /* + * Check that the index is still the one with indisclustered set, if + * needed. + */ + if ((options & CLUOPT_RECHECK_ISCLUSTERED) !=3D 0 && + !get_index_isclustered(indexOid)) + { + relation_close(OldHeap, AccessExclusiveLock); + return false; + } + } + + return true; +} + /* * Verify that the specified heap and index are valid to cluster on * @@ -642,8 +645,8 @@ rebuild_relation(Relation OldHeap, Relation index, bool= verbose) Assert(CheckRelationLockedByMe(OldHeap, AccessExclusiveLock, false) && (index =3D=3D NULL || CheckRelationLockedByMe(index, AccessExclusiveL= ock, false))); =20 - if (index) - /* Mark the correct index as clustered */ + /* for CLUSTER or REPACK USING INDEX, mark the index as the one to use */ + if (index !=3D NULL) mark_index_clustered(OldHeap, RelationGetRelid(index), true); =20 /* Remember info about rel before closing OldHeap */ @@ -958,20 +961,20 @@ copy_table_data(Relation NewHeap, Relation OldHeap, R= elation OldIndex, bool verb /* Log what we're doing */ if (OldIndex !=3D NULL && !use_sort) ereport(elevel, - (errmsg("clustering \"%s.%s\" using index scan on \"%s\"", - nspname, - RelationGetRelationName(OldHeap), - RelationGetRelationName(OldIndex)))); + errmsg("repacking \"%s.%s\" using index scan on \"%s\"", + nspname, + RelationGetRelationName(OldHeap), + RelationGetRelationName(OldIndex))); else if (use_sort) ereport(elevel, - (errmsg("clustering \"%s.%s\" using sequential scan and sort", - nspname, - RelationGetRelationName(OldHeap)))); + errmsg("repacking \"%s.%s\" using sequential scan and sort", + nspname, + RelationGetRelationName(OldHeap))); else ereport(elevel, - (errmsg("vacuuming \"%s.%s\"", - nspname, - RelationGetRelationName(OldHeap)))); + errmsg("repacking \"%s.%s\" in physical order", + nspname, + RelationGetRelationName(OldHeap))); =20 /* * Hand off the actual copying to AM specific function, the generic code @@ -1458,8 +1461,8 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, int i; =20 /* Report that we are now swapping relation files */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_SWAP_REL_FILES); =20 /* Zero out possible results from swapped_relation_files */ memset(mapped_tables, 0, sizeof(mapped_tables)); @@ -1509,14 +1512,14 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, reindex_flags |=3D REINDEX_REL_FORCE_INDEXES_PERMANENT; =20 /* Report that we are now reindexing relations */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_REBUILD_INDEX); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_REBUILD_INDEX); =20 reindex_relation(NULL, OIDOldHeap, reindex_flags, &reindex_params); =20 /* Report that we are now doing clean up */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_FINAL_CLEANUP); =20 /* * If the relation being rebuilt is pg_class, swap_relation_files() @@ -1632,106 +1635,191 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, } } =20 - /* - * Get a list of tables that the current user has privileges on and - * have indisclustered set. Return the list in a List * of RelToCluster - * (stored in the specified memory context), each one giving the tableOid - * and the indexOid on which the table is already clustered. + * Determine which relations to process, when REPACK/CLUSTER is called + * without specifying a table name. The exact process depends on whether + * USING INDEX was given or not, and in any case we only return tables and + * materialized views that the current user has privileges to repack/clust= er. + * + * If USING INDEX was given, we scan pg_index to find those that have + * indisclustered set; if it was not given, scan pg_class and return all + * tables. + * + * Return it as a list of RelToCluster in the given memory context. */ static List * -get_tables_to_cluster(MemoryContext cluster_context) +get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext per= mcxt) { - Relation indRelation; + Relation catalog; TableScanDesc scan; - ScanKeyData entry; - HeapTuple indexTuple; - Form_pg_index index; - MemoryContext old_context; + HeapTuple tuple; List *rtcs =3D NIL; =20 - /* - * Get all indexes that have indisclustered set and that the current user - * has the appropriate privileges for. - */ - indRelation =3D table_open(IndexRelationId, AccessShareLock); - ScanKeyInit(&entry, - Anum_pg_index_indisclustered, - BTEqualStrategyNumber, F_BOOLEQ, - BoolGetDatum(true)); - scan =3D table_beginscan_catalog(indRelation, 1, &entry); - while ((indexTuple =3D heap_getnext(scan, ForwardScanDirection)) !=3D NUL= L) + if (usingindex) { - RelToCluster *rtc; + ScanKeyData entry; + + catalog =3D table_open(IndexRelationId, AccessShareLock); + ScanKeyInit(&entry, + Anum_pg_index_indisclustered, + BTEqualStrategyNumber, F_BOOLEQ, + BoolGetDatum(true)); + scan =3D table_beginscan_catalog(catalog, 1, &entry); + while ((tuple =3D heap_getnext(scan, ForwardScanDirection)) !=3D NULL) + { + RelToCluster *rtc; + Form_pg_index index; + MemoryContext oldcxt; =20 - index =3D (Form_pg_index) GETSTRUCT(indexTuple); + index =3D (Form_pg_index) GETSTRUCT(tuple); =20 - if (!cluster_is_permitted_for_relation(index->indrelid, GetUserId())) - continue; + /* + * Try to obtain a light lock on the index's table, to ensure it + * doesn't go away while we collect the list. If we cannot, just + * disregard it. + */ + if (!ConditionalLockRelationOid(index->indrelid, AccessShareLock)) + continue; =20 - /* Use a permanent memory context for the result list */ - old_context =3D MemoryContextSwitchTo(cluster_context); + /* Verify that the table still exists */ + if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(index->indrelid))) + { + /* Release useless lock */ + UnlockRelationOid(index->indrelid, AccessShareLock); + continue; + } =20 - rtc =3D palloc_object(RelToCluster); - rtc->tableOid =3D index->indrelid; - rtc->indexOid =3D index->indexrelid; - rtcs =3D lappend(rtcs, rtc); + if (!cluster_is_permitted_for_relation(cmd, index->indrelid, + GetUserId())) + continue; + + /* Use a permanent memory context for the result list */ + oldcxt =3D MemoryContextSwitchTo(permcxt); + rtc =3D palloc_object(RelToCluster); + rtc->tableOid =3D index->indrelid; + rtc->indexOid =3D index->indexrelid; + rtcs =3D lappend(rtcs, rtc); + MemoryContextSwitchTo(oldcxt); + } + } + else + { + catalog =3D table_open(RelationRelationId, AccessShareLock); + scan =3D table_beginscan_catalog(catalog, 0, NULL); + + while ((tuple =3D heap_getnext(scan, ForwardScanDirection)) !=3D NULL) + { + RelToCluster *rtc; + Form_pg_class class; + MemoryContext oldcxt; + + class =3D (Form_pg_class) GETSTRUCT(tuple); + + /* + * Try to obtain a light lock on the table, to ensure it doesn't + * go away while we collect the list. If we cannot, just + * disregard the table. + */ + if (!ConditionalLockRelationOid(class->oid, AccessShareLock)) + continue; =20 - MemoryContextSwitchTo(old_context); + /* Verify that the table still exists */ + if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(class->oid))) + { + /* Release useless lock */ + UnlockRelationOid(class->oid, AccessShareLock); + continue; + } + + /* Can only process plain tables and matviews */ + if (class->relkind !=3D RELKIND_RELATION && + class->relkind !=3D RELKIND_MATVIEW) + continue; + + /* noisily skip rels which the user can't process */ + if (!cluster_is_permitted_for_relation(cmd, class->oid, + GetUserId())) + continue; + + /* Use a permanent memory context for the result list */ + oldcxt =3D MemoryContextSwitchTo(permcxt); + rtc =3D palloc_object(RelToCluster); + rtc->tableOid =3D class->oid; + rtc->indexOid =3D InvalidOid; + rtcs =3D lappend(rtcs, rtc); + MemoryContextSwitchTo(oldcxt); + } } - table_endscan(scan); =20 - relation_close(indRelation, AccessShareLock); + table_endscan(scan); + relation_close(catalog, AccessShareLock); =20 return rtcs; } =20 /* - * Given an index on a partitioned table, return a list of RelToCluster for + * Given a partitioned table or its index, return a list of RelToCluster f= or * all the children leaves tables/indexes. * * Like expand_vacuum_rel, but here caller must hold AccessExclusiveLock * on the table containing the index. + * + * 'rel_is_index' tells whether 'relid' is that of an index (true) or of t= he + * owning relation. */ static List * -get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid index= Oid) +get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid, + bool rel_is_index, MemoryContext permcxt) { List *inhoids; - ListCell *lc; List *rtcs =3D NIL; - MemoryContext old_context; - - /* Do not lock the children until they're processed */ - inhoids =3D find_all_inheritors(indexOid, NoLock, NULL); =20 - foreach(lc, inhoids) + /* + * Do not lock the children until they're processed. Note that we do hold + * a lock on the parent partitioned table. + */ + inhoids =3D find_all_inheritors(relid, NoLock, NULL); + foreach_oid(child_oid, inhoids) { - Oid indexrelid =3D lfirst_oid(lc); - Oid relid =3D IndexGetRelation(indexrelid, false); + Oid table_oid, + index_oid; RelToCluster *rtc; + MemoryContext oldcxt; =20 - /* consider only leaf indexes */ - if (get_rel_relkind(indexrelid) !=3D RELKIND_INDEX) - continue; + if (rel_is_index) + { + /* consider only leaf indexes */ + if (get_rel_relkind(child_oid) !=3D RELKIND_INDEX) + continue; + + table_oid =3D IndexGetRelation(child_oid, false); + index_oid =3D child_oid; + } + else + { + /* consider only leaf relations */ + if (get_rel_relkind(child_oid) !=3D RELKIND_RELATION) + continue; + + table_oid =3D child_oid; + index_oid =3D InvalidOid; + } =20 /* * It's possible that the user does not have privileges to CLUSTER the - * leaf partition despite having such privileges on the partitioned - * table. We skip any partitions which the user is not permitted to - * CLUSTER. + * leaf partition despite having them on the partitioned table. Skip + * if so. */ - if (!cluster_is_permitted_for_relation(relid, GetUserId())) + if (!cluster_is_permitted_for_relation(cmd, table_oid, GetUserId())) continue; =20 /* Use a permanent memory context for the result list */ - old_context =3D MemoryContextSwitchTo(cluster_context); - + oldcxt =3D MemoryContextSwitchTo(permcxt); rtc =3D palloc_object(RelToCluster); - rtc->tableOid =3D relid; - rtc->indexOid =3D indexrelid; + rtc->tableOid =3D table_oid; + rtc->indexOid =3D index_oid; rtcs =3D lappend(rtcs, rtc); - - MemoryContextSwitchTo(old_context); + MemoryContextSwitchTo(oldcxt); } =20 return rtcs; @@ -1742,13 +1830,167 @@ get_tables_to_cluster_partitioned(MemoryContext cl= uster_context, Oid indexOid) * function emits a WARNING. */ static bool -cluster_is_permitted_for_relation(Oid relid, Oid userid) +cluster_is_permitted_for_relation(RepackCommand cmd, Oid relid, Oid userid) { + Assert(cmd =3D=3D REPACK_COMMAND_CLUSTER || cmd =3D=3D REPACK_COMMAND_REP= ACK); + if (pg_class_aclcheck(relid, userid, ACL_MAINTAIN) =3D=3D ACLCHECK_OK) return true; =20 ereport(WARNING, - (errmsg("permission denied to cluster \"%s\", skipping it", - get_rel_name(relid)))); + errmsg("permission denied to execute %s on \"%s\", skipping it", + RepackCommandAsString(cmd), + get_rel_name(relid))); + return false; } + + +/* + * Given a RepackStmt with an indicated relation name, resolve the relation + * name, obtain lock on it, then determine what to do based on the relation + * type: if it's table and not partitioned, repack it as indicated (using = an + * existing clustered index, or following the given one), and return NULL. + * + * On the other hand, if the table is partitioned, do nothing further and + * instead return the opened and locked relcache entry, so that caller can + * process the partitions using the multiple-table handling code. In this + * case, if an index name is given, it's up to the caller to resolve it. + */ +static Relation +process_single_relation(RepackStmt *stmt, ClusterParams *params) +{ + Relation rel; + Oid tableOid; + + Assert(stmt->relation !=3D NULL); + Assert(stmt->command =3D=3D REPACK_COMMAND_CLUSTER || + stmt->command =3D=3D REPACK_COMMAND_REPACK); + + /* + * Find, lock, and check permissions on the table. We obtain + * AccessExclusiveLock right away to avoid lock-upgrade hazard in the + * single-transaction case. + */ + tableOid =3D RangeVarGetRelidExtended(stmt->relation->relation, + AccessExclusiveLock, + 0, + RangeVarCallbackMaintainsTable, + NULL); + rel =3D table_open(tableOid, NoLock); + + /* + * Reject clustering a remote temp table ... their local buffer manager is + * not going to cope. + */ + if (RELATION_IS_OTHER_TEMP(rel)) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot execute %s on temporary tables of other sessions", + RepackCommandAsString(stmt->command))); + + /* + * Make sure ANALYZE is specified if a column list is present. + */ + if ((params->options & CLUOPT_ANALYZE) =3D=3D 0 && stmt->relation->va_col= s !=3D NIL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ANALYZE option must be specified when a column list is provide= d")); + + /* + * For partitioned tables, let caller handle this. Otherwise, process it + * here and we're done. + */ + if (rel->rd_rel->relkind =3D=3D RELKIND_PARTITIONED_TABLE) + return rel; + else + { + Oid indexOid; + + indexOid =3D determine_clustered_index(rel, stmt->usingindex, + stmt->indexname); + if (OidIsValid(indexOid)) + check_index_is_clusterable(rel, indexOid, AccessExclusiveLock); + cluster_rel(stmt->command, rel, indexOid, params); + + /* Do an analyze, if requested */ + if (params->options & CLUOPT_ANALYZE) + { + VacuumParams vac_params =3D {0}; + + vac_params.options |=3D VACOPT_ANALYZE; + if (params->options & CLUOPT_VERBOSE) + vac_params.options |=3D VACOPT_VERBOSE; + analyze_rel(tableOid, NULL, vac_params, + stmt->relation->va_cols, true, NULL); + } + + return NULL; + } +} + +/* + * Given a relation and the usingindex/indexname options in a + * REPACK USING INDEX or CLUSTER command, return the OID of the + * index to use for clustering the table. + * + * Caller must hold lock on the relation so that the set of indexes + * doesn't change, and must call check_index_is_clusterable. + */ +static Oid +determine_clustered_index(Relation rel, bool usingindex, const char *index= name) +{ + Oid indexOid; + + if (indexname =3D=3D NULL && usingindex) + { + /* + * If USING INDEX with no name is given, find a clustered index, or + * error out if none. + */ + indexOid =3D InvalidOid; + foreach_oid(idxoid, RelationGetIndexList(rel)) + { + if (get_index_isclustered(idxoid)) + { + indexOid =3D idxoid; + break; + } + } + + if (!OidIsValid(indexOid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("there is no previously clustered index for table \"%s\"", + RelationGetRelationName(rel))); + } + else if (indexname !=3D NULL) + { + /* An index was specified; obtain its OID. */ + indexOid =3D get_relname_relid(indexname, rel->rd_rel->relnamespace); + if (!OidIsValid(indexOid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("index \"%s\" for table \"%s\" does not exist", + indexname, RelationGetRelationName(rel))); + } + else + indexOid =3D InvalidOid; + + return indexOid; +} + +static const char * +RepackCommandAsString(RepackCommand cmd) +{ + switch (cmd) + { + case REPACK_COMMAND_REPACK: + return "REPACK"; + case REPACK_COMMAND_VACUUMFULL: + return "VACUUM"; + case REPACK_COMMAND_CLUSTER: + return "CLUSTER"; + } + return "???"; /* keep compiler quiet */ +} diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index aa4fbec143f..c363467a9cc 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -351,7 +351,6 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, boo= l isTopLevel) } } =20 - /* * Sanity check DISABLE_PAGE_SKIPPING option. */ @@ -2289,8 +2288,9 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParam= s params, if ((params.options & VACOPT_VERBOSE) !=3D 0) cluster_params.options |=3D CLUOPT_VERBOSE; =20 - /* VACUUM FULL is now a variant of CLUSTER; see cluster.c */ - cluster_rel(rel, InvalidOid, &cluster_params); + /* VACUUM FULL is a variant of REPACK; see cluster.c */ + cluster_rel(REPACK_COMMAND_VACUUMFULL, rel, InvalidOid, + &cluster_params); /* cluster_rel closes the relation, but keeps lock */ =20 rel =3D NULL; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 713ee5c10a2..54d37c10447 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -287,7 +287,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); AlterCompositeTypeStmt AlterUserMappingStmt AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt AlterStatsStmt AlterDefaultPrivilegesStmt DefACLAction - AnalyzeStmt CallStmt ClosePortalStmt ClusterStmt CommentStmt + AnalyzeStmt CallStmt ClosePortalStmt CommentStmt ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt CreateDomainStmt CreateExtensionStmt CreateGroupStmt CreateOpClassStmt CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt @@ -304,7 +304,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt ListenStmt LoadStmt LockStmt MergeStmt NotifyStmt ExplainableStmt Prepar= ableStmt CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt - RemoveFuncStmt RemoveOperStmt RenameStmt ReturnStmt RevokeStmt RevokeRol= eStmt + RemoveFuncStmt RemoveOperStmt RenameStmt RepackStmt ReturnStmt RevokeStm= t RevokeRoleStmt RuleActionStmt RuleActionStmtOrEmpty RuleStmt SecLabelStmt SelectStmt TransactionStmt TransactionStmtLegacy TruncateSt= mt UnlistenStmt UpdateStmt VacuumStmt @@ -323,7 +323,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); =20 %type <str> opt_single_name %type <list> opt_qualified_name -%type <boolean> opt_concurrently +%type <boolean> opt_concurrently opt_usingindex %type <dbehavior> opt_drop_behavior %type <list> opt_utility_option_list %type <list> opt_wait_with_clause @@ -773,7 +773,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); QUOTE QUOTES =20 RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING - REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA + REFRESH REINDEX RELATIVE_P RELEASE RENAME REPACK REPEATABLE REPLACE REPLI= CA RESET RESPECT_P RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT RO= LE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE =20 @@ -1035,7 +1035,6 @@ stmt: | CallStmt | CheckPointStmt | ClosePortalStmt - | ClusterStmt | CommentStmt | ConstraintsSetStmt | CopyStmt @@ -1109,6 +1108,7 @@ stmt: | RemoveFuncStmt | RemoveOperStmt | RenameStmt + | RepackStmt | RevokeStmt | RevokeRoleStmt | RuleStmt @@ -1146,6 +1146,11 @@ opt_concurrently: | /*EMPTY*/ { $$ =3D false; } ; =20 +opt_usingindex: + USING INDEX { $$ =3D true; } + | /* EMPTY */ { $$ =3D false; } + ; + opt_drop_behavior: CASCADE { $$ =3D DROP_CASCADE; } | RESTRICT { $$ =3D DROP_RESTRICT; } @@ -12036,38 +12041,82 @@ CreateConversionStmt: /*************************************************************************= **** * * QUERY: + * REPACK [ (options) ] [ <qualified_name> [ <name_list> ] [ USING INDE= X <index_name> ] ] + * + * obsolete variants: * CLUSTER (options) [ <qualified_name> [ USING <index_name> ] ] * CLUSTER [VERBOSE] [ <qualified_name> [ USING <index_name> ] ] * CLUSTER [VERBOSE] <index_name> ON <qualified_name> (for pre-8.3) * *************************************************************************= ****/ =20 -ClusterStmt: - CLUSTER '(' utility_option_list ')' qualified_name cluster_index_specif= ication +RepackStmt: + REPACK opt_utility_option_list vacuum_relation USING INDEX name { - ClusterStmt *n =3D makeNode(ClusterStmt); + RepackStmt *n =3D makeNode(RepackStmt); =20 - n->relation =3D $5; + n->command =3D REPACK_COMMAND_REPACK; + n->relation =3D (VacuumRelation *) $3; n->indexname =3D $6; + n->usingindex =3D true; + n->params =3D $2; + $$ =3D (Node *) n; + } + | REPACK opt_utility_option_list vacuum_relation opt_usingindex + { + RepackStmt *n =3D makeNode(RepackStmt); + + n->command =3D REPACK_COMMAND_REPACK; + n->relation =3D (VacuumRelation *) $3; + n->indexname =3D NULL; + n->usingindex =3D $4; + n->params =3D $2; + $$ =3D (Node *) n; + } + | REPACK opt_utility_option_list opt_usingindex + { + RepackStmt *n =3D makeNode(RepackStmt); + + n->command =3D REPACK_COMMAND_REPACK; + n->relation =3D NULL; + n->indexname =3D NULL; + n->usingindex =3D $3; + n->params =3D $2; + $$ =3D (Node *) n; + } + | CLUSTER '(' utility_option_list ')' qualified_name cluster_index_spec= ification + { + RepackStmt *n =3D makeNode(RepackStmt); + + n->command =3D REPACK_COMMAND_CLUSTER; + n->relation =3D makeNode(VacuumRelation); + n->relation->relation =3D $5; + n->indexname =3D $6; + n->usingindex =3D true; n->params =3D $3; $$ =3D (Node *) n; } | CLUSTER opt_utility_option_list { - ClusterStmt *n =3D makeNode(ClusterStmt); + RepackStmt *n =3D makeNode(RepackStmt); =20 + n->command =3D REPACK_COMMAND_CLUSTER; n->relation =3D NULL; n->indexname =3D NULL; + n->usingindex =3D true; n->params =3D $2; $$ =3D (Node *) n; } /* unparenthesized VERBOSE kept for pre-14 compatibility */ | CLUSTER opt_verbose qualified_name cluster_index_specification { - ClusterStmt *n =3D makeNode(ClusterStmt); + RepackStmt *n =3D makeNode(RepackStmt); =20 - n->relation =3D $3; + n->command =3D REPACK_COMMAND_CLUSTER; + n->relation =3D makeNode(VacuumRelation); + n->relation->relation =3D $3; n->indexname =3D $4; + n->usingindex =3D true; if ($2) n->params =3D list_make1(makeDefElem("verbose", NULL, @2)); $$ =3D (Node *) n; @@ -12075,20 +12124,25 @@ ClusterStmt: /* unparenthesized VERBOSE kept for pre-17 compatibility */ | CLUSTER VERBOSE { - ClusterStmt *n =3D makeNode(ClusterStmt); + RepackStmt *n =3D makeNode(RepackStmt); =20 + n->command =3D REPACK_COMMAND_CLUSTER; n->relation =3D NULL; n->indexname =3D NULL; + n->usingindex =3D true; n->params =3D list_make1(makeDefElem("verbose", NULL, @2)); $$ =3D (Node *) n; } /* kept for pre-8.3 compatibility */ | CLUSTER opt_verbose name ON qualified_name { - ClusterStmt *n =3D makeNode(ClusterStmt); + RepackStmt *n =3D makeNode(RepackStmt); =20 - n->relation =3D $5; + n->command =3D REPACK_COMMAND_CLUSTER; + n->relation =3D makeNode(VacuumRelation); + n->relation->relation =3D $5; n->indexname =3D $3; + n->usingindex =3D true; if ($2) n->params =3D list_make1(makeDefElem("verbose", NULL, @2)); $$ =3D (Node *) n; @@ -18127,6 +18181,7 @@ unreserved_keyword: | RELATIVE_P | RELEASE | RENAME + | REPACK | REPEATABLE | REPLACE | REPLICA @@ -18764,6 +18819,7 @@ bare_label_keyword: | RELATIVE_P | RELEASE | RENAME + | REPACK | REPEATABLE | REPLACE | REPLICA diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 34dd6e18df5..ca737b05115 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -279,9 +279,9 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree) return COMMAND_OK_IN_RECOVERY | COMMAND_OK_IN_READ_ONLY_TXN; } =20 - case T_ClusterStmt: case T_ReindexStmt: case T_VacuumStmt: + case T_RepackStmt: { /* * These commands write WAL, so they're not strictly @@ -856,14 +856,14 @@ standard_ProcessUtility(PlannedStmt *pstmt, ExecuteCallStmt(castNode(CallStmt, parsetree), params, isAtomicContext,= dest); break; =20 - case T_ClusterStmt: - cluster(pstate, (ClusterStmt *) parsetree, isTopLevel); - break; - case T_VacuumStmt: ExecVacuum(pstate, (VacuumStmt *) parsetree, isTopLevel); break; =20 + case T_RepackStmt: + ExecRepack(pstate, (RepackStmt *) parsetree, isTopLevel); + break; + case T_ExplainStmt: ExplainQuery(pstate, (ExplainStmt *) parsetree, params, dest); break; @@ -2865,10 +2865,6 @@ CreateCommandTag(Node *parsetree) tag =3D CMDTAG_CALL; break; =20 - case T_ClusterStmt: - tag =3D CMDTAG_CLUSTER; - break; - case T_VacuumStmt: if (((VacuumStmt *) parsetree)->is_vacuumcmd) tag =3D CMDTAG_VACUUM; @@ -2876,6 +2872,13 @@ CreateCommandTag(Node *parsetree) tag =3D CMDTAG_ANALYZE; break; =20 + case T_RepackStmt: + if (((RepackStmt *) parsetree)->command =3D=3D REPACK_COMMAND_CLUSTER) + tag =3D CMDTAG_CLUSTER; + else + tag =3D CMDTAG_REPACK; + break; + case T_ExplainStmt: tag =3D CMDTAG_EXPLAIN; break; @@ -3517,7 +3520,7 @@ GetCommandLogLevel(Node *parsetree) lev =3D LOGSTMT_ALL; break; =20 - case T_ClusterStmt: + case T_RepackStmt: lev =3D LOGSTMT_DDL; break; =20 diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pg= statfuncs.c index 73ca0bb0b7f..55a69bf681d 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -287,8 +287,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS) cmdtype =3D PROGRESS_COMMAND_VACUUM; else if (pg_strcasecmp(cmd, "ANALYZE") =3D=3D 0) cmdtype =3D PROGRESS_COMMAND_ANALYZE; - else if (pg_strcasecmp(cmd, "CLUSTER") =3D=3D 0) - cmdtype =3D PROGRESS_COMMAND_CLUSTER; + else if (pg_strcasecmp(cmd, "REPACK") =3D=3D 0) + cmdtype =3D PROGRESS_COMMAND_REPACK; else if (pg_strcasecmp(cmd, "CREATE INDEX") =3D=3D 0) cmdtype =3D PROGRESS_COMMAND_CREATE_INDEX; else if (pg_strcasecmp(cmd, "BASEBACKUP") =3D=3D 0) diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 8b91bc00062..2a1bb47ff03 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -1267,7 +1267,7 @@ static const char *const sql_commands[] =3D { "DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO", "LISTEN", "LOAD= ", "LOCK", "MERGE INTO", "MOVE", "NOTIFY", "PREPARE", - "REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE", + "REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE", "REPACK", "RESET", "REVOKE", "ROLLBACK", "SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START", "TABLE", "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", "VALUES", @@ -5086,6 +5086,46 @@ match_previous_words(int pattern_id, COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces); } =20 +/* REPACK */ + else if (Matches("REPACK")) + COMPLETE_WITH_SCHEMA_QUERY_PLUS(Query_for_list_of_clusterables, + "(", "USING INDEX"); + else if (Matches("REPACK", "(*)")) + COMPLETE_WITH_SCHEMA_QUERY_PLUS(Query_for_list_of_clusterables, + "USING INDEX"); + else if (Matches("REPACK", MatchAnyExcept("("))) + COMPLETE_WITH("USING INDEX"); + else if (Matches("REPACK", "(*)", MatchAnyExcept("("))) + COMPLETE_WITH("USING INDEX"); + else if (Matches("REPACK", MatchAny, "USING", "INDEX") || + Matches("REPACK", "(*)", MatchAny, "USING", "INDEX")) + { + set_completion_reference(prev3_wd); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_index_of_table); + } + /* + * Complete ... [ (*) ] <sth> USING INDEX, with a list of indexes for + * <sth>. + */ + else if (TailMatches(MatchAny, "USING", "INDEX")) + { + set_completion_reference(prev3_wd); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_index_of_table); + } + else if (HeadMatches("REPACK", "(*") && + !HeadMatches("REPACK", "(*)")) + { + /* + * This fires if we're in an unfinished parenthesized option list. + * get_previous_words treats a completed parenthesized option list as + * one word, so the above test is correct. + */ + if (ends_with(prev_wd, '(') || ends_with(prev_wd, ',')) + COMPLETE_WITH("ANALYZE", "VERBOSE"); + else if (TailMatches("ANALYZE", "VERBOSE")) + COMPLETE_WITH("ON", "OFF"); + } + /* SECURITY LABEL */ else if (Matches("SECURITY")) COMPLETE_WITH("LABEL"); diff --git a/src/bin/scripts/Makefile b/src/bin/scripts/Makefile index e6cd9ef4af5..f964a5803ca 100644 --- a/src/bin/scripts/Makefile +++ b/src/bin/scripts/Makefile @@ -23,6 +23,7 @@ PROGRAMS =3D \ dropdb \ dropuser \ pg_isready \ + pg_repackdb \ reindexdb \ vacuumdb =20 @@ -39,6 +40,7 @@ clusterdb: clusterdb.o common.o $(WIN32RES) | submake-lib= pq submake-libpgport su vacuumdb: vacuumdb.o vacuuming.o common.o $(WIN32RES) | submake-libpq subm= ake-libpgport submake-libpgfeutils reindexdb: reindexdb.o common.o $(WIN32RES) | submake-libpq submake-libpgp= ort submake-libpgfeutils pg_isready: pg_isready.o common.o $(WIN32RES) | submake-libpq submake-libp= gport submake-libpgfeutils +pg_repackdb: pg_repackdb.o vacuuming.o common.o $(WIN32RES) | submake-libp= q submake-libpgport submake-libpgfeutils =20 install: all installdirs $(INSTALL_PROGRAM) createdb$(X) '$(DESTDIR)$(bindir)'/createdb$(X) @@ -49,6 +51,7 @@ install: all installdirs $(INSTALL_PROGRAM) vacuumdb$(X) '$(DESTDIR)$(bindir)'/vacuumdb$(X) $(INSTALL_PROGRAM) reindexdb$(X) '$(DESTDIR)$(bindir)'/reindexdb$(X) $(INSTALL_PROGRAM) pg_isready$(X) '$(DESTDIR)$(bindir)'/pg_isready$(X) + $(INSTALL_PROGRAM) pg_repackdb$(X) '$(DESTDIR)$(bindir)'/pg_repackdb$(X) =20 installdirs: $(MKDIR_P) '$(DESTDIR)$(bindir)' diff --git a/src/bin/scripts/meson.build b/src/bin/scripts/meson.build index c083ec38099..1e88bacebba 100644 --- a/src/bin/scripts/meson.build +++ b/src/bin/scripts/meson.build @@ -42,6 +42,7 @@ vacuuming_common =3D static_library('libvacuuming_common', =20 binaries =3D [ 'vacuumdb', + 'pg_repackdb', ] foreach binary : binaries binary_sources =3D files('@[email protected]'.format(binary)) @@ -80,6 +81,7 @@ tests +=3D { 't/100_vacuumdb.pl', 't/101_vacuumdb_all.pl', 't/102_vacuumdb_stages.pl', + 't/103_repackdb.pl', 't/200_connstr.pl', ], }, diff --git a/src/bin/scripts/pg_repackdb.c b/src/bin/scripts/pg_repackdb.c new file mode 100644 index 00000000000..2765d1e97b8 --- /dev/null +++ b/src/bin/scripts/pg_repackdb.c @@ -0,0 +1,240 @@ +/*------------------------------------------------------------------------- + * + * pg_repackdb + * An utility to run REPACK + * + * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * FIXME: this is missing a way to specify the index to use to repack one + * table, or whether to pass a WITH INDEX clause when multiple tables are + * used. Something like --index[=3Dindexname]. Adding that bleeds into + * vacuuming.c as well. + * + * src/bin/scripts/pg_repackdb.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres_fe.h" + +#include <limits.h> + +#include "common.h" +#include "common/logging.h" +#include "fe_utils/option_utils.h" +#include "vacuuming.h" + +static void help(const char *progname); +static void check_objfilter(bits32 objfilter); + +int +main(int argc, char *argv[]) +{ + static struct option long_options[] =3D { + {"host", required_argument, NULL, 'h'}, + {"port", required_argument, NULL, 'p'}, + {"username", required_argument, NULL, 'U'}, + {"no-password", no_argument, NULL, 'w'}, + {"password", no_argument, NULL, 'W'}, + {"echo", no_argument, NULL, 'e'}, + {"quiet", no_argument, NULL, 'q'}, + {"dbname", required_argument, NULL, 'd'}, + {"analyze", no_argument, NULL, 'z'}, + {"all", no_argument, NULL, 'a'}, + /* XXX this could be 'i', but optional_arg is messy */ + {"index", optional_argument, NULL, 1}, + {"table", required_argument, NULL, 't'}, + {"verbose", no_argument, NULL, 'v'}, + {"jobs", required_argument, NULL, 'j'}, + {"schema", required_argument, NULL, 'n'}, + {"exclude-schema", required_argument, NULL, 'N'}, + {"maintenance-db", required_argument, NULL, 2}, + {NULL, 0, NULL, 0} + }; + + const char *progname; + int optindex; + int c; + const char *dbname =3D NULL; + const char *maintenance_db =3D NULL; + ConnParams cparams; + vacuumingOptions vacopts; + SimpleStringList objects =3D {NULL, NULL}; + int concurrentCons =3D 1; + int tbl_count =3D 0; + int ret; + + /* initialize options */ + memset(&vacopts, 0, sizeof(vacopts)); + vacopts.mode =3D MODE_REPACK; + + /* the same for connection parameters */ + memset(&cparams, 0, sizeof(cparams)); + cparams.prompt_password =3D TRI_DEFAULT; + + pg_logging_init(argv[0]); + progname =3D get_progname(argv[0]); + set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); + + handle_help_version_opts(argc, argv, progname, help); + + while ((c =3D getopt_long(argc, argv, "ad:eh:j:n:N:p:qt:U:vwWz", + long_options, &optindex)) !=3D -1) + { + switch (c) + { + case 'a': + vacopts.objfilter |=3D OBJFILTER_ALL_DBS; + break; + case 'd': + vacopts.objfilter |=3D OBJFILTER_DATABASE; + dbname =3D pg_strdup(optarg); + break; + case 'e': + vacopts.echo =3D true; + break; + case 'h': + cparams.pghost =3D pg_strdup(optarg); + break; + case 'j': + if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX, + &concurrentCons)) + exit(1); + break; + case 'n': + vacopts.objfilter |=3D OBJFILTER_SCHEMA; + simple_string_list_append(&objects, optarg); + break; + case 'N': + vacopts.objfilter |=3D OBJFILTER_SCHEMA_EXCLUDE; + simple_string_list_append(&objects, optarg); + break; + case 'p': + cparams.pgport =3D pg_strdup(optarg); + break; + case 'q': + vacopts.quiet =3D true; + break; + case 't': + vacopts.objfilter |=3D OBJFILTER_TABLE; + simple_string_list_append(&objects, optarg); + tbl_count++; + break; + case 'U': + cparams.pguser =3D pg_strdup(optarg); + break; + case 'v': + vacopts.verbose =3D true; + break; + case 'w': + cparams.prompt_password =3D TRI_NO; + break; + case 'W': + cparams.prompt_password =3D TRI_YES; + break; + case 'z': + vacopts.and_analyze =3D true; + break; + case 1: + vacopts.using_index =3D true; + if (optarg) + vacopts.indexname =3D pg_strdup(optarg); + else + vacopts.indexname =3D NULL; + break; + case 2: + maintenance_db =3D pg_strdup(optarg); + break; + default: + /* getopt_long already emitted a complaint */ + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit(1); + } + } + + /* + * Non-option argument specifies database name as long as it wasn't + * already specified with -d / --dbname + */ + if (optind < argc && dbname =3D=3D NULL) + { + vacopts.objfilter |=3D OBJFILTER_DATABASE; + dbname =3D argv[optind]; + optind++; + } + + if (optind < argc) + { + pg_log_error("too many command-line arguments (first is \"%s\")", + argv[optind]); + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit(1); + } + + /* + * Validate the combination of filters specified in the command-line + * options. + */ + check_objfilter(vacopts.objfilter); + + ret =3D vacuuming_main(&cparams, dbname, maintenance_db, &vacopts, + &objects, tbl_count, concurrentCons, + progname); + exit(ret); +} + +/* + * Verify that the filters used at command line are compatible. + */ +void +check_objfilter(bits32 objfilter) +{ + if ((objfilter & OBJFILTER_ALL_DBS) && + (objfilter & OBJFILTER_DATABASE)) + pg_fatal("cannot repack all databases and a specific one at the same tim= e"); + + if ((objfilter & OBJFILTER_TABLE) && + (objfilter & OBJFILTER_SCHEMA)) + pg_fatal("cannot repack all tables in schema(s) and specific table(s) at= the same time"); + + if ((objfilter & OBJFILTER_TABLE) && + (objfilter & OBJFILTER_SCHEMA_EXCLUDE)) + pg_fatal("cannot repack specific table(s) and exclude schema(s) at the s= ame time"); + + if ((objfilter & OBJFILTER_SCHEMA) && + (objfilter & OBJFILTER_SCHEMA_EXCLUDE)) + pg_fatal("cannot repack all tables in schema(s) and exclude schema(s) at= the same time"); +} + +static void +help(const char *progname) +{ + printf(_("%s repacks a PostgreSQL database.\n\n"), progname); + printf(_("Usage:\n")); + printf(_(" %s [OPTION]... [DBNAME]\n"), progname); + printf(_("\nOptions:\n")); + printf(_(" -a, --all repack all databases\n")); + printf(_(" -d, --dbname=3DDBNAME database to repack\n")); + printf(_(" -e, --echo show the commands being sent = to the server\n")); + printf(_(" --index[=3DINDEX] repack following an index\n= ")); + printf(_(" -j, --jobs=3DNUM use this many concurrent co= nnections to repack\n")); + printf(_(" -n, --schema=3DSCHEMA repack tables in the specif= ied schema(s) only\n")); + printf(_(" -N, --exclude-schema=3DSCHEMA do not repack tables in the= specified schema(s)\n")); + printf(_(" -q, --quiet don't write any messages\n")); + printf(_(" -t, --table=3D'TABLE[(COLUMNS)]' repack specific table(s) on= ly\n")); + printf(_(" -v, --verbose write a lot of output\n")); + printf(_(" -V, --version output version information, t= hen exit\n")); + printf(_(" -z, --analyze update optimizer statistics\n= ")); + printf(_(" -?, --help show this help, then exit\n")= ); + printf(_("\nConnection options:\n")); + printf(_(" -h, --host=3DHOSTNAME database server host or socket di= rectory\n")); + printf(_(" -p, --port=3DPORT database server port\n")); + printf(_(" -U, --username=3DUSERNAME user name to connect as\n")); + printf(_(" -w, --no-password never prompt for password\n")); + printf(_(" -W, --password force password prompt\n")); + printf(_(" --maintenance-db=3DDBNAME alternate maintenance database\n"= )); + printf(_("\nRead the description of the SQL command REPACK for details.\n= ")); + printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT); + printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL); +} diff --git a/src/bin/scripts/t/103_repackdb.pl b/src/bin/scripts/t/103_repa= ckdb.pl new file mode 100644 index 00000000000..44113367a61 --- /dev/null +++ b/src/bin/scripts/t/103_repackdb.pl @@ -0,0 +1,47 @@ +# Copyright (c) 2021-2026, PostgreSQL Global Development Group + +use strict; +use warnings FATAL =3D> 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +program_help_ok('pg_repackdb'); +program_version_ok('pg_repackdb'); +program_options_handling_ok('pg_repackdb'); + +my $node =3D PostgreSQL::Test::Cluster->new('main'); +$node->init; +$node->start; + +$node->issues_sql_like( + [ 'pg_repackdb', 'postgres', '-t', 'pg_class'], + qr/statement: REPACK.*pg_class;/, + 'pg_repackdb processes a single table'); + +$node->safe_psql('postgres', 'CREATE USER testusr; + GRANT CREATE ON SCHEMA public TO testusr'); +$node->safe_psql('postgres', + 'CREATE TABLE cluster_1 (a int primary key); + ALTER TABLE cluster_1 CLUSTER ON cluster_1_pkey; + CREATE TABLE cluster_2 (a int unique); + ALTER TABLE cluster_2 CLUSTER ON cluster_2_a_key;', + extra_params =3D> ['-U' =3D> 'testusr']); + +$node->issues_sql_like( + [ 'pg_repackdb', 'postgres', '-U', 'testusr' ], + qr/statement: REPACK.*;/, + 'SQL REPACK run'); + +$node->issues_sql_like( + [ 'pg_repackdb', 'postgres', '--index'], + qr/statement: REPACK.*cluster_1 USING INDEX.*statement: REPACK.*cluster_2= USING INDEX/ms, + 'pg_repackdb --index chooses multiple tables'); + +$node->issues_sql_like( + [ 'pg_repackdb', 'postgres', '--analyze', '-t', 'cluster_1'], + qr/statement: REPACK \(ANALYZE\) public.cluster_1/, + 'pg_repackdb --analyze works'); + +done_testing(); diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c index faac9089a01..0fa7be3c0c3 100644 --- a/src/bin/scripts/vacuuming.c +++ b/src/bin/scripts/vacuuming.c @@ -1,6 +1,6 @@ /*------------------------------------------------------------------------- * vacuuming.c - * Helper routines for vacuumdb + * Helper routines for vacuumdb and pg_repackdb * * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California @@ -194,6 +194,14 @@ vacuum_one_database(ConnParams *cparams, =20 conn =3D connectDatabase(cparams, progname, vacopts->echo, false, true); =20 + if (vacopts->mode =3D=3D MODE_REPACK && PQserverVersion(conn) < 190000) + { + /* XXX arguably, here we should use VACUUM FULL instead of failing */ + PQfinish(conn); + pg_fatal("cannot use the \"%s\" command on server versions older than Po= stgreSQL %s", + "REPACK", "19"); + } + if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600) { PQfinish(conn); @@ -286,9 +294,18 @@ vacuum_one_database(ConnParams *cparams, if (vacopts->mode =3D=3D MODE_ANALYZE_IN_STAGES) printf(_("%s: processing database \"%s\": %s\n"), progname, PQdb(conn), _(stage_messages[stage])); - else + else if (vacopts->mode =3D=3D MODE_ANALYZE) + printf(_("%s: analyzing database \"%s\"\n"), + progname, PQdb(conn)); + else if (vacopts->mode =3D=3D MODE_VACUUM) printf(_("%s: vacuuming database \"%s\"\n"), progname, PQdb(conn)); + else + { + Assert(vacopts->mode =3D=3D MODE_REPACK); + printf(_("%s: repacking database \"%s\"\n"), + progname, PQdb(conn)); + } fflush(stdout); } =20 @@ -640,6 +657,35 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacop= ts, " AND listed_objects.object_oid IS NOT NULL\n"); } =20 + /* + * In REPACK mode, if the 'using_index' option was given but no index + * name, filter only tables that have an index with indisclustered set. + * (If an index name is given, we trust the user to pass a reasonable list + * of tables.) + * + * XXX it may be worth printing an error if an index name is given with no + * list of tables. + */ + if (vacopts->mode =3D=3D MODE_REPACK && + vacopts->using_index && !vacopts->indexname) + { + appendPQExpBufferStr(&catalog_query, + " AND EXISTS (SELECT 1 FROM pg_catalog.pg_index\n" + " WHERE indrelid =3D c.oid AND indisclustered)\n"); + } + + /* + * In REPACK mode, only consider the tables that the current user has + * MAINTAIN privileges on. XXX maybe we should do this in all cases, not + * just REPACK. The vacuumdb output is too noisy for no reason. + */ + if (vacopts->mode =3D=3D MODE_REPACK) + { + appendPQExpBufferStr(&catalog_query, + " AND pg_catalog.has_table_privilege(current_user, " + "c.oid, 'MAINTAIN')\n"); + } + /* * If no tables were listed, filter for the relevant relation types. If * tables were given via --table, don't bother filtering by relation type. @@ -878,8 +924,10 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql, if (vacopts->verbose) appendPQExpBufferStr(sql, " VERBOSE"); } + + appendPQExpBuffer(sql, " %s", table); } - else + else if (vacopts->mode =3D=3D MODE_VACUUM) { appendPQExpBufferStr(sql, "VACUUM"); =20 @@ -993,9 +1041,39 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql, if (vacopts->and_analyze) appendPQExpBufferStr(sql, " ANALYZE"); } + + appendPQExpBuffer(sql, " %s", table); } + else if (vacopts->mode =3D=3D MODE_REPACK) + { + appendPQExpBufferStr(sql, "REPACK"); =20 - appendPQExpBuffer(sql, " %s;", table); + if (vacopts->verbose) + { + appendPQExpBuffer(sql, "%sVERBOSE", sep); + sep =3D comma; + } + if (vacopts->and_analyze) + { + appendPQExpBuffer(sql, "%sANALYZE", sep); + sep =3D comma; + } + + if (sep !=3D paren) + appendPQExpBufferChar(sql, ')'); + + appendPQExpBuffer(sql, " %s", table); + + if (vacopts->using_index) + { + appendPQExpBuffer(sql, " USING INDEX"); + if (vacopts->indexname) + appendPQExpBuffer(sql, " %s", fmtIdEnc(vacopts->indexname, + PQclientEncoding(conn))); + } + } + + appendPQExpBufferChar(sql, ';'); } =20 /* @@ -1024,13 +1102,21 @@ run_vacuum_command(ParallelSlot *free_slot, vacuumi= ngOptions *vacopts, { if (table) { - pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s", - table, PQdb(conn), PQerrorMessage(conn)); + if (vacopts->mode =3D=3D MODE_VACUUM) + pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s", + table, PQdb(conn), PQerrorMessage(conn)); + else + pg_log_error("repacking of table \"%s\" in database \"%s\" failed: %s", + table, PQdb(conn), PQerrorMessage(conn)); } else { - pg_log_error("vacuuming of database \"%s\" failed: %s", - PQdb(conn), PQerrorMessage(conn)); + if (vacopts->mode =3D=3D MODE_VACUUM) + pg_log_error("vacuuming of database \"%s\" failed: %s", + PQdb(conn), PQerrorMessage(conn)); + else + pg_log_error("repacking of database \"%s\" failed: %s", + PQdb(conn), PQerrorMessage(conn)); } } } diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h index be4a75ef2f4..6e8abe00a4f 100644 --- a/src/bin/scripts/vacuuming.h +++ b/src/bin/scripts/vacuuming.h @@ -20,6 +20,7 @@ typedef enum { MODE_VACUUM, + MODE_REPACK, MODE_ANALYZE, MODE_ANALYZE_IN_STAGES } RunMode; @@ -37,6 +38,8 @@ typedef struct vacuumingOptions bool and_analyze; bool full; bool freeze; + bool using_index; + char *indexname; bool disable_page_skipping; bool skip_locked; int min_xid_age; diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h index 8ea81622f9d..28741988478 100644 --- a/src/include/commands/cluster.h +++ b/src/include/commands/cluster.h @@ -24,6 +24,7 @@ #define CLUOPT_RECHECK 0x02 /* recheck relation state */ #define CLUOPT_RECHECK_ISCLUSTERED 0x04 /* recheck relation state for * indisclustered */ +#define CLUOPT_ANALYZE 0x08 /* do an ANALYZE */ =20 /* options for CLUSTER */ typedef struct ClusterParams @@ -31,8 +32,11 @@ typedef struct ClusterParams bits32 options; /* bitmask of CLUOPT_* */ } ClusterParams; =20 -extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel= ); -extern void cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *par= ams); + +extern void ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLev= el); + +extern void cluster_rel(RepackCommand command, Relation OldHeap, Oid index= Oid, + ClusterParams *params); extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, LOCKMODE lockmode); extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_inter= nal); diff --git a/src/include/commands/progress.h b/src/include/commands/progres= s.h index 359221dc296..f00e39b937d 100644 --- a/src/include/commands/progress.h +++ b/src/include/commands/progress.h @@ -73,28 +73,34 @@ #define PROGRESS_ANALYZE_STARTED_BY_MANUAL 1 #define PROGRESS_ANALYZE_STARTED_BY_AUTOVACUUM 2 =20 -/* Progress parameters for cluster */ -#define PROGRESS_CLUSTER_COMMAND 0 -#define PROGRESS_CLUSTER_PHASE 1 -#define PROGRESS_CLUSTER_INDEX_RELID 2 -#define PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED 3 -#define PROGRESS_CLUSTER_HEAP_TUPLES_WRITTEN 4 -#define PROGRESS_CLUSTER_TOTAL_HEAP_BLKS 5 -#define PROGRESS_CLUSTER_HEAP_BLKS_SCANNED 6 -#define PROGRESS_CLUSTER_INDEX_REBUILD_COUNT 7 - -/* Phases of cluster (as advertised via PROGRESS_CLUSTER_PHASE) */ -#define PROGRESS_CLUSTER_PHASE_SEQ_SCAN_HEAP 1 -#define PROGRESS_CLUSTER_PHASE_INDEX_SCAN_HEAP 2 -#define PROGRESS_CLUSTER_PHASE_SORT_TUPLES 3 -#define PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP 4 -#define PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES 5 -#define PROGRESS_CLUSTER_PHASE_REBUILD_INDEX 6 -#define PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP 7 - -/* Commands of PROGRESS_CLUSTER */ -#define PROGRESS_CLUSTER_COMMAND_CLUSTER 1 -#define PROGRESS_CLUSTER_COMMAND_VACUUM_FULL 2 +/* + * Progress parameters for REPACK. + * + * Values for PROGRESS_REPACK_COMMAND are defined as in RepackCommand. + * + * Note: Since REPACK shares code with CLUSTER, these values are also + * used by CLUSTER. (CLUSTER is now deprecated, so it makes little sense to + * introduce a separate set of constants.) + */ +#define PROGRESS_REPACK_COMMAND 0 +#define PROGRESS_REPACK_PHASE 1 +#define PROGRESS_REPACK_INDEX_RELID 2 +#define PROGRESS_REPACK_HEAP_TUPLES_SCANNED 3 +#define PROGRESS_REPACK_HEAP_TUPLES_WRITTEN 4 +#define PROGRESS_REPACK_TOTAL_HEAP_BLKS 5 +#define PROGRESS_REPACK_HEAP_BLKS_SCANNED 6 +#define PROGRESS_REPACK_INDEX_REBUILD_COUNT 7 + +/* + * Phases of repack (as advertised via PROGRESS_REPACK_PHASE). + */ +#define PROGRESS_REPACK_PHASE_SEQ_SCAN_HEAP 1 +#define PROGRESS_REPACK_PHASE_INDEX_SCAN_HEAP 2 +#define PROGRESS_REPACK_PHASE_SORT_TUPLES 3 +#define PROGRESS_REPACK_PHASE_WRITE_NEW_HEAP 4 +#define PROGRESS_REPACK_PHASE_SWAP_REL_FILES 5 +#define PROGRESS_REPACK_PHASE_REBUILD_INDEX 6 +#define PROGRESS_REPACK_PHASE_FINAL_CLEANUP 7 =20 /* Progress parameters for CREATE INDEX */ /* 3, 4 and 5 reserved for "waitfor" metrics */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index aac4bfc70d9..e651ed3a1ff 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3980,18 +3980,6 @@ typedef struct AlterSystemStmt VariableSetStmt *setstmt; /* SET subcommand */ } AlterSystemStmt; =20 -/* ---------------------- - * Cluster Statement (support pbrown's cluster index implementation) - * ---------------------- - */ -typedef struct ClusterStmt -{ - NodeTag type; - RangeVar *relation; /* relation being indexed, or NULL if all */ - char *indexname; /* original index defined */ - List *params; /* list of DefElem nodes */ -} ClusterStmt; - /* ---------------------- * Vacuum and Analyze Statements * @@ -4004,7 +3992,7 @@ typedef struct VacuumStmt NodeTag type; List *options; /* list of DefElem nodes */ List *rels; /* list of VacuumRelation, or NIL for all */ - bool is_vacuumcmd; /* true for VACUUM, false for ANALYZE */ + bool is_vacuumcmd; /* true for VACUUM, false otherwise */ } VacuumStmt; =20 /* @@ -4022,6 +4010,27 @@ typedef struct VacuumRelation List *va_cols; /* list of column names, or NIL for all */ } VacuumRelation; =20 +/* ---------------------- + * Repack Statement + * ---------------------- + */ +typedef enum RepackCommand +{ + REPACK_COMMAND_CLUSTER =3D 1, + REPACK_COMMAND_REPACK, + REPACK_COMMAND_VACUUMFULL, +} RepackCommand; + +typedef struct RepackStmt +{ + NodeTag type; + RepackCommand command; /* type of command being run */ + VacuumRelation *relation; /* relation being repacked */ + char *indexname; /* order tuples by this index */ + bool usingindex; /* whether USING INDEX is specified */ + List *params; /* list of DefElem nodes */ +} RepackStmt; + /* ---------------------- * Explain Statement * diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f7753c5c8a8..6f74a8c05c7 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -377,6 +377,7 @@ PG_KEYWORD("reindex", REINDEX, UNRESERVED_KEYWORD, BARE= _LABEL) PG_KEYWORD("relative", RELATIVE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("release", RELEASE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("rename", RENAME, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("repack", REPACK, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h index 1290c9bab68..652dc61b834 100644 --- a/src/include/tcop/cmdtaglist.h +++ b/src/include/tcop/cmdtaglist.h @@ -196,6 +196,7 @@ PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", fals= e, false, false) PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED VIEW", t= rue, false, false) PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", true, false, false) PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false) +PG_CMDTAG(CMDTAG_REPACK, "REPACK", false, false, false) PG_CMDTAG(CMDTAG_RESET, "RESET", false, false, false) PG_CMDTAG(CMDTAG_REVOKE, "REVOKE", true, false, false) PG_CMDTAG(CMDTAG_REVOKE_ROLE, "REVOKE ROLE", false, false, false) diff --git a/src/include/utils/backend_progress.h b/src/include/utils/backe= nd_progress.h index 19f63b41431..6300dbd15d5 100644 --- a/src/include/utils/backend_progress.h +++ b/src/include/utils/backend_progress.h @@ -24,10 +24,10 @@ typedef enum ProgressCommandType PROGRESS_COMMAND_INVALID, PROGRESS_COMMAND_VACUUM, PROGRESS_COMMAND_ANALYZE, - PROGRESS_COMMAND_CLUSTER, PROGRESS_COMMAND_CREATE_INDEX, PROGRESS_COMMAND_BASEBACKUP, PROGRESS_COMMAND_COPY, + PROGRESS_COMMAND_REPACK, } ProgressCommandType; =20 #define PGSTAT_NUM_PROGRESS_PARAM 20 diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expec= ted/cluster.out index 4d40a6809ab..277854418fa 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -495,6 +495,43 @@ ALTER TABLE clstrpart SET WITHOUT CLUSTER; ERROR: cannot mark index clustered in partitioned table ALTER TABLE clstrpart CLUSTER ON clstrpart_idx; ERROR: cannot mark index clustered in partitioned table +-- Check that REPACK sets new relfilenodes: it should process exactly the = same +-- tables as CLUSTER did. +DROP TABLE old_cluster_info; +DROP TABLE new_cluster_info; +CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, = relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class= c ON c.oid=3Dtree.relid ; +REPACK clstrpart USING INDEX clstrpart_idx; +CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, = relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class= c ON c.oid=3Dtree.relid ; +SELECT relname, old.level, old.relkind, old.relfilenode =3D new.relfilenod= e FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname)= ORDER BY relname COLLATE "C"; + relname | level | relkind | ?column?=20 +-------------+-------+---------+---------- + clstrpart | 0 | p | t + clstrpart1 | 1 | p | t + clstrpart11 | 2 | r | f + clstrpart12 | 2 | p | t + clstrpart2 | 1 | r | f + clstrpart3 | 1 | p | t + clstrpart33 | 2 | r | f +(7 rows) + +-- And finally the same for REPACK w/o index. +DROP TABLE old_cluster_info; +DROP TABLE new_cluster_info; +CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, = relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class= c ON c.oid=3Dtree.relid ; +REPACK clstrpart; +CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, = relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class= c ON c.oid=3Dtree.relid ; +SELECT relname, old.level, old.relkind, old.relfilenode =3D new.relfilenod= e FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname)= ORDER BY relname COLLATE "C"; + relname | level | relkind | ?column?=20 +-------------+-------+---------+---------- + clstrpart | 0 | p | t + clstrpart1 | 1 | p | t + clstrpart11 | 2 | r | f + clstrpart12 | 2 | p | t + clstrpart2 | 1 | r | f + clstrpart3 | 1 | p | t + clstrpart33 | 2 | r | f +(7 rows) + DROP TABLE clstrpart; -- Ownership of partitions is checked CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i); @@ -513,7 +550,7 @@ CREATE TEMP TABLE ptnowner_oldnodes AS JOIN pg_class AS c ON c.oid=3Dtree.relid; SET SESSION AUTHORIZATION regress_ptnowner; CLUSTER ptnowner USING ptnowner_i_idx; -WARNING: permission denied to cluster "ptnowner2", skipping it +WARNING: permission denied to execute CLUSTER on "ptnowner2", skipping it RESET SESSION AUTHORIZATION; SELECT a.relname, a.relfilenode=3Db.relfilenode FROM pg_class a JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C"; @@ -665,6 +702,101 @@ SELECT * FROM clstr_expression WHERE -a =3D -3 ORDER = BY -a, b; (4 rows) =20 COMMIT; +---------------------------------------------------------------------- +-- +-- REPACK +-- +---------------------------------------------------------------------- +-- REPACK handles individual tables identically to CLUSTER, but it's worth +-- checking if it handles table hierarchies identically as well. +REPACK clstr_tst USING INDEX clstr_tst_c; +-- Verify that inheritance link still works +INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table 2'); +SELECT a,b,c,substring(d for 30), length(d) from clstr_tst; + a | b | c | substring | length=20 +----+-----+------------------+--------------------------------+-------- + 10 | 14 | catorce | |=20=20=20= =20=20=20=20 + 18 | 5 | cinco | |=20=20=20= =20=20=20=20 + 9 | 4 | cuatro | |=20=20=20= =20=20=20=20 + 26 | 19 | diecinueve | |=20=20=20= =20=20=20=20 + 12 | 18 | dieciocho | |=20=20=20= =20=20=20=20 + 30 | 16 | dieciseis | |=20=20=20= =20=20=20=20 + 24 | 17 | diecisiete | |=20=20=20= =20=20=20=20 + 2 | 10 | diez | |=20=20=20= =20=20=20=20 + 23 | 12 | doce | |=20=20=20= =20=20=20=20 + 11 | 2 | dos | |=20=20=20= =20=20=20=20 + 25 | 9 | nueve | |=20=20=20= =20=20=20=20 + 31 | 8 | ocho | |=20=20=20= =20=20=20=20 + 1 | 11 | once | |=20=20=20= =20=20=20=20 + 28 | 15 | quince | |=20=20=20= =20=20=20=20 + 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000 + 29 | 7 | siete | |=20=20=20= =20=20=20=20 + 15 | 13 | trece | |=20=20=20= =20=20=20=20 + 22 | 30 | treinta | |=20=20=20= =20=20=20=20 + 17 | 32 | treinta y dos | |=20=20=20= =20=20=20=20 + 3 | 31 | treinta y uno | |=20=20=20= =20=20=20=20 + 5 | 3 | tres | |=20=20=20= =20=20=20=20 + 20 | 1 | uno | |=20=20=20= =20=20=20=20 + 6 | 20 | veinte | |=20=20=20= =20=20=20=20 + 14 | 25 | veinticinco | |=20=20=20= =20=20=20=20 + 21 | 24 | veinticuatro | |=20=20=20= =20=20=20=20 + 4 | 22 | veintidos | |=20=20=20= =20=20=20=20 + 19 | 29 | veintinueve | |=20=20=20= =20=20=20=20 + 16 | 28 | veintiocho | |=20=20=20= =20=20=20=20 + 27 | 26 | veintiseis | |=20=20=20= =20=20=20=20 + 13 | 27 | veintisiete | |=20=20=20= =20=20=20=20 + 7 | 23 | veintitres | |=20=20=20= =20=20=20=20 + 8 | 21 | veintiuno | |=20=20=20= =20=20=20=20 + 0 | 100 | in child table | |=20=20=20= =20=20=20=20 + 0 | 100 | in child table 2 | |=20=20=20= =20=20=20=20 +(34 rows) + +-- Verify that foreign key link still works +INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail'); +ERROR: insert or update on table "clstr_tst" violates foreign key constra= int "clstr_tst_con" +DETAIL: Key (b)=3D(1111) is not present in table "clstr_tst_s". +SELECT conname FROM pg_constraint WHERE conrelid =3D 'clstr_tst'::regclass +ORDER BY 1; + conname=20=20=20=20=20=20=20=20 +---------------------- + clstr_tst_a_not_null + clstr_tst_con + clstr_tst_pkey +(3 rows) + +-- Verify partial analyze works +REPACK (ANALYZE) clstr_tst (a); +REPACK (ANALYZE) clstr_tst; +REPACK (VERBOSE) clstr_tst (a); +ERROR: ANALYZE option must be specified when a column list is provided +-- REPACK w/o argument performs no ordering, so we can only check which ta= bles +-- have the relfilenode changed. +RESET SESSION AUTHORIZATION; +CREATE TEMP TABLE relnodes_old AS +(SELECT relname, relfilenode +FROM pg_class +WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3')); +SET SESSION AUTHORIZATION regress_clstr_user; +SET client_min_messages =3D ERROR; -- order of "skipping" warnings may va= ry +REPACK; +RESET client_min_messages; +RESET SESSION AUTHORIZATION; +CREATE TEMP TABLE relnodes_new AS +(SELECT relname, relfilenode +FROM pg_class +WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3')); +-- Do the actual comparison. Unlike CLUSTER, clstr_3 should have been +-- processed because there is nothing like clustering index here. +SELECT o.relname FROM relnodes_old o +JOIN relnodes_new n ON o.relname =3D n.relname +WHERE o.relfilenode <> n.relfilenode +ORDER BY o.relname; + relname=20 +--------- + clstr_1 + clstr_3 +(2 rows) + -- clean up DROP TABLE clustertest; DROP TABLE clstr_1; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expecte= d/rules.out index f4ee2bd7459..48461550636 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2002,34 +2002,23 @@ pg_stat_progress_basebackup| SELECT pid, ELSE NULL::text END AS backup_type FROM pg_stat_get_progress_info('BASEBACKUP'::text) s(pid, datid, relid,= param1, param2, param3, param4, param5, param6, param7, param8, param9, pa= ram10, param11, param12, param13, param14, param15, param16, param17, param= 18, param19, param20); -pg_stat_progress_cluster| SELECT s.pid, - s.datid, - d.datname, - s.relid, - CASE s.param1 - WHEN 1 THEN 'CLUSTER'::text - WHEN 2 THEN 'VACUUM FULL'::text - ELSE NULL::text +pg_stat_progress_cluster| SELECT pid, + datid, + datname, + relid, + CASE + WHEN (command =3D ANY (ARRAY['CLUSTER'::text, 'VACUUM FULL'::t= ext])) THEN command + WHEN (repack_index_relid =3D (0)::oid) THEN 'VACUUM FULL'::text + ELSE 'CLUSTER'::text END AS command, - CASE s.param2 - WHEN 0 THEN 'initializing'::text - WHEN 1 THEN 'seq scanning heap'::text - WHEN 2 THEN 'index scanning heap'::text - WHEN 3 THEN 'sorting tuples'::text - WHEN 4 THEN 'writing new heap'::text - WHEN 5 THEN 'swapping relation files'::text - WHEN 6 THEN 'rebuilding index'::text - WHEN 7 THEN 'performing final cleanup'::text - ELSE NULL::text - END AS phase, - (s.param3)::oid AS cluster_index_relid, - s.param4 AS heap_tuples_scanned, - s.param5 AS heap_tuples_written, - s.param6 AS heap_blks_total, - s.param7 AS heap_blks_scanned, - s.param8 AS index_rebuild_count - FROM (pg_stat_get_progress_info('CLUSTER'::text) s(pid, datid, relid, p= aram1, param2, param3, param4, param5, param6, param7, param8, param9, para= m10, param11, param12, param13, param14, param15, param16, param17, param18= , param19, param20) - LEFT JOIN pg_database d ON ((s.datid =3D d.oid))); + phase, + repack_index_relid AS cluster_index_relid, + heap_tuples_scanned, + heap_tuples_written, + heap_blks_total, + heap_blks_scanned, + index_rebuild_count + FROM pg_stat_progress_repack; pg_stat_progress_copy| SELECT s.pid, s.datid, d.datname, @@ -2089,6 +2078,35 @@ pg_stat_progress_create_index| SELECT s.pid, s.param15 AS partitions_done FROM (pg_stat_get_progress_info('CREATE INDEX'::text) s(pid, datid, rel= id, param1, param2, param3, param4, param5, param6, param7, param8, param9,= param10, param11, param12, param13, param14, param15, param16, param17, pa= ram18, param19, param20) LEFT JOIN pg_database d ON ((s.datid =3D d.oid))); +pg_stat_progress_repack| SELECT s.pid, + s.datid, + d.datname, + s.relid, + CASE s.param1 + WHEN 1 THEN 'CLUSTER'::text + WHEN 2 THEN 'REPACK'::text + WHEN 3 THEN 'VACUUM FULL'::text + ELSE NULL::text + END AS command, + CASE s.param2 + WHEN 0 THEN 'initializing'::text + WHEN 1 THEN 'seq scanning heap'::text + WHEN 2 THEN 'index scanning heap'::text + WHEN 3 THEN 'sorting tuples'::text + WHEN 4 THEN 'writing new heap'::text + WHEN 5 THEN 'swapping relation files'::text + WHEN 6 THEN 'rebuilding index'::text + WHEN 7 THEN 'performing final cleanup'::text + ELSE NULL::text + END AS phase, + (s.param3)::oid AS repack_index_relid, + s.param4 AS heap_tuples_scanned, + s.param5 AS heap_tuples_written, + s.param6 AS heap_blks_total, + s.param7 AS heap_blks_scanned, + s.param8 AS index_rebuild_count + FROM (pg_stat_get_progress_info('REPACK'::text) s(pid, datid, relid, pa= ram1, param2, param3, param4, param5, param6, param7, param8, param9, param= 10, param11, param12, param13, param14, param15, param16, param17, param18,= param19, param20) + LEFT JOIN pg_database d ON ((s.datid =3D d.oid))); pg_stat_progress_vacuum| SELECT s.pid, s.datid, d.datname, diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluste= r.sql index b7115f86104..c976823a3cb 100644 --- a/src/test/regress/sql/cluster.sql +++ b/src/test/regress/sql/cluster.sql @@ -76,7 +76,6 @@ INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should f= ail'); SELECT conname FROM pg_constraint WHERE conrelid =3D 'clstr_tst'::regclass ORDER BY 1; =20 - SELECT relname, relkind, EXISTS(SELECT 1 FROM pg_class WHERE oid =3D c.reltoastrelid) AS hastoa= st FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname; @@ -229,6 +228,24 @@ SELECT relname, old.level, old.relkind, old.relfilenod= e =3D new.relfilenode FROM o CLUSTER clstrpart; ALTER TABLE clstrpart SET WITHOUT CLUSTER; ALTER TABLE clstrpart CLUSTER ON clstrpart_idx; + +-- Check that REPACK sets new relfilenodes: it should process exactly the = same +-- tables as CLUSTER did. +DROP TABLE old_cluster_info; +DROP TABLE new_cluster_info; +CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, = relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class= c ON c.oid=3Dtree.relid ; +REPACK clstrpart USING INDEX clstrpart_idx; +CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, = relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class= c ON c.oid=3Dtree.relid ; +SELECT relname, old.level, old.relkind, old.relfilenode =3D new.relfilenod= e FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname)= ORDER BY relname COLLATE "C"; + +-- And finally the same for REPACK w/o index. +DROP TABLE old_cluster_info; +DROP TABLE new_cluster_info; +CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, = relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class= c ON c.oid=3Dtree.relid ; +REPACK clstrpart; +CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, = relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class= c ON c.oid=3Dtree.relid ; +SELECT relname, old.level, old.relkind, old.relfilenode =3D new.relfilenod= e FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname)= ORDER BY relname COLLATE "C"; + DROP TABLE clstrpart; =20 -- Ownership of partitions is checked @@ -313,6 +330,57 @@ EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHE= RE -a =3D -3 ORDER BY -a, b; SELECT * FROM clstr_expression WHERE -a =3D -3 ORDER BY -a, b; COMMIT; =20 +---------------------------------------------------------------------- +-- +-- REPACK +-- +---------------------------------------------------------------------- + +-- REPACK handles individual tables identically to CLUSTER, but it's worth +-- checking if it handles table hierarchies identically as well. +REPACK clstr_tst USING INDEX clstr_tst_c; + +-- Verify that inheritance link still works +INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table 2'); +SELECT a,b,c,substring(d for 30), length(d) from clstr_tst; + +-- Verify that foreign key link still works +INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail'); + +SELECT conname FROM pg_constraint WHERE conrelid =3D 'clstr_tst'::regclass +ORDER BY 1; + +-- Verify partial analyze works +REPACK (ANALYZE) clstr_tst (a); +REPACK (ANALYZE) clstr_tst; +REPACK (VERBOSE) clstr_tst (a); + +-- REPACK w/o argument performs no ordering, so we can only check which ta= bles +-- have the relfilenode changed. +RESET SESSION AUTHORIZATION; +CREATE TEMP TABLE relnodes_old AS +(SELECT relname, relfilenode +FROM pg_class +WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3')); + +SET SESSION AUTHORIZATION regress_clstr_user; +SET client_min_messages =3D ERROR; -- order of "skipping" warnings may va= ry +REPACK; +RESET client_min_messages; + +RESET SESSION AUTHORIZATION; +CREATE TEMP TABLE relnodes_new AS +(SELECT relname, relfilenode +FROM pg_class +WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3')); + +-- Do the actual comparison. Unlike CLUSTER, clstr_3 should have been +-- processed because there is nothing like clustering index here. +SELECT o.relname FROM relnodes_old o +JOIN relnodes_new n ON o.relname =3D n.relname +WHERE o.relfilenode <> n.relfilenode +ORDER BY o.relname; + -- clean up DROP TABLE clustertest; DROP TABLE clstr_1; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs= .list index 09e7f1d420e..c81d93d0e5a 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2569,6 +2569,8 @@ ReorderBufferTupleCidKey ReorderBufferUpdateProgressTxnCB ReorderTuple RepOriginId +RepackCommand +RepackStmt ReparameterizeForeignPathByChild_function ReplaceVarsFromTargetList_context ReplaceVarsNoMatchOption --=20 2.47.3 --=-=-= Content-Type: text/x-diff Content-Disposition: attachment; filename=v30-0002-Refactor-index_concurrently_create_copy-for-use-with.patch ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-01-12 16:30 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-05-27 07:06 [PATCH 2/3] Fix WAL skipping feature Kyotaro Horiguchi <[email protected]> 2023-08-09 07:56 [PATCH v4 4/7] Row pattern recognition patch (executor). Tatsuo Ishii <[email protected]> 2026-01-12 16:30 [PATCH 1/6] Add REPACK command Antonin Houska <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox