agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH v4 1/2] Move a few ResourceOwnerEnlarge() calls for safety and clarity.
5+ messages / 4 participants
[nested] [flat]
* [PATCH v4 1/2] Move a few ResourceOwnerEnlarge() calls for safety and clarity.
@ 2021-01-13 10:21 Heikki Linnakangas <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Heikki Linnakangas @ 2021-01-13 10:21 UTC (permalink / raw)
These are functions where quite a lot of things happen between the
ResourceOwnerEnlarge and ResourceOwnerRemember calls. It's important that
there are no unrelated ResourceOwnerRemember() calls in the code
inbetween, otherwise the entry reserved by the ResourceOwnerEnlarge() call
might be used up by the intervening ResourceOwnerRemember() and not be
available at the intended ResourceOwnerRemember() call anymore. The longer
the code path between them is, the harder it is to verify that.
In bufmgr.c, there is a function similar to ResourceOwnerEnlarge(),
to ensure that the private refcount array has enough space. The
ReservePrivateRefCountEntry() calls, analogous to ResourceOwnerEnlarge(),
were made at different places than the ResourceOwnerEnlarge() calls.
Move the ResourceOwnerEnlarge() calls together with the
ReservePrivateRefCountEntry() calls for consistency.
---
src/backend/storage/buffer/bufmgr.c | 39 +++++++++++----------------
src/backend/storage/buffer/localbuf.c | 3 +++
src/backend/utils/cache/catcache.c | 13 ++++++---
3 files changed, 28 insertions(+), 27 deletions(-)
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 561c212092f..cde869e7d64 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -738,9 +738,6 @@ ReadBuffer_common(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
*hit = false;
- /* Make sure we will have room to remember the buffer pin */
- ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
-
isExtend = (blockNum == P_NEW);
TRACE_POSTGRESQL_BUFFER_READ_START(forkNum, blockNum,
@@ -1093,9 +1090,11 @@ BufferAlloc(SMgrRelation smgr, char relpersistence, ForkNumber forkNum,
{
/*
* Ensure, while the spinlock's not yet held, that there's a free
- * refcount entry.
+ * refcount entry and that the resoure owner has room to remember the
+ * pin.
*/
ReservePrivateRefCountEntry();
+ ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
/*
* Select a victim buffer. The buffer is returned with its header
@@ -1595,8 +1594,6 @@ ReleaseAndReadBuffer(Buffer buffer,
* taking the buffer header lock; instead update the state variable in loop of
* CAS operations. Hopefully it's just a single CAS.
*
- * Note that ResourceOwnerEnlargeBuffers must have been done already.
- *
* Returns true if buffer is BM_VALID, else false. This provision allows
* some callers to avoid an extra spinlock cycle.
*/
@@ -1607,6 +1604,8 @@ PinBuffer(BufferDesc *buf, BufferAccessStrategy strategy)
bool result;
PrivateRefCountEntry *ref;
+ ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
+
ref = GetPrivateRefCountEntry(b, true);
if (ref == NULL)
@@ -1687,7 +1686,8 @@ PinBuffer(BufferDesc *buf, BufferAccessStrategy strategy)
* The spinlock is released before return.
*
* As this function is called with the spinlock held, the caller has to
- * previously call ReservePrivateRefCountEntry().
+ * previously call ReservePrivateRefCountEntry() and
+ * ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
*
* Currently, no callers of this function want to modify the buffer's
* usage_count at all, so there's no need for a strategy parameter.
@@ -1857,9 +1857,6 @@ BufferSync(int flags)
int mask = BM_DIRTY;
WritebackContext wb_context;
- /* Make sure we can handle the pin inside SyncOneBuffer */
- ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
-
/*
* Unless this is a shutdown checkpoint or we have been explicitly told,
* we write only permanent, dirty buffers. But at shutdown or end of
@@ -2334,9 +2331,6 @@ BgBufferSync(WritebackContext *wb_context)
* requirements, or hit the bgwriter_lru_maxpages limit.
*/
- /* Make sure we can handle the pin inside SyncOneBuffer */
- ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
-
num_to_scan = bufs_to_lap;
num_written = 0;
reusable_buffers = reusable_buffers_est;
@@ -2418,8 +2412,6 @@ BgBufferSync(WritebackContext *wb_context)
*
* (BUF_WRITTEN could be set in error if FlushBuffer finds the buffer clean
* after locking it, but we don't care all that much.)
- *
- * Note: caller must have done ResourceOwnerEnlargeBuffers.
*/
static int
SyncOneBuffer(int buf_id, bool skip_recently_used, WritebackContext *wb_context)
@@ -2429,7 +2421,9 @@ SyncOneBuffer(int buf_id, bool skip_recently_used, WritebackContext *wb_context)
uint32 buf_state;
BufferTag tag;
+ /* Make sure we can handle the pin */
ReservePrivateRefCountEntry();
+ ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
/*
* Check whether buffer needs writing.
@@ -3487,9 +3481,6 @@ FlushRelationBuffers(Relation rel)
return;
}
- /* Make sure we can handle the pin inside the loop */
- ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
-
for (i = 0; i < NBuffers; i++)
{
uint32 buf_state;
@@ -3503,7 +3494,9 @@ FlushRelationBuffers(Relation rel)
if (!RelFileNodeEquals(bufHdr->tag.rnode, rel->rd_node))
continue;
+ /* Make sure we can handle the pin */
ReservePrivateRefCountEntry();
+ ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
buf_state = LockBufHdr(bufHdr);
if (RelFileNodeEquals(bufHdr->tag.rnode, rel->rd_node) &&
@@ -3560,9 +3553,6 @@ FlushRelationsAllBuffers(SMgrRelation *smgrs, int nrels)
if (use_bsearch)
pg_qsort(srels, nrels, sizeof(SMgrSortArray), rnode_comparator);
- /* Make sure we can handle the pin inside the loop */
- ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
-
for (i = 0; i < NBuffers; i++)
{
SMgrSortArray *srelent = NULL;
@@ -3599,7 +3589,9 @@ FlushRelationsAllBuffers(SMgrRelation *smgrs, int nrels)
if (srelent == NULL)
continue;
+ /* Make sure we can handle the pin */
ReservePrivateRefCountEntry();
+ ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
buf_state = LockBufHdr(bufHdr);
if (RelFileNodeEquals(bufHdr->tag.rnode, srelent->rnode) &&
@@ -3639,9 +3631,6 @@ FlushDatabaseBuffers(Oid dbid)
int i;
BufferDesc *bufHdr;
- /* Make sure we can handle the pin inside the loop */
- ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
-
for (i = 0; i < NBuffers; i++)
{
uint32 buf_state;
@@ -3655,7 +3644,9 @@ FlushDatabaseBuffers(Oid dbid)
if (bufHdr->tag.rnode.dbNode != dbid)
continue;
+ /* Make sure we can handle the pin */
ReservePrivateRefCountEntry();
+ ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
buf_state = LockBufHdr(bufHdr);
if (bufHdr->tag.rnode.dbNode == dbid &&
diff --git a/src/backend/storage/buffer/localbuf.c b/src/backend/storage/buffer/localbuf.c
index 04b3558ea33..f7c15ea8a44 100644
--- a/src/backend/storage/buffer/localbuf.c
+++ b/src/backend/storage/buffer/localbuf.c
@@ -123,6 +123,9 @@ LocalBufferAlloc(SMgrRelation smgr, ForkNumber forkNum, BlockNumber blockNum,
if (LocalBufHash == NULL)
InitLocalBuffers();
+ /* Make sure we will have room to remember the buffer pin */
+ ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
+
/* See if the desired buffer already exists */
hresult = (LocalBufferLookupEnt *)
hash_search(LocalBufHash, (void *) &newTag, HASH_FIND, NULL);
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index fa2b49c676e..86fdfc454d2 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1607,8 +1607,6 @@ SearchCatCacheList(CatCache *cache,
* block to ensure we can undo those refcounts if we get an error before
* we finish constructing the CatCList.
*/
- ResourceOwnerEnlargeCatCacheListRefs(CurrentResourceOwner);
-
ctlist = NIL;
PG_TRY();
@@ -1696,13 +1694,22 @@ SearchCatCacheList(CatCache *cache,
table_close(relation, AccessShareLock);
+ /* Make sure the resource owner has room to remember this entry. */
+ ResourceOwnerEnlargeCatCacheListRefs(CurrentResourceOwner);
+
/* Now we can build the CatCList entry. */
oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
nmembers = list_length(ctlist);
cl = (CatCList *)
palloc(offsetof(CatCList, members) + nmembers * sizeof(CatCTup *));
- /* Extract key values */
+ /*
+ * Extract key values.
+ *
+ * XXX: If we run out of memory while copying the key values, we will
+ * leak any allocations we had already made in the CacheMemoryContext.
+ * That is unlikely enough that we just accept the risk.
+ */
CatCacheCopyKeys(cache->cc_tupdesc, nkeys, cache->cc_keyno,
arguments, cl->keys);
MemoryContextSwitchTo(oldcxt);
--
2.29.2
--------------7349B9D66ED595EF3B1030B4
Content-Type: text/x-patch; charset=UTF-8;
name="v4-0002-Make-resowners-more-easily-extensible.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="v4-0002-Make-resowners-more-easily-extensible.patch"
^ permalink raw reply [nested|flat] 5+ messages in thread
* pub/sub - specifying optional parameters without values.
@ 2022-10-14 08:54 Peter Smith <[email protected]>
2022-10-17 20:09 ` Re: pub/sub - specifying optional parameters without values. Justin Pryzby <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Smith @ 2022-10-14 08:54 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>
Hi hackers.
This post is about parameter default values. Specifically. it's about
the CREATE PUBLICATION and CREATE SUBSCRIPTION syntax, although the
same issue might apply to other commands I am unaware of...
~~~
Background:
CREATE PUBLICATION syntax has a WITH clause:
[ WITH ( publication_parameter [= value] [, ... ] ) ]
CREATE SUBSCRIPTION syntax has a similar clause:
[ WITH ( subscription_parameter [= value] [, ... ] ) ]
~~~
The docs describe all the parameters that can be specified. Parameters
are optional, so the docs describe the defaults if the parameter name
is not specified. However, notice that the parameter *value* part is
also optional.
So, what is the defined behaviour if a parameter name is specified but
no *value* is given?
In practice, it seems to just be a shorthand for assigning a boolean
value to true... BUT -
a) I can't find anywhere in the docs where it actually says this
b) Without documentation some might consider it to be strange that now
there are 2 kinds of defaults - a default when there is no name, and
another default when there is no value - and those are not always the
same. e.g. if publish_via_partition root is not specified at all, it
is equivalent of WITH (publish_via_partition_root=false), but OTOH,
WITH (publish_via_partition_root) is equivalent of WITH
(publish_via_partition_root=true).
c) What about non-boolean parameters? In practice, it seems they all
give errors:
test_pub=# CREATE PUBLICATION pub99 FOR ALL TABLES WITH (publish);
ERROR: publish requires a parameter
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost
dbname=test_pub' PUBLICATION pub1 WITH (slot_name);
ERROR: slot_name requires a parameter
test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost
dbname=test_pub' PUBLICATION pub1 WITH (synchronous_commit);
ERROR: synchronous_commit requires a parameter
~~~
It almost feels like this is an undocumented feature, except it isn't
quite undocumented because it is right there in black-and-white in the
syntax "[= value]". Or perhaps this implied boolean-true behaviour is
already described elsewhere? But if it is, I have not found it yet.
IMO a simple patch (PSA) is needed to clarify the behaviour.
Thoughts?
------
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
[application/octet-stream] v1-0001-clarify-behavior-of-specifying-a-parameter-with-n.patch (2.0K, ../../CAHut+PvwjZfdGt2R8HTXgSZft=jZKymrS8KUg31pS7zqaaWKKw@mail.gmail.com/2-v1-0001-clarify-behavior-of-specifying-a-parameter-with-n.patch)
download | inline diff:
From 5b8f68403ff1ff024e339ee43dd56f3ad4c26fc8 Mon Sep 17 00:00:00 2001
From: Peter Smith <[email protected]>
Date: Fri, 14 Oct 2022 19:38:32 +1100
Subject: [PATCH v1] clarify behavior of specifying a parameter with no value
---
doc/src/sgml/ref/create_publication.sgml | 10 ++++++++--
doc/src/sgml/ref/create_subscription.sgml | 4 ++++
2 files changed, 12 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index c5190f0..b4b742d 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -162,8 +162,14 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
<para>
- This clause specifies optional parameters for a publication. The
- following parameters are supported:
+ This clause specifies optional parameters for a publication.
+ </para>
+ <para>
+ A <type>boolean</type> parameter can omit the value. This is equivalent
+ to assigning the parameter to <literal>true</literal>.
+ </para>
+ <para>
+ The following parameters are supported:
<variablelist>
<varlistentry>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index 43b32e8..21bd90f 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -96,6 +96,10 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
This clause specifies optional parameters for a subscription.
</para>
+ <para>
+ A <type>boolean</type> parameter can omit the value. This is equivalent
+ to assigning the parameter to <literal>true</literal>.
+ </para>
<para>
The following parameters control what happens during subscription creation:
--
1.8.3.1
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pub/sub - specifying optional parameters without values.
2022-10-14 08:54 pub/sub - specifying optional parameters without values. Peter Smith <[email protected]>
@ 2022-10-17 20:09 ` Justin Pryzby <[email protected]>
2022-10-19 02:10 ` Re: pub/sub - specifying optional parameters without values. Peter Smith <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Justin Pryzby @ 2022-10-17 20:09 UTC (permalink / raw)
To: Peter Smith <[email protected]>; +Cc: [email protected]
On Fri, Oct 14, 2022 at 07:54:37PM +1100, Peter Smith wrote:
> Hi hackers.
>
> This post is about parameter default values. Specifically. it's about
> the CREATE PUBLICATION and CREATE SUBSCRIPTION syntax, although the
> same issue might apply to other commands I am unaware of...
The same thing seems to be true in various other pages:
git grep 'WITH.*value' doc
In addition to WITH, it's also true of SET:
git grep -F '[= <replaceable class="parameter">value' doc/src/sgml/ref/alter_index.sgml doc/src/sgml/ref/alter_table.sgml doc/src/sgml/ref/create_materialized_view.sgml doc/src/sgml/ref/create_publication.sgml doc/src/sgml/ref/create_subscription.sgml
Note that some utility statements (analyze,cluster,vacuum,reindex) which
have parenthesized syntax with booleans say this:
| The boolean value can also be omitted, in which case TRUE is assumed.
BTW, in your patch:
+ <para>
+ A <type>boolean</type> parameter can omit the value. This is equivalent
+ to assigning the parameter to <literal>true</literal>.
+ </para>
+ <para>
should say: "The value can be omitted, which is equivalent to specifying
TRUE".
--
Justin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pub/sub - specifying optional parameters without values.
2022-10-14 08:54 pub/sub - specifying optional parameters without values. Peter Smith <[email protected]>
2022-10-17 20:09 ` Re: pub/sub - specifying optional parameters without values. Justin Pryzby <[email protected]>
@ 2022-10-19 02:10 ` Peter Smith <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Peter Smith @ 2022-10-19 02:10 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: [email protected]
On Tue, Oct 18, 2022 at 7:09 AM Justin Pryzby <[email protected]> wrote:
>
> On Fri, Oct 14, 2022 at 07:54:37PM +1100, Peter Smith wrote:
> > Hi hackers.
> >
> > This post is about parameter default values. Specifically. it's about
> > the CREATE PUBLICATION and CREATE SUBSCRIPTION syntax, although the
> > same issue might apply to other commands I am unaware of...
>
> The same thing seems to be true in various other pages:
> git grep 'WITH.*value' doc
>
> In addition to WITH, it's also true of SET:
>
> git grep -F '[= <replaceable class="parameter">value' doc/src/sgml/ref/alter_index.sgml doc/src/sgml/ref/alter_table.sgml doc/src/sgml/ref/create_materialized_view.sgml doc/src/sgml/ref/create_publication.sgml doc/src/sgml/ref/create_subscription.sgml
>
> Note that some utility statements (analyze,cluster,vacuum,reindex) which
> have parenthesized syntax with booleans say this:
> | The boolean value can also be omitted, in which case TRUE is assumed.
Thank you for the feedback and for reporting about other places
similar to this. For now, I only intended to fix docs related to
logical replication. Scope creep to other areas maybe can be addressed
by subsequent patches if this one gets accepted.
>
> BTW, in your patch:
> + <para>
> + A <type>boolean</type> parameter can omit the value. This is equivalent
> + to assigning the parameter to <literal>true</literal>.
> + </para>
> + <para>
>
> should say: "The value can be omitted, which is equivalent to specifying
> TRUE".
>
I've changed the text as you suggested, except in a couple of places
where I qualified by saying "For boolean parameters..."; that's
because the value part is not *always* optional. I've also made
similar updates to the ALTER PUBLICATION/SUBSCRIPTION pages, which
were accidentally missed before.
------
Kind Regards,
Peter Smith.
Fujitsu Australia
Attachments:
[application/octet-stream] v2-0001-clarify-behavior-of-specifying-a-parameter-with-n.patch (5.0K, ../../CAHut+Pt_GEKrRQuqKKJu6v1g61_BEGKx8TNuhYN_euDmZwqRfA@mail.gmail.com/2-v2-0001-clarify-behavior-of-specifying-a-parameter-with-n.patch)
download | inline diff:
From 19b2182a747675ac0f509b13a9c86908edaa0385 Mon Sep 17 00:00:00 2001
From: Peter Smith <[email protected]>
Date: Wed, 19 Oct 2022 12:58:50 +1100
Subject: [PATCH v2] clarify behavior of specifying a parameter with no value
Modified docs for
- CREATE/ALTER PUBLICATION
- CREATE/ALTER SUBSCRIPTION
---
doc/src/sgml/ref/alter_publication.sgml | 3 +++
doc/src/sgml/ref/alter_subscription.sgml | 12 +++++++++---
doc/src/sgml/ref/create_publication.sgml | 11 +++++++++--
doc/src/sgml/ref/create_subscription.sgml | 5 +++++
4 files changed, 26 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index c84b11f..2b4eb1f 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -148,6 +148,9 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<para>
This clause alters publication parameters originally set by
<xref linkend="sql-createpublication"/>. See there for more information.
+ For <type>boolean</type> parameters the
+ <replaceable class="parameter">value</replaceable> can be omitted, which
+ is equivalent to specifying <literal>TRUE</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml
index 1e8d720..759b7a3 100644
--- a/doc/src/sgml/ref/alter_subscription.sgml
+++ b/doc/src/sgml/ref/alter_subscription.sgml
@@ -132,7 +132,9 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
<para>
When false, the command will not try to refresh table information.
<literal>REFRESH PUBLICATION</literal> should then be executed separately.
- The default is <literal>true</literal>.
+ The default is <literal>true</literal>. The
+ <replaceable class="parameter">value</replaceable> can be omitted,
+ which is equivalent to specifying <literal>TRUE</literal>.
</para>
</listitem>
</varlistentry>
@@ -166,7 +168,9 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
<para>
Specifies whether to copy pre-existing data in the publications
that are being subscribed to when the replication starts.
- The default is <literal>true</literal>.
+ The default is <literal>true</literal>. The
+ <replaceable class="parameter">value</replaceable> can be omitted,
+ which is equivalent to specifying <literal>TRUE</literal>.
</para>
<para>
Previously subscribed tables are not copied, even if a table's row
@@ -214,7 +218,9 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
<literal>synchronous_commit</literal>,
<literal>binary</literal>, <literal>streaming</literal>,
<literal>disable_on_error</literal>, and
- <literal>origin</literal>.
+ <literal>origin</literal>. For <type>boolean</type> parameters the
+ <replaceable class="parameter">value</replaceable> can be omitted, which
+ is equivalent to specifying <literal>TRUE</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index e229384..d8015af 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -162,8 +162,15 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
<para>
- This clause specifies optional parameters for a publication. The
- following parameters are supported:
+ This clause specifies optional parameters for a publication.
+ </para>
+ <para>
+ For <type>boolean</type> parameters the
+ <replaceable class="parameter">value</replaceable> can be omitted, which
+ is equivalent to specifying <literal>TRUE</literal>.
+ </para>
+ <para>
+ The following parameters are supported:
<variablelist>
<varlistentry>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index f9a1776..9406aa9 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -96,6 +96,11 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
<para>
This clause specifies optional parameters for a subscription.
</para>
+ <para>
+ For <type>boolean</type> parameters the
+ <replaceable class="parameter">value</replaceable> can be omitted, which
+ is equivalent to specifying <literal>TRUE</literal>.
+ </para>
<para>
The following parameters control what happens during subscription creation:
--
1.8.3.1
^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH v9 6/7] Row pattern recognition patch (tests).
@ 2023-10-04 05:51 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Tatsuo Ishii @ 2023-10-04 05:51 UTC (permalink / raw)
---
src/test/regress/expected/rpr.out | 633 +++++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/rpr.sql | 308 ++++++++++++++
3 files changed, 942 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..194f763bce
--- /dev/null
+++ b/src/test/regress/expected/rpr.out
@@ -0,0 +1,633 @@
+--
+-- 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)
+
+-- 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 | | |
+ company1 | 07-03-2023 | 150 | | |
+ company1 | 07-04-2023 | 140 | 07-04-2023 | 07-06-2023 | 3
+ company1 | 07-05-2023 | 150 | | |
+ company1 | 07-06-2023 | 90 | | |
+ company1 | 07-07-2023 | 110 | 07-07-2023 | 07-09-2023 | 3
+ company1 | 07-08-2023 | 130 | | |
+ company1 | 07-09-2023 | 120 | | |
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | 07-01-2023 | 07-03-2023 | 3
+ company2 | 07-02-2023 | 2000 | | |
+ company2 | 07-03-2023 | 1500 | | |
+ company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-06-2023 | 3
+ company2 | 07-05-2023 | 1500 | | |
+ company2 | 07-06-2023 | 60 | | |
+ company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-09-2023 | 3
+ company2 | 07-08-2023 | 1300 | | |
+ company2 | 07-09-2023 | 1200 | | |
+ 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 | | | | | | |
+ company1 | 07-03-2023 | 150 | | | | | | |
+ company1 | 07-04-2023 | 140 | | | | | | |
+ company1 | 07-05-2023 | 150 | | | | | | | 0
+ company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
+ company1 | 07-07-2023 | 110 | | | | | | |
+ company1 | 07-08-2023 | 130 | | | | | | |
+ company1 | 07-09-2023 | 120 | | | | | | |
+ company1 | 07-10-2023 | 130 | | | | | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
+ company2 | 07-02-2023 | 2000 | | | | | | |
+ company2 | 07-03-2023 | 1500 | | | | | | |
+ company2 | 07-04-2023 | 1400 | | | | | | |
+ company2 | 07-05-2023 | 1500 | | | | | | | 0
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
+ company2 | 07-07-2023 | 1100 | | | | | | |
+ company2 | 07-08-2023 | 1300 | | | | | | |
+ company2 | 07-09-2023 | 1200 | | | | | | |
+ 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)
+
+--
+-- 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
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ORDER BY tdate
+ 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: syntax error at or near "ORDER"
+LINE 6: ORDER BY tdate
+ ^
+-- pattern variable name must appear in DEFINE
+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+ END)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+ERROR: syntax error at or near "END"
+LINE 8: PATTERN (START UP+ DOWN+ END)
+ ^
+-- 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.
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df9d8503b..896531002b 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..c19e0fed0d
--- /dev/null
+++ b/src/test/regress/sql/rpr.sql
@@ -0,0 +1,308 @@
+--
+-- 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)
+);
+
+-- 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)
+);
+
+--
+-- 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
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ ORDER BY tdate
+ INITIAL
+ PATTERN (START UP+ DOWN+)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price),
+ UP AS price > PREV(price)
+);
+
+-- pattern variable name must appear in DEFINE
+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+ END)
+ DEFINE
+ START AS TRUE,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+
+-- 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)
+);
--
2.25.1
----Next_Part(Wed_Oct__4_15_03_28_2023_821)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v9-0007-Allow-to-print-raw-parse-tree.patch"
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-10-04 05:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-13 10:21 [PATCH v4 1/2] Move a few ResourceOwnerEnlarge() calls for safety and clarity. Heikki Linnakangas <[email protected]>
2022-10-14 08:54 pub/sub - specifying optional parameters without values. Peter Smith <[email protected]>
2022-10-17 20:09 ` Re: pub/sub - specifying optional parameters without values. Justin Pryzby <[email protected]>
2022-10-19 02:10 ` Re: pub/sub - specifying optional parameters without values. Peter Smith <[email protected]>
2023-10-04 05:51 [PATCH v9 6/7] 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