($INBOX_DIR/description missing)help / color / mirror / Atom feed
[PATCH 4/7] Add infrastructure to WAL-logging skip feature 7+ messages / 3 participants [nested] [flat]
* [PATCH 4/7] Add infrastructure to WAL-logging skip feature @ 2018-10-11 07:00 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Kyotaro Horiguchi @ 2018-10-11 07:00 UTC (permalink / raw) We used to optimize WAL-logging for truncation of in-transaction crated tables in minimal mode by just signaling by HEAP_INSERT_SKIP_WAL option on heap operations. This mechanism can emit WAL records that results in corrupt state for certain series of in-transaction operations. This patch provides infrastructure to track pending at-commit fsyncs for a relation and in-transaction truncations. heap_register_sync() should be used to start tracking before batch operations like COPY and CLUSTER, and use BufferNeedsWAL() instead of RelationNeedsWAL() at the places related to WAL-logging about heap-modifying operations. --- src/backend/access/heap/heapam.c | 31 ++++ src/backend/access/transam/xact.c | 11 ++ src/backend/catalog/storage.c | 344 +++++++++++++++++++++++++++++++++--- src/backend/commands/tablecmds.c | 3 +- src/backend/storage/buffer/bufmgr.c | 40 ++++- src/backend/utils/cache/relcache.c | 3 + src/include/access/heapam.h | 1 + src/include/catalog/storage.h | 5 + src/include/storage/bufmgr.h | 2 + src/include/utils/rel.h | 8 + 10 files changed, 417 insertions(+), 31 deletions(-) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index fe5d939c45..024620ddc1 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -51,6 +51,7 @@ #include "access/xloginsert.h" #include "access/xlogutils.h" #include "catalog/catalog.h" +#include "catalog/storage.h" #include "miscadmin.h" #include "pgstat.h" #include "port/atomics.h" @@ -8829,3 +8830,33 @@ heap_mask(char *pagedata, BlockNumber blkno) } } } + +/* + * heap_register_sync - register a heap to be synced to disk at commit + * + * This can be used to skip WAL-logging changes on a relation file that has + * been created in the same transaction. This makes note of the current size of + * the relation, and ensures that when the relation is extended, any changes + * to the new blocks in the heap, in the same transaction, will not be + * WAL-logged. Instead, the heap contents are flushed to disk at commit, + * like heap_sync() does. + * + * This does the same for the TOAST heap, if any. Indexes are not affected. + */ +void +heap_register_sync(Relation rel) +{ + /* non-WAL-logged tables never need fsync */ + if (!RelationNeedsWAL(rel)) + return; + + RecordWALSkipping(rel); + if (OidIsValid(rel->rd_rel->reltoastrelid)) + { + Relation toastrel; + + toastrel = heap_open(rel->rd_rel->reltoastrelid, AccessShareLock); + RecordWALSkipping(toastrel); + heap_close(toastrel, AccessShareLock); + } +} diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index c3214d4f4d..32a6a877f3 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -2022,6 +2022,9 @@ CommitTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrDoPendingSyncs(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2254,6 +2257,9 @@ PrepareTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrDoPendingSyncs(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2579,6 +2585,7 @@ AbortTransaction(void) AtAbort_Notify(); AtEOXact_RelationMap(false, is_parallel_worker); AtAbort_Twophase(); + smgrDoPendingSyncs(false); /* abandone pending syncs */ /* * Advertise the fact that we aborted in pg_xact (assuming that we got as @@ -4097,6 +4104,8 @@ ReleaseSavepoint(const char *name) (errcode(ERRCODE_S_E_INVALID_SPECIFICATION), errmsg("savepoint \"%s\" does not exist within current savepoint level", name))); + smgrProcessWALRequirementInval(s->subTransactionId, true); + /* * Mark "commit pending" all subtransactions up to the target * subtransaction. The actual commits will happen when control gets to @@ -4206,6 +4215,8 @@ RollbackToSavepoint(const char *name) (errcode(ERRCODE_S_E_INVALID_SPECIFICATION), errmsg("savepoint \"%s\" does not exist within current savepoint level", name))); + smgrProcessWALRequirementInval(s->subTransactionId, false); + /* * Mark "abort pending" all subtransactions up to the target * subtransaction. The actual aborts will happen when control gets to diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index 0302507e6f..a0cf8d3e27 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -28,6 +28,7 @@ #include "catalog/storage_xlog.h" #include "storage/freespace.h" #include "storage/smgr.h" +#include "utils/hsearch.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -62,6 +63,54 @@ typedef struct PendingRelDelete static PendingRelDelete *pendingDeletes = NULL; /* head of linked list */ +/* + * We also track relation files (RelFileNode values) that have been created + * in the same transaction, and that have been modified without WAL-logging + * the action (an optimization possible with wal_level=minimal). When we are + * about to skip WAL-logging, a PendingRelSync entry is created, and + * 'skip_wal_min_blk' is set to the current size of the relation. Any operations + * on blocks < skip_wal_min_blk need to be WAL-logged as usual, but for + * operations on higher blocks, WAL-logging is skipped. + + * + * NB: after WAL-logging has been skipped for a block, we must not WAL-log + * any subsequent actions on the same block either. Replaying the WAL record + * of the subsequent action might fail otherwise, as the "before" state of + * the block might not match, as the earlier actions were not WAL-logged. + * Likewise, after we have WAL-logged an operation for a block, we must + * WAL-log any subsequent operations on the same page as well. Replaying + * a possible full-page-image from the earlier WAL record would otherwise + * revert the page to the old state, even if we sync the relation at end + * of transaction. + * + * If a relation is truncated (without creating a new relfilenode), and we + * emit a WAL record of the truncation, we can't skip WAL-logging for any + * of the truncated blocks anymore, as replaying the truncation record will + * destroy all the data inserted after that. But if we have already decided + * to skip WAL-logging changes to a relation, and the relation is truncated, + * we don't need to WAL-log the truncation either. + * + * This mechanism is currently only used by heaps. Indexes are always + * WAL-logged. Also, this only applies for wal_level=minimal; with higher + * WAL levels we need the WAL for PITR/replication anyway. + */ +typedef struct RelWalRequirement +{ + RelFileNode relnode; /* relation created in same xact */ + BlockNumber skip_wal_min_blk;/* WAL-logging skipped for blocks >= + * skip_wal_min_blk */ + BlockNumber wal_log_min_blk; /* The minimum blk number that requires + * WAL-logging even if skipped by the above*/ + SubTransactionId invalidate_sxid; /* subxid where this entry is + * invalidated */ +} RelWalRequirement; + +/* Relations that need to be fsync'd at commit */ +static HTAB *relWalRequirements = NULL; +static int walreq_pending_invals = 0; + +static RelWalRequirement *getWalRequirementEntry(Relation rel, bool create); + /* * RelationCreateStorage * Create physical storage for a relation. @@ -259,37 +308,114 @@ RelationTruncate(Relation rel, BlockNumber nblocks) */ if (RelationNeedsWAL(rel)) { - /* - * Make an XLOG entry reporting the file truncation. - */ - XLogRecPtr lsn; - xl_smgr_truncate xlrec; + RelWalRequirement *walreq; - xlrec.blkno = nblocks; - xlrec.rnode = rel->rd_node; - xlrec.flags = SMGR_TRUNCATE_ALL; + /* get pending sync entry, create if not yet */ + walreq = getWalRequirementEntry(rel, true); - XLogBeginInsert(); - XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + if (walreq->skip_wal_min_blk == InvalidBlockNumber || + walreq->skip_wal_min_blk < nblocks) + { + /* + * This is the first time truncation of this relation in this + * transaction or truncation that leaves pages that need at-commit + * fsync. Make an XLOG entry reporting the file truncation. + */ + XLogRecPtr lsn; + xl_smgr_truncate xlrec; - lsn = XLogInsert(RM_SMGR_ID, - XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + xlrec.blkno = nblocks; + xlrec.rnode = rel->rd_node; + xlrec.flags = SMGR_TRUNCATE_ALL; - /* - * Flush, because otherwise the truncation of the main relation might - * hit the disk before the WAL record, and the truncation of the FSM - * or visibility map. If we crashed during that window, we'd be left - * with a truncated heap, but the FSM or visibility map would still - * contain entries for the non-existent heap pages. - */ - if (fsm || vm) - XLogFlush(lsn); + XLogBeginInsert(); + XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + + lsn = XLogInsert(RM_SMGR_ID, + XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + + /* + * Flush, because otherwise the truncation of the main relation + * might hit the disk before the WAL record, and the truncation of + * the FSM or visibility map. If we crashed during that window, + * we'd be left with a truncated heap, but the FSM or visibility + * map would still contain entries for the non-existent heap + * pages. + */ + if (fsm || vm) + XLogFlush(lsn); + + /* no longer skip WAL-logging for the blocks */ + rel->rd_walrequirement->wal_log_min_blk = nblocks; + } } /* Do the real work */ smgrtruncate(rel->rd_smgr, MAIN_FORKNUM, nblocks); } +/* + * getWalRequirementEntry: get WAL requirement entry. + * + * Returns WAL requirement entry for the relation. The entry tracks + * WAL-skipping blocks for the relation. The WAL-skipped blocks need fsync at + * commit time. Creates one if needed when create is true. + */ +static RelWalRequirement * +getWalRequirementEntry(Relation rel, bool create) +{ + RelWalRequirement *walreq_entry = NULL; + bool found; + + if (rel->rd_walrequirement) + return rel->rd_walrequirement; + + /* we know we don't have pending sync entry */ + if (!create && rel->rd_nowalrequirement) + return NULL; + + if (!relWalRequirements) + { + /* First time through: initialize the hash table */ + HASHCTL ctl; + + if (!create) + return NULL; + + MemSet(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(RelFileNode); + ctl.entrysize = sizeof(RelWalRequirement); + ctl.hash = tag_hash; + relWalRequirements = hash_create("pending relation sync table", 5, + &ctl, HASH_ELEM | HASH_FUNCTION); + } + + walreq_entry = (RelWalRequirement *) + hash_search(relWalRequirements, (void *) &rel->rd_node, + create ? HASH_ENTER: HASH_FIND, &found); + + if (!walreq_entry) + { + /* prevent further hash lookup */ + rel->rd_nowalrequirement = true; + return NULL; + } + + /* new entry created */ + if (!found) + { + walreq_entry->wal_log_min_blk = InvalidBlockNumber; + walreq_entry->skip_wal_min_blk = InvalidBlockNumber; + walreq_entry->invalidate_sxid = InvalidSubTransactionId; + } + + /* hold shortcut in Relation */ + rel->rd_nowalrequirement = false; + rel->rd_walrequirement = walreq_entry; + + return walreq_entry; +} + /* * smgrDoPendingDeletes() -- Take care of relation deletes at end of xact. * @@ -367,6 +493,34 @@ smgrDoPendingDeletes(bool isCommit) } } +/* + * RelationInvalidateWALRequirements() -- invalidate wal requirement entry + */ +void +RelationInvalidateWALRequirements(Relation rel) +{ + RelWalRequirement *walreq; + + /* we know we don't have one */ + if (rel->rd_nowalrequirement) + return; + + walreq = getWalRequirementEntry(rel, false); + + if (!walreq) + return; + + /* + * The state is reset at subtransaction commit/abort. No invalidation + * request must not come for the same relation in the same subtransaction. + */ + Assert(walreq->invalidate_sxid == InvalidSubTransactionId); + + walreq_pending_invals++; + walreq->invalidate_sxid = GetCurrentSubTransactionId(); +} + + /* * smgrGetPendingDeletes() -- Get a list of non-temp relations to be deleted. * @@ -418,6 +572,154 @@ smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr) return nrels; } + +/* + * Remember that the given relation doesn't need WAL-logging for the blocks + * after the current block size and the blocks are going to be sync'd at + * commit. + */ +void +RecordWALSkipping(Relation rel) +{ + BlockNumber nblocks; + RelWalRequirement *walreq; + + Assert(RelationNeedsWAL(rel)); + + /* get pending sync entry, create if not yet */ + walreq = getWalRequirementEntry(rel, true); + + nblocks = RelationGetNumberOfBlocks(rel); + + /* + * Record only the first registration. + */ + if (walreq->skip_wal_min_blk != InvalidBlockNumber) + return; + + walreq->skip_wal_min_blk = nblocks; +} + +/* + * Do changes to given heap page need to be WAL-logged? + * + * This takes into account any previous RecordPendingSync() requests. + * + * Note that it is required to check this before creating any WAL records for + * heap pages - it is not merely an optimization! WAL-logging a record, when + * we have already skipped a previous WAL record for the same page could lead + * to failure at WAL replay, as the "before" state expected by the record + * might not match what's on disk. Also, if the heap was truncated earlier, we + * must WAL-log any changes to the once-truncated blocks, because replaying + * the truncation record will destroy them. + */ +bool +BufferNeedsWAL(Relation rel, Buffer buf) +{ + BlockNumber blkno = InvalidBlockNumber; + RelWalRequirement *walreq; + + if (!RelationNeedsWAL(rel)) + return false; + + /* fetch exising pending sync entry */ + walreq = getWalRequirementEntry(rel, false); + + /* + * no point in doing further work if we know that we don't have special + * WAL requirement + */ + if (!walreq) + return true; + + Assert(BufferIsValid(buf)); + + blkno = BufferGetBlockNumber(buf); + + /* + * We don't skip WAL-logging for pages that once done. + */ + if (walreq->skip_wal_min_blk == InvalidBlockNumber || + walreq->skip_wal_min_blk > blkno) + return true; + + /* + * we don't skip WAL-logging for blocks that have got WAL-logged + * truncation + */ + if (walreq->wal_log_min_blk != InvalidBlockNumber && + walreq->wal_log_min_blk <= blkno) + return true; + + return false; +} + +/* + * Sync to disk any relations that we have skipped WAL-logging earlier. + */ +void +smgrDoPendingSyncs(bool isCommit) +{ + if (!relWalRequirements) + return; + + if (isCommit) + { + HASH_SEQ_STATUS status; + RelWalRequirement *walreq; + + hash_seq_init(&status, relWalRequirements); + + while ((walreq = hash_seq_search(&status)) != NULL) + { + if (walreq->skip_wal_min_blk != InvalidBlockNumber) + { + FlushRelationBuffersWithoutRelCache(walreq->relnode, false); + smgrimmedsync(smgropen(walreq->relnode, InvalidBackendId), + MAIN_FORKNUM); + } + } + } + + hash_destroy(relWalRequirements); + relWalRequirements = NULL; +} + +/* + * Process pending invalidation of WAL requirements happened in the + * subtransaction + */ +void +smgrProcessWALRequirementInval(SubTransactionId sxid, bool isCommit) +{ + HASH_SEQ_STATUS status; + RelWalRequirement *walreq; + + if (!relWalRequirements || walreq_pending_invals == 0) + return; + + /* + * It may take some time when there're many relWalRequirements entries. We + * expect that we don't have relWalRequirements in almost all cases. + */ + hash_seq_init(&status, relWalRequirements); + + while ((walreq = hash_seq_search(&status)) != NULL) + { + if (walreq->invalidate_sxid == sxid) + { + Assert(walreq_pending_invals > 0); + walreq->invalidate_sxid = InvalidSubTransactionId; + walreq_pending_invals--; + if (isCommit) + { + walreq->skip_wal_min_blk = InvalidBlockNumber; + walreq->wal_log_min_blk = InvalidBlockNumber; + } + } + } +} + /* * PostPrepare_smgr -- Clean up after a successful PREPARE * diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3183b2aaa1..45bb0b5614 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -11587,11 +11587,12 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode) /* * Create and copy all forks of the relation, and schedule unlinking of - * old physical files. + * old physical files. Pending syncs for the old node is no longer needed. * * NOTE: any conflict in relfilenode value will be caught in * RelationCreateStorage(). */ + RelationInvalidateWALRequirements(rel); RelationCreateStorage(newrnode, rel->rd_rel->relpersistence); /* copy main fork */ diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 273e2f385f..a9741f138c 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -451,6 +451,7 @@ static BufferDesc *BufferAlloc(SMgrRelation smgr, BufferAccessStrategy strategy, bool *foundPtr); static void FlushBuffer(BufferDesc *buf, SMgrRelation reln); +static void FlushRelationBuffers_common(SMgrRelation smgr, bool islocal); static void AtProcExit_Buffers(int code, Datum arg); static void CheckForBufferLeaks(void); static int rnode_comparator(const void *p1, const void *p2); @@ -3153,20 +3154,41 @@ 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)); +} + +/* + * Like FlushRelationBuffers(), but the relation is specified by a + * RelFileNode + */ +void +FlushRelationBuffersWithoutRelCache(RelFileNode rnode, bool islocal) +{ + FlushRelationBuffers_common(smgropen(rnode, InvalidBackendId), islocal); +} + +/* + * Code shared between functions FlushRelationBuffers() and + * FlushRelationBuffersWithoutRelCache(). + */ +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)) { @@ -3183,7 +3205,7 @@ FlushRelationBuffers(Relation rel) PageSetChecksumInplace(localpage, bufHdr->tag.blockNum); - smgrwrite(rel->rd_smgr, + smgrwrite(smgr, bufHdr->tag.forkNum, bufHdr->tag.blockNum, localpage, @@ -3213,18 +3235,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 84609e0725..95e834d45e 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -75,6 +75,7 @@ #include "partitioning/partdesc.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rowsecurity.h" +#include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/smgr.h" #include "utils/array.h" @@ -5625,6 +5626,8 @@ load_relcache_init_file(bool shared) rel->rd_newRelfilenodeSubid = InvalidSubTransactionId; rel->rd_amcache = NULL; MemSet(&rel->pgstat_info, 0, sizeof(rel->pgstat_info)); + rel->rd_nowalrequirement = false; + rel->rd_walrequirement = NULL; /* * Recompute lock and physical addressing info. This is needed in diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index 945ca50616..509394bb35 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -174,6 +174,7 @@ extern void simple_heap_delete(Relation relation, ItemPointer tid); extern void simple_heap_update(Relation relation, ItemPointer otid, HeapTuple tup); +extern void heap_register_sync(Relation relation); extern void heap_sync(Relation relation); /* in heap/pruneheap.c */ diff --git a/src/include/catalog/storage.h b/src/include/catalog/storage.h index 9f638be924..76178b87f2 100644 --- a/src/include/catalog/storage.h +++ b/src/include/catalog/storage.h @@ -22,6 +22,7 @@ extern void RelationCreateStorage(RelFileNode rnode, char relpersistence); extern void RelationDropStorage(Relation rel); extern void RelationPreserveStorage(RelFileNode rnode, bool atCommit); extern void RelationTruncate(Relation rel, BlockNumber nblocks); +extern void RelationInvalidateWALRequirements(Relation rel); /* * These functions used to be in storage/smgr/smgr.c, which explains the @@ -29,6 +30,10 @@ extern void RelationTruncate(Relation rel, BlockNumber nblocks); */ extern void smgrDoPendingDeletes(bool isCommit); extern int smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr); +extern void smgrDoPendingSyncs(bool isCommit); +extern void smgrProcessWALRequirementInval(SubTransactionId sxid, bool isCommit); +extern void RecordWALSkipping(Relation rel); +bool BufferNeedsWAL(Relation rel, Buffer buf); extern void AtSubCommit_smgr(void); extern void AtSubAbort_smgr(void); extern void PostPrepare_smgr(void); diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index c5826f691d..8a9ea041dd 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -189,6 +189,8 @@ 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 54028515a7..30f0d5bd83 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -198,6 +198,14 @@ typedef struct RelationData /* use "struct" here to avoid needing to include pgstat.h: */ struct PgStat_TableStatus *pgstat_info; /* statistics collection area */ + + /* + * rd_nowalrequirement is true if this relation is known not to have + * special WAL requirements. Otherwise we need to ask smgr for an entry + * if rd_walrequirement is NULL. + */ + bool rd_nowalrequirement; + struct RelWalRequirement *rd_walrequirement; } RelationData; -- 2.16.3 ----Next_Part(Mon_Mar_25_21_32_04_2019_838)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v8-0005-Fix-WAL-skipping-feature.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH 3/4] Add infrastructure to WAL-logging skip feature @ 2018-10-11 07:00 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Kyotaro Horiguchi @ 2018-10-11 07:00 UTC (permalink / raw) We used to optimize WAL-logging for truncation of in-transaction crated tables in minimal mode by just singaling by HEAP_INSERT_SKIP_WAL option on heap operations. This mechanism can emit WAL records that results in corrupt state for certain series of in-transaction operations. This patch provides infrastructure to track pending at-comit fsyncs for a relation and in-transaction truncations. heap_register_sync() should be used to start tracking before batch operations like COPY and CLUSTER, and use BufferNeedsWAL() instead of RelationNeedsWAL() at the places related to WAL-logging about heap-modifying operations. --- src/backend/access/heap/heapam.c | 31 ++++ src/backend/access/transam/xact.c | 7 + src/backend/catalog/storage.c | 317 +++++++++++++++++++++++++++++++++--- src/backend/commands/tablecmds.c | 3 +- src/backend/storage/buffer/bufmgr.c | 40 ++++- src/backend/utils/cache/relcache.c | 13 ++ src/include/access/heapam.h | 1 + src/include/catalog/storage.h | 5 +- src/include/storage/bufmgr.h | 2 + src/include/utils/rel.h | 8 + 10 files changed, 395 insertions(+), 32 deletions(-) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index dc3499349b..5ea5ff5848 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -50,6 +50,7 @@ #include "access/xloginsert.h" #include "access/xlogutils.h" #include "catalog/catalog.h" +#include "catalog/storage.h" #include "miscadmin.h" #include "pgstat.h" #include "port/atomics.h" @@ -9079,3 +9080,33 @@ heap_mask(char *pagedata, BlockNumber blkno) } } } + +/* + * heap_register_sync - register a heap to be synced to disk at commit + * + * This can be used to skip WAL-logging changes on a relation file that has + * been created in the same transaction. This makes note of the current size of + * the relation, and ensures that when the relation is extended, any changes + * to the new blocks in the heap, in the same transaction, will not be + * WAL-logged. Instead, the heap contents are flushed to disk at commit, + * like heap_sync() does. + * + * This does the same for the TOAST heap, if any. Indexes are not affected. + */ +void +heap_register_sync(Relation rel) +{ + /* non-WAL-logged tables never need fsync */ + if (!RelationNeedsWAL(rel)) + return; + + RecordPendingSync(rel); + if (OidIsValid(rel->rd_rel->reltoastrelid)) + { + Relation toastrel; + + toastrel = heap_open(rel->rd_rel->reltoastrelid, AccessShareLock); + RecordPendingSync(toastrel); + heap_close(toastrel, AccessShareLock); + } +} diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index e93262975d..6d62d6e34f 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -2021,6 +2021,9 @@ CommitTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrDoPendingSyncs(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2250,6 +2253,9 @@ PrepareTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrDoPendingSyncs(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2575,6 +2581,7 @@ AbortTransaction(void) AtAbort_Notify(); AtEOXact_RelationMap(false, is_parallel_worker); AtAbort_Twophase(); + smgrDoPendingSyncs(false); /* abandone pending syncs */ /* * Advertise the fact that we aborted in pg_xact (assuming that we got as diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index 0302507e6f..26dc3ddb1b 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -28,6 +28,7 @@ #include "catalog/storage_xlog.h" #include "storage/freespace.h" #include "storage/smgr.h" +#include "utils/hsearch.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -62,6 +63,49 @@ typedef struct PendingRelDelete static PendingRelDelete *pendingDeletes = NULL; /* head of linked list */ +/* + * We also track relation files (RelFileNode values) that have been created + * in the same transaction, and that have been modified without WAL-logging + * the action (an optimization possible with wal_level=minimal). When we are + * about to skip WAL-logging, a PendingRelSync entry is created, and + * 'sync_above' is set to the current size of the relation. Any operations + * on blocks < sync_above need to be WAL-logged as usual, but for operations + * on higher blocks, WAL-logging is skipped. + * + * NB: after WAL-logging has been skipped for a block, we must not WAL-log + * any subsequent actions on the same block either. Replaying the WAL record + * of the subsequent action might fail otherwise, as the "before" state of + * the block might not match, as the earlier actions were not WAL-logged. + * Likewise, after we have WAL-logged an operation for a block, we must + * WAL-log any subsequent operations on the same page as well. Replaying + * a possible full-page-image from the earlier WAL record would otherwise + * revert the page to the old state, even if we sync the relation at end + * of transaction. + * + * If a relation is truncated (without creating a new relfilenode), and we + * emit a WAL record of the truncation, we can't skip WAL-logging for any + * of the truncated blocks anymore, as replaying the truncation record will + * destroy all the data inserted after that. But if we have already decided + * to skip WAL-logging changes to a relation, and the relation is truncated, + * we don't need to WAL-log the truncation either. + * + * This mechanism is currently only used by heaps. Indexes are always + * WAL-logged. Also, this only applies for wal_level=minimal; with higher + * WAL levels we need the WAL for PITR/replication anyway. + */ +typedef struct PendingRelSync +{ + RelFileNode relnode; /* relation created in same xact */ + BlockNumber sync_above; /* WAL-logging skipped for blocks >= + * sync_above */ + BlockNumber truncated_to; /* truncation WAL record was written */ +} PendingRelSync; + +/* Relations that need to be fsync'd at commit */ +static HTAB *pendingSyncs = NULL; + +static PendingRelSync *getPendingSyncEntry(Relation rel, bool create); + /* * RelationCreateStorage * Create physical storage for a relation. @@ -259,37 +303,117 @@ RelationTruncate(Relation rel, BlockNumber nblocks) */ if (RelationNeedsWAL(rel)) { - /* - * Make an XLOG entry reporting the file truncation. - */ - XLogRecPtr lsn; - xl_smgr_truncate xlrec; + PendingRelSync *pending_sync; - xlrec.blkno = nblocks; - xlrec.rnode = rel->rd_node; - xlrec.flags = SMGR_TRUNCATE_ALL; + /* get pending sync entry, create if not yet */ + pending_sync = getPendingSyncEntry(rel, true); - XLogBeginInsert(); - XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + if (pending_sync->sync_above == InvalidBlockNumber || + pending_sync->sync_above < nblocks) + { + /* + * This is the first time truncation of this relation in this + * transaction or truncation that leaves pages that need at-commit + * fsync. Make an XLOG entry reporting the file truncation. + */ + XLogRecPtr lsn; + xl_smgr_truncate xlrec; - lsn = XLogInsert(RM_SMGR_ID, - XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + xlrec.blkno = nblocks; + xlrec.rnode = rel->rd_node; + xlrec.flags = SMGR_TRUNCATE_ALL; - /* - * Flush, because otherwise the truncation of the main relation might - * hit the disk before the WAL record, and the truncation of the FSM - * or visibility map. If we crashed during that window, we'd be left - * with a truncated heap, but the FSM or visibility map would still - * contain entries for the non-existent heap pages. - */ - if (fsm || vm) - XLogFlush(lsn); + XLogBeginInsert(); + XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + + lsn = XLogInsert(RM_SMGR_ID, + XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + + elog(DEBUG2, "WAL-logged truncation of rel %u/%u/%u to %u blocks", + rel->rd_node.spcNode, rel->rd_node.dbNode, rel->rd_node.relNode, + nblocks); + + /* + * Flush, because otherwise the truncation of the main relation + * might hit the disk before the WAL record, and the truncation of + * the FSM or visibility map. If we crashed during that window, + * we'd be left with a truncated heap, but the FSM or visibility + * map would still contain entries for the non-existent heap + * pages. + */ + if (fsm || vm) + XLogFlush(lsn); + + rel->pending_sync->truncated_to = nblocks; + } } /* Do the real work */ smgrtruncate(rel->rd_smgr, MAIN_FORKNUM, nblocks); } +/* + * getPendingSyncEntry: get pendig sync entry. + * + * Returns pending sync entry for the relation. The entry tracks pending + * at-commit fsyncs for the relation. Creates one if needed when create is + * true. + */ +static PendingRelSync * +getPendingSyncEntry(Relation rel, bool create) +{ + PendingRelSync *pendsync_entry = NULL; + bool found; + + if (rel->pending_sync) + return rel->pending_sync; + + /* we know we don't have pending sync entry */ + if (!create && rel->no_pending_sync) + return NULL; + + if (!pendingSyncs) + { + /* First time through: initialize the hash table */ + HASHCTL ctl; + + if (!create) + return NULL; + + MemSet(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(RelFileNode); + ctl.entrysize = sizeof(PendingRelSync); + ctl.hash = tag_hash; + pendingSyncs = hash_create("pending relation sync table", 5, + &ctl, HASH_ELEM | HASH_FUNCTION); + } + + elog(DEBUG2, "getPendingSyncEntry: accessing hash for %d", + rel->rd_node.relNode); + pendsync_entry = (PendingRelSync *) + hash_search(pendingSyncs, (void *) &rel->rd_node, + create ? HASH_ENTER: HASH_FIND, &found); + + if (!pendsync_entry) + { + rel->no_pending_sync = true; + return NULL; + } + + /* new entry created */ + if (!found) + { + pendsync_entry->truncated_to = InvalidBlockNumber; + pendsync_entry->sync_above = InvalidBlockNumber; + } + + /* hold shortcut in Relation */ + rel->no_pending_sync = false; + rel->pending_sync = pendsync_entry; + + return pendsync_entry; +} + /* * smgrDoPendingDeletes() -- Take care of relation deletes at end of xact. * @@ -367,6 +491,24 @@ smgrDoPendingDeletes(bool isCommit) } } +/* + * RelationRemovePendingSync() -- remove pendingSync entry for a relation + */ +void +RelationRemovePendingSync(Relation rel) +{ + bool found; + + rel->pending_sync = NULL; + rel->no_pending_sync = true; + if (pendingSyncs) + { + elog(DEBUG2, "RelationRemovePendingSync: accessing hash"); + hash_search(pendingSyncs, (void *) &rel->rd_node, HASH_REMOVE, &found); + } +} + + /* * smgrGetPendingDeletes() -- Get a list of non-temp relations to be deleted. * @@ -418,6 +560,139 @@ smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr) return nrels; } + +/* + * Remember that the given relation needs to be sync'd at commit, because we + * are going to skip WAL-logging subsequent actions to it. + */ +void +RecordPendingSync(Relation rel) +{ + BlockNumber nblocks; + PendingRelSync *pending_sync; + + Assert(RelationNeedsWAL(rel)); + + /* get pending sync entry, create if not yet */ + pending_sync = getPendingSyncEntry(rel, true); + + nblocks = RelationGetNumberOfBlocks(rel); + + if (pending_sync->sync_above != InvalidBlockNumber) + { + elog(DEBUG2, + "pending sync for rel %u/%u/%u was already registered at block %u (new %u)", + rel->rd_node.spcNode, rel->rd_node.dbNode, rel->rd_node.relNode, + rel->pending_sync->sync_above, nblocks); + + return; + } + + elog(DEBUG2, + "registering new pending sync for rel %u/%u/%u at block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, rel->rd_node.relNode, + nblocks); + pending_sync->sync_above = nblocks; +} + +/* + * Do changes to given heap page need to be WAL-logged? + * + * This takes into account any previous RecordPendingSync() requests. + * + * Note that it is required to check this before creating any WAL records for + * heap pages - it is not merely an optimization! WAL-logging a record, when + * we have already skipped a previous WAL record for the same page could lead + * to failure at WAL replay, as the "before" state expected by the record + * might not match what's on disk. Also, if the heap was truncated earlier, we + * must WAL-log any changes to the once-truncated blocks, because replaying + * the truncation record will destroy them. + */ +bool +BufferNeedsWAL(Relation rel, Buffer buf) +{ + BlockNumber blkno = InvalidBlockNumber; + PendingRelSync *pending_sync; + + if (!RelationNeedsWAL(rel)) + return false; + + /* fetch exising pending sync entry */ + pending_sync = getPendingSyncEntry(rel, false); + + /* + * no point in doing further work if we know that we don't have pending + * sync + */ + if (!pending_sync) + return true; + + Assert(BufferIsValid(buf)); + + blkno = BufferGetBlockNumber(buf); + + /* we don't skip WAL-logging for pages that already done */ + if (pending_sync->sync_above == InvalidBlockNumber || + pending_sync->sync_above > blkno) + { + elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because sync_above is %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, rel->rd_node.relNode, + blkno, rel->pending_sync->sync_above); + return true; + } + + /* + * We have emitted a truncation record for this block. + */ + if (pending_sync->truncated_to != InvalidBlockNumber && + pending_sync->truncated_to <= blkno) + { + elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because it was truncated earlier in the same xact", + rel->rd_node.spcNode, rel->rd_node.dbNode, rel->rd_node.relNode, + blkno); + return true; + } + + elog(DEBUG2, "skipping WAL-logging for rel %u/%u/%u block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, rel->rd_node.relNode, + blkno); + + return false; +} + +/* + * Sync to disk any relations that we skipped WAL-logging for earlier. + */ +void +smgrDoPendingSyncs(bool isCommit) +{ + if (!pendingSyncs) + return; + + if (isCommit) + { + HASH_SEQ_STATUS status; + PendingRelSync *pending; + + hash_seq_init(&status, pendingSyncs); + + while ((pending = hash_seq_search(&status)) != NULL) + { + if (pending->sync_above != InvalidBlockNumber) + { + FlushRelationBuffersWithoutRelCache(pending->relnode, false); + smgrimmedsync(smgropen(pending->relnode, InvalidBackendId), MAIN_FORKNUM); + + elog(DEBUG2, "syncing rel %u/%u/%u", pending->relnode.spcNode, + pending->relnode.dbNode, pending->relnode.relNode); + } + } + } + + hash_destroy(pendingSyncs); + pendingSyncs = NULL; +} + /* * PostPrepare_smgr -- Clean up after a successful PREPARE * diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index a93b13c2fe..6190b3f605 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -11412,11 +11412,12 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode) /* * Create and copy all forks of the relation, and schedule unlinking of - * old physical files. + * old physical files. Pending syncs for the old node is no longer needed. * * NOTE: any conflict in relfilenode value will be caught in * RelationCreateStorage(). */ + RelationRemovePendingSync(rel); RelationCreateStorage(newrnode, rel->rd_rel->relpersistence); /* copy main fork */ diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 273e2f385f..a9741f138c 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -451,6 +451,7 @@ static BufferDesc *BufferAlloc(SMgrRelation smgr, BufferAccessStrategy strategy, bool *foundPtr); static void FlushBuffer(BufferDesc *buf, SMgrRelation reln); +static void FlushRelationBuffers_common(SMgrRelation smgr, bool islocal); static void AtProcExit_Buffers(int code, Datum arg); static void CheckForBufferLeaks(void); static int rnode_comparator(const void *p1, const void *p2); @@ -3153,20 +3154,41 @@ 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)); +} + +/* + * Like FlushRelationBuffers(), but the relation is specified by a + * RelFileNode + */ +void +FlushRelationBuffersWithoutRelCache(RelFileNode rnode, bool islocal) +{ + FlushRelationBuffers_common(smgropen(rnode, InvalidBackendId), islocal); +} + +/* + * Code shared between functions FlushRelationBuffers() and + * FlushRelationBuffersWithoutRelCache(). + */ +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)) { @@ -3183,7 +3205,7 @@ FlushRelationBuffers(Relation rel) PageSetChecksumInplace(localpage, bufHdr->tag.blockNum); - smgrwrite(rel->rd_smgr, + smgrwrite(smgr, bufHdr->tag.forkNum, bufHdr->tag.blockNum, localpage, @@ -3213,18 +3235,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 54a40ef00b..b5baa430db 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -75,6 +75,7 @@ #include "partitioning/partdesc.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rowsecurity.h" +#include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/smgr.h" #include "utils/array.h" @@ -412,6 +413,10 @@ AllocateRelationDesc(Form_pg_class relp) /* which we mark as a reference-counted tupdesc */ relation->rd_att->tdrefcount = 1; + /* We don't know if pending sync for this relation exists so far */ + relation->pending_sync = NULL; + relation->no_pending_sync = false; + MemoryContextSwitchTo(oldcxt); return relation; @@ -1813,6 +1818,10 @@ formrdesc(const char *relationName, Oid relationReltype, relation->rd_rel->relhasindex = true; } + /* We don't know if pending sync for this relation exists so far */ + relation->pending_sync = NULL; + relation->no_pending_sync = false; + /* * add new reldesc to relcache */ @@ -3207,6 +3216,10 @@ RelationBuildLocalRelation(const char *relname, else rel->rd_rel->relfilenode = relfilenode; + /* newly built relation has no pending sync */ + rel->no_pending_sync = true; + rel->pending_sync = NULL; + RelationInitLockInfo(rel); /* see lmgr.c */ RelationInitPhysicalAddr(rel); diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index ab0879138f..fab5052868 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -163,6 +163,7 @@ extern void simple_heap_delete(Relation relation, ItemPointer tid); extern void simple_heap_update(Relation relation, ItemPointer otid, HeapTuple tup); +extern void heap_register_sync(Relation relation); extern void heap_sync(Relation relation); extern void heap_update_snapshot(HeapScanDesc scan, Snapshot snapshot); diff --git a/src/include/catalog/storage.h b/src/include/catalog/storage.h index 9f638be924..95d7898e25 100644 --- a/src/include/catalog/storage.h +++ b/src/include/catalog/storage.h @@ -22,13 +22,16 @@ extern void RelationCreateStorage(RelFileNode rnode, char relpersistence); extern void RelationDropStorage(Relation rel); extern void RelationPreserveStorage(RelFileNode rnode, bool atCommit); extern void RelationTruncate(Relation rel, BlockNumber nblocks); - +extern void RelationRemovePendingSync(Relation rel); /* * These functions used to be in storage/smgr/smgr.c, which explains the * naming */ extern void smgrDoPendingDeletes(bool isCommit); extern int smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr); +extern void smgrDoPendingSyncs(bool isCommit); +extern void RecordPendingSync(Relation rel); +bool BufferNeedsWAL(Relation rel, Buffer buf); extern void AtSubCommit_smgr(void); extern void AtSubAbort_smgr(void); extern void PostPrepare_smgr(void); diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index c5826f691d..8a9ea041dd 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -189,6 +189,8 @@ 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 1d05465303..0f39f209d3 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -185,6 +185,14 @@ typedef struct RelationData /* use "struct" here to avoid needing to include pgstat.h: */ struct PgStat_TableStatus *pgstat_info; /* statistics collection area */ + + /* + * no_pending_sync is true if this relation is known not to have pending + * syncs. Elsewise searching for registered sync is required if + * pending_sync is NULL. + */ + bool no_pending_sync; + struct PendingRelSync *pending_sync; } RelationData; -- 2.16.3 ----Next_Part(Mon_Mar_04_12_24_48_2019_788)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v7-0004-Fix-WAL-skipping-feature.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH 4/8] Add infrastructure to WAL-logging skip feature @ 2019-03-26 06:34 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Kyotaro Horiguchi @ 2019-03-26 06:34 UTC (permalink / raw) We used to optimize WAL-logging for truncation of in-transaction crated tables in minimal mode by just signaling by HEAP_INSERT_SKIP_WAL option on heap operations. This mechanism can emit WAL records that results in corrupt state for certain series of in-transaction operations. This patch provides infrastructure to track pending at-commit fsyncs for a relation and in-transaction truncations. heap_register_sync() should be used to start tracking before batch operations like COPY and CLUSTER, and use BufferNeedsWAL() instead of RelationNeedsWAL() at the places related to WAL-logging about heap-modifying operations. --- src/backend/access/heap/heapam.c | 31 +++ src/backend/access/transam/xact.c | 11 + src/backend/catalog/storage.c | 418 ++++++++++++++++++++++++++++++++++-- src/backend/commands/tablecmds.c | 4 +- src/backend/storage/buffer/bufmgr.c | 39 +++- src/backend/utils/cache/relcache.c | 3 + src/include/access/heapam.h | 1 + src/include/catalog/storage.h | 8 + src/include/storage/bufmgr.h | 2 + src/include/utils/rel.h | 8 + 10 files changed, 493 insertions(+), 32 deletions(-) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index c6e71dba6b..5a8627507f 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -51,6 +51,7 @@ #include "access/xloginsert.h" #include "access/xlogutils.h" #include "catalog/catalog.h" +#include "catalog/storage.h" #include "miscadmin.h" #include "pgstat.h" #include "port/atomics.h" @@ -8800,3 +8801,33 @@ heap_mask(char *pagedata, BlockNumber blkno) } } } + +/* + * heap_register_sync - register a heap to be synced to disk at commit + * + * This can be used to skip WAL-logging changes on a relation file that has + * been created in the same transaction. This makes note of the current size of + * the relation, and ensures that when the relation is extended, any changes + * to the new blocks in the heap, in the same transaction, will not be + * WAL-logged. Instead, the heap contents are flushed to disk at commit, + * like heap_sync() does. + * + * This does the same for the TOAST heap, if any. Indexes are not affected. + */ +void +heap_register_sync(Relation rel) +{ + /* non-WAL-logged tables never need fsync */ + if (!RelationNeedsWAL(rel)) + return; + + RecordWALSkipping(rel); + if (OidIsValid(rel->rd_rel->reltoastrelid)) + { + Relation toastrel; + + toastrel = heap_open(rel->rd_rel->reltoastrelid, AccessShareLock); + RecordWALSkipping(toastrel); + heap_close(toastrel, AccessShareLock); + } +} diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index c3214d4f4d..ad7cb3bcb9 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -2022,6 +2022,9 @@ CommitTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrDoPendingSyncs(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2254,6 +2257,9 @@ PrepareTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrDoPendingSyncs(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2579,6 +2585,7 @@ AbortTransaction(void) AtAbort_Notify(); AtEOXact_RelationMap(false, is_parallel_worker); AtAbort_Twophase(); + smgrDoPendingSyncs(false); /* abandon pending syncs */ /* * Advertise the fact that we aborted in pg_xact (assuming that we got as @@ -4097,6 +4104,8 @@ ReleaseSavepoint(const char *name) (errcode(ERRCODE_S_E_INVALID_SPECIFICATION), errmsg("savepoint \"%s\" does not exist within current savepoint level", name))); + smgrProcessWALRequirementInval(s->subTransactionId, true); + /* * Mark "commit pending" all subtransactions up to the target * subtransaction. The actual commits will happen when control gets to @@ -4206,6 +4215,8 @@ RollbackToSavepoint(const char *name) (errcode(ERRCODE_S_E_INVALID_SPECIFICATION), errmsg("savepoint \"%s\" does not exist within current savepoint level", name))); + smgrProcessWALRequirementInval(s->subTransactionId, false); + /* * Mark "abort pending" all subtransactions up to the target * subtransaction. The actual aborts will happen when control gets to diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index 0302507e6f..be37174ef2 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -27,7 +27,7 @@ #include "catalog/storage.h" #include "catalog/storage_xlog.h" #include "storage/freespace.h" -#include "storage/smgr.h" +#include "utils/hsearch.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -62,6 +62,58 @@ typedef struct PendingRelDelete static PendingRelDelete *pendingDeletes = NULL; /* head of linked list */ +/* + * We also track relation files (RelFileNode values) that have been created + * in the same transaction, and that have been modified without WAL-logging + * the action (an optimization possible with wal_level=minimal). When we are + * about to skip WAL-logging, a RelWalRequirement entry is created, and + * 'skip_wal_min_blk' is set to the current size of the relation. Any operations + * on blocks < skip_wal_min_blk need to be WAL-logged as usual, but for + * operations on higher blocks, WAL-logging is skipped. + + * + * NB: after WAL-logging has been skipped for a block, we must not WAL-log + * any subsequent actions on the same block either. Replaying the WAL record + * of the subsequent action might fail otherwise, as the "before" state of + * the block might not match, as the earlier actions were not WAL-logged. + * Likewise, after we have WAL-logged an operation for a block, we must + * WAL-log any subsequent operations on the same page as well. Replaying + * a possible full-page-image from the earlier WAL record would otherwise + * revert the page to the old state, even if we sync the relation at end + * of transaction. + * + * If a relation is truncated (without creating a new relfilenode), and we + * emit a WAL record of the truncation, we can't skip WAL-logging for any + * of the truncated blocks anymore, as replaying the truncation record will + * destroy all the data inserted after that. But if we have already decided + * to skip WAL-logging changes to a relation, and the relation is truncated, + * we don't need to WAL-log the truncation either. + * + * This mechanism is currently only used by heaps. Indexes are always + * WAL-logged. Also, this only applies for wal_level=minimal; with higher + * WAL levels we need the WAL for PITR/replication anyway. + */ +typedef struct RelWalRequirement +{ + RelFileNode relnode; /* relation created in same xact */ + bool forks[MAX_FORKNUM + 1]; /* target forknums */ + BlockNumber skip_wal_min_blk; /* WAL-logging skipped for blocks >= + * skip_wal_min_blk */ + BlockNumber wal_log_min_blk; /* The minimum blk number that requires + * WAL-logging even if skipped by the + * above*/ + SubTransactionId create_sxid; /* subxid where this entry is created */ + SubTransactionId invalidate_sxid; /* subxid where this entry is + * invalidated */ +} RelWalRequirement; + +/* Relations that need to be fsync'd at commit */ +static HTAB *walRequirements = NULL; + +static RelWalRequirement *getWalRequirementEntry(Relation rel, bool create); +static RelWalRequirement *getWalRequirementEntryRNode(RelFileNode *node, + bool create); + /* * RelationCreateStorage * Create physical storage for a relation. @@ -259,37 +311,290 @@ RelationTruncate(Relation rel, BlockNumber nblocks) */ if (RelationNeedsWAL(rel)) { - /* - * Make an XLOG entry reporting the file truncation. - */ - XLogRecPtr lsn; - xl_smgr_truncate xlrec; + RelWalRequirement *walreq; - xlrec.blkno = nblocks; - xlrec.rnode = rel->rd_node; - xlrec.flags = SMGR_TRUNCATE_ALL; + /* get pending sync entry, create if not yet */ + walreq = getWalRequirementEntry(rel, true); - XLogBeginInsert(); - XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + if (walreq->skip_wal_min_blk == InvalidBlockNumber || + walreq->skip_wal_min_blk < nblocks) + { + /* + * This is the first time truncation of this relation in this + * transaction or truncation that leaves pages that need at-commit + * fsync. Make an XLOG entry reporting the file truncation. + */ + XLogRecPtr lsn; + xl_smgr_truncate xlrec; - lsn = XLogInsert(RM_SMGR_ID, - XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + xlrec.blkno = nblocks; + xlrec.rnode = rel->rd_node; + xlrec.flags = SMGR_TRUNCATE_ALL; - /* - * Flush, because otherwise the truncation of the main relation might - * hit the disk before the WAL record, and the truncation of the FSM - * or visibility map. If we crashed during that window, we'd be left - * with a truncated heap, but the FSM or visibility map would still - * contain entries for the non-existent heap pages. - */ - if (fsm || vm) - XLogFlush(lsn); + XLogBeginInsert(); + XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + + lsn = XLogInsert(RM_SMGR_ID, + XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + + /* + * Flush, because otherwise the truncation of the main relation + * might hit the disk before the WAL record, and the truncation of + * the FSM or visibility map. If we crashed during that window, + * we'd be left with a truncated heap, but the FSM or visibility + * map would still contain entries for the non-existent heap + * pages. + */ + if (fsm || vm) + XLogFlush(lsn); + + /* no longer skip WAL-logging for the blocks */ + rel->rd_walrequirement->wal_log_min_blk = nblocks; + } } /* Do the real work */ smgrtruncate(rel->rd_smgr, MAIN_FORKNUM, nblocks); } +/* + * Do changes to given heap page need to be WAL-logged? + * + * This takes into account any previous RecordPendingSync() requests. + * + * Note that it is required to check this before creating any WAL records for + * heap pages - it is not merely an optimization! WAL-logging a record, when + * we have already skipped a previous WAL record for the same page could lead + * to failure at WAL replay, as the "before" state expected by the record + * might not match what's on disk. Also, if the heap was truncated earlier, we + * must WAL-log any changes to the once-truncated blocks, because replaying + * the truncation record will destroy them. + */ +bool +BufferNeedsWAL(Relation rel, Buffer buf) +{ + BlockNumber blkno = InvalidBlockNumber; + RelWalRequirement *walreq; + + if (!RelationNeedsWAL(rel)) + return false; + + /* fetch existing pending sync entry */ + walreq = getWalRequirementEntry(rel, false); + + /* + * no point in doing further work if we know that we don't have special + * WAL requirement + */ + if (!walreq) + return true; + + Assert(BufferIsValid(buf)); + + blkno = BufferGetBlockNumber(buf); + + /* + * We don't skip WAL-logging for pages that once done. + */ + if (walreq->skip_wal_min_blk == InvalidBlockNumber || + walreq->skip_wal_min_blk > blkno) + return true; + + /* + * we don't skip WAL-logging for blocks that have got WAL-logged + * truncation + */ + if (walreq->wal_log_min_blk != InvalidBlockNumber && + walreq->wal_log_min_blk <= blkno) + return true; + + return false; +} + +bool +BlockNeedsWAL(Relation rel, BlockNumber blkno) +{ + RelWalRequirement *walreq; + + if (!RelationNeedsWAL(rel)) + return false; + + /* fetch exising pending sync entry */ + walreq = getWalRequirementEntry(rel, false); + + /* + * no point in doing further work if we know that we don't have special + * WAL requirement + */ + if (!walreq) + return true; + + /* + * We don't skip WAL-logging for pages that once done. + */ + if (walreq->skip_wal_min_blk == InvalidBlockNumber || + walreq->skip_wal_min_blk > blkno) + return true; + + /* + * we don't skip WAL-logging for blocks that have got WAL-logged + * truncation + */ + if (walreq->wal_log_min_blk != InvalidBlockNumber && + walreq->wal_log_min_blk <= blkno) + return true; + + return false; +} + +/* + * Remember that the given relation doesn't need WAL-logging for the blocks + * after the current block size and fo the blocks that are going to be synced + * at commit. + */ +void +RecordWALSkipping(Relation rel) +{ + RelWalRequirement *walreq; + + Assert(RelationNeedsWAL(rel)); + + /* get pending sync entry, create if not yet */ + walreq = getWalRequirementEntry(rel, true); + + /* + * Record only the first registration. + */ + if (walreq->skip_wal_min_blk != InvalidBlockNumber) + return; + + walreq->skip_wal_min_blk = RelationGetNumberOfBlocks(rel); +} + +/* + * Record commit-time file sync. This shouldn't be used mixing with + * RecordWALSkipping. + */ +void +RecordPendingSync(SMgrRelation rel, ForkNumber forknum) +{ + RelWalRequirement *walreq; + + walreq = getWalRequirementEntryRNode(&rel->smgr_rnode.node, true); + walreq->forks[forknum] = true; + walreq->skip_wal_min_blk = 0; +} + +/* + * RelationInvalidateWALRequirements() -- invalidate wal requirement entry + */ +void +RelationInvalidateWALRequirements(Relation rel) +{ + RelWalRequirement *walreq; + + /* we know we don't have one */ + if (rel->rd_nowalrequirement) + return; + + walreq = getWalRequirementEntry(rel, false); + + if (!walreq) + return; + + /* + * The state is reset at subtransaction commit/abort. No invalidation + * request must not come for the same relation in the same subtransaction. + */ + Assert(walreq->invalidate_sxid == InvalidSubTransactionId); + + walreq->invalidate_sxid = GetCurrentSubTransactionId(); +} + +/* + * getWalRequirementEntry: get WAL requirement entry. + * + * Returns WAL requirement entry for the relation. The entry tracks + * WAL-skipping blocks for the relation. The WAL-skipped blocks need fsync at + * commit time. Creates one if needed when create is true. + */ +static RelWalRequirement * +getWalRequirementEntry(Relation rel, bool create) +{ + RelWalRequirement *walreq_entry = NULL; + + if (rel->rd_walrequirement) + return rel->rd_walrequirement; + + /* we know we don't have pending sync entry */ + if (!create && rel->rd_nowalrequirement) + return NULL; + + walreq_entry = getWalRequirementEntryRNode(&rel->rd_node, create); + + if (!walreq_entry) + { + /* prevent further hash lookup */ + rel->rd_nowalrequirement = true; + return NULL; + } + + walreq_entry->forks[MAIN_FORKNUM] = true; + + /* hold shortcut in Relation */ + rel->rd_nowalrequirement = false; + rel->rd_walrequirement = walreq_entry; + + return walreq_entry; +} + +/* + * getWalRequirementEntryRNode: get WAL requirement entry by rnode + * + * Returns WAL requirement entry for the RelFileNode. + */ +static RelWalRequirement * +getWalRequirementEntryRNode(RelFileNode *rnode, bool create) +{ + RelWalRequirement *walreq_entry = NULL; + bool found; + + if (!walRequirements) + { + /* First time through: initialize the hash table */ + HASHCTL ctl; + + if (!create) + return NULL; + + MemSet(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(RelFileNode); + ctl.entrysize = sizeof(RelWalRequirement); + ctl.hash = tag_hash; + walRequirements = hash_create("pending relation sync table", 5, + &ctl, HASH_ELEM | HASH_FUNCTION); + } + + walreq_entry = (RelWalRequirement *) + hash_search(walRequirements, (void *) rnode, + create ? HASH_ENTER: HASH_FIND, &found); + + if (!walreq_entry) + return NULL; + + /* new entry created */ + if (!found) + { + memset(&walreq_entry->forks, 0, sizeof(sizeof(walreq_entry->forks))); + walreq_entry->wal_log_min_blk = InvalidBlockNumber; + walreq_entry->skip_wal_min_blk = InvalidBlockNumber; + walreq_entry->create_sxid = GetCurrentSubTransactionId(); + walreq_entry->invalidate_sxid = InvalidSubTransactionId; + } + + return walreq_entry; +} + /* * smgrDoPendingDeletes() -- Take care of relation deletes at end of xact. * @@ -418,6 +723,75 @@ smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr) return nrels; } +/* + * Sync to disk any relations that we have skipped WAL-logging earlier. + */ +void +smgrDoPendingSyncs(bool isCommit) +{ + if (!walRequirements) + return; + + if (isCommit) + { + HASH_SEQ_STATUS status; + RelWalRequirement *walreq; + + hash_seq_init(&status, walRequirements); + + while ((walreq = hash_seq_search(&status)) != NULL) + { + if (walreq->skip_wal_min_blk != InvalidBlockNumber && + walreq->invalidate_sxid == InvalidSubTransactionId) + { + int f; + + FlushRelationBuffersWithoutRelCache(walreq->relnode, false); + + /* flush all requested forks */ + for (f = MAIN_FORKNUM ; f <= MAX_FORKNUM ; f++) + { + if (walreq->forks[f]) + smgrimmedsync(smgropen(walreq->relnode, + InvalidBackendId), f); + } + } + } + } + + hash_destroy(walRequirements); + walRequirements = NULL; +} + +/* + * Process pending invalidation of WAL requirements happened in the + * subtransaction + */ +void +smgrProcessWALRequirementInval(SubTransactionId sxid, bool isCommit) +{ + HASH_SEQ_STATUS status; + RelWalRequirement *walreq; + + if (!walRequirements) + return; + + /* We expect that we don't have walRequirements in almost all cases */ + hash_seq_init(&status, walRequirements); + + while ((walreq = hash_seq_search(&status)) != NULL) + { + /* remove useless entry */ + if (isCommit ? + walreq->invalidate_sxid == sxid : + walreq->create_sxid == sxid) + hash_search(walRequirements, &walreq->relnode, HASH_REMOVE, NULL); + /* or cancel invalidation */ + else if (!isCommit && walreq->invalidate_sxid == sxid) + walreq->invalidate_sxid = InvalidSubTransactionId; + } +} + /* * PostPrepare_smgr -- Clean up after a successful PREPARE * diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3183b2aaa1..c9a0e02168 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -11587,11 +11587,13 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode) /* * Create and copy all forks of the relation, and schedule unlinking of - * old physical files. + * old physical files. WAL requirements for the old node is no longer + * needed. * * NOTE: any conflict in relfilenode value will be caught in * RelationCreateStorage(). */ + RelationInvalidateWALRequirements(rel); RelationCreateStorage(newrnode, rel->rd_rel->relpersistence); /* copy main fork */ diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 273e2f385f..f00826712a 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -451,6 +451,7 @@ static BufferDesc *BufferAlloc(SMgrRelation smgr, BufferAccessStrategy strategy, bool *foundPtr); static void FlushBuffer(BufferDesc *buf, SMgrRelation reln); +static void FlushRelationBuffers_common(SMgrRelation smgr, bool islocal); static void AtProcExit_Buffers(int code, Datum arg); static void CheckForBufferLeaks(void); static int rnode_comparator(const void *p1, const void *p2); @@ -3153,20 +3154,40 @@ 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)); +} + +/* + * Like FlushRelationBuffers(), but the relation is specified by RelFileNode + */ +void +FlushRelationBuffersWithoutRelCache(RelFileNode rnode, bool islocal) +{ + FlushRelationBuffers_common(smgropen(rnode, InvalidBackendId), islocal); +} + +/* + * Code shared between functions FlushRelationBuffers() and + * FlushRelationBuffersWithoutRelCache(). + */ +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)) { @@ -3183,7 +3204,7 @@ FlushRelationBuffers(Relation rel) PageSetChecksumInplace(localpage, bufHdr->tag.blockNum); - smgrwrite(rel->rd_smgr, + smgrwrite(smgr, bufHdr->tag.forkNum, bufHdr->tag.blockNum, localpage, @@ -3213,18 +3234,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 84609e0725..95e834d45e 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -75,6 +75,7 @@ #include "partitioning/partdesc.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rowsecurity.h" +#include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/smgr.h" #include "utils/array.h" @@ -5625,6 +5626,8 @@ load_relcache_init_file(bool shared) rel->rd_newRelfilenodeSubid = InvalidSubTransactionId; rel->rd_amcache = NULL; MemSet(&rel->pgstat_info, 0, sizeof(rel->pgstat_info)); + rel->rd_nowalrequirement = false; + rel->rd_walrequirement = NULL; /* * Recompute lock and physical addressing info. This is needed in diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h index 3773a4df85..3d4fb7f3c3 100644 --- a/src/include/access/heapam.h +++ b/src/include/access/heapam.h @@ -172,6 +172,7 @@ extern void simple_heap_delete(Relation relation, ItemPointer tid); extern void simple_heap_update(Relation relation, ItemPointer otid, HeapTuple tup); +extern void heap_register_sync(Relation relation); extern void heap_sync(Relation relation); /* in heap/pruneheap.c */ diff --git a/src/include/catalog/storage.h b/src/include/catalog/storage.h index 9f638be924..9034465001 100644 --- a/src/include/catalog/storage.h +++ b/src/include/catalog/storage.h @@ -16,12 +16,18 @@ #include "storage/block.h" #include "storage/relfilenode.h" +#include "storage/smgr.h" #include "utils/relcache.h" extern void RelationCreateStorage(RelFileNode rnode, char relpersistence); extern void RelationDropStorage(Relation rel); extern void RelationPreserveStorage(RelFileNode rnode, bool atCommit); extern void RelationTruncate(Relation rel, BlockNumber nblocks); +extern bool BufferNeedsWAL(Relation rel, Buffer buf); +extern bool BlockNeedsWAL(Relation rel, BlockNumber blkno); +extern void RecordWALSkipping(Relation rel); +extern void RecordPendingSync(SMgrRelation rel, ForkNumber forknum); +extern void RelationInvalidateWALRequirements(Relation rel); /* * These functions used to be in storage/smgr/smgr.c, which explains the @@ -29,6 +35,8 @@ extern void RelationTruncate(Relation rel, BlockNumber nblocks); */ extern void smgrDoPendingDeletes(bool isCommit); extern int smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr); +extern void smgrDoPendingSyncs(bool isCommit); +extern void smgrProcessWALRequirementInval(SubTransactionId sxid, bool isCommit); extern void AtSubCommit_smgr(void); extern void AtSubAbort_smgr(void); extern void PostPrepare_smgr(void); diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index c5826f691d..8a9ea041dd 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -189,6 +189,8 @@ 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 54028515a7..30f0d5bd83 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -198,6 +198,14 @@ typedef struct RelationData /* use "struct" here to avoid needing to include pgstat.h: */ struct PgStat_TableStatus *pgstat_info; /* statistics collection area */ + + /* + * rd_nowalrequirement is true if this relation is known not to have + * special WAL requirements. Otherwise we need to ask smgr for an entry + * if rd_walrequirement is NULL. + */ + bool rd_nowalrequirement; + struct RelWalRequirement *rd_walrequirement; } RelationData; -- 2.16.3 ----Next_Part(Tue_Mar_26_16_35_07_2019_128)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v9-0005-Fix-WAL-skipping-feature.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH 5/7] Add infrastructure to WAL-logging skip feature @ 2019-04-02 09:05 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Kyotaro Horiguchi @ 2019-04-02 09:05 UTC (permalink / raw) We used to optimize WAL-logging for truncation of in-transaction crated tables in minimal mode by just signaling by HEAP_INSERT_SKIP_WAL option on heap operations. This mechanism can emit WAL records that results in corrupt state for certain series of in-transaction operations. This patch provides infrastructure to track pending at-commit fsyncs for a relation and in-transaction truncations. table_relation_register_walskip() should be used to start tracking before batch operations like COPY and CLUSTER, and use BufferNeedsWAL() instead of RelationNeedsWAL() at the places related to WAL-logging about heap-modifying operations, then remove call to table_finish_bulk_insert() and the tableam intaface. --- src/backend/access/transam/xact.c | 12 +- src/backend/catalog/storage.c | 612 +++++++++++++++++++++++++++++++++--- src/backend/commands/tablecmds.c | 6 +- src/backend/storage/buffer/bufmgr.c | 39 ++- src/backend/utils/cache/relcache.c | 3 + src/include/catalog/storage.h | 17 +- src/include/storage/bufmgr.h | 2 + src/include/utils/rel.h | 7 + 8 files changed, 631 insertions(+), 67 deletions(-) diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index bd5024ef00..a2c689f414 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -2111,6 +2111,9 @@ CommitTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrFinishBulkInsert(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2343,6 +2346,9 @@ PrepareTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrFinishBulkInsert(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2668,6 +2674,7 @@ AbortTransaction(void) AtAbort_Notify(); AtEOXact_RelationMap(false, is_parallel_worker); AtAbort_Twophase(); + smgrFinishBulkInsert(false); /* abandon pending syncs */ /* * Advertise the fact that we aborted in pg_xact (assuming that we got as @@ -4801,8 +4808,7 @@ CommitSubTransaction(void) AtEOSubXact_RelationCache(true, s->subTransactionId, s->parent->subTransactionId); AtEOSubXact_Inval(true); - AtSubCommit_smgr(); - + AtSubCommit_smgr(s->subTransactionId, s->parent->subTransactionId); /* * The only lock we actually release here is the subtransaction XID lock. */ @@ -4979,7 +4985,7 @@ AbortSubTransaction(void) ResourceOwnerRelease(s->curTransactionOwner, RESOURCE_RELEASE_AFTER_LOCKS, false, false); - AtSubAbort_smgr(); + AtSubAbort_smgr(s->subTransactionId, s->parent->subTransactionId); AtEOXact_GUC(false, s->gucNestLevel); AtEOSubXact_SPI(false, s->subTransactionId); diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index 72242b2476..4cd112f86c 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -21,6 +21,7 @@ #include "miscadmin.h" +#include "access/tableam.h" #include "access/visibilitymap.h" #include "access/xact.h" #include "access/xlog.h" @@ -29,10 +30,18 @@ #include "catalog/storage.h" #include "catalog/storage_xlog.h" #include "storage/freespace.h" -#include "storage/smgr.h" +#include "utils/hsearch.h" #include "utils/memutils.h" #include "utils/rel.h" + /* #define STORAGEDEBUG */ /* turns DEBUG elogs on */ + +#ifdef STORAGEDEBUG +#define STORAGE_elog(...) elog(__VA_ARGS__) +#else +#define STORAGE_elog(...) +#endif + /* * We keep a list of all relations (represented as RelFileNode values) * that have been created or deleted in the current transaction. When @@ -64,6 +73,61 @@ typedef struct PendingRelDelete static PendingRelDelete *pendingDeletes = NULL; /* head of linked list */ +/* + * We also track relation files (RelFileNode values) that have been created + * in the same transaction, and that have been modified without WAL-logging + * the action (an optimization possible with wal_level=minimal). When we are + * about to skip WAL-logging, a RelWalSkip entry is created, and + * 'skip_wal_min_blk' is set to the current size of the relation. Any + * operations on blocks < skip_wal_min_blk need to be WAL-logged as usual, but + * for operations on higher blocks, WAL-logging is skipped. + + * + * NB: after WAL-logging has been skipped for a block, we must not WAL-log + * any subsequent actions on the same block either. Replaying the WAL record + * of the subsequent action might fail otherwise, as the "before" state of + * the block might not match, as the earlier actions were not WAL-logged. + * Likewise, after we have WAL-logged an operation for a block, we must + * WAL-log any subsequent operations on the same page as well. Replaying + * a possible full-page-image from the earlier WAL record would otherwise + * revert the page to the old state, even if we sync the relation at end + * of transaction. + * + * If a relation is truncated (without creating a new relfilenode), and we + * emit a WAL record of the truncation, we can't skip WAL-logging for any + * of the truncated blocks anymore, as replaying the truncation record will + * destroy all the data inserted after that. But if we have already decided + * to skip WAL-logging changes to a relation, and the relation is truncated, + * we don't need to WAL-log the truncation either. + * + * This mechanism is currently only used by heaps. Indexes are always + * WAL-logged. Also, this only applies for wal_level=minimal; with higher + * WAL levels we need the WAL for PITR/replication anyway. + */ +typedef struct RelWalSkip +{ + RelFileNode relnode; /* relation created in same xact */ + bool forks[MAX_FORKNUM + 1]; /* target forknums */ + BlockNumber skip_wal_min_blk; /* WAL-logging skipped for blocks >= + * skip_wal_min_blk */ + BlockNumber wal_log_min_blk; /* The minimum blk number that requires + * WAL-logging even if skipped by the + * above*/ + SubTransactionId create_sxid; /* subxid where this entry is created */ + SubTransactionId invalidate_sxid; /* subxid where this entry is + * invalidated */ + const TableAmRoutine *tableam; /* Table access routine */ +} RelWalSkip; + +/* Relations that need to be fsync'd at commit */ +static HTAB *walSkipHash = NULL; + +static RelWalSkip *getWalSkipEntry(Relation rel, bool create); +static RelWalSkip *getWalSkipEntryRNode(RelFileNode *node, + bool create); +static void smgrProcessWALSkipInval(bool isCommit, SubTransactionId mySubid, + SubTransactionId parentSubid); + /* * RelationCreateStorage * Create physical storage for a relation. @@ -261,31 +325,59 @@ RelationTruncate(Relation rel, BlockNumber nblocks) */ if (RelationNeedsWAL(rel)) { - /* - * Make an XLOG entry reporting the file truncation. - */ - XLogRecPtr lsn; - xl_smgr_truncate xlrec; + RelWalSkip *walskip; - xlrec.blkno = nblocks; - xlrec.rnode = rel->rd_node; - xlrec.flags = SMGR_TRUNCATE_ALL; - - XLogBeginInsert(); - XLogRegisterData((char *) &xlrec, sizeof(xlrec)); - - lsn = XLogInsert(RM_SMGR_ID, - XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + /* get pending sync entry, create if not yet */ + walskip = getWalSkipEntry(rel, true); /* - * Flush, because otherwise the truncation of the main relation might - * hit the disk before the WAL record, and the truncation of the FSM - * or visibility map. If we crashed during that window, we'd be left - * with a truncated heap, but the FSM or visibility map would still - * contain entries for the non-existent heap pages. + * walskip is null here if rel doesn't support WAL-logging skip, + * otherwise check for WAL-skipping status. */ - if (fsm || vm) - XLogFlush(lsn); + if (walskip == NULL || + walskip->skip_wal_min_blk == InvalidBlockNumber || + walskip->skip_wal_min_blk < nblocks) + { + /* + * If WAL-skipping is enabled, this is the first time truncation + * of this relation in this transaction or truncation that leaves + * pages that need at-commit fsync. Make an XLOG entry reporting + * the file truncation. + */ + XLogRecPtr lsn; + xl_smgr_truncate xlrec; + + xlrec.blkno = nblocks; + xlrec.rnode = rel->rd_node; + xlrec.flags = SMGR_TRUNCATE_ALL; + + XLogBeginInsert(); + XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + + lsn = XLogInsert(RM_SMGR_ID, + XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + + STORAGE_elog(DEBUG2, + "WAL-logged truncation of rel %u/%u/%u to %u blocks", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, nblocks); + /* + * Flush, because otherwise the truncation of the main relation + * might hit the disk before the WAL record, and the truncation of + * the FSM or visibility map. If we crashed during that window, + * we'd be left with a truncated heap, but the FSM or visibility + * map would still contain entries for the non-existent heap + * pages. + */ + if (fsm || vm) + XLogFlush(lsn); + + if (walskip) + { + /* no longer skip WAL-logging for the blocks */ + walskip->wal_log_min_blk = nblocks; + } + } } /* Do the real work */ @@ -296,8 +388,7 @@ RelationTruncate(Relation rel, BlockNumber nblocks) * Copy a fork's data, block by block. */ void -RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence) +RelationCopyStorage(Relation srcrel, SMgrRelation dst, ForkNumber forkNum) { PGAlignedBlock buf; Page page; @@ -305,6 +396,8 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, bool copying_initfork; BlockNumber nblocks; BlockNumber blkno; + SMgrRelation src = srcrel->rd_smgr; + char relpersistence = srcrel->rd_rel->relpersistence; page = (Page) buf.data; @@ -316,12 +409,33 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, copying_initfork = relpersistence == RELPERSISTENCE_UNLOGGED && forkNum == INIT_FORKNUM; - /* - * We need to log the copied data in WAL iff WAL archiving/streaming is - * enabled AND it's a permanent relation. - */ - use_wal = XLogIsNeeded() && - (relpersistence == RELPERSISTENCE_PERMANENT || copying_initfork); + if (relpersistence == RELPERSISTENCE_PERMANENT || copying_initfork) + { + /* + * We need to log the copied data in WAL iff WAL archiving/streaming + * is enabled AND it's a permanent relation. + */ + if (XLogIsNeeded()) + use_wal = true; + + /* + * If the rel is WAL-logged, must fsync before commit. We use + * heap_sync to ensure that the toast table gets fsync'd too. (For a + * temp or unlogged rel we don't care since the data will be gone + * after a crash anyway.) + * + * It's obvious that we must do this when not WAL-logging the + * copy. It's less obvious that we have to do it even if we did + * WAL-log the copied pages. The reason is that since we're copying + * outside shared buffers, a CHECKPOINT occurring during the copy has + * no way to flush the previously written data to disk (indeed it + * won't know the new rel even exists). A crash later on would replay + * WAL from the checkpoint, therefore it wouldn't replay our earlier + * WAL entries. If we do not fsync those pages here, they might still + * not be on disk when the crash occurs. + */ + RecordPendingSync(srcrel, dst, forkNum); + } nblocks = smgrnblocks(src, forkNum); @@ -358,24 +472,321 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, */ smgrextend(dst, forkNum, blkno, buf.data, true); } +} + +/* + * Do changes to given heap page need to be WAL-logged? + * + * This takes into account any previous RecordPendingSync() requests. + * + * Note that it is required to check this before creating any WAL records for + * heap pages - it is not merely an optimization! WAL-logging a record, when + * we have already skipped a previous WAL record for the same page could lead + * to failure at WAL replay, as the "before" state expected by the record + * might not match what's on disk. Also, if the heap was truncated earlier, we + * must WAL-log any changes to the once-truncated blocks, because replaying + * the truncation record will destroy them. + */ +bool +BufferNeedsWAL(Relation rel, Buffer buf) +{ + BlockNumber blkno = InvalidBlockNumber; + RelWalSkip *walskip; + + if (!RelationNeedsWAL(rel)) + return false; + + /* fetch existing pending sync entry */ + walskip = getWalSkipEntry(rel, false); /* - * If the rel is WAL-logged, must fsync before commit. We use heap_sync - * to ensure that the toast table gets fsync'd too. (For a temp or - * unlogged rel we don't care since the data will be gone after a crash - * anyway.) - * - * It's obvious that we must do this when not WAL-logging the copy. It's - * less obvious that we have to do it even if we did WAL-log the copied - * pages. The reason is that since we're copying outside shared buffers, a - * CHECKPOINT occurring during the copy has no way to flush the previously - * written data to disk (indeed it won't know the new rel even exists). A - * crash later on would replay WAL from the checkpoint, therefore it - * wouldn't replay our earlier WAL entries. If we do not fsync those pages - * here, they might still not be on disk when the crash occurs. + * no point in doing further work if we know that we don't skip + * WAL-logging. */ - if (relpersistence == RELPERSISTENCE_PERMANENT || copying_initfork) - smgrimmedsync(dst, forkNum); + if (!walskip) + { + STORAGE_elog(DEBUG2, + "not skipping WAL-logging for rel %u/%u/%u block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, BufferGetBlockNumber(buf)); + return true; + } + + Assert(BufferIsValid(buf)); + + blkno = BufferGetBlockNumber(buf); + + /* + * We don't skip WAL-logging for pages that once done. + */ + if (walskip->skip_wal_min_blk == InvalidBlockNumber || + walskip->skip_wal_min_blk > blkno) + { + STORAGE_elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because skip_wal_min_blk is %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno, walskip->skip_wal_min_blk); + return true; + } + + /* + * we don't skip WAL-logging for blocks that have got WAL-logged + * truncation + */ + if (walskip->wal_log_min_blk != InvalidBlockNumber && + walskip->wal_log_min_blk <= blkno) + { + STORAGE_elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because wal_log_min_blk is %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno, walskip->wal_log_min_blk); + return true; + } + + STORAGE_elog(DEBUG2, "skipping WAL-logging for rel %u/%u/%u block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno); + + return false; +} + +bool +BlockNeedsWAL(Relation rel, BlockNumber blkno) +{ + RelWalSkip *walskip; + + if (!RelationNeedsWAL(rel)) + return false; + + /* fetch exising pending sync entry */ + walskip = getWalSkipEntry(rel, false); + + /* + * no point in doing further work if we know that we don't skip + * WAL-logging. + */ + if (!walskip) + return true; + + /* + * We don't skip WAL-logging for pages that once done. + */ + if (walskip->skip_wal_min_blk == InvalidBlockNumber || + walskip->skip_wal_min_blk > blkno) + { + STORAGE_elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because skip_wal_min_blk is %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno, walskip->skip_wal_min_blk); + return true; + } + + /* + * we don't skip WAL-logging for blocks that have got WAL-logged + * truncation + */ + if (walskip->wal_log_min_blk != InvalidBlockNumber && + walskip->wal_log_min_blk <= blkno) + { + STORAGE_elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because wal_log_min_blk is %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno, walskip->wal_log_min_blk); + + return true; + } + + STORAGE_elog(DEBUG2, "skipping WAL-logging for rel %u/%u/%u block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno); + + return false; +} + +/* + * Remember that the given relation doesn't need WAL-logging for the blocks + * after the current block size and for the blocks that are going to be synced + * at commit. + */ +void +RecordWALSkipping(Relation rel) +{ + RelWalSkip *walskip; + + Assert(RelationNeedsWAL(rel)); + + /* get pending sync entry, create if not yet */ + walskip = getWalSkipEntry(rel, true); + + if (walskip == NULL) + return; + + /* + * Record only the first registration. + */ + if (walskip->skip_wal_min_blk != InvalidBlockNumber) + { + STORAGE_elog(DEBUG2, "WAL skipping for rel %u/%u/%u was already registered at block %u (new %u)", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, walskip->skip_wal_min_blk, + RelationGetNumberOfBlocks(rel)); + return; + } + + STORAGE_elog(DEBUG2, "registering new WAL skipping rel %u/%u/%u at block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, RelationGetNumberOfBlocks(rel)); + + walskip->skip_wal_min_blk = RelationGetNumberOfBlocks(rel); +} + +/* + * Record commit-time file sync. This shouldn't be used mixing with + * RecordWALSkipping. + */ +void +RecordPendingSync(Relation rel, SMgrRelation targetsrel, ForkNumber forknum) +{ + RelWalSkip *walskip; + + Assert(RelationNeedsWAL(rel)); + + /* check for support for this feature */ + if (rel->rd_tableam == NULL || + rel->rd_tableam->relation_register_walskip == NULL) + return; + + walskip = getWalSkipEntryRNode(&targetsrel->smgr_rnode.node, true); + walskip->forks[forknum] = true; + walskip->skip_wal_min_blk = 0; + walskip->tableam = rel->rd_tableam; + + STORAGE_elog(DEBUG2, + "registering new pending sync for rel %u/%u/%u at block %u", + walskip->relnode.spcNode, walskip->relnode.dbNode, + walskip->relnode.relNode, 0); +} + +/* + * RelationInvalidateWALSkip() -- invalidate WAL-skip entry + */ +void +RelationInvalidateWALSkip(Relation rel) +{ + RelWalSkip *walskip; + + /* we know we don't have one */ + if (rel->rd_nowalskip) + return; + + walskip = getWalSkipEntry(rel, false); + + if (!walskip) + return; + + /* + * The state is reset at subtransaction commit/abort. No invalidation + * request must not come for the same relation in the same subtransaction. + */ + Assert(walskip->invalidate_sxid == InvalidSubTransactionId); + + walskip->invalidate_sxid = GetCurrentSubTransactionId(); + + STORAGE_elog(DEBUG2, + "WAL skip of rel %u/%u/%u invalidated by sxid %d", + walskip->relnode.spcNode, walskip->relnode.dbNode, + walskip->relnode.relNode, walskip->invalidate_sxid); +} + +/* + * getWalSkipEntry: get WAL skip entry. + * + * Returns WAL skip entry for the relation. The entry tracks WAL-skipping + * blocks for the relation. The WAL-skipped blocks need fsync at commit time. + * Creates one if needed when create is true. If rel doesn't support this + * feature, returns true even if create is true. + */ +static inline RelWalSkip * +getWalSkipEntry(Relation rel, bool create) +{ + RelWalSkip *walskip_entry = NULL; + + if (rel->rd_walskip) + return rel->rd_walskip; + + /* we know we don't have pending sync entry */ + if (!create && rel->rd_nowalskip) + return NULL; + + /* check for support for this feature */ + if (rel->rd_tableam == NULL || + rel->rd_tableam->relation_register_walskip == NULL) + { + rel->rd_nowalskip = true; + return NULL; + } + + walskip_entry = getWalSkipEntryRNode(&rel->rd_node, create); + + if (!walskip_entry) + { + /* prevent further hash lookup */ + rel->rd_nowalskip = true; + return NULL; + } + + walskip_entry->forks[MAIN_FORKNUM] = true; + walskip_entry->tableam = rel->rd_tableam; + + /* hold shortcut in Relation */ + rel->rd_nowalskip = false; + rel->rd_walskip = walskip_entry; + + return walskip_entry; +} + +/* + * getWalSkipEntryRNode: get WAL skip entry by rnode + * + * Returns a WAL skip entry for the RelFileNode. + */ +static RelWalSkip * +getWalSkipEntryRNode(RelFileNode *rnode, bool create) +{ + RelWalSkip *walskip_entry = NULL; + bool found; + + if (!walSkipHash) + { + /* First time through: initialize the hash table */ + HASHCTL ctl; + + if (!create) + return NULL; + + MemSet(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(RelFileNode); + ctl.entrysize = sizeof(RelWalSkip); + ctl.hash = tag_hash; + walSkipHash = hash_create("pending relation sync table", 5, + &ctl, HASH_ELEM | HASH_FUNCTION); + } + + walskip_entry = (RelWalSkip *) + hash_search(walSkipHash, (void *) rnode, + create ? HASH_ENTER: HASH_FIND, &found); + + if (!walskip_entry) + return NULL; + + /* new entry created */ + if (!found) + { + memset(&walskip_entry->forks, 0, sizeof(walskip_entry->forks)); + walskip_entry->wal_log_min_blk = InvalidBlockNumber; + walskip_entry->skip_wal_min_blk = InvalidBlockNumber; + walskip_entry->create_sxid = GetCurrentSubTransactionId(); + walskip_entry->invalidate_sxid = InvalidSubTransactionId; + walskip_entry->tableam = NULL; + } + + return walskip_entry; } /* @@ -506,6 +917,107 @@ smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr) return nrels; } +/* + * Finish bulk insert of files. + */ +void +smgrFinishBulkInsert(bool isCommit) +{ + if (!walSkipHash) + return; + + if (isCommit) + { + HASH_SEQ_STATUS status; + RelWalSkip *walskip; + + hash_seq_init(&status, walSkipHash); + + while ((walskip = hash_seq_search(&status)) != NULL) + { + /* + * On commit, process valid entreis. Rollback doesn't need sync on + * all changes during the transaction. + */ + if (walskip->skip_wal_min_blk != InvalidBlockNumber && + walskip->invalidate_sxid == InvalidSubTransactionId) + { + int f; + + FlushRelationBuffersWithoutRelCache(walskip->relnode, false); + + /* + * We mustn't create an entry when the table AM doesn't + * support WAL-skipping. + */ + Assert (walskip->tableam->finish_bulk_insert); + + /* flush all requested forks */ + for (f = MAIN_FORKNUM ; f <= MAX_FORKNUM ; f++) + { + if (walskip->forks[f]) + { + walskip->tableam->finish_bulk_insert(walskip->relnode, f); + STORAGE_elog(DEBUG2, "finishing bulk insert to rel %u/%u/%u fork %d", + walskip->relnode.spcNode, + walskip->relnode.dbNode, + walskip->relnode.relNode, f); + } + } + } + } + } + + hash_destroy(walSkipHash); + walSkipHash = NULL; +} + +/* + * Process pending invalidation of WAL skip happened in the subtransaction + */ +void +smgrProcessWALSkipInval(bool isCommit, SubTransactionId mySubid, + SubTransactionId parentSubid) +{ + HASH_SEQ_STATUS status; + RelWalSkip *walskip; + + if (!walSkipHash) + return; + + /* We expect that we don't have walSkipHash in almost all cases */ + hash_seq_init(&status, walSkipHash); + + while ((walskip = hash_seq_search(&status)) != NULL) + { + if (walskip->create_sxid == mySubid) + { + /* + * The entry was created in this subxact. Remove it on abort, or + * on commit after invalidation. + */ + if (!isCommit || walskip->invalidate_sxid == mySubid) + hash_search(walSkipHash, &walskip->relnode, + HASH_REMOVE, NULL); + /* Treat committing valid entry as creation by the parent. */ + else if (walskip->invalidate_sxid == InvalidSubTransactionId) + walskip->create_sxid = parentSubid; + } + else if (walskip->invalidate_sxid == mySubid) + { + /* + * This entry was created elsewhere then invalidated by this + * subxact. Treat commit as invalidation by the parent. Otherwise + * cancel invalidation. + */ + if (isCommit) + walskip->invalidate_sxid = parentSubid; + else + walskip->invalidate_sxid = InvalidSubTransactionId; + } + } +} + /* * PostPrepare_smgr -- Clean up after a successful PREPARE * @@ -535,7 +1047,7 @@ PostPrepare_smgr(void) * Reassign all items in the pending-deletes list to the parent transaction. */ void -AtSubCommit_smgr(void) +AtSubCommit_smgr(SubTransactionId mySubid, SubTransactionId parentSubid) { int nestLevel = GetCurrentTransactionNestLevel(); PendingRelDelete *pending; @@ -545,6 +1057,9 @@ AtSubCommit_smgr(void) if (pending->nestLevel >= nestLevel) pending->nestLevel = nestLevel - 1; } + + /* Remove invalidated WAL skip in this subtransaction */ + smgrProcessWALSkipInval(true, mySubid, parentSubid); } /* @@ -555,9 +1070,12 @@ AtSubCommit_smgr(void) * subtransaction will not commit. */ void -AtSubAbort_smgr(void) +AtSubAbort_smgr(SubTransactionId mySubid, SubTransactionId parentSubid) { smgrDoPendingDeletes(false); + + /* Remove invalidated WAL skip in this subtransaction */ + smgrProcessWALSkipInval(false, mySubid, parentSubid); } void diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index e842f9152b..013eb203f4 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -12452,8 +12452,7 @@ index_copy_data(Relation rel, RelFileNode newrnode) RelationCreateStorage(newrnode, rel->rd_rel->relpersistence); /* copy main fork */ - RelationCopyStorage(rel->rd_smgr, dstrel, MAIN_FORKNUM, - rel->rd_rel->relpersistence); + RelationCopyStorage(rel, dstrel, MAIN_FORKNUM); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -12471,8 +12470,7 @@ index_copy_data(Relation rel, RelFileNode newrnode) (rel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED && forkNum == INIT_FORKNUM)) log_smgrcreate(&newrnode, forkNum); - RelationCopyStorage(rel->rd_smgr, dstrel, forkNum, - rel->rd_rel->relpersistence); + RelationCopyStorage(rel, dstrel, forkNum); } } diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 887023fc8a..0c6598d9af 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -451,6 +451,7 @@ static BufferDesc *BufferAlloc(SMgrRelation smgr, BufferAccessStrategy strategy, bool *foundPtr); static void FlushBuffer(BufferDesc *buf, SMgrRelation reln); +static void FlushRelationBuffers_common(SMgrRelation smgr, bool islocal); static void AtProcExit_Buffers(int code, Datum arg); static void CheckForBufferLeaks(void); static int rnode_comparator(const void *p1, const void *p2); @@ -3153,20 +3154,40 @@ 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)); +} + +/* + * Like FlushRelationBuffers(), but the relation is specified by RelFileNode + */ +void +FlushRelationBuffersWithoutRelCache(RelFileNode rnode, bool islocal) +{ + FlushRelationBuffers_common(smgropen(rnode, InvalidBackendId), islocal); +} + +/* + * Code shared between functions FlushRelationBuffers() and + * FlushRelationBuffersWithoutRelCache(). + */ +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)) { @@ -3183,7 +3204,7 @@ FlushRelationBuffers(Relation rel) PageSetChecksumInplace(localpage, bufHdr->tag.blockNum); - smgrwrite(rel->rd_smgr, + smgrwrite(smgr, bufHdr->tag.forkNum, bufHdr->tag.blockNum, localpage, @@ -3213,18 +3234,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 64f3c2e887..f06d55a8fe 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -75,6 +75,7 @@ #include "partitioning/partdesc.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rowsecurity.h" +#include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/smgr.h" #include "utils/array.h" @@ -5644,6 +5645,8 @@ load_relcache_init_file(bool shared) rel->rd_newRelfilenodeSubid = InvalidSubTransactionId; rel->rd_amcache = NULL; MemSet(&rel->pgstat_info, 0, sizeof(rel->pgstat_info)); + rel->rd_nowalskip = false; + rel->rd_walskip = NULL; /* * Recompute lock and physical addressing info. This is needed in diff --git a/src/include/catalog/storage.h b/src/include/catalog/storage.h index 882dc65c89..83fee7dbfe 100644 --- a/src/include/catalog/storage.h +++ b/src/include/catalog/storage.h @@ -23,8 +23,14 @@ extern void RelationCreateStorage(RelFileNode rnode, char relpersistence); extern void RelationDropStorage(Relation rel); extern void RelationPreserveStorage(RelFileNode rnode, bool atCommit); extern void RelationTruncate(Relation rel, BlockNumber nblocks); -extern void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence); +extern void RelationCopyStorage(Relation srcrel, SMgrRelation dst, + ForkNumber forkNum); +extern bool BufferNeedsWAL(Relation rel, Buffer buf); +extern bool BlockNeedsWAL(Relation rel, BlockNumber blkno); +extern void RecordWALSkipping(Relation rel); +extern void RecordPendingSync(Relation rel, SMgrRelation srel, + ForkNumber forknum); +extern void RelationInvalidateWALSkip(Relation rel); /* * These functions used to be in storage/smgr/smgr.c, which explains the @@ -32,8 +38,11 @@ extern void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, */ extern void smgrDoPendingDeletes(bool isCommit); extern int smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr); -extern void AtSubCommit_smgr(void); -extern void AtSubAbort_smgr(void); +extern void smgrFinishBulkInsert(bool isCommit); +extern void AtSubCommit_smgr(SubTransactionId mySubid, + SubTransactionId parentSubid); +extern void AtSubAbort_smgr(SubTransactionId mySubid, + SubTransactionId parentSubid); extern void PostPrepare_smgr(void); #endif /* STORAGE_H */ diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index c5826f691d..8a9ea041dd 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -189,6 +189,8 @@ 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 89a7fbf73a..0adc2aba06 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -198,6 +198,13 @@ typedef struct RelationData /* use "struct" here to avoid needing to include pgstat.h: */ struct PgStat_TableStatus *pgstat_info; /* statistics collection area */ + + /* + * rd_nowalskip is true if this relation is known not to skip WAL. + * Otherwise we need to ask smgr for an entry if rd_walskip is NULL. + */ + bool rd_nowalskip; + struct RelWalSkip *rd_walskip; } RelationData; -- 2.16.3 ----Next_Part(Fri_Apr_05_12_55_20_2019_986)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v11-0006-Fix-WAL-skipping-feature.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH 5/7] Add infrastructure to WAL-logging skip feature @ 2019-04-02 09:05 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Kyotaro Horiguchi @ 2019-04-02 09:05 UTC (permalink / raw) We used to optimize WAL-logging for truncation of in-transaction crated tables in minimal mode by just signaling by HEAP_INSERT_SKIP_WAL option on heap operations. This mechanism can emit WAL records that results in corrupt state for certain series of in-transaction operations. This patch provides infrastructure to track pending at-commit fsyncs for a relation and in-transaction truncations. table_relation_register_walskip() should be used to start tracking before batch operations like COPY and CLUSTER, and use BufferNeedsWAL() instead of RelationNeedsWAL() at the places related to WAL-logging about heap-modifying operations, then remove call to table_finish_bulk_insert() and the tableam intaface. --- src/backend/access/transam/xact.c | 12 +- src/backend/catalog/storage.c | 612 +++++++++++++++++++++++++++++++++--- src/backend/commands/tablecmds.c | 6 +- src/backend/storage/buffer/bufmgr.c | 39 ++- src/backend/utils/cache/relcache.c | 3 + src/include/catalog/storage.h | 17 +- src/include/storage/bufmgr.h | 2 + src/include/utils/rel.h | 7 + 8 files changed, 631 insertions(+), 67 deletions(-) diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index e9ed92b70b..33a83dc784 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -2102,6 +2102,9 @@ CommitTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrFinishBulkInsert(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2334,6 +2337,9 @@ PrepareTransaction(void) /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); + /* Flush updates to relations that we didn't WAL-logged */ + smgrFinishBulkInsert(true); + /* * Mark serializable transaction as complete for predicate locking * purposes. This should be done as late as we can put it and still allow @@ -2659,6 +2665,7 @@ AbortTransaction(void) AtAbort_Notify(); AtEOXact_RelationMap(false, is_parallel_worker); AtAbort_Twophase(); + smgrFinishBulkInsert(false); /* abandon pending syncs */ /* * Advertise the fact that we aborted in pg_xact (assuming that we got as @@ -4792,8 +4799,7 @@ CommitSubTransaction(void) AtEOSubXact_RelationCache(true, s->subTransactionId, s->parent->subTransactionId); AtEOSubXact_Inval(true); - AtSubCommit_smgr(); - + AtSubCommit_smgr(s->subTransactionId, s->parent->subTransactionId); /* * The only lock we actually release here is the subtransaction XID lock. */ @@ -4970,7 +4976,7 @@ AbortSubTransaction(void) ResourceOwnerRelease(s->curTransactionOwner, RESOURCE_RELEASE_AFTER_LOCKS, false, false); - AtSubAbort_smgr(); + AtSubAbort_smgr(s->subTransactionId, s->parent->subTransactionId); AtEOXact_GUC(false, s->gucNestLevel); AtEOSubXact_SPI(false, s->subTransactionId); diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index 72242b2476..4cd112f86c 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -21,6 +21,7 @@ #include "miscadmin.h" +#include "access/tableam.h" #include "access/visibilitymap.h" #include "access/xact.h" #include "access/xlog.h" @@ -29,10 +30,18 @@ #include "catalog/storage.h" #include "catalog/storage_xlog.h" #include "storage/freespace.h" -#include "storage/smgr.h" +#include "utils/hsearch.h" #include "utils/memutils.h" #include "utils/rel.h" + /* #define STORAGEDEBUG */ /* turns DEBUG elogs on */ + +#ifdef STORAGEDEBUG +#define STORAGE_elog(...) elog(__VA_ARGS__) +#else +#define STORAGE_elog(...) +#endif + /* * We keep a list of all relations (represented as RelFileNode values) * that have been created or deleted in the current transaction. When @@ -64,6 +73,61 @@ typedef struct PendingRelDelete static PendingRelDelete *pendingDeletes = NULL; /* head of linked list */ +/* + * We also track relation files (RelFileNode values) that have been created + * in the same transaction, and that have been modified without WAL-logging + * the action (an optimization possible with wal_level=minimal). When we are + * about to skip WAL-logging, a RelWalSkip entry is created, and + * 'skip_wal_min_blk' is set to the current size of the relation. Any + * operations on blocks < skip_wal_min_blk need to be WAL-logged as usual, but + * for operations on higher blocks, WAL-logging is skipped. + + * + * NB: after WAL-logging has been skipped for a block, we must not WAL-log + * any subsequent actions on the same block either. Replaying the WAL record + * of the subsequent action might fail otherwise, as the "before" state of + * the block might not match, as the earlier actions were not WAL-logged. + * Likewise, after we have WAL-logged an operation for a block, we must + * WAL-log any subsequent operations on the same page as well. Replaying + * a possible full-page-image from the earlier WAL record would otherwise + * revert the page to the old state, even if we sync the relation at end + * of transaction. + * + * If a relation is truncated (without creating a new relfilenode), and we + * emit a WAL record of the truncation, we can't skip WAL-logging for any + * of the truncated blocks anymore, as replaying the truncation record will + * destroy all the data inserted after that. But if we have already decided + * to skip WAL-logging changes to a relation, and the relation is truncated, + * we don't need to WAL-log the truncation either. + * + * This mechanism is currently only used by heaps. Indexes are always + * WAL-logged. Also, this only applies for wal_level=minimal; with higher + * WAL levels we need the WAL for PITR/replication anyway. + */ +typedef struct RelWalSkip +{ + RelFileNode relnode; /* relation created in same xact */ + bool forks[MAX_FORKNUM + 1]; /* target forknums */ + BlockNumber skip_wal_min_blk; /* WAL-logging skipped for blocks >= + * skip_wal_min_blk */ + BlockNumber wal_log_min_blk; /* The minimum blk number that requires + * WAL-logging even if skipped by the + * above*/ + SubTransactionId create_sxid; /* subxid where this entry is created */ + SubTransactionId invalidate_sxid; /* subxid where this entry is + * invalidated */ + const TableAmRoutine *tableam; /* Table access routine */ +} RelWalSkip; + +/* Relations that need to be fsync'd at commit */ +static HTAB *walSkipHash = NULL; + +static RelWalSkip *getWalSkipEntry(Relation rel, bool create); +static RelWalSkip *getWalSkipEntryRNode(RelFileNode *node, + bool create); +static void smgrProcessWALSkipInval(bool isCommit, SubTransactionId mySubid, + SubTransactionId parentSubid); + /* * RelationCreateStorage * Create physical storage for a relation. @@ -261,31 +325,59 @@ RelationTruncate(Relation rel, BlockNumber nblocks) */ if (RelationNeedsWAL(rel)) { - /* - * Make an XLOG entry reporting the file truncation. - */ - XLogRecPtr lsn; - xl_smgr_truncate xlrec; + RelWalSkip *walskip; - xlrec.blkno = nblocks; - xlrec.rnode = rel->rd_node; - xlrec.flags = SMGR_TRUNCATE_ALL; - - XLogBeginInsert(); - XLogRegisterData((char *) &xlrec, sizeof(xlrec)); - - lsn = XLogInsert(RM_SMGR_ID, - XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + /* get pending sync entry, create if not yet */ + walskip = getWalSkipEntry(rel, true); /* - * Flush, because otherwise the truncation of the main relation might - * hit the disk before the WAL record, and the truncation of the FSM - * or visibility map. If we crashed during that window, we'd be left - * with a truncated heap, but the FSM or visibility map would still - * contain entries for the non-existent heap pages. + * walskip is null here if rel doesn't support WAL-logging skip, + * otherwise check for WAL-skipping status. */ - if (fsm || vm) - XLogFlush(lsn); + if (walskip == NULL || + walskip->skip_wal_min_blk == InvalidBlockNumber || + walskip->skip_wal_min_blk < nblocks) + { + /* + * If WAL-skipping is enabled, this is the first time truncation + * of this relation in this transaction or truncation that leaves + * pages that need at-commit fsync. Make an XLOG entry reporting + * the file truncation. + */ + XLogRecPtr lsn; + xl_smgr_truncate xlrec; + + xlrec.blkno = nblocks; + xlrec.rnode = rel->rd_node; + xlrec.flags = SMGR_TRUNCATE_ALL; + + XLogBeginInsert(); + XLogRegisterData((char *) &xlrec, sizeof(xlrec)); + + lsn = XLogInsert(RM_SMGR_ID, + XLOG_SMGR_TRUNCATE | XLR_SPECIAL_REL_UPDATE); + + STORAGE_elog(DEBUG2, + "WAL-logged truncation of rel %u/%u/%u to %u blocks", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, nblocks); + /* + * Flush, because otherwise the truncation of the main relation + * might hit the disk before the WAL record, and the truncation of + * the FSM or visibility map. If we crashed during that window, + * we'd be left with a truncated heap, but the FSM or visibility + * map would still contain entries for the non-existent heap + * pages. + */ + if (fsm || vm) + XLogFlush(lsn); + + if (walskip) + { + /* no longer skip WAL-logging for the blocks */ + walskip->wal_log_min_blk = nblocks; + } + } } /* Do the real work */ @@ -296,8 +388,7 @@ RelationTruncate(Relation rel, BlockNumber nblocks) * Copy a fork's data, block by block. */ void -RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence) +RelationCopyStorage(Relation srcrel, SMgrRelation dst, ForkNumber forkNum) { PGAlignedBlock buf; Page page; @@ -305,6 +396,8 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, bool copying_initfork; BlockNumber nblocks; BlockNumber blkno; + SMgrRelation src = srcrel->rd_smgr; + char relpersistence = srcrel->rd_rel->relpersistence; page = (Page) buf.data; @@ -316,12 +409,33 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, copying_initfork = relpersistence == RELPERSISTENCE_UNLOGGED && forkNum == INIT_FORKNUM; - /* - * We need to log the copied data in WAL iff WAL archiving/streaming is - * enabled AND it's a permanent relation. - */ - use_wal = XLogIsNeeded() && - (relpersistence == RELPERSISTENCE_PERMANENT || copying_initfork); + if (relpersistence == RELPERSISTENCE_PERMANENT || copying_initfork) + { + /* + * We need to log the copied data in WAL iff WAL archiving/streaming + * is enabled AND it's a permanent relation. + */ + if (XLogIsNeeded()) + use_wal = true; + + /* + * If the rel is WAL-logged, must fsync before commit. We use + * heap_sync to ensure that the toast table gets fsync'd too. (For a + * temp or unlogged rel we don't care since the data will be gone + * after a crash anyway.) + * + * It's obvious that we must do this when not WAL-logging the + * copy. It's less obvious that we have to do it even if we did + * WAL-log the copied pages. The reason is that since we're copying + * outside shared buffers, a CHECKPOINT occurring during the copy has + * no way to flush the previously written data to disk (indeed it + * won't know the new rel even exists). A crash later on would replay + * WAL from the checkpoint, therefore it wouldn't replay our earlier + * WAL entries. If we do not fsync those pages here, they might still + * not be on disk when the crash occurs. + */ + RecordPendingSync(srcrel, dst, forkNum); + } nblocks = smgrnblocks(src, forkNum); @@ -358,24 +472,321 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, */ smgrextend(dst, forkNum, blkno, buf.data, true); } +} + +/* + * Do changes to given heap page need to be WAL-logged? + * + * This takes into account any previous RecordPendingSync() requests. + * + * Note that it is required to check this before creating any WAL records for + * heap pages - it is not merely an optimization! WAL-logging a record, when + * we have already skipped a previous WAL record for the same page could lead + * to failure at WAL replay, as the "before" state expected by the record + * might not match what's on disk. Also, if the heap was truncated earlier, we + * must WAL-log any changes to the once-truncated blocks, because replaying + * the truncation record will destroy them. + */ +bool +BufferNeedsWAL(Relation rel, Buffer buf) +{ + BlockNumber blkno = InvalidBlockNumber; + RelWalSkip *walskip; + + if (!RelationNeedsWAL(rel)) + return false; + + /* fetch existing pending sync entry */ + walskip = getWalSkipEntry(rel, false); /* - * If the rel is WAL-logged, must fsync before commit. We use heap_sync - * to ensure that the toast table gets fsync'd too. (For a temp or - * unlogged rel we don't care since the data will be gone after a crash - * anyway.) - * - * It's obvious that we must do this when not WAL-logging the copy. It's - * less obvious that we have to do it even if we did WAL-log the copied - * pages. The reason is that since we're copying outside shared buffers, a - * CHECKPOINT occurring during the copy has no way to flush the previously - * written data to disk (indeed it won't know the new rel even exists). A - * crash later on would replay WAL from the checkpoint, therefore it - * wouldn't replay our earlier WAL entries. If we do not fsync those pages - * here, they might still not be on disk when the crash occurs. + * no point in doing further work if we know that we don't skip + * WAL-logging. */ - if (relpersistence == RELPERSISTENCE_PERMANENT || copying_initfork) - smgrimmedsync(dst, forkNum); + if (!walskip) + { + STORAGE_elog(DEBUG2, + "not skipping WAL-logging for rel %u/%u/%u block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, BufferGetBlockNumber(buf)); + return true; + } + + Assert(BufferIsValid(buf)); + + blkno = BufferGetBlockNumber(buf); + + /* + * We don't skip WAL-logging for pages that once done. + */ + if (walskip->skip_wal_min_blk == InvalidBlockNumber || + walskip->skip_wal_min_blk > blkno) + { + STORAGE_elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because skip_wal_min_blk is %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno, walskip->skip_wal_min_blk); + return true; + } + + /* + * we don't skip WAL-logging for blocks that have got WAL-logged + * truncation + */ + if (walskip->wal_log_min_blk != InvalidBlockNumber && + walskip->wal_log_min_blk <= blkno) + { + STORAGE_elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because wal_log_min_blk is %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno, walskip->wal_log_min_blk); + return true; + } + + STORAGE_elog(DEBUG2, "skipping WAL-logging for rel %u/%u/%u block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno); + + return false; +} + +bool +BlockNeedsWAL(Relation rel, BlockNumber blkno) +{ + RelWalSkip *walskip; + + if (!RelationNeedsWAL(rel)) + return false; + + /* fetch exising pending sync entry */ + walskip = getWalSkipEntry(rel, false); + + /* + * no point in doing further work if we know that we don't skip + * WAL-logging. + */ + if (!walskip) + return true; + + /* + * We don't skip WAL-logging for pages that once done. + */ + if (walskip->skip_wal_min_blk == InvalidBlockNumber || + walskip->skip_wal_min_blk > blkno) + { + STORAGE_elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because skip_wal_min_blk is %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno, walskip->skip_wal_min_blk); + return true; + } + + /* + * we don't skip WAL-logging for blocks that have got WAL-logged + * truncation + */ + if (walskip->wal_log_min_blk != InvalidBlockNumber && + walskip->wal_log_min_blk <= blkno) + { + STORAGE_elog(DEBUG2, "not skipping WAL-logging for rel %u/%u/%u block %u, because wal_log_min_blk is %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno, walskip->wal_log_min_blk); + + return true; + } + + STORAGE_elog(DEBUG2, "skipping WAL-logging for rel %u/%u/%u block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, blkno); + + return false; +} + +/* + * Remember that the given relation doesn't need WAL-logging for the blocks + * after the current block size and for the blocks that are going to be synced + * at commit. + */ +void +RecordWALSkipping(Relation rel) +{ + RelWalSkip *walskip; + + Assert(RelationNeedsWAL(rel)); + + /* get pending sync entry, create if not yet */ + walskip = getWalSkipEntry(rel, true); + + if (walskip == NULL) + return; + + /* + * Record only the first registration. + */ + if (walskip->skip_wal_min_blk != InvalidBlockNumber) + { + STORAGE_elog(DEBUG2, "WAL skipping for rel %u/%u/%u was already registered at block %u (new %u)", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, walskip->skip_wal_min_blk, + RelationGetNumberOfBlocks(rel)); + return; + } + + STORAGE_elog(DEBUG2, "registering new WAL skipping rel %u/%u/%u at block %u", + rel->rd_node.spcNode, rel->rd_node.dbNode, + rel->rd_node.relNode, RelationGetNumberOfBlocks(rel)); + + walskip->skip_wal_min_blk = RelationGetNumberOfBlocks(rel); +} + +/* + * Record commit-time file sync. This shouldn't be used mixing with + * RecordWALSkipping. + */ +void +RecordPendingSync(Relation rel, SMgrRelation targetsrel, ForkNumber forknum) +{ + RelWalSkip *walskip; + + Assert(RelationNeedsWAL(rel)); + + /* check for support for this feature */ + if (rel->rd_tableam == NULL || + rel->rd_tableam->relation_register_walskip == NULL) + return; + + walskip = getWalSkipEntryRNode(&targetsrel->smgr_rnode.node, true); + walskip->forks[forknum] = true; + walskip->skip_wal_min_blk = 0; + walskip->tableam = rel->rd_tableam; + + STORAGE_elog(DEBUG2, + "registering new pending sync for rel %u/%u/%u at block %u", + walskip->relnode.spcNode, walskip->relnode.dbNode, + walskip->relnode.relNode, 0); +} + +/* + * RelationInvalidateWALSkip() -- invalidate WAL-skip entry + */ +void +RelationInvalidateWALSkip(Relation rel) +{ + RelWalSkip *walskip; + + /* we know we don't have one */ + if (rel->rd_nowalskip) + return; + + walskip = getWalSkipEntry(rel, false); + + if (!walskip) + return; + + /* + * The state is reset at subtransaction commit/abort. No invalidation + * request must not come for the same relation in the same subtransaction. + */ + Assert(walskip->invalidate_sxid == InvalidSubTransactionId); + + walskip->invalidate_sxid = GetCurrentSubTransactionId(); + + STORAGE_elog(DEBUG2, + "WAL skip of rel %u/%u/%u invalidated by sxid %d", + walskip->relnode.spcNode, walskip->relnode.dbNode, + walskip->relnode.relNode, walskip->invalidate_sxid); +} + +/* + * getWalSkipEntry: get WAL skip entry. + * + * Returns WAL skip entry for the relation. The entry tracks WAL-skipping + * blocks for the relation. The WAL-skipped blocks need fsync at commit time. + * Creates one if needed when create is true. If rel doesn't support this + * feature, returns true even if create is true. + */ +static inline RelWalSkip * +getWalSkipEntry(Relation rel, bool create) +{ + RelWalSkip *walskip_entry = NULL; + + if (rel->rd_walskip) + return rel->rd_walskip; + + /* we know we don't have pending sync entry */ + if (!create && rel->rd_nowalskip) + return NULL; + + /* check for support for this feature */ + if (rel->rd_tableam == NULL || + rel->rd_tableam->relation_register_walskip == NULL) + { + rel->rd_nowalskip = true; + return NULL; + } + + walskip_entry = getWalSkipEntryRNode(&rel->rd_node, create); + + if (!walskip_entry) + { + /* prevent further hash lookup */ + rel->rd_nowalskip = true; + return NULL; + } + + walskip_entry->forks[MAIN_FORKNUM] = true; + walskip_entry->tableam = rel->rd_tableam; + + /* hold shortcut in Relation */ + rel->rd_nowalskip = false; + rel->rd_walskip = walskip_entry; + + return walskip_entry; +} + +/* + * getWalSkipEntryRNode: get WAL skip entry by rnode + * + * Returns a WAL skip entry for the RelFileNode. + */ +static RelWalSkip * +getWalSkipEntryRNode(RelFileNode *rnode, bool create) +{ + RelWalSkip *walskip_entry = NULL; + bool found; + + if (!walSkipHash) + { + /* First time through: initialize the hash table */ + HASHCTL ctl; + + if (!create) + return NULL; + + MemSet(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(RelFileNode); + ctl.entrysize = sizeof(RelWalSkip); + ctl.hash = tag_hash; + walSkipHash = hash_create("pending relation sync table", 5, + &ctl, HASH_ELEM | HASH_FUNCTION); + } + + walskip_entry = (RelWalSkip *) + hash_search(walSkipHash, (void *) rnode, + create ? HASH_ENTER: HASH_FIND, &found); + + if (!walskip_entry) + return NULL; + + /* new entry created */ + if (!found) + { + memset(&walskip_entry->forks, 0, sizeof(walskip_entry->forks)); + walskip_entry->wal_log_min_blk = InvalidBlockNumber; + walskip_entry->skip_wal_min_blk = InvalidBlockNumber; + walskip_entry->create_sxid = GetCurrentSubTransactionId(); + walskip_entry->invalidate_sxid = InvalidSubTransactionId; + walskip_entry->tableam = NULL; + } + + return walskip_entry; } /* @@ -506,6 +917,107 @@ smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr) return nrels; } +/* + * Finish bulk insert of files. + */ +void +smgrFinishBulkInsert(bool isCommit) +{ + if (!walSkipHash) + return; + + if (isCommit) + { + HASH_SEQ_STATUS status; + RelWalSkip *walskip; + + hash_seq_init(&status, walSkipHash); + + while ((walskip = hash_seq_search(&status)) != NULL) + { + /* + * On commit, process valid entreis. Rollback doesn't need sync on + * all changes during the transaction. + */ + if (walskip->skip_wal_min_blk != InvalidBlockNumber && + walskip->invalidate_sxid == InvalidSubTransactionId) + { + int f; + + FlushRelationBuffersWithoutRelCache(walskip->relnode, false); + + /* + * We mustn't create an entry when the table AM doesn't + * support WAL-skipping. + */ + Assert (walskip->tableam->finish_bulk_insert); + + /* flush all requested forks */ + for (f = MAIN_FORKNUM ; f <= MAX_FORKNUM ; f++) + { + if (walskip->forks[f]) + { + walskip->tableam->finish_bulk_insert(walskip->relnode, f); + STORAGE_elog(DEBUG2, "finishing bulk insert to rel %u/%u/%u fork %d", + walskip->relnode.spcNode, + walskip->relnode.dbNode, + walskip->relnode.relNode, f); + } + } + } + } + } + + hash_destroy(walSkipHash); + walSkipHash = NULL; +} + +/* + * Process pending invalidation of WAL skip happened in the subtransaction + */ +void +smgrProcessWALSkipInval(bool isCommit, SubTransactionId mySubid, + SubTransactionId parentSubid) +{ + HASH_SEQ_STATUS status; + RelWalSkip *walskip; + + if (!walSkipHash) + return; + + /* We expect that we don't have walSkipHash in almost all cases */ + hash_seq_init(&status, walSkipHash); + + while ((walskip = hash_seq_search(&status)) != NULL) + { + if (walskip->create_sxid == mySubid) + { + /* + * The entry was created in this subxact. Remove it on abort, or + * on commit after invalidation. + */ + if (!isCommit || walskip->invalidate_sxid == mySubid) + hash_search(walSkipHash, &walskip->relnode, + HASH_REMOVE, NULL); + /* Treat committing valid entry as creation by the parent. */ + else if (walskip->invalidate_sxid == InvalidSubTransactionId) + walskip->create_sxid = parentSubid; + } + else if (walskip->invalidate_sxid == mySubid) + { + /* + * This entry was created elsewhere then invalidated by this + * subxact. Treat commit as invalidation by the parent. Otherwise + * cancel invalidation. + */ + if (isCommit) + walskip->invalidate_sxid = parentSubid; + else + walskip->invalidate_sxid = InvalidSubTransactionId; + } + } +} + /* * PostPrepare_smgr -- Clean up after a successful PREPARE * @@ -535,7 +1047,7 @@ PostPrepare_smgr(void) * Reassign all items in the pending-deletes list to the parent transaction. */ void -AtSubCommit_smgr(void) +AtSubCommit_smgr(SubTransactionId mySubid, SubTransactionId parentSubid) { int nestLevel = GetCurrentTransactionNestLevel(); PendingRelDelete *pending; @@ -545,6 +1057,9 @@ AtSubCommit_smgr(void) if (pending->nestLevel >= nestLevel) pending->nestLevel = nestLevel - 1; } + + /* Remove invalidated WAL skip in this subtransaction */ + smgrProcessWALSkipInval(true, mySubid, parentSubid); } /* @@ -555,9 +1070,12 @@ AtSubCommit_smgr(void) * subtransaction will not commit. */ void -AtSubAbort_smgr(void) +AtSubAbort_smgr(SubTransactionId mySubid, SubTransactionId parentSubid) { smgrDoPendingDeletes(false); + + /* Remove invalidated WAL skip in this subtransaction */ + smgrProcessWALSkipInval(false, mySubid, parentSubid); } void diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 654179297c..8908b77d98 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -11983,8 +11983,7 @@ index_copy_data(Relation rel, RelFileNode newrnode) RelationCreateStorage(newrnode, rel->rd_rel->relpersistence); /* copy main fork */ - RelationCopyStorage(rel->rd_smgr, dstrel, MAIN_FORKNUM, - rel->rd_rel->relpersistence); + RelationCopyStorage(rel, dstrel, MAIN_FORKNUM); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -12002,8 +12001,7 @@ index_copy_data(Relation rel, RelFileNode newrnode) (rel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED && forkNum == INIT_FORKNUM)) log_smgrcreate(&newrnode, forkNum); - RelationCopyStorage(rel->rd_smgr, dstrel, forkNum, - rel->rd_rel->relpersistence); + RelationCopyStorage(rel, dstrel, forkNum); } } diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 273e2f385f..f00826712a 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -451,6 +451,7 @@ static BufferDesc *BufferAlloc(SMgrRelation smgr, BufferAccessStrategy strategy, bool *foundPtr); static void FlushBuffer(BufferDesc *buf, SMgrRelation reln); +static void FlushRelationBuffers_common(SMgrRelation smgr, bool islocal); static void AtProcExit_Buffers(int code, Datum arg); static void CheckForBufferLeaks(void); static int rnode_comparator(const void *p1, const void *p2); @@ -3153,20 +3154,40 @@ 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)); +} + +/* + * Like FlushRelationBuffers(), but the relation is specified by RelFileNode + */ +void +FlushRelationBuffersWithoutRelCache(RelFileNode rnode, bool islocal) +{ + FlushRelationBuffers_common(smgropen(rnode, InvalidBackendId), islocal); +} + +/* + * Code shared between functions FlushRelationBuffers() and + * FlushRelationBuffersWithoutRelCache(). + */ +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)) { @@ -3183,7 +3204,7 @@ FlushRelationBuffers(Relation rel) PageSetChecksumInplace(localpage, bufHdr->tag.blockNum); - smgrwrite(rel->rd_smgr, + smgrwrite(smgr, bufHdr->tag.forkNum, bufHdr->tag.blockNum, localpage, @@ -3213,18 +3234,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 64f3c2e887..f06d55a8fe 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -75,6 +75,7 @@ #include "partitioning/partdesc.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rowsecurity.h" +#include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/smgr.h" #include "utils/array.h" @@ -5644,6 +5645,8 @@ load_relcache_init_file(bool shared) rel->rd_newRelfilenodeSubid = InvalidSubTransactionId; rel->rd_amcache = NULL; MemSet(&rel->pgstat_info, 0, sizeof(rel->pgstat_info)); + rel->rd_nowalskip = false; + rel->rd_walskip = NULL; /* * Recompute lock and physical addressing info. This is needed in diff --git a/src/include/catalog/storage.h b/src/include/catalog/storage.h index 882dc65c89..83fee7dbfe 100644 --- a/src/include/catalog/storage.h +++ b/src/include/catalog/storage.h @@ -23,8 +23,14 @@ extern void RelationCreateStorage(RelFileNode rnode, char relpersistence); extern void RelationDropStorage(Relation rel); extern void RelationPreserveStorage(RelFileNode rnode, bool atCommit); extern void RelationTruncate(Relation rel, BlockNumber nblocks); -extern void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence); +extern void RelationCopyStorage(Relation srcrel, SMgrRelation dst, + ForkNumber forkNum); +extern bool BufferNeedsWAL(Relation rel, Buffer buf); +extern bool BlockNeedsWAL(Relation rel, BlockNumber blkno); +extern void RecordWALSkipping(Relation rel); +extern void RecordPendingSync(Relation rel, SMgrRelation srel, + ForkNumber forknum); +extern void RelationInvalidateWALSkip(Relation rel); /* * These functions used to be in storage/smgr/smgr.c, which explains the @@ -32,8 +38,11 @@ extern void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, */ extern void smgrDoPendingDeletes(bool isCommit); extern int smgrGetPendingDeletes(bool forCommit, RelFileNode **ptr); -extern void AtSubCommit_smgr(void); -extern void AtSubAbort_smgr(void); +extern void smgrFinishBulkInsert(bool isCommit); +extern void AtSubCommit_smgr(SubTransactionId mySubid, + SubTransactionId parentSubid); +extern void AtSubAbort_smgr(SubTransactionId mySubid, + SubTransactionId parentSubid); extern void PostPrepare_smgr(void); #endif /* STORAGE_H */ diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index c5826f691d..8a9ea041dd 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -189,6 +189,8 @@ 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 54028515a7..b2b46322b2 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -198,6 +198,13 @@ typedef struct RelationData /* use "struct" here to avoid needing to include pgstat.h: */ struct PgStat_TableStatus *pgstat_info; /* statistics collection area */ + + /* + * rd_nowalskip is true if this relation is known not to skip WAL. + * Otherwise we need to ask smgr for an entry if rd_walskip is NULL. + */ + bool rd_nowalskip; + struct RelWalSkip *rd_walskip; } RelationData; -- 2.16.3 ----Next_Part(Tue_Apr_02_19_54_06_2019_801)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v10-0006-Fix-WAL-skipping-feature.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: cataloguing NOT NULL constraints @ 2023-08-16 10:09 Peter Eisentraut <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Peter Eisentraut @ 2023-08-16 10:09 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; Dean Rasheed <[email protected]>; +Cc: [email protected] I have two small patches that you can integrate into your patch set: The first just changes the punctuation of "Not-null constraints" in the psql output to match what the documentation mostly uses. The second has some changes to ddl.sgml to reflect that not-null constraints are now named and can be operated on like other constraints. You might want to read that again to make sure it matches your latest intentions, but I think it catches all the places that are required to change. From 324f0050eee51c47e4c558867e6cc832652b39bb Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Wed, 16 Aug 2023 10:26:09 +0200 Subject: [PATCH 1/2] fixup! Have psql print the NOT NULL constraints on \d+ --- contrib/test_decoding/expected/ddl.out | 8 +- src/bin/psql/describe.c | 2 +- src/test/regress/expected/create_table.out | 6 +- .../regress/expected/create_table_like.out | 10 +-- src/test/regress/expected/foreign_data.out | 84 +++++++++---------- src/test/regress/expected/generated.out | 2 +- src/test/regress/expected/identity.out | 2 +- src/test/regress/expected/publication.out | 6 +- .../regress/expected/replica_identity.out | 6 +- src/test/regress/expected/rowsecurity.out | 2 +- 10 files changed, 64 insertions(+), 64 deletions(-) diff --git a/contrib/test_decoding/expected/ddl.out b/contrib/test_decoding/expected/ddl.out index 95a0722c33..bcd1f74b2b 100644 --- a/contrib/test_decoding/expected/ddl.out +++ b/contrib/test_decoding/expected/ddl.out @@ -492,7 +492,7 @@ WITH (user_catalog_table = true) options | text[] | | | | extended | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not null constraints: +Not-null constraints: "replication_metadata_id_not_null" NOT NULL "id" "replication_metadata_relation_not_null" NOT NULL "relation" Options: user_catalog_table=true @@ -509,7 +509,7 @@ ALTER TABLE replication_metadata RESET (user_catalog_table); options | text[] | | | | extended | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not null constraints: +Not-null constraints: "replication_metadata_id_not_null" NOT NULL "id" "replication_metadata_relation_not_null" NOT NULL "relation" @@ -525,7 +525,7 @@ ALTER TABLE replication_metadata SET (user_catalog_table = true); options | text[] | | | | extended | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not null constraints: +Not-null constraints: "replication_metadata_id_not_null" NOT NULL "id" "replication_metadata_relation_not_null" NOT NULL "relation" Options: user_catalog_table=true @@ -547,7 +547,7 @@ ALTER TABLE replication_metadata SET (user_catalog_table = false); rewritemeornot | integer | | | | plain | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not null constraints: +Not-null constraints: "replication_metadata_id_not_null" NOT NULL "id" "replication_metadata_relation_not_null" NOT NULL "relation" Options: user_catalog_table=false diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index d1dc8fa066..4d36e0cfd8 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3072,7 +3072,7 @@ describeOneTableDetails(const char *schemaname, tuples = PQntuples(result); if (tuples > 0) - printTableAddFooter(&cont, _("Not null constraints:")); + printTableAddFooter(&cont, _("Not-null constraints:")); /* Might be an empty set - that's ok */ for (i = 0; i < tuples; i++) diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 3f6516c3f8..477e8839e9 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -854,7 +854,7 @@ drop table test_part_coll_posix; b | integer | | not null | 1 | plain | | Partition of: parted FOR VALUES IN ('b') Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text)) -Not null constraints: +Not-null constraints: "part_b_b_not_null" NOT NULL "b" -- Both partition bound and partition key in describe output @@ -867,7 +867,7 @@ Not null constraints: Partition of: parted FOR VALUES IN ('c') Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text)) Partition key: RANGE (b) -Not null constraints: +Not-null constraints: "part_c_b_not_null" NOT NULL "b" Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) @@ -880,7 +880,7 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) b | integer | | not null | 0 | plain | | Partition of: part_c FOR VALUES FROM (1) TO (10) Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10)) -Not null constraints: +Not-null constraints: "part_c_b_not_null" NOT NULL "b" (inherited) -- Show partition count in the parent's describe output diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index ecac822adb..953d270455 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -333,7 +333,7 @@ CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING a | text | | not null | | main | | b | text | | | | extended | | c | text | | | | external | | -Not null constraints: +Not-null constraints: "ctlt12_storage_a_not_null" NOT NULL "a" CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); @@ -344,7 +344,7 @@ CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDIN a | text | | not null | | extended | | A b | text | | | | extended | | B c | text | | | | extended | | C -Not null constraints: +Not-null constraints: "ctlt12_comments_a_not_null" NOT NULL "a" CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); @@ -359,7 +359,7 @@ NOTICE: merging constraint "ctlt1_a_check" with inherited definition b | text | | | | extended | | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) -Not null constraints: +Not-null constraints: "ctlt1_inh_a_not_null" NOT NULL "a" Inherits: ctlt1 @@ -382,7 +382,7 @@ Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) -Not null constraints: +Not-null constraints: "ctlt13_inh_a_not_null" NOT NULL "a" (inherited) Inherits: ctlt1, ctlt3 @@ -402,7 +402,7 @@ Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) -Not null constraints: +Not-null constraints: "ctlt13_like_a_not_null" NOT NULL "a" (inherited) Inherits: ctlt1 diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 5b242081ae..f7f5bb6766 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -742,7 +742,7 @@ COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) -Not null constraints: +Not-null constraints: "ft1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -866,7 +866,7 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN; Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) -Not null constraints: +Not-null constraints: "ft1_c1_not_null" NOT NULL "c1" "ft1_c6_not_null" NOT NULL "c6" Server: s0 @@ -1409,7 +1409,7 @@ CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1) c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1420,7 +1420,7 @@ Child tables: ft2, FOREIGN c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1434,7 +1434,7 @@ DROP FOREIGN TABLE ft2; c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" CREATE FOREIGN TABLE ft2 ( @@ -1449,7 +1449,7 @@ CREATE FOREIGN TABLE ft2 ( c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1462,7 +1462,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1473,7 +1473,7 @@ Child tables: ft2, FOREIGN c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1496,7 +1496,7 @@ NOTICE: merging column "c3" with inherited definition c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1511,7 +1511,7 @@ Child tables: ct3, c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (inherited) Inherits: ft2 @@ -1522,7 +1522,7 @@ Inherits: ft2 c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft3_c1_not_null" NOT NULL "c1" Server: s0 Inherits: ft2 @@ -1545,7 +1545,7 @@ ALTER TABLE fd_pt1 ADD COLUMN c8 integer; c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" "fd_pt1_c7_not_null" NOT NULL "c7" Child tables: ft2, FOREIGN @@ -1562,7 +1562,7 @@ Child tables: ft2, FOREIGN c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) "ft2_c1_not_null" NOT NULL "c1" Server: s0 @@ -1583,7 +1583,7 @@ Child tables: ct3, c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) "ft2_c1_not_null" NOT NULL "c1" (inherited) Inherits: ft2 @@ -1600,7 +1600,7 @@ Inherits: ft2 c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) "ft3_c1_not_null" NOT NULL "c1" Server: s0 @@ -1631,7 +1631,7 @@ ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; c6 | integer | | not null | | plain | | c7 | integer | | | | plain | | c8 | text | | | | external | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" "fd_pt1_c6_not_null" NOT NULL "c6" Child tables: ft2, FOREIGN @@ -1648,7 +1648,7 @@ Child tables: ft2, FOREIGN c6 | integer | | not null | | | plain | | c7 | integer | | | | | plain | | c8 | text | | | | | external | | -Not null constraints: +Not-null constraints: "fd_pt1_c6_not_null" NOT NULL "c6" (inherited) "ft2_c1_not_null" NOT NULL "c1" Server: s0 @@ -1670,7 +1670,7 @@ ALTER TABLE fd_pt1 DROP COLUMN c8; c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1681,7 +1681,7 @@ Child tables: ft2, FOREIGN c1 | integer | | not null | | | plain | 10000 | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1715,7 +1715,7 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1728,7 +1728,7 @@ Child tables: ft2, FOREIGN c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1766,7 +1766,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1779,7 +1779,7 @@ Child tables: ft2, FOREIGN c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1800,7 +1800,7 @@ ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID; c3 | date | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1814,7 +1814,7 @@ Child tables: ft2, FOREIGN Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1831,7 +1831,7 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; c3 | date | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1845,7 +1845,7 @@ Child tables: ft2, FOREIGN Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1866,7 +1866,7 @@ ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check; f3 | date | | | | plain | | Check constraints: "f2_check" CHECK (f2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "f1" Child tables: ft2, FOREIGN @@ -1880,7 +1880,7 @@ Child tables: ft2, FOREIGN Check constraints: "f2_check" CHECK (f2 <> ''::text) "fd_pt1chk2" CHECK (f2 <> ''::text) -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "f1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1928,7 +1928,7 @@ CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1) c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN @@ -1941,7 +1941,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1962,7 +1962,7 @@ CREATE FOREIGN TABLE fd_pt2_1 ( c2 | text | | | | | extended | | c3 | date | | | | | plain | | c4 | character(1) | | | | | extended | | -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1979,7 +1979,7 @@ DROP FOREIGN TABLE fd_pt2_1; c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Number of partitions: 0 @@ -1995,7 +1995,7 @@ CREATE FOREIGN TABLE fd_pt2_1 ( c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2010,7 +2010,7 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN @@ -2023,7 +2023,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2042,7 +2042,7 @@ ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> ''); c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN @@ -2057,7 +2057,7 @@ Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Check constraints: "p21chk" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 @@ -2077,7 +2077,7 @@ ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; c2 | text | | not null | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" "fd_pt2_c2_not_null" NOT NULL "c2" Number of partitions: 0 @@ -2091,7 +2091,7 @@ Number of partitions: 0 c3 | date | | not null | | | plain | | Check constraints: "p21chk" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 @@ -2113,7 +2113,7 @@ ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); Partition key: LIST (c1) Check constraints: "fd_pt2chk1" CHECK (c1 > 0) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" "fd_pt2_c2_not_null" NOT NULL "c2" Number of partitions: 0 @@ -2127,7 +2127,7 @@ Number of partitions: 0 c3 | date | | not null | | | plain | | Check constraints: "p21chk" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" "fd_pt2_1_c2_not_null" NOT NULL "c2" "fd_pt2_1_c3_not_null" NOT NULL "c3" diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index 930c5790fb..dc97ed3fe0 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -315,7 +315,7 @@ NOTICE: merging column "b" with inherited definition a | integer | | not null | | plain | | b | integer | | | generated always as (a * 22) stored | plain | | x | integer | | | | plain | | -Not null constraints: +Not-null constraints: "gtestx_a_not_null" NOT NULL "a" (inherited) Inherits: gtest1 diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 733dda74b9..7c6e87e8a5 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -506,7 +506,7 @@ TABLE itest8; f3 | integer | | not null | generated by default as identity | plain | | f4 | bigint | | not null | generated always as identity | plain | | f5 | bigint | | | | plain | | -Not null constraints: +Not-null constraints: "itest8_f2_not_null" NOT NULL "f2" "itest8_f3_not_null" NOT NULL "f3" "itest8_f4_not_null" NOT NULL "f4" diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index f0ccc39630..16361a91f9 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -193,7 +193,7 @@ Indexes: "testpub_tbl2_pkey" PRIMARY KEY, btree (id) Publications: "testpub_foralltables" -Not null constraints: +Not-null constraints: "testpub_tbl2_id_not_null" NOT NULL "id" \dRp+ testpub_foralltables @@ -1149,7 +1149,7 @@ Publications: "testpib_ins_trunct" "testpub_default" "testpub_fortbl" -Not null constraints: +Not-null constraints: "testpub_tbl1_id_not_null" NOT NULL "id" \dRp+ testpub_default @@ -1176,7 +1176,7 @@ Indexes: Publications: "testpib_ins_trunct" "testpub_fortbl" -Not null constraints: +Not-null constraints: "testpub_tbl1_id_not_null" NOT NULL "id" -- verify relation cache invalidation when a primary key is added using diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out index 4d4cb95732..6038bf8e9f 100644 --- a/src/test/regress/expected/replica_identity.out +++ b/src/test/regress/expected/replica_identity.out @@ -170,7 +170,7 @@ Indexes: "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) -Not null constraints: +Not-null constraints: "test_replica_identity_id_not_null" NOT NULL "id" "test_replica_identity_keya_not_null" NOT NULL "keya" "test_replica_identity_keyb_not_null" NOT NULL "keyb" @@ -256,7 +256,7 @@ ALTER TABLE ONLY test_replica_identity4_1 Partition key: LIST (id) Indexes: "test_replica_identity4_pkey" PRIMARY KEY, btree (id) INVALID REPLICA IDENTITY -Not null constraints: +Not-null constraints: "test_replica_identity4_id_not_null" NOT NULL "id" Partitions: test_replica_identity4_1 FOR VALUES IN (1) @@ -270,7 +270,7 @@ ALTER INDEX test_replica_identity4_pkey Partition key: LIST (id) Indexes: "test_replica_identity4_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY -Not null constraints: +Not-null constraints: "test_replica_identity4_id_not_null" NOT NULL "id" Partitions: test_replica_identity4_1 FOR VALUES IN (1) diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 0e45c03d43..6988128aa4 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -955,7 +955,7 @@ Policies: POLICY "pp1r" AS RESTRICTIVE TO regress_rls_dave USING ((cid < 55)) -Not null constraints: +Not-null constraints: "part_document_dlevel_not_null" NOT NULL "dlevel" Partitions: part_document_fiction FOR VALUES FROM (11) TO (12), part_document_nonfiction FOR VALUES FROM (99) TO (100), -- 2.41.0 From e5a304b2008e34a4386f5896d3a702aa4b71b33a Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Wed, 16 Aug 2023 10:46:23 +0200 Subject: [PATCH 2/2] Update ddl.sgml for named not-null constraints --- doc/src/sgml/ddl.sgml | 55 ++++++++++++++++++++++++++++++++++--------- 1 file changed, 44 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 58aaa691c6..bf331cafd5 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -651,17 +651,38 @@ <title>Not-Null Constraints</title> price numeric ); </programlisting> + An explicit constraint name can also be specified, for example: +<programlisting> +CREATE TABLE products ( + product_no integer NOT NULL, + name text <emphasis>CONSTRAINT products_name_not_null</emphasis> NOT NULL, + price numeric +); +</programlisting> + </para> + + <para> + A not-null constraint is usually written as a column constraint. The + syntax for writing it as a table constraint is +<programlisting> +CREATE TABLE products ( + product_no integer, + name text, + price numeric, + <emphasis>NOT NULL product_no</emphasis>, + <emphasis>NOT NULL name</emphasis> +); +</programlisting> + But this syntax is not standard and mainly intended for use by + <application>pg_dump</application>. </para> <para> - A not-null constraint is always written as a column constraint. A - not-null constraint is functionally equivalent to creating a check + A not-null constraint is functionally equivalent to creating a check constraint <literal>CHECK (<replaceable>column_name</replaceable> IS NOT NULL)</literal>, but in <productname>PostgreSQL</productname> creating an explicit - not-null constraint is more efficient. The drawback is that you - cannot give explicit names to not-null constraints created this - way. + not-null constraint is more efficient. </para> <para> @@ -678,6 +699,10 @@ <title>Not-Null Constraints</title> order the constraints are checked. </para> + <para> + However, a column can have at most one explicit not-null constraint. + </para> + <para> The <literal>NOT NULL</literal> constraint has an inverse: the <literal>NULL</literal> constraint. This does not mean that the @@ -871,7 +896,7 @@ <title>Primary Keys</title> <para> A table can have at most one primary key. (There can be any number - of unique and not-null constraints, which are functionally almost the + of unique constraints, which combined with not-null constraints are functionally almost the same thing, but only one can be identified as the primary key.) Relational database theory dictates that every table must have a primary key. This rule is @@ -1531,11 +1556,16 @@ <title>Adding a Constraint</title> ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; </programlisting> - To add a not-null constraint, which cannot be written as a table - constraint, use this syntax: + </para> + + <para> + To add a not-null constraint, which is normally not written as a table + constraint, this special syntax is available: <programlisting> ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; </programlisting> + Unlike the <literal>ADD</literal> syntax above, this command silently does + nothing if the column already has a not-null constraint. </para> <para> @@ -1576,12 +1606,15 @@ <title>Removing a Constraint</title> </para> <para> - This works the same for all constraint types except not-null - constraints. To drop a not null constraint use: + Simplified syntax is available to drop a not-null constraint: <programlisting> ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; </programlisting> - (Recall that not-null constraints do not have names.) + This mirrors the <literal>SET NOT NULL</literal> syntax for adding a + not-null constraints. This command will silently do nothing if the column + does not have a not-null constraint. (Recall that a column can have at + most one not-null constraint, so it is never ambigous which constraint + this command acts on.) </para> </sect2> -- 2.41.0 Attachments: [text/plain] 0001-fixup-Have-psql-print-the-NOT-NULL-constraints-on-d.patch.nocfbot (28.1K, ../../[email protected]/2-0001-fixup-Have-psql-print-the-NOT-NULL-constraints-on-d.patch.nocfbot) download | inline diff: From 324f0050eee51c47e4c558867e6cc832652b39bb Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Wed, 16 Aug 2023 10:26:09 +0200 Subject: [PATCH 1/2] fixup! Have psql print the NOT NULL constraints on \d+ --- contrib/test_decoding/expected/ddl.out | 8 +- src/bin/psql/describe.c | 2 +- src/test/regress/expected/create_table.out | 6 +- .../regress/expected/create_table_like.out | 10 +-- src/test/regress/expected/foreign_data.out | 84 +++++++++---------- src/test/regress/expected/generated.out | 2 +- src/test/regress/expected/identity.out | 2 +- src/test/regress/expected/publication.out | 6 +- .../regress/expected/replica_identity.out | 6 +- src/test/regress/expected/rowsecurity.out | 2 +- 10 files changed, 64 insertions(+), 64 deletions(-) diff --git a/contrib/test_decoding/expected/ddl.out b/contrib/test_decoding/expected/ddl.out index 95a0722c33..bcd1f74b2b 100644 --- a/contrib/test_decoding/expected/ddl.out +++ b/contrib/test_decoding/expected/ddl.out @@ -492,7 +492,7 @@ WITH (user_catalog_table = true) options | text[] | | | | extended | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not null constraints: +Not-null constraints: "replication_metadata_id_not_null" NOT NULL "id" "replication_metadata_relation_not_null" NOT NULL "relation" Options: user_catalog_table=true @@ -509,7 +509,7 @@ ALTER TABLE replication_metadata RESET (user_catalog_table); options | text[] | | | | extended | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not null constraints: +Not-null constraints: "replication_metadata_id_not_null" NOT NULL "id" "replication_metadata_relation_not_null" NOT NULL "relation" @@ -525,7 +525,7 @@ ALTER TABLE replication_metadata SET (user_catalog_table = true); options | text[] | | | | extended | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not null constraints: +Not-null constraints: "replication_metadata_id_not_null" NOT NULL "id" "replication_metadata_relation_not_null" NOT NULL "relation" Options: user_catalog_table=true @@ -547,7 +547,7 @@ ALTER TABLE replication_metadata SET (user_catalog_table = false); rewritemeornot | integer | | | | plain | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) -Not null constraints: +Not-null constraints: "replication_metadata_id_not_null" NOT NULL "id" "replication_metadata_relation_not_null" NOT NULL "relation" Options: user_catalog_table=false diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index d1dc8fa066..4d36e0cfd8 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3072,7 +3072,7 @@ describeOneTableDetails(const char *schemaname, tuples = PQntuples(result); if (tuples > 0) - printTableAddFooter(&cont, _("Not null constraints:")); + printTableAddFooter(&cont, _("Not-null constraints:")); /* Might be an empty set - that's ok */ for (i = 0; i < tuples; i++) diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 3f6516c3f8..477e8839e9 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -854,7 +854,7 @@ drop table test_part_coll_posix; b | integer | | not null | 1 | plain | | Partition of: parted FOR VALUES IN ('b') Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text)) -Not null constraints: +Not-null constraints: "part_b_b_not_null" NOT NULL "b" -- Both partition bound and partition key in describe output @@ -867,7 +867,7 @@ Not null constraints: Partition of: parted FOR VALUES IN ('c') Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text)) Partition key: RANGE (b) -Not null constraints: +Not-null constraints: "part_c_b_not_null" NOT NULL "b" Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) @@ -880,7 +880,7 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) b | integer | | not null | 0 | plain | | Partition of: part_c FOR VALUES FROM (1) TO (10) Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10)) -Not null constraints: +Not-null constraints: "part_c_b_not_null" NOT NULL "b" (inherited) -- Show partition count in the parent's describe output diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index ecac822adb..953d270455 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -333,7 +333,7 @@ CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING a | text | | not null | | main | | b | text | | | | extended | | c | text | | | | external | | -Not null constraints: +Not-null constraints: "ctlt12_storage_a_not_null" NOT NULL "a" CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); @@ -344,7 +344,7 @@ CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDIN a | text | | not null | | extended | | A b | text | | | | extended | | B c | text | | | | extended | | C -Not null constraints: +Not-null constraints: "ctlt12_comments_a_not_null" NOT NULL "a" CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); @@ -359,7 +359,7 @@ NOTICE: merging constraint "ctlt1_a_check" with inherited definition b | text | | | | extended | | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) -Not null constraints: +Not-null constraints: "ctlt1_inh_a_not_null" NOT NULL "a" Inherits: ctlt1 @@ -382,7 +382,7 @@ Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) -Not null constraints: +Not-null constraints: "ctlt13_inh_a_not_null" NOT NULL "a" (inherited) Inherits: ctlt1, ctlt3 @@ -402,7 +402,7 @@ Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) -Not null constraints: +Not-null constraints: "ctlt13_like_a_not_null" NOT NULL "a" (inherited) Inherits: ctlt1 diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 5b242081ae..f7f5bb6766 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -742,7 +742,7 @@ COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) -Not null constraints: +Not-null constraints: "ft1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -866,7 +866,7 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN; Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) -Not null constraints: +Not-null constraints: "ft1_c1_not_null" NOT NULL "c1" "ft1_c6_not_null" NOT NULL "c6" Server: s0 @@ -1409,7 +1409,7 @@ CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1) c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1420,7 +1420,7 @@ Child tables: ft2, FOREIGN c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1434,7 +1434,7 @@ DROP FOREIGN TABLE ft2; c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" CREATE FOREIGN TABLE ft2 ( @@ -1449,7 +1449,7 @@ CREATE FOREIGN TABLE ft2 ( c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1462,7 +1462,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1473,7 +1473,7 @@ Child tables: ft2, FOREIGN c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1496,7 +1496,7 @@ NOTICE: merging column "c3" with inherited definition c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1511,7 +1511,7 @@ Child tables: ct3, c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" (inherited) Inherits: ft2 @@ -1522,7 +1522,7 @@ Inherits: ft2 c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft3_c1_not_null" NOT NULL "c1" Server: s0 Inherits: ft2 @@ -1545,7 +1545,7 @@ ALTER TABLE fd_pt1 ADD COLUMN c8 integer; c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" "fd_pt1_c7_not_null" NOT NULL "c7" Child tables: ft2, FOREIGN @@ -1562,7 +1562,7 @@ Child tables: ft2, FOREIGN c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) "ft2_c1_not_null" NOT NULL "c1" Server: s0 @@ -1583,7 +1583,7 @@ Child tables: ct3, c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) "ft2_c1_not_null" NOT NULL "c1" (inherited) Inherits: ft2 @@ -1600,7 +1600,7 @@ Inherits: ft2 c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c7_not_null" NOT NULL "c7" (inherited) "ft3_c1_not_null" NOT NULL "c1" Server: s0 @@ -1631,7 +1631,7 @@ ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; c6 | integer | | not null | | plain | | c7 | integer | | | | plain | | c8 | text | | | | external | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" "fd_pt1_c6_not_null" NOT NULL "c6" Child tables: ft2, FOREIGN @@ -1648,7 +1648,7 @@ Child tables: ft2, FOREIGN c6 | integer | | not null | | | plain | | c7 | integer | | | | | plain | | c8 | text | | | | | external | | -Not null constraints: +Not-null constraints: "fd_pt1_c6_not_null" NOT NULL "c6" (inherited) "ft2_c1_not_null" NOT NULL "c1" Server: s0 @@ -1670,7 +1670,7 @@ ALTER TABLE fd_pt1 DROP COLUMN c8; c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1681,7 +1681,7 @@ Child tables: ft2, FOREIGN c1 | integer | | not null | | | plain | 10000 | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1715,7 +1715,7 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1728,7 +1728,7 @@ Child tables: ft2, FOREIGN c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1766,7 +1766,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1779,7 +1779,7 @@ Child tables: ft2, FOREIGN c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1800,7 +1800,7 @@ ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID; c3 | date | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1814,7 +1814,7 @@ Child tables: ft2, FOREIGN Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1831,7 +1831,7 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; c3 | date | | | | plain | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "c1" Child tables: ft2, FOREIGN @@ -1845,7 +1845,7 @@ Child tables: ft2, FOREIGN Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1866,7 +1866,7 @@ ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check; f3 | date | | | | plain | | Check constraints: "f2_check" CHECK (f2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt1_c1_not_null" NOT NULL "f1" Child tables: ft2, FOREIGN @@ -1880,7 +1880,7 @@ Child tables: ft2, FOREIGN Check constraints: "f2_check" CHECK (f2 <> ''::text) "fd_pt1chk2" CHECK (f2 <> ''::text) -Not null constraints: +Not-null constraints: "ft2_c1_not_null" NOT NULL "f1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1928,7 +1928,7 @@ CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1) c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN @@ -1941,7 +1941,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1962,7 +1962,7 @@ CREATE FOREIGN TABLE fd_pt2_1 ( c2 | text | | | | | extended | | c3 | date | | | | | plain | | c4 | character(1) | | | | | extended | | -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1979,7 +1979,7 @@ DROP FOREIGN TABLE fd_pt2_1; c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Number of partitions: 0 @@ -1995,7 +1995,7 @@ CREATE FOREIGN TABLE fd_pt2_1 ( c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2010,7 +2010,7 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN @@ -2023,7 +2023,7 @@ Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2042,7 +2042,7 @@ ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> ''); c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" Partitions: fd_pt2_1 FOR VALUES IN (1), FOREIGN @@ -2057,7 +2057,7 @@ Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Check constraints: "p21chk" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" (inherited) "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 @@ -2077,7 +2077,7 @@ ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; c2 | text | | not null | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" "fd_pt2_c2_not_null" NOT NULL "c2" Number of partitions: 0 @@ -2091,7 +2091,7 @@ Number of partitions: 0 c3 | date | | not null | | | plain | | Check constraints: "p21chk" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" "fd_pt2_1_c3_not_null" NOT NULL "c3" Server: s0 @@ -2113,7 +2113,7 @@ ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); Partition key: LIST (c1) Check constraints: "fd_pt2chk1" CHECK (c1 > 0) -Not null constraints: +Not-null constraints: "fd_pt2_c1_not_null" NOT NULL "c1" "fd_pt2_c2_not_null" NOT NULL "c2" Number of partitions: 0 @@ -2127,7 +2127,7 @@ Number of partitions: 0 c3 | date | | not null | | | plain | | Check constraints: "p21chk" CHECK (c2 <> ''::text) -Not null constraints: +Not-null constraints: "fd_pt2_1_c1_not_null" NOT NULL "c1" "fd_pt2_1_c2_not_null" NOT NULL "c2" "fd_pt2_1_c3_not_null" NOT NULL "c3" diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index 930c5790fb..dc97ed3fe0 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -315,7 +315,7 @@ NOTICE: merging column "b" with inherited definition a | integer | | not null | | plain | | b | integer | | | generated always as (a * 22) stored | plain | | x | integer | | | | plain | | -Not null constraints: +Not-null constraints: "gtestx_a_not_null" NOT NULL "a" (inherited) Inherits: gtest1 diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 733dda74b9..7c6e87e8a5 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -506,7 +506,7 @@ TABLE itest8; f3 | integer | | not null | generated by default as identity | plain | | f4 | bigint | | not null | generated always as identity | plain | | f5 | bigint | | | | plain | | -Not null constraints: +Not-null constraints: "itest8_f2_not_null" NOT NULL "f2" "itest8_f3_not_null" NOT NULL "f3" "itest8_f4_not_null" NOT NULL "f4" diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index f0ccc39630..16361a91f9 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -193,7 +193,7 @@ Indexes: "testpub_tbl2_pkey" PRIMARY KEY, btree (id) Publications: "testpub_foralltables" -Not null constraints: +Not-null constraints: "testpub_tbl2_id_not_null" NOT NULL "id" \dRp+ testpub_foralltables @@ -1149,7 +1149,7 @@ Publications: "testpib_ins_trunct" "testpub_default" "testpub_fortbl" -Not null constraints: +Not-null constraints: "testpub_tbl1_id_not_null" NOT NULL "id" \dRp+ testpub_default @@ -1176,7 +1176,7 @@ Indexes: Publications: "testpib_ins_trunct" "testpub_fortbl" -Not null constraints: +Not-null constraints: "testpub_tbl1_id_not_null" NOT NULL "id" -- verify relation cache invalidation when a primary key is added using diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out index 4d4cb95732..6038bf8e9f 100644 --- a/src/test/regress/expected/replica_identity.out +++ b/src/test/regress/expected/replica_identity.out @@ -170,7 +170,7 @@ Indexes: "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) -Not null constraints: +Not-null constraints: "test_replica_identity_id_not_null" NOT NULL "id" "test_replica_identity_keya_not_null" NOT NULL "keya" "test_replica_identity_keyb_not_null" NOT NULL "keyb" @@ -256,7 +256,7 @@ ALTER TABLE ONLY test_replica_identity4_1 Partition key: LIST (id) Indexes: "test_replica_identity4_pkey" PRIMARY KEY, btree (id) INVALID REPLICA IDENTITY -Not null constraints: +Not-null constraints: "test_replica_identity4_id_not_null" NOT NULL "id" Partitions: test_replica_identity4_1 FOR VALUES IN (1) @@ -270,7 +270,7 @@ ALTER INDEX test_replica_identity4_pkey Partition key: LIST (id) Indexes: "test_replica_identity4_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY -Not null constraints: +Not-null constraints: "test_replica_identity4_id_not_null" NOT NULL "id" Partitions: test_replica_identity4_1 FOR VALUES IN (1) diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 0e45c03d43..6988128aa4 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -955,7 +955,7 @@ Policies: POLICY "pp1r" AS RESTRICTIVE TO regress_rls_dave USING ((cid < 55)) -Not null constraints: +Not-null constraints: "part_document_dlevel_not_null" NOT NULL "dlevel" Partitions: part_document_fiction FOR VALUES FROM (11) TO (12), part_document_nonfiction FOR VALUES FROM (99) TO (100), -- 2.41.0 [text/plain] 0002-Update-ddl.sgml-for-named-not-null-constraints.patch.nocfbot (4.3K, ../../[email protected]/3-0002-Update-ddl.sgml-for-named-not-null-constraints.patch.nocfbot) download | inline diff: From e5a304b2008e34a4386f5896d3a702aa4b71b33a Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Wed, 16 Aug 2023 10:46:23 +0200 Subject: [PATCH 2/2] Update ddl.sgml for named not-null constraints --- doc/src/sgml/ddl.sgml | 55 ++++++++++++++++++++++++++++++++++--------- 1 file changed, 44 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 58aaa691c6..bf331cafd5 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -651,17 +651,38 @@ <title>Not-Null Constraints</title> price numeric ); </programlisting> + An explicit constraint name can also be specified, for example: +<programlisting> +CREATE TABLE products ( + product_no integer NOT NULL, + name text <emphasis>CONSTRAINT products_name_not_null</emphasis> NOT NULL, + price numeric +); +</programlisting> + </para> + + <para> + A not-null constraint is usually written as a column constraint. The + syntax for writing it as a table constraint is +<programlisting> +CREATE TABLE products ( + product_no integer, + name text, + price numeric, + <emphasis>NOT NULL product_no</emphasis>, + <emphasis>NOT NULL name</emphasis> +); +</programlisting> + But this syntax is not standard and mainly intended for use by + <application>pg_dump</application>. </para> <para> - A not-null constraint is always written as a column constraint. A - not-null constraint is functionally equivalent to creating a check + A not-null constraint is functionally equivalent to creating a check constraint <literal>CHECK (<replaceable>column_name</replaceable> IS NOT NULL)</literal>, but in <productname>PostgreSQL</productname> creating an explicit - not-null constraint is more efficient. The drawback is that you - cannot give explicit names to not-null constraints created this - way. + not-null constraint is more efficient. </para> <para> @@ -678,6 +699,10 @@ <title>Not-Null Constraints</title> order the constraints are checked. </para> + <para> + However, a column can have at most one explicit not-null constraint. + </para> + <para> The <literal>NOT NULL</literal> constraint has an inverse: the <literal>NULL</literal> constraint. This does not mean that the @@ -871,7 +896,7 @@ <title>Primary Keys</title> <para> A table can have at most one primary key. (There can be any number - of unique and not-null constraints, which are functionally almost the + of unique constraints, which combined with not-null constraints are functionally almost the same thing, but only one can be identified as the primary key.) Relational database theory dictates that every table must have a primary key. This rule is @@ -1531,11 +1556,16 @@ <title>Adding a Constraint</title> ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; </programlisting> - To add a not-null constraint, which cannot be written as a table - constraint, use this syntax: + </para> + + <para> + To add a not-null constraint, which is normally not written as a table + constraint, this special syntax is available: <programlisting> ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; </programlisting> + Unlike the <literal>ADD</literal> syntax above, this command silently does + nothing if the column already has a not-null constraint. </para> <para> @@ -1576,12 +1606,15 @@ <title>Removing a Constraint</title> </para> <para> - This works the same for all constraint types except not-null - constraints. To drop a not null constraint use: + Simplified syntax is available to drop a not-null constraint: <programlisting> ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; </programlisting> - (Recall that not-null constraints do not have names.) + This mirrors the <literal>SET NOT NULL</literal> syntax for adding a + not-null constraints. This command will silently do nothing if the column + does not have a not-null constraint. (Recall that a column can have at + most one not-null constraint, so it is never ambigous which constraint + this command acts on.) </para> </sect2> -- 2.41.0 ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v24 7/8] Row pattern recognition patch (tests). @ 2024-12-19 06:06 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Tatsuo Ishii @ 2024-12-19 06:06 UTC (permalink / raw) --- src/test/regress/expected/rpr.out | 919 +++++++++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/rpr.sql | 467 +++++++++++++++ 3 files changed, 1387 insertions(+), 1 deletion(-) create mode 100644 src/test/regress/expected/rpr.out create mode 100644 src/test/regress/sql/rpr.sql diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out new file mode 100644 index 0000000000..b8cd6190b4 --- /dev/null +++ b/src/test/regress/expected/rpr.out @@ -0,0 +1,919 @@ +-- +-- Test for row pattern definition clause +-- +CREATE TEMP TABLE stock ( + company TEXT, + tdate DATE, + price INTEGER +); +INSERT INTO stock VALUES ('company1', '2023-07-01', 100); +INSERT INTO stock VALUES ('company1', '2023-07-02', 200); +INSERT INTO stock VALUES ('company1', '2023-07-03', 150); +INSERT INTO stock VALUES ('company1', '2023-07-04', 140); +INSERT INTO stock VALUES ('company1', '2023-07-05', 150); +INSERT INTO stock VALUES ('company1', '2023-07-06', 90); +INSERT INTO stock VALUES ('company1', '2023-07-07', 110); +INSERT INTO stock VALUES ('company1', '2023-07-08', 130); +INSERT INTO stock VALUES ('company1', '2023-07-09', 120); +INSERT INTO stock VALUES ('company1', '2023-07-10', 130); +INSERT INTO stock VALUES ('company2', '2023-07-01', 50); +INSERT INTO stock VALUES ('company2', '2023-07-02', 2000); +INSERT INTO stock VALUES ('company2', '2023-07-03', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-04', 1400); +INSERT INTO stock VALUES ('company2', '2023-07-05', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-06', 60); +INSERT INTO stock VALUES ('company2', '2023-07-07', 1100); +INSERT INTO stock VALUES ('company2', '2023-07-08', 1300); +INSERT INTO stock VALUES ('company2', '2023-07-09', 1200); +INSERT INTO stock VALUES ('company2', '2023-07-10', 1300); +SELECT * FROM stock; + company | tdate | price +----------+------------+------- + company1 | 07-01-2023 | 100 + company1 | 07-02-2023 | 200 + company1 | 07-03-2023 | 150 + company1 | 07-04-2023 | 140 + company1 | 07-05-2023 | 150 + company1 | 07-06-2023 | 90 + company1 | 07-07-2023 | 110 + company1 | 07-08-2023 | 130 + company1 | 07-09-2023 | 120 + company1 | 07-10-2023 | 130 + company2 | 07-01-2023 | 50 + company2 | 07-02-2023 | 2000 + company2 | 07-03-2023 | 1500 + company2 | 07-04-2023 | 1400 + company2 | 07-05-2023 | 1500 + company2 | 07-06-2023 | 60 + company2 | 07-07-2023 | 1100 + company2 | 07-08-2023 | 1300 + company2 | 07-09-2023 | 1200 + company2 | 07-10-2023 | 1300 +(20 rows) + +-- basic test using PREV +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | | | + company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023 + company1 | 07-07-2023 | 110 | | | + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | | | + company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023 + company2 | 07-07-2023 | 1100 | | | + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- basic test using PREV. UP appears twice +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+ UP+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 150 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | | | + company1 | 07-06-2023 | 90 | 90 | 130 | 07-07-2023 + company1 | 07-07-2023 | 110 | | | + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1500 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | | | + company2 | 07-06-2023 | 60 | 60 | 1300 | 07-07-2023 + company2 | 07-07-2023 | 1100 | | | + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- basic test using PREV. Use '*' +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP* DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | 150 | 90 | 07-06-2023 + company1 | 07-06-2023 | 90 | | | + company1 | 07-07-2023 | 110 | 110 | 120 | 07-08-2023 + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | 1500 | 60 | 07-06-2023 + company2 | 07-06-2023 | 60 | | | + company2 | 07-07-2023 | 1100 | 1100 | 1200 | 07-08-2023 + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- basic test with none greedy pattern +SELECT company, tdate, price, count(*) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A A A) + DEFINE + A AS price >= 140 AND price <= 150 +); + company | tdate | price | count +----------+------------+-------+------- + company1 | 07-01-2023 | 100 | 0 + company1 | 07-02-2023 | 200 | 0 + company1 | 07-03-2023 | 150 | 3 + company1 | 07-04-2023 | 140 | 0 + company1 | 07-05-2023 | 150 | 0 + company1 | 07-06-2023 | 90 | 0 + company1 | 07-07-2023 | 110 | 0 + company1 | 07-08-2023 | 130 | 0 + company1 | 07-09-2023 | 120 | 0 + company1 | 07-10-2023 | 130 | 0 + company2 | 07-01-2023 | 50 | 0 + company2 | 07-02-2023 | 2000 | 0 + company2 | 07-03-2023 | 1500 | 0 + company2 | 07-04-2023 | 1400 | 0 + company2 | 07-05-2023 | 1500 | 0 + company2 | 07-06-2023 | 60 | 0 + company2 | 07-07-2023 | 1100 | 0 + company2 | 07-08-2023 | 1300 | 0 + company2 | 07-09-2023 | 1200 | 0 + company2 | 07-10-2023 | 1300 | 0 +(20 rows) + +-- last_value() should remain consistent +SELECT company, tdate, price, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | last_value +----------+------------+-------+------------ + company1 | 07-01-2023 | 100 | 140 + company1 | 07-02-2023 | 200 | + company1 | 07-03-2023 | 150 | + company1 | 07-04-2023 | 140 | + company1 | 07-05-2023 | 150 | + company1 | 07-06-2023 | 90 | 120 + company1 | 07-07-2023 | 110 | + company1 | 07-08-2023 | 130 | + company1 | 07-09-2023 | 120 | + company1 | 07-10-2023 | 130 | + company2 | 07-01-2023 | 50 | 1400 + company2 | 07-02-2023 | 2000 | + company2 | 07-03-2023 | 1500 | + company2 | 07-04-2023 | 1400 | + company2 | 07-05-2023 | 1500 | + company2 | 07-06-2023 | 60 | 1200 + company2 | 07-07-2023 | 1100 | + company2 | 07-08-2023 | 1300 | + company2 | 07-09-2023 | 1200 | + company2 | 07-10-2023 | 1300 | +(20 rows) + +-- omit "START" in DEFINE but it is ok because "START AS TRUE" is +-- implicitly defined. per spec. +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | nth_second +----------+------------+-------+-------------+------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 | 07-02-2023 + company1 | 07-02-2023 | 200 | | | + company1 | 07-03-2023 | 150 | | | + company1 | 07-04-2023 | 140 | | | + company1 | 07-05-2023 | 150 | | | + company1 | 07-06-2023 | 90 | 90 | 120 | 07-07-2023 + company1 | 07-07-2023 | 110 | | | + company1 | 07-08-2023 | 130 | | | + company1 | 07-09-2023 | 120 | | | + company1 | 07-10-2023 | 130 | | | + company2 | 07-01-2023 | 50 | 50 | 1400 | 07-02-2023 + company2 | 07-02-2023 | 2000 | | | + company2 | 07-03-2023 | 1500 | | | + company2 | 07-04-2023 | 1400 | | | + company2 | 07-05-2023 | 1500 | | | + company2 | 07-06-2023 | 60 | 60 | 1200 | 07-07-2023 + company2 | 07-07-2023 | 1100 | | | + company2 | 07-08-2023 | 1300 | | | + company2 | 07-09-2023 | 1200 | | | + company2 | 07-10-2023 | 1300 | | | +(20 rows) + +-- the first row start with less than or equal to 100 +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | 90 | 120 + company1 | 07-07-2023 | 110 | | + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 1400 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | 60 | 1200 + company2 | 07-07-2023 | 1100 | | + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- second row raises 120% +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price) * 1.2, + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 140 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | | + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 1400 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | | + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- using NEXT +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 200 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | 140 | 150 + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 110 | 130 + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 2000 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | 1400 | 1500 + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 1100 | 1300 + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 200 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | 140 | 150 + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 110 | 130 + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 2000 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | 1400 | 1500 + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 1100 | 1300 + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- match everything +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+) + DEFINE + A AS TRUE +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | 100 | 130 + company1 | 07-02-2023 | 200 | | + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | | + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | 50 | 1300 + company2 | 07-02-2023 | 2000 | | + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | | + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | | + company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 + company1 | 07-03-2023 | 150 | | + company1 | 07-04-2023 | 140 | | + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023 + company1 | 07-08-2023 | 130 | | + company1 | 07-09-2023 | 120 | | + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | | + company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023 + company2 | 07-03-2023 | 1500 | | + company2 | 07-04-2023 | 1400 | | + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023 + company2 | 07-08-2023 | 1300 | | + company2 | 07-09-2023 | 1200 | | + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + company | tdate | price | first_value | last_value +----------+------------+-------+-------------+------------ + company1 | 07-01-2023 | 100 | | + company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 + company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023 + company1 | 07-04-2023 | 140 | 07-04-2023 | 07-05-2023 + company1 | 07-05-2023 | 150 | | + company1 | 07-06-2023 | 90 | | + company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023 + company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023 + company1 | 07-09-2023 | 120 | 07-09-2023 | 07-10-2023 + company1 | 07-10-2023 | 130 | | + company2 | 07-01-2023 | 50 | | + company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023 + company2 | 07-03-2023 | 1500 | 07-03-2023 | 07-05-2023 + company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-05-2023 + company2 | 07-05-2023 | 1500 | | + company2 | 07-06-2023 | 60 | | + company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023 + company2 | 07-08-2023 | 1300 | 07-08-2023 | 07-10-2023 + company2 | 07-09-2023 | 1200 | 07-09-2023 | 07-10-2023 + company2 | 07-10-2023 | 1300 | | +(20 rows) + +-- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w, + count(*) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | count +----------+------------+-------+-------------+------------+------- + company1 | 07-01-2023 | 100 | 07-01-2023 | 07-03-2023 | 3 + company1 | 07-02-2023 | 200 | | | 0 + company1 | 07-03-2023 | 150 | | | 0 + company1 | 07-04-2023 | 140 | 07-04-2023 | 07-06-2023 | 3 + company1 | 07-05-2023 | 150 | | | 0 + company1 | 07-06-2023 | 90 | | | 0 + company1 | 07-07-2023 | 110 | 07-07-2023 | 07-09-2023 | 3 + company1 | 07-08-2023 | 130 | | | 0 + company1 | 07-09-2023 | 120 | | | 0 + company1 | 07-10-2023 | 130 | | | 0 + company2 | 07-01-2023 | 50 | 07-01-2023 | 07-03-2023 | 3 + company2 | 07-02-2023 | 2000 | | | 0 + company2 | 07-03-2023 | 1500 | | | 0 + company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-06-2023 | 3 + company2 | 07-05-2023 | 1500 | | | 0 + company2 | 07-06-2023 | 60 | | | 0 + company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-09-2023 | 3 + company2 | 07-08-2023 | 1300 | | | 0 + company2 | 07-09-2023 | 1200 | | | 0 + company2 | 07-10-2023 | 1300 | | | 0 +(20 rows) + +-- +-- Aggregates +-- +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP PAST LAST ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | max | min | sum | avg | count +----------+------------+-------+-------------+------------+------+-----+------+-----------------------+------- + company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4 + company1 | 07-02-2023 | 200 | | | | | | | 0 + company1 | 07-03-2023 | 150 | | | | | | | 0 + company1 | 07-04-2023 | 140 | | | | | | | 0 + company1 | 07-05-2023 | 150 | | | | | | | 0 + company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4 + company1 | 07-07-2023 | 110 | | | | | | | 0 + company1 | 07-08-2023 | 130 | | | | | | | 0 + company1 | 07-09-2023 | 120 | | | | | | | 0 + company1 | 07-10-2023 | 130 | | | | | | | 0 + company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4 + company2 | 07-02-2023 | 2000 | | | | | | | 0 + company2 | 07-03-2023 | 1500 | | | | | | | 0 + company2 | 07-04-2023 | 1400 | | | | | | | 0 + company2 | 07-05-2023 | 1500 | | | | | | | 0 + company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4 + company2 | 07-07-2023 | 1100 | | | | | | | 0 + company2 | 07-08-2023 | 1300 | | | | | | | 0 + company2 | 07-09-2023 | 1200 | | | | | | | 0 + company2 | 07-10-2023 | 1300 | | | | | | | 0 +(20 rows) + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP TO NEXT ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + company | tdate | price | first_value | last_value | max | min | sum | avg | count +----------+------------+-------+-------------+------------+------+------+------+-----------------------+------- + company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 | 147.5000000000000000 | 4 + company1 | 07-02-2023 | 200 | | | | | | | 0 + company1 | 07-03-2023 | 150 | | | | | | | 0 + company1 | 07-04-2023 | 140 | 140 | 90 | 150 | 90 | 380 | 126.6666666666666667 | 3 + company1 | 07-05-2023 | 150 | | | | | | | 0 + company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4 + company1 | 07-07-2023 | 110 | 110 | 120 | 130 | 110 | 360 | 120.0000000000000000 | 3 + company1 | 07-08-2023 | 130 | | | | | | | 0 + company1 | 07-09-2023 | 120 | | | | | | | 0 + company1 | 07-10-2023 | 130 | | | | | | | 0 + company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4 + company2 | 07-02-2023 | 2000 | | | | | | | 0 + company2 | 07-03-2023 | 1500 | | | | | | | 0 + company2 | 07-04-2023 | 1400 | 1400 | 60 | 1500 | 60 | 2960 | 986.6666666666666667 | 3 + company2 | 07-05-2023 | 1500 | | | | | | | 0 + company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4 + company2 | 07-07-2023 | 1100 | 1100 | 1200 | 1300 | 1100 | 3600 | 1200.0000000000000000 | 3 + company2 | 07-08-2023 | 1300 | | | | | | | 0 + company2 | 07-09-2023 | 1200 | | | | | | | 0 + company2 | 07-10-2023 | 1300 | | | | | | | 0 +(20 rows) + +-- JOIN case +CREATE TEMP TABLE t1 (i int, v1 int); +CREATE TEMP TABLE t2 (j int, v2 int); +INSERT INTO t1 VALUES(1,10); +INSERT INTO t1 VALUES(1,11); +INSERT INTO t1 VALUES(1,12); +INSERT INTO t2 VALUES(2,10); +INSERT INTO t2 VALUES(2,11); +INSERT INTO t2 VALUES(2,12); +SELECT * FROM t1, t2 WHERE t1.v1 <= 11 AND t2.v2 <= 11; + i | v1 | j | v2 +---+----+---+---- + 1 | 10 | 2 | 10 + 1 | 10 | 2 | 11 + 1 | 11 | 2 | 10 + 1 | 11 | 2 | 11 +(4 rows) + +SELECT *, count(*) OVER w FROM t1, t2 +WINDOW w AS ( + PARTITION BY t1.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE + A AS v1 <= 11 AND v2 <= 11 +); + i | v1 | j | v2 | count +---+----+---+----+------- + 1 | 10 | 2 | 10 | 1 + 1 | 10 | 2 | 11 | 1 + 1 | 10 | 2 | 12 | 0 + 1 | 11 | 2 | 10 | 1 + 1 | 11 | 2 | 11 | 1 + 1 | 11 | 2 | 12 | 0 + 1 | 12 | 2 | 10 | 0 + 1 | 12 | 2 | 11 | 0 + 1 | 12 | 2 | 12 | 0 +(9 rows) + +-- WITH case +WITH wstock AS ( + SELECT * FROM stock WHERE tdate < '2023-07-08' +) +SELECT tdate, price, +first_value(tdate) OVER w, +count(*) OVER w + FROM wstock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + tdate | price | first_value | count +------------+-------+-------------+------- + 07-01-2023 | 100 | 07-01-2023 | 4 + 07-02-2023 | 200 | | 0 + 07-03-2023 | 150 | | 0 + 07-04-2023 | 140 | | 0 + 07-05-2023 | 150 | | 0 + 07-06-2023 | 90 | | 0 + 07-07-2023 | 110 | | 0 + 07-01-2023 | 50 | 07-01-2023 | 4 + 07-02-2023 | 2000 | | 0 + 07-03-2023 | 1500 | | 0 + 07-04-2023 | 1400 | | 0 + 07-05-2023 | 1500 | | 0 + 07-06-2023 | 60 | | 0 + 07-07-2023 | 1100 | | 0 +(14 rows) + +-- PREV has multiple column reference +CREATE TEMP TABLE rpr1 (id INTEGER, i SERIAL, j INTEGER); +INSERT INTO rpr1(id, j) SELECT 1, g*2 FROM generate_series(1, 10) AS g; +SELECT id, i, j, count(*) OVER w + FROM rpr1 + WINDOW w AS ( + PARTITION BY id + ORDER BY i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (START COND+) + DEFINE + START AS TRUE, + COND AS PREV(i + j + 1) < 10 +); + id | i | j | count +----+----+----+------- + 1 | 1 | 2 | 3 + 1 | 2 | 4 | 0 + 1 | 3 | 6 | 0 + 1 | 4 | 8 | 0 + 1 | 5 | 10 | 0 + 1 | 6 | 12 | 0 + 1 | 7 | 14 | 0 + 1 | 8 | 16 | 0 + 1 | 9 | 18 | 0 + 1 | 10 | 20 | 0 +(10 rows) + +-- Smoke test for larger partitions. +WITH s AS ( + SELECT v, count(*) OVER w AS c + FROM (SELECT generate_series(1, 5000) v) + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN ( r+ ) + DEFINE r AS TRUE + ) +) +-- Should be exactly one long match across all rows. +SELECT * FROM s WHERE c > 0; + v | c +---+------ + 1 | 5000 +(1 row) + +WITH s AS ( + SELECT v, count(*) OVER w AS c + FROM (SELECT generate_series(1, 5000) v) + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN ( r ) + DEFINE r AS TRUE + ) +) +-- Every row should be its own match. +SELECT count(*) FROM s WHERE c > 0; + count +------- + 5000 +(1 row) + +-- +-- Error cases +-- +-- row pattern definition variable name must not appear more than once +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price), + UP AS price > PREV(price) +); +ERROR: row pattern definition variable name "up" appears more than once in DEFINE clause +LINE 11: UP AS price > PREV(price), + ^ +-- subqueries in DEFINE clause are not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START LOWPRICE) + DEFINE + START AS TRUE, + LOWPRICE AS price < (SELECT 100) +); +ERROR: cannot use subquery in DEFINE expression +LINE 11: LOWPRICE AS price < (SELECT 100) + ^ +-- aggregates in DEFINE clause are not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START LOWPRICE) + DEFINE + START AS TRUE, + LOWPRICE AS price < count(*) +); +ERROR: aggregate functions are not allowed in DEFINE +LINE 11: LOWPRICE AS price < count(*) + ^ +-- FRAME must start at current row when row patttern recognition is used +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +ERROR: FRAME must start at current row when row patttern recognition is used +-- SEEK is not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + SEEK + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +ERROR: SEEK is not supported +LINE 8: SEEK + ^ +HINT: Use INITIAL. +-- PREV's argument must have at least 1 column reference +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(1), + DOWN AS price < PREV(1) +); +ERROR: row pattern navigation operation's argument must include at least one column reference diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 81e4222d26..35df8f159e 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -98,7 +98,7 @@ test: publication subscription # Another group of parallel tests # select_views depends on create_view # ---------- -test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass +test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock indirect_toast equivclass rpr # ---------- # Another group of parallel tests (JSON related) diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql new file mode 100644 index 0000000000..a46abe6f0f --- /dev/null +++ b/src/test/regress/sql/rpr.sql @@ -0,0 +1,467 @@ +-- +-- Test for row pattern definition clause +-- + +CREATE TEMP TABLE stock ( + company TEXT, + tdate DATE, + price INTEGER +); +INSERT INTO stock VALUES ('company1', '2023-07-01', 100); +INSERT INTO stock VALUES ('company1', '2023-07-02', 200); +INSERT INTO stock VALUES ('company1', '2023-07-03', 150); +INSERT INTO stock VALUES ('company1', '2023-07-04', 140); +INSERT INTO stock VALUES ('company1', '2023-07-05', 150); +INSERT INTO stock VALUES ('company1', '2023-07-06', 90); +INSERT INTO stock VALUES ('company1', '2023-07-07', 110); +INSERT INTO stock VALUES ('company1', '2023-07-08', 130); +INSERT INTO stock VALUES ('company1', '2023-07-09', 120); +INSERT INTO stock VALUES ('company1', '2023-07-10', 130); +INSERT INTO stock VALUES ('company2', '2023-07-01', 50); +INSERT INTO stock VALUES ('company2', '2023-07-02', 2000); +INSERT INTO stock VALUES ('company2', '2023-07-03', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-04', 1400); +INSERT INTO stock VALUES ('company2', '2023-07-05', 1500); +INSERT INTO stock VALUES ('company2', '2023-07-06', 60); +INSERT INTO stock VALUES ('company2', '2023-07-07', 1100); +INSERT INTO stock VALUES ('company2', '2023-07-08', 1300); +INSERT INTO stock VALUES ('company2', '2023-07-09', 1200); +INSERT INTO stock VALUES ('company2', '2023-07-10', 1300); + +SELECT * FROM stock; + +-- basic test using PREV +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- basic test using PREV. UP appears twice +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+ UP+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- basic test using PREV. Use '*' +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP* DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- basic test with none greedy pattern +SELECT company, tdate, price, count(*) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A A A) + DEFINE + A AS price >= 140 AND price <= 150 +); + +-- last_value() should remain consistent +SELECT company, tdate, price, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- omit "START" in DEFINE but it is ok because "START AS TRUE" is +-- implicitly defined. per spec. +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, + nth_value(tdate, 2) OVER w AS nth_second + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- the first row start with less than or equal to 100 +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- second row raises 120% +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price) * 1.2, + DOWN AS price < PREV(price) +); + +-- using NEXT +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (START UPDOWN) + DEFINE + START AS TRUE, + UPDOWN AS price > PREV(price) AND price > NEXT(price) +); + +-- match everything + +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+) + DEFINE + A AS TRUE +); + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + +-- backtracking with reclassification of rows +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (A+ B+) + DEFINE + A AS price > 100, + B AS price > 100 +); + +-- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING +SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w, + count(*) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- +-- Aggregates +-- + +-- using AFTER MATCH SKIP PAST LAST ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP PAST LAST ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + +-- using AFTER MATCH SKIP TO NEXT ROW +SELECT company, tdate, price, + first_value(price) OVER w, + last_value(price) OVER w, + max(price) OVER w, + min(price) OVER w, + sum(price) OVER w, + avg(price) OVER w, + count(price) OVER w +FROM stock +WINDOW w AS ( +PARTITION BY company +ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +AFTER MATCH SKIP TO NEXT ROW +INITIAL +PATTERN (START UP+ DOWN+) +DEFINE +START AS TRUE, +UP AS price > PREV(price), +DOWN AS price < PREV(price) +); + +-- JOIN case +CREATE TEMP TABLE t1 (i int, v1 int); +CREATE TEMP TABLE t2 (j int, v2 int); +INSERT INTO t1 VALUES(1,10); +INSERT INTO t1 VALUES(1,11); +INSERT INTO t1 VALUES(1,12); +INSERT INTO t2 VALUES(2,10); +INSERT INTO t2 VALUES(2,11); +INSERT INTO t2 VALUES(2,12); + +SELECT * FROM t1, t2 WHERE t1.v1 <= 11 AND t2.v2 <= 11; + +SELECT *, count(*) OVER w FROM t1, t2 +WINDOW w AS ( + PARTITION BY t1.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE + A AS v1 <= 11 AND v2 <= 11 +); + +-- WITH case +WITH wstock AS ( + SELECT * FROM stock WHERE tdate < '2023-07-08' +) +SELECT tdate, price, +first_value(tdate) OVER w, +count(*) OVER w + FROM wstock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- PREV has multiple column reference +CREATE TEMP TABLE rpr1 (id INTEGER, i SERIAL, j INTEGER); +INSERT INTO rpr1(id, j) SELECT 1, g*2 FROM generate_series(1, 10) AS g; +SELECT id, i, j, count(*) OVER w + FROM rpr1 + WINDOW w AS ( + PARTITION BY id + ORDER BY i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (START COND+) + DEFINE + START AS TRUE, + COND AS PREV(i + j + 1) < 10 +); + +-- Smoke test for larger partitions. +WITH s AS ( + SELECT v, count(*) OVER w AS c + FROM (SELECT generate_series(1, 5000) v) + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN ( r+ ) + DEFINE r AS TRUE + ) +) +-- Should be exactly one long match across all rows. +SELECT * FROM s WHERE c > 0; + +WITH s AS ( + SELECT v, count(*) OVER w AS c + FROM (SELECT generate_series(1, 5000) v) + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN ( r ) + DEFINE r AS TRUE + ) +) +-- Every row should be its own match. +SELECT count(*) FROM s WHERE c > 0; + +-- +-- Error cases +-- + +-- row pattern definition variable name must not appear more than once +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price), + UP AS price > PREV(price) +); + +-- subqueries in DEFINE clause are not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START LOWPRICE) + DEFINE + START AS TRUE, + LOWPRICE AS price < (SELECT 100) +); + +-- aggregates in DEFINE clause are not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START LOWPRICE) + DEFINE + START AS TRUE, + LOWPRICE AS price < count(*) +); + +-- FRAME must start at current row when row patttern recognition is used +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- SEEK is not supported +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + SEEK + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); + +-- PREV's argument must have at least 1 column reference +SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w + FROM stock + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + INITIAL + PATTERN (START UP+ DOWN+) + DEFINE + START AS TRUE, + UP AS price > PREV(1), + DOWN AS price < PREV(1) +); -- 2.25.1 ----Next_Part(Thu_Dec_19_15_19_50_2024_894)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v24-0008-Allow-to-print-raw-parse-tree.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-12-19 06:06 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-10-11 07:00 [PATCH 4/7] Add infrastructure to WAL-logging skip feature Kyotaro Horiguchi <[email protected]> 2018-10-11 07:00 [PATCH 3/4] Add infrastructure to WAL-logging skip feature Kyotaro Horiguchi <[email protected]> 2019-03-26 06:34 [PATCH 4/8] Add infrastructure to WAL-logging skip feature Kyotaro Horiguchi <[email protected]> 2019-04-02 09:05 [PATCH 5/7] Add infrastructure to WAL-logging skip feature Kyotaro Horiguchi <[email protected]> 2019-04-02 09:05 [PATCH 5/7] Add infrastructure to WAL-logging skip feature Kyotaro Horiguchi <[email protected]> 2023-08-16 10:09 Re: cataloguing NOT NULL constraints Peter Eisentraut <[email protected]> 2024-12-19 06:06 [PATCH v24 7/8] Row pattern recognition patch (tests). Tatsuo Ishii <[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