public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v18 12/18] tableam: VACUUM and ANALYZE.
3+ messages / 3 participants
[nested] [flat]
* [PATCH v18 12/18] tableam: VACUUM and ANALYZE.
@ 2019-01-20 08:02 Andres Freund <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Andres Freund @ 2019-01-20 08:02 UTC (permalink / raw)
Author:
Reviewed-By:
Discussion: https://postgr.es/m/
Backpatch:
---
src/backend/access/heap/heapam_handler.c | 160 ++++++++++++++++++
src/backend/commands/analyze.c | 205 +++++------------------
src/backend/commands/vacuum.c | 2 +-
src/include/access/tableam.h | 25 +++
4 files changed, 229 insertions(+), 163 deletions(-)
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 9e67d48b6ea..b35ed21581e 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -678,6 +678,163 @@ heapam_relation_copy_data(Relation rel, RelFileNode newrnode)
smgrclose(dstrel);
}
+static void
+heapam_scan_analyze_next_block(TableScanDesc sscan, BlockNumber blockno, BufferAccessStrategy bstrategy)
+{
+ HeapScanDesc scan = (HeapScanDesc) sscan;
+
+ /*
+ * We must maintain a pin on the target page's buffer to ensure that the
+ * maxoffset value stays good (else concurrent VACUUM might delete tuples
+ * out from under us). Hence, pin the page until we are done looking at
+ * it. We also choose to hold sharelock on the buffer throughout --- we
+ * could release and re-acquire sharelock for each tuple, but since we
+ * aren't doing much work per tuple, the extra lock traffic is probably
+ * better avoided.
+ */
+ scan->rs_cblock = blockno;
+ scan->rs_cbuf = ReadBufferExtended(scan->rs_base.rs_rd, MAIN_FORKNUM, blockno,
+ RBM_NORMAL, bstrategy);
+ scan->rs_cindex = FirstOffsetNumber;
+ LockBuffer(scan->rs_cbuf, BUFFER_LOCK_SHARE);
+}
+
+static bool
+heapam_scan_analyze_next_tuple(TableScanDesc sscan, TransactionId OldestXmin, double *liverows, double *deadrows, TupleTableSlot *slot)
+{
+ HeapScanDesc scan = (HeapScanDesc) sscan;
+ Page targpage;
+ OffsetNumber maxoffset;
+ BufferHeapTupleTableSlot *hslot;
+
+ Assert(TTS_IS_BUFFERTUPLE(slot));
+
+ hslot = (BufferHeapTupleTableSlot *) slot;
+ targpage = BufferGetPage(scan->rs_cbuf);
+ maxoffset = PageGetMaxOffsetNumber(targpage);
+
+ /* Inner loop over all tuples on the selected page */
+ for (; scan->rs_cindex <= maxoffset; scan->rs_cindex++)
+ {
+ ItemId itemid;
+ HeapTuple targtuple = &hslot->base.tupdata;
+ bool sample_it = false;
+
+ itemid = PageGetItemId(targpage, scan->rs_cindex);
+
+ /*
+ * We ignore unused and redirect line pointers. DEAD line pointers
+ * should be counted as dead, because we need vacuum to run to get rid
+ * of them. Note that this rule agrees with the way that
+ * heap_page_prune() counts things.
+ */
+ if (!ItemIdIsNormal(itemid))
+ {
+ if (ItemIdIsDead(itemid))
+ *deadrows += 1;
+ continue;
+ }
+
+ ItemPointerSet(&targtuple->t_self, scan->rs_cblock, scan->rs_cindex);
+
+ targtuple->t_tableOid = RelationGetRelid(scan->rs_base.rs_rd);
+ targtuple->t_data = (HeapTupleHeader) PageGetItem(targpage, itemid);
+ targtuple->t_len = ItemIdGetLength(itemid);
+
+ switch (HeapTupleSatisfiesVacuum(targtuple, OldestXmin, scan->rs_cbuf))
+ {
+ case HEAPTUPLE_LIVE:
+ sample_it = true;
+ *liverows += 1;
+ break;
+
+ case HEAPTUPLE_DEAD:
+ case HEAPTUPLE_RECENTLY_DEAD:
+ /* Count dead and recently-dead rows */
+ *deadrows += 1;
+ break;
+
+ case HEAPTUPLE_INSERT_IN_PROGRESS:
+
+ /*
+ * Insert-in-progress rows are not counted. We assume that
+ * when the inserting transaction commits or aborts, it will
+ * send a stats message to increment the proper count. This
+ * works right only if that transaction ends after we finish
+ * analyzing the table; if things happen in the other order,
+ * its stats update will be overwritten by ours. However, the
+ * error will be large only if the other transaction runs long
+ * enough to insert many tuples, so assuming it will finish
+ * after us is the safer option.
+ *
+ * A special case is that the inserting transaction might be
+ * our own. In this case we should count and sample the row,
+ * to accommodate users who load a table and analyze it in one
+ * transaction. (pgstat_report_analyze has to adjust the
+ * numbers we send to the stats collector to make this come
+ * out right.)
+ */
+ if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(targtuple->t_data)))
+ {
+ sample_it = true;
+ *liverows += 1;
+ }
+ break;
+
+ case HEAPTUPLE_DELETE_IN_PROGRESS:
+
+ /*
+ * We count and sample delete-in-progress rows the same as
+ * live ones, so that the stats counters come out right if the
+ * deleting transaction commits after us, per the same
+ * reasoning given above.
+ *
+ * If the delete was done by our own transaction, however, we
+ * must count the row as dead to make pgstat_report_analyze's
+ * stats adjustments come out right. (Note: this works out
+ * properly when the row was both inserted and deleted in our
+ * xact.)
+ *
+ * The net effect of these choices is that we act as though an
+ * IN_PROGRESS transaction hasn't happened yet, except if it
+ * is our own transaction, which we assume has happened.
+ *
+ * This approach ensures that we behave sanely if we see both
+ * the pre-image and post-image rows for a row being updated
+ * by a concurrent transaction: we will sample the pre-image
+ * but not the post-image. We also get sane results if the
+ * concurrent transaction never commits.
+ */
+ if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(targtuple->t_data)))
+ deadrows += 1;
+ else
+ {
+ sample_it = true;
+ liverows += 1;
+ }
+ break;
+
+ default:
+ elog(ERROR, "unexpected HeapTupleSatisfiesVacuum result");
+ break;
+ }
+
+ if (sample_it)
+ {
+ ExecStoreBufferHeapTuple(targtuple, slot, scan->rs_cbuf);
+ scan->rs_cindex++;
+
+ /* note that we leave the buffer locked here! */
+ return true;
+ }
+ }
+
+ /* Now release the lock and pin on the page */
+ UnlockReleaseBuffer(scan->rs_cbuf);
+ scan->rs_cbuf = InvalidBuffer;
+
+ return false;
+}
static void
heapam_copy_for_cluster(Relation OldHeap, Relation NewHeap, Relation OldIndex,
@@ -1721,6 +1878,9 @@ static const TableAmRoutine heapam_methods = {
.relation_set_new_filenode = heapam_set_new_filenode,
.relation_nontransactional_truncate = heapam_relation_nontransactional_truncate,
.relation_copy_data = heapam_relation_copy_data,
+ .relation_vacuum = heap_vacuum_rel,
+ .scan_analyze_next_block = heapam_scan_analyze_next_block,
+ .scan_analyze_next_tuple = heapam_scan_analyze_next_tuple,
.relation_copy_for_cluster = heapam_copy_for_cluster,
.index_build_range_scan = heapam_index_build_range_scan,
.index_validate_scan = heapam_index_validate_scan,
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index c8192353ebe..996dc500a8f 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -17,11 +17,11 @@
#include <math.h>
#include "access/genam.h"
-#include "access/heapam.h"
#include "access/multixact.h"
#include "access/relation.h"
#include "access/sysattr.h"
#include "access/table.h"
+#include "access/tableam.h"
#include "access/transam.h"
#include "access/tupconvert.h"
#include "access/tuptoaster.h"
@@ -1014,6 +1014,8 @@ acquire_sample_rows(Relation onerel, int elevel,
TransactionId OldestXmin;
BlockSamplerData bs;
ReservoirStateData rstate;
+ TupleTableSlot *slot;
+ TableScanDesc scan;
Assert(targrows > 0);
@@ -1027,193 +1029,72 @@ acquire_sample_rows(Relation onerel, int elevel,
/* Prepare for sampling rows */
reservoir_init_selection_state(&rstate, targrows);
+ scan = table_beginscan_analyze(onerel);
+ slot = table_slot_create(onerel, NULL);
+
/* Outer loop over blocks to sample */
while (BlockSampler_HasMore(&bs))
{
BlockNumber targblock = BlockSampler_Next(&bs);
- Buffer targbuffer;
- Page targpage;
- OffsetNumber targoffset,
- maxoffset;
vacuum_delay_point();
/*
- * We must maintain a pin on the target page's buffer to ensure that
- * the maxoffset value stays good (else concurrent VACUUM might delete
- * tuples out from under us). Hence, pin the page until we are done
- * looking at it. We also choose to hold sharelock on the buffer
- * throughout --- we could release and re-acquire sharelock for each
- * tuple, but since we aren't doing much work per tuple, the extra
- * lock traffic is probably better avoided.
+ * XXX: we could have this function return a boolean, instead of
+ * forcing such checks to happen in next_tuple().
*/
- targbuffer = ReadBufferExtended(onerel, MAIN_FORKNUM, targblock,
- RBM_NORMAL, vac_strategy);
- LockBuffer(targbuffer, BUFFER_LOCK_SHARE);
- targpage = BufferGetPage(targbuffer);
- maxoffset = PageGetMaxOffsetNumber(targpage);
+ table_scan_analyze_next_block(scan, targblock, vac_strategy);
- /* Inner loop over all tuples on the selected page */
- for (targoffset = FirstOffsetNumber; targoffset <= maxoffset; targoffset++)
+ while (table_scan_analyze_next_tuple(scan, OldestXmin, &liverows, &deadrows, slot))
{
- ItemId itemid;
- HeapTupleData targtuple;
- bool sample_it = false;
-
- itemid = PageGetItemId(targpage, targoffset);
-
/*
- * We ignore unused and redirect line pointers. DEAD line
- * pointers should be counted as dead, because we need vacuum to
- * run to get rid of them. Note that this rule agrees with the
- * way that heap_page_prune() counts things.
+ * The first targrows sample rows are simply copied into the
+ * reservoir. Then we start replacing tuples in the sample
+ * until we reach the end of the relation. This algorithm is
+ * from Jeff Vitter's paper (see full citation below). It
+ * works by repeatedly computing the number of tuples to skip
+ * before selecting a tuple, which replaces a randomly chosen
+ * element of the reservoir (current set of tuples). At all
+ * times the reservoir is a true random sample of the tuples
+ * we've passed over so far, so when we fall off the end of
+ * the relation we're done.
*/
- if (!ItemIdIsNormal(itemid))
- {
- if (ItemIdIsDead(itemid))
- deadrows += 1;
- continue;
- }
-
- ItemPointerSet(&targtuple.t_self, targblock, targoffset);
-
- targtuple.t_tableOid = RelationGetRelid(onerel);
- targtuple.t_data = (HeapTupleHeader) PageGetItem(targpage, itemid);
- targtuple.t_len = ItemIdGetLength(itemid);
-
- switch (HeapTupleSatisfiesVacuum(&targtuple,
- OldestXmin,
- targbuffer))
- {
- case HEAPTUPLE_LIVE:
- sample_it = true;
- liverows += 1;
- break;
-
- case HEAPTUPLE_DEAD:
- case HEAPTUPLE_RECENTLY_DEAD:
- /* Count dead and recently-dead rows */
- deadrows += 1;
- break;
-
- case HEAPTUPLE_INSERT_IN_PROGRESS:
-
- /*
- * Insert-in-progress rows are not counted. We assume
- * that when the inserting transaction commits or aborts,
- * it will send a stats message to increment the proper
- * count. This works right only if that transaction ends
- * after we finish analyzing the table; if things happen
- * in the other order, its stats update will be
- * overwritten by ours. However, the error will be large
- * only if the other transaction runs long enough to
- * insert many tuples, so assuming it will finish after us
- * is the safer option.
- *
- * A special case is that the inserting transaction might
- * be our own. In this case we should count and sample
- * the row, to accommodate users who load a table and
- * analyze it in one transaction. (pgstat_report_analyze
- * has to adjust the numbers we send to the stats
- * collector to make this come out right.)
- */
- if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(targtuple.t_data)))
- {
- sample_it = true;
- liverows += 1;
- }
- break;
-
- case HEAPTUPLE_DELETE_IN_PROGRESS:
-
- /*
- * We count and sample delete-in-progress rows the same as
- * live ones, so that the stats counters come out right if
- * the deleting transaction commits after us, per the same
- * reasoning given above.
- *
- * If the delete was done by our own transaction, however,
- * we must count the row as dead to make
- * pgstat_report_analyze's stats adjustments come out
- * right. (Note: this works out properly when the row was
- * both inserted and deleted in our xact.)
- *
- * The net effect of these choices is that we act as
- * though an IN_PROGRESS transaction hasn't happened yet,
- * except if it is our own transaction, which we assume
- * has happened.
- *
- * This approach ensures that we behave sanely if we see
- * both the pre-image and post-image rows for a row being
- * updated by a concurrent transaction: we will sample the
- * pre-image but not the post-image. We also get sane
- * results if the concurrent transaction never commits.
- */
- if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(targtuple.t_data)))
- deadrows += 1;
- else
- {
- sample_it = true;
- liverows += 1;
- }
- break;
-
- default:
- elog(ERROR, "unexpected HeapTupleSatisfiesVacuum result");
- break;
- }
-
- if (sample_it)
+ if (numrows < targrows)
+ rows[numrows++] = ExecCopySlotHeapTuple(slot);
+ else
{
/*
- * The first targrows sample rows are simply copied into the
- * reservoir. Then we start replacing tuples in the sample
- * until we reach the end of the relation. This algorithm is
- * from Jeff Vitter's paper (see full citation below). It
- * works by repeatedly computing the number of tuples to skip
- * before selecting a tuple, which replaces a randomly chosen
- * element of the reservoir (current set of tuples). At all
- * times the reservoir is a true random sample of the tuples
- * we've passed over so far, so when we fall off the end of
- * the relation we're done.
+ * t in Vitter's paper is the number of records already
+ * processed. If we need to compute a new S value, we
+ * must use the not-yet-incremented value of samplerows as
+ * t.
*/
- if (numrows < targrows)
- rows[numrows++] = heap_copytuple(&targtuple);
- else
+ if (rowstoskip < 0)
+ rowstoskip = reservoir_get_next_S(&rstate, samplerows, targrows);
+
+ if (rowstoskip <= 0)
{
/*
- * t in Vitter's paper is the number of records already
- * processed. If we need to compute a new S value, we
- * must use the not-yet-incremented value of samplerows as
- * t.
+ * Found a suitable tuple, so save it, replacing one
+ * old tuple at random
*/
- if (rowstoskip < 0)
- rowstoskip = reservoir_get_next_S(&rstate, samplerows, targrows);
+ int k = (int) (targrows * sampler_random_fract(rstate.randstate));
- if (rowstoskip <= 0)
- {
- /*
- * Found a suitable tuple, so save it, replacing one
- * old tuple at random
- */
- int k = (int) (targrows * sampler_random_fract(rstate.randstate));
-
- Assert(k >= 0 && k < targrows);
- heap_freetuple(rows[k]);
- rows[k] = heap_copytuple(&targtuple);
- }
-
- rowstoskip -= 1;
+ Assert(k >= 0 && k < targrows);
+ heap_freetuple(rows[k]);
+ rows[k] = ExecCopySlotHeapTuple(slot);
}
- samplerows += 1;
+ rowstoskip -= 1;
}
- }
- /* Now release the lock and pin on the page */
- UnlockReleaseBuffer(targbuffer);
+ samplerows += 1;
+ }
}
+ ExecDropSingleTupleTableSlot(slot);
+ table_endscan(scan);
+
/*
* If we didn't find as many tuples as we wanted then we're done. No sort
* is needed, since they're already in order.
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 3763a8c39e0..61d6d62e6d9 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1711,7 +1711,7 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
cluster_rel(relid, InvalidOid, cluster_options);
}
else
- heap_vacuum_rel(onerel, options, params, vac_strategy);
+ table_vacuum_rel(onerel, options, params, vac_strategy);
/* Roll back any GUC changes executed by index functions */
AtEOXact_GUC(false, save_nestlevel);
diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h
index 125ed1c012a..8df3abd90a2 100644
--- a/src/include/access/tableam.h
+++ b/src/include/access/tableam.h
@@ -28,6 +28,7 @@ extern char *default_table_access_method;
extern bool synchronize_seqscans;
+struct VacuumParams;
struct ValidateIndexState;
struct BulkInsertStateData;
@@ -308,6 +309,12 @@ typedef struct TableAmRoutine
MultiXactId *minmulti);
void (*relation_nontransactional_truncate) (Relation rel);
void (*relation_copy_data) (Relation rel, RelFileNode newrnode);
+ void (*relation_vacuum) (Relation onerel, int options,
+ struct VacuumParams *params, BufferAccessStrategy bstrategy);
+ void (*scan_analyze_next_block) (TableScanDesc scan, BlockNumber blockno,
+ BufferAccessStrategy bstrategy);
+ bool (*scan_analyze_next_tuple) (TableScanDesc scan, TransactionId OldestXmin,
+ double *liverows, double *deadrows, TupleTableSlot *slot);
void (*relation_copy_for_cluster) (Relation NewHeap, Relation OldHeap, Relation OldIndex,
bool use_sort,
TransactionId OldestXmin, TransactionId FreezeXid, MultiXactId MultiXactCutoff,
@@ -765,6 +772,24 @@ table_relation_copy_data(Relation rel, RelFileNode newrnode)
rel->rd_tableam->relation_copy_data(rel, newrnode);
}
+static inline void
+table_vacuum_rel(Relation rel, int options,
+ struct VacuumParams *params, BufferAccessStrategy bstrategy)
+{
+ rel->rd_tableam->relation_vacuum(rel, options, params, bstrategy);
+}
+
+static inline void
+table_scan_analyze_next_block(TableScanDesc scan, BlockNumber blockno, BufferAccessStrategy bstrategy)
+{
+ scan->rs_rd->rd_tableam->scan_analyze_next_block(scan, blockno, bstrategy);
+}
+
+static inline bool
+table_scan_analyze_next_tuple(TableScanDesc scan, TransactionId OldestXmin, double *liverows, double *deadrows, TupleTableSlot *slot)
+{
+ return scan->rs_rd->rd_tableam->scan_analyze_next_tuple(scan, OldestXmin, liverows, deadrows, slot);
+}
/* XXX: Move arguments to struct? */
static inline void
--
2.21.0.dirty
--yvn3crbc4qf4vymf
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v18-0013-tableam-planner-size-estimation.patch"
^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH v15 6/8] Row pattern recognition patch (docs).
@ 2024-03-28 10:30 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Tatsuo Ishii @ 2024-03-28 10:30 UTC (permalink / raw)
---
doc/src/sgml/advanced.sgml | 80 ++++++++++++++++++++++++++++++++++++
doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++
doc/src/sgml/ref/select.sgml | 38 ++++++++++++++++-
3 files changed, 170 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 755c9f1485..cf18dd887e 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -537,6 +537,86 @@ WHERE pos < 3;
<literal>rank</literal> less than 3.
</para>
+ <para>
+ Row pattern common syntax can be used to perform row pattern recognition
+ in a query. Row pattern common syntax includes two sub
+ clauses: <literal>DEFINE</literal>
+ and <literal>PATTERN</literal>. <literal>DEFINE</literal> defines
+ definition variables along with an expression. The expression must be a
+ logical expression, which means it must
+ return <literal>TRUE</literal>, <literal>FALSE</literal>
+ or <literal>NULL</literal>. The expression may comprise column references
+ and functions. Window functions, aggregate functions and subqueries are
+ not allowed. An example of <literal>DEFINE</literal> is as follows.
+
+<programlisting>
+DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+</programlisting>
+
+ Note that <function>PREV</function> returns the price column in the
+ previous row if it's called in a context of row pattern recognition. So in
+ the second line the definition variable "UP" is <literal>TRUE</literal>
+ when the price column in the current row is greater than the price column
+ in the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when
+ the price column in the current row is lower than the price column in the
+ previous row.
+ </para>
+ <para>
+ Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be
+ used. <literal>PATTERN</literal> defines a sequence of rows that satisfies
+ certain conditions. For example following <literal>PATTERN</literal>
+ defines that a row starts with the condition "LOWPRICE", then one or more
+ rows satisfy "UP" and finally one or more rows satisfy "DOWN". Note that
+ "+" means one or more matches. Also you can use "*", which means zero or
+ more matches. If a sequence of rows which satisfies the PATTERN is found,
+ in the starting row of the sequence of rows all window functions and
+ aggregates are shown in the target list. Note that aggregations only look
+ into the matched rows, rather than whole frame. In the second or
+ subsequent rows all window functions and aggregates are NULL. For rows
+ that do not match the PATTERN, all window functions and aggregates are
+ shown AS NULL too, except count which shows 0. This is because the
+ unmatched rows are in an empty frame. Example of
+ a <literal>SELECT</literal> using the <literal>DEFINE</literal>
+ and <literal>PATTERN</literal> clause is as follows.
+
+<programlisting>
+SELECT company, tdate, price,
+ first_value(price) OVER w,
+ max(price) OVER w,
+ count(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 (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+</programlisting>
+<screen>
+ company | tdate | price | first_value | max | count
+----------+------------+-------+-------------+-----+-------
+ company1 | 2023-07-01 | 100 | 100 | 200 | 4
+ company1 | 2023-07-02 | 200 | | |
+ company1 | 2023-07-03 | 150 | | |
+ company1 | 2023-07-04 | 140 | | |
+ company1 | 2023-07-05 | 150 | | | 0
+ company1 | 2023-07-06 | 90 | 90 | 130 | 4
+ company1 | 2023-07-07 | 110 | | |
+ company1 | 2023-07-08 | 130 | | |
+ company1 | 2023-07-09 | 120 | | |
+ company1 | 2023-07-10 | 130 | | | 0
+</screen>
+ </para>
+
<para>
When a query involves multiple window functions, it is possible to write
out each one with a separate <literal>OVER</literal> clause, but this is
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93b0bc2bc6..d25eeb3327 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -22637,6 +22637,7 @@ SELECT count(*) FROM sometable;
returns <literal>NULL</literal> if there is no such row.
</para></entry>
</row>
+
</tbody>
</tgroup>
</table>
@@ -22676,6 +22677,59 @@ SELECT count(*) FROM sometable;
Other frame specifications can be used to obtain other effects.
</para>
+ <para>
+ Row pattern recognition navigation functions are listed in
+ <xref linkend="functions-rpr-navigation-table"/>. These functions
+ can be used to describe DEFINE clause of Row pattern recognition.
+ </para>
+
+ <table id="functions-rpr-navigation-table">
+ <title>Row Pattern Navigation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>prev</primary>
+ </indexterm>
+ <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the previous row;
+ returns NULL if there is no previous row in the window frame.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>next</primary>
+ </indexterm>
+ <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Returns the column value at the next row;
+ returns NULL if there is no next row in the window frame.
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
<note>
<para>
The SQL standard defines a <literal>RESPECT NULLS</literal> or
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 066aed44e6..8f18718d58 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -969,8 +969,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
The <replaceable class="parameter">frame_clause</replaceable> can be one of
<synopsis>
-{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
-{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
+{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
+{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax]
</synopsis>
where <replaceable>frame_start</replaceable>
@@ -1077,6 +1077,40 @@ EXCLUDE NO OTHERS
a given peer group will be in the frame or excluded from it.
</para>
+ <para>
+ The
+ optional <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ defines the <firstterm>row pattern recognition condition</firstterm> for
+ this
+ window. <replaceable class="parameter">row_pattern_common_syntax</replaceable>
+ includes following subclauses. <literal>AFTER MATCH SKIP PAST LAST
+ ROW</literal> or <literal>AFTER MATCH SKIP TO NEXT ROW</literal> controls
+ how to proceed to next row position after a match
+ found. With <literal>AFTER MATCH SKIP PAST LAST ROW</literal> (the
+ default) next row position is next to the last row of previous match. On
+ the other hand, with <literal>AFTER MATCH SKIP TO NEXT ROW</literal> next
+ row position is always next to the last row of previous
+ match. <literal>DEFINE</literal> defines definition variables along with a
+ boolean expression. <literal>PATTERN</literal> defines a sequence of rows
+ that satisfies certain conditions using variables defined
+ in <literal>DEFINE</literal> clause. If the variable is not defined in
+ the <literal>DEFINE</literal> clause, it is implicitly assumed
+ following is defined in the <literal>DEFINE</literal> clause.
+
+<synopsis>
+<literal>variable_name</literal> AS TRUE
+</synopsis>
+
+ Note that the maximu number of variables defined
+ in <literal>DEFINE</literal> clause is 26.
+
+<synopsis>
+[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ]
+PATTERN <replaceable class="parameter">pattern_variable_name</replaceable>[+] [, ...]
+DEFINE <replaceable class="parameter">definition_varible_name</replaceable> AS <replaceable class="parameter">expression</replaceable> [, ...]
+</synopsis>
+ </para>
+
<para>
The purpose of a <literal>WINDOW</literal> clause is to specify the
behavior of <firstterm>window functions</firstterm> appearing in the query's
--
2.25.1
----Next_Part(Thu_Mar_28_19_59_25_2024_076)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v15-0007-Row-pattern-recognition-patch-tests.patch"
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SPI_connect, SPI_connect_ext return type
@ 2024-08-10 14:45 Stepan Neretin <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Stepan Neretin @ 2024-08-10 14:45 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
> >That would break a lot of code (much of it not under our control) to
> little purpose; it would also foreclose the option to return to using
> SPI_ERROR_CONNECT someday.
>
Agree, it makes sense.
The only question left is, is there any logic in why in some places its
return value of these functions is checked, and in some not? Can I add
checks everywhere?
Best Regards, Stepan Neretin.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-08-10 14:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-20 08:02 [PATCH v18 12/18] tableam: VACUUM and ANALYZE. Andres Freund <[email protected]>
2024-03-28 10:30 [PATCH v15 6/8] Row pattern recognition patch (docs). Tatsuo Ishii <[email protected]>
2024-08-10 14:45 Re: SPI_connect, SPI_connect_ext return type Stepan Neretin <[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