public inbox for [email protected]  

help / color / mirror / Atom feed

[PATCH 4/7] Add infrastructure to WAL-logging skip feature
3+ 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; 3+ 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] 3+ messages in thread

* Re: cataloguing NOT NULL constraints
@ 2023-08-16 10:09  Peter Eisentraut <[email protected]>
  0 siblings, 0 replies; 3+ 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] 3+ 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; 3+ 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] 3+ messages in thread

end of thread, other threads:[~2024-12-19 06:06 UTC | newest]

Thread overview: 3+ 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]>
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