public inbox for [email protected]
help / color / mirror / Atom feedRe: plpython vs _POSIX_C_SOURCE
8+ messages / 4 participants
[nested] [flat]
* Re: plpython vs _POSIX_C_SOURCE
@ 2023-01-25 01:48 Andres Freund <[email protected]>
2023-01-25 02:32 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Andres Freund @ 2023-01-25 01:48 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Noah Misch <[email protected]>; +Cc: pgsql-hackers; Peter Eisentraut <[email protected]>; Andrew Dunstan <[email protected]>
Hi,
On 2023-01-24 16:16:06 -0500, Tom Lane wrote:
> Andres Freund <[email protected]> writes:
> > Python's _POSIX_C_SOURCE value is set to a specific value in their configure
> > script:
>
> > if test $define_xopen_source = yes
> > then
> > ...
> > AC_DEFINE(_POSIX_C_SOURCE, 200809L, Define to activate features from IEEE Stds 1003.1-2008)
> > fi
>
> Hm. I looked into Python 3.2 (the oldest release we still support)
> and it has similar code but
>
> AC_DEFINE(_POSIX_C_SOURCE, 200112L, Define to activate features from IEEE Stds 1003.1-2001)
>
> So yeah it's fixed (or else not defined) for any particular Python
> release, but could vary across releases.
Looks like it changed in 3.3:
$ git grep -E 'AC_DEFINE.*_POSIX_C_SOURCE' v3.2 v3.3.0
v3.2:configure.in: AC_DEFINE(_POSIX_C_SOURCE, 200112L, Define to activate features from IEEE Stds 1003.1-2001)
v3.3.0:configure.ac: AC_DEFINE(_POSIX_C_SOURCE, 200809L, Define to activate features from IEEE Stds 1003.1-2008)
I'm not sure we need to care a lot about a build with python 3.3 triggering a
bunch of warnings.
Personally I'd just bump the python requirements to well above it - the last
3.2 release was Oct. 12, 2014.
Official EOL date:
Ver Last Release EOL Date
3.2 2014-10-11 2016-02-20
3.3 2017-09-19 2017-09-29
From 3.4 on there's just an official last release:
3.4 2019-03-18
3.5 2020-09-05
3.6 2021-09-04
3.7 2023-06-??
> > Solaris and AIX are the ones missing.
> > I guess I'll test them manually. It seems promising not to need this stuff
> > anymore?
>
> Given that hoverfly is AIX, I'm betting there's an issue there.
Doesn't look that way.
I found plenty problems on AIX, but all independent of _POSIX_C_SOURCE.
Both autoconf and meson builds seem to need externally specified
-D_LARGE_FILES=1 to build successfully when using plpython, otherwise we end
up with conflicting signatures with lseek. I see that Noah has that in his
buildfarm config. ISTM that we should just move that into our build specs.
To get 64 bit autoconf to link plpython3.so correctly, I needed to add to
manually add -lpthread:
ld: 0711-317 ERROR: Undefined symbol: .pthread_init
...
I suspect Noah might not hit this, because one of the dependencies he has
enabled already adds it to the backend LDFLAGS.
Also for autoconf, I needed to link
$prefix/lib/python3.11/config-3.11/libpython3.11.a
to
$prefix/lib/libpython3.11.a
That might be a python version difference or be related to building python
with --enable-shared - but I see saw other problems without --enable-shared.
I ran out of energy to test on aix with xlc, I spent way more time on this
than I have already. I'll pick it up later.
I also tested 64bit solaris. No relevant warnings (lots of other warnings
though), tests pass, with both acc and gcc.
> >> Anyway, I'm still of the opinion that what a11cf433413 tried to do
> >> was the best available fix, and we need to do whatever we have to do
> >> to plpython's headers to reinstate that coding rule.
>
> > You think it's not a viable path to just remove the _POSIX_C_SOURCE,
> > _XOPEN_SOURCE undefines?
>
> I think at the least that will result in warnings on some platforms,
> and at the worst in actual build problems. Maybe there are no more
> of the latter a dozen years after the fact, but ...
I think it might be ok. I tested nearly all OSs that we support, with the
exception of DragonFlyBSD and Illumos, which both are very similar to tested
operating systems.
> Nice idea. We did not have that option while we were using HAVE_SNPRINTF
> ourselves, but now that we are not I concur that this should work.
Cool.
> (I confirmed that their code looks the same in Python 3.2.)
> Note that you'd better make it
>
> #define HAVE_SNPRINTF 1
>
> or you risk macro-redefinition warnings.
Good point.
I guess I'll push that part first, given that we have agreement how it should
look like.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpython vs _POSIX_C_SOURCE
2023-01-25 01:48 Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
@ 2023-01-25 02:32 ` Andres Freund <[email protected]>
2023-01-25 04:28 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Andres Freund @ 2023-01-25 02:32 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Noah Misch <[email protected]>; +Cc: pgsql-hackers; Peter Eisentraut <[email protected]>; Andrew Dunstan <[email protected]>
Hi,
On 2023-01-24 17:48:56 -0800, Andres Freund wrote:
> Also for autoconf, I needed to link
> $prefix/lib/python3.11/config-3.11/libpython3.11.a
> to
> $prefix/lib/libpython3.11.a
> That might be a python version difference or be related to building python
> with --enable-shared - but I see saw other problems without --enable-shared.
That actually doesn't quite work right. One needs to either link to the file
by name (i.e. just $prefix/lib/libpython3.11.so instead of -lpython3.11), or
create a wrapper .a "manually". I.e.
ar crs $prefix/lib/libpython3.11.a $prefix/lib/libpython3.11.so
I tried quite a few things and got confused between the attempts.
> I ran out of energy to test on aix with xlc, I spent way more time on this
> than I have already. I'll pick it up later.
Building in the background now.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpython vs _POSIX_C_SOURCE
2023-01-25 01:48 Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 02:32 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
@ 2023-01-25 04:28 ` Andres Freund <[email protected]>
2023-01-25 04:37 ` Re: plpython vs _POSIX_C_SOURCE Tom Lane <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Andres Freund @ 2023-01-25 04:28 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Noah Misch <[email protected]>; +Cc: pgsql-hackers; Peter Eisentraut <[email protected]>; Andrew Dunstan <[email protected]>
Hi,
On 2023-01-24 18:32:46 -0800, Andres Freund wrote:
> > I ran out of energy to test on aix with xlc, I spent way more time on this
> > than I have already. I'll pick it up later.
>
> Building in the background now.
Also passes.
Thus I think getting rid of the #undefines is the best plan going
forward. Fewer complicated rules to follow => fewer rule violations.
Patches attached.
Greetings,
Andres Freund
Attachments:
[text/x-diff] v1-0001-plpython-Avoid-the-need-to-redefine-printf-macros.patch (2.3K, ../../[email protected]/2-v1-0001-plpython-Avoid-the-need-to-redefine-printf-macros.patch)
download | inline diff:
From eb3fb959b3db609db44437f783fc58cdb7f28e9f Mon Sep 17 00:00:00 2001
From: Andres Freund <[email protected]>
Date: Tue, 24 Jan 2023 11:42:35 -0800
Subject: [PATCH v1 1/3] plpython: Avoid the need to redefine *printf macros
Until now we undefined and then redefined a lot of *printf macros due to
worries about conflicts with Python.h macro definitions. Current Python.h
doesn't define any *printf macros, and older versions just defined snprintf,
vsnprintf, guarded by #if defined(MS_WIN32) && !defined(HAVE_SNPRINTF).
Thus we can replace the undefine/define section with a single
#define HAVE_SNPRINTF 1
Discussion: https://postgr.es/m/[email protected]
---
src/pl/plpython/plpython.h | 48 +++-----------------------------------
1 file changed, 3 insertions(+), 45 deletions(-)
diff --git a/src/pl/plpython/plpython.h b/src/pl/plpython/plpython.h
index 2af0d04d1f8..2d18fc2dc1b 100644
--- a/src/pl/plpython/plpython.h
+++ b/src/pl/plpython/plpython.h
@@ -30,17 +30,10 @@
#undef _XOPEN_SOURCE
/*
- * Sometimes python carefully scribbles on our *printf macros.
- * So we undefine them here and redefine them after it's done its dirty deed.
+ * Python versions <= 3.8 otherwise define a replacement, causing macro
+ * redefinition warnings.
*/
-#undef vsnprintf
-#undef snprintf
-#undef vsprintf
-#undef sprintf
-#undef vfprintf
-#undef fprintf
-#undef vprintf
-#undef printf
+#define HAVE_SNPRINTF 1
#if defined(_MSC_VER) && defined(_DEBUG)
/* Python uses #pragma to bring in a non-default libpython on VC++ if
@@ -63,41 +56,6 @@
#undef TEXTDOMAIN
#define TEXTDOMAIN PG_TEXTDOMAIN("plpython")
-/* put back our *printf macros ... this must match src/include/port.h */
-#ifdef vsnprintf
-#undef vsnprintf
-#endif
-#ifdef snprintf
-#undef snprintf
-#endif
-#ifdef vsprintf
-#undef vsprintf
-#endif
-#ifdef sprintf
-#undef sprintf
-#endif
-#ifdef vfprintf
-#undef vfprintf
-#endif
-#ifdef fprintf
-#undef fprintf
-#endif
-#ifdef vprintf
-#undef vprintf
-#endif
-#ifdef printf
-#undef printf
-#endif
-
-#define vsnprintf pg_vsnprintf
-#define snprintf pg_snprintf
-#define vsprintf pg_vsprintf
-#define sprintf pg_sprintf
-#define vfprintf pg_vfprintf
-#define fprintf pg_fprintf
-#define vprintf pg_vprintf
-#define printf(...) pg_printf(__VA_ARGS__)
-
/*
* Used throughout, so it's easier to just include it everywhere.
*/
--
2.38.0
[text/x-diff] v1-0002-plpython-Stop-undefining-_POSIX_C_SOURCE-_XOPEN_S.patch (2.6K, ../../[email protected]/3-v1-0002-plpython-Stop-undefining-_POSIX_C_SOURCE-_XOPEN_S.patch)
download | inline diff:
From 54f95177b0f32522862cc3589c587cbe3595304b Mon Sep 17 00:00:00 2001
From: Andres Freund <[email protected]>
Date: Tue, 24 Jan 2023 19:03:36 -0800
Subject: [PATCH v1 2/3] plpython: Stop undefining _POSIX_C_SOURCE,
_XOPEN_SOURCE
We undefined them to avoid warnings about macro redefinitions. But we haven't
fully followed the necessary include order, since at least 147c2482542, in
2011. Recently the combination of the include order rules not being followed
and undefining _POSIX_C_SOURCE started to cause a compile failure, starting
with 03023a2664f. Undefining _POSIX_C_SOURCE hides clock_gettime(), which is
referenced in an inline function as of 03023a2664f, whereas it was a macro
before.
After seeing some evidence that undefining _POSIX_C_SOURCE et al isn't
required, I tried to build postgres with plpython on most of our supported
platforms (except DragonFlyBSD and Illumos, but similar systems were tested),
with/without the #undefines. No compiler warning / behavioral difference.
The oldest supported python version, 3.2, defines _POSIX_C_SOURCE to 200112L
ad _XOPEN_SOURCE to 600, whereas newer versions of python use 200809L/700
respectively. As _POSIX_C_SOURCE/_XOPEN_SOURCE will default to the newer
operating system on most platforms, it's possible that when using python 3.2
new warnings would be emitted - but that seems acceptable.
Discussion: https://postgr.es/m/[email protected]
---
src/pl/plpython/plpython.h | 18 ++++--------------
1 file changed, 4 insertions(+), 14 deletions(-)
diff --git a/src/pl/plpython/plpython.h b/src/pl/plpython/plpython.h
index 2d18fc2dc1b..91f6f8d8607 100644
--- a/src/pl/plpython/plpython.h
+++ b/src/pl/plpython/plpython.h
@@ -12,23 +12,13 @@
#ifndef PLPYTHON_H
#define PLPYTHON_H
-/*
- * Include order should be: postgres.h, other postgres headers, plpython.h,
- * other plpython headers. (In practice, other plpython headers will also
- * include this file, so that they can compile standalone.)
- */
-#ifndef POSTGRES_H
+/* postgres.h needs to be included before Python.h, as usual */
+#if !defined(POSTGRES_H)
#error postgres.h must be included before plpython.h
+#elif defined(Py_PYTHON_H)
+#error Python.h must be included via plpython.h
#endif
-/*
- * Undefine some things that get (re)defined in the Python headers. They aren't
- * used by the PL/Python code, and all PostgreSQL headers should be included
- * earlier, so this should be pretty safe.
- */
-#undef _POSIX_C_SOURCE
-#undef _XOPEN_SOURCE
-
/*
* Python versions <= 3.8 otherwise define a replacement, causing macro
* redefinition warnings.
--
2.38.0
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpython vs _POSIX_C_SOURCE
2023-01-25 01:48 Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 02:32 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 04:28 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
@ 2023-01-25 04:37 ` Tom Lane <[email protected]>
2023-01-25 08:52 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 13:31 ` Re: plpython vs _POSIX_C_SOURCE Robert Haas <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2023-01-25 04:37 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Noah Misch <[email protected]>; pgsql-hackers; Peter Eisentraut <[email protected]>; Andrew Dunstan <[email protected]>
Andres Freund <[email protected]> writes:
> Patches attached.
+1 for 0001. I'm still nervous about 0002. However, maybe the
cases that we had trouble with are legacy issues that nobody cares
about anymore in 2023. We can always look for another answer if
we get complaints, I guess.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpython vs _POSIX_C_SOURCE
2023-01-25 01:48 Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 02:32 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 04:28 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 04:37 ` Re: plpython vs _POSIX_C_SOURCE Tom Lane <[email protected]>
@ 2023-01-25 08:52 ` Andres Freund <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Andres Freund @ 2023-01-25 08:52 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Noah Misch <[email protected]>; pgsql-hackers; Peter Eisentraut <[email protected]>; Andrew Dunstan <[email protected]>
Hi,
On 2023-01-24 23:37:44 -0500, Tom Lane wrote:
> Andres Freund <[email protected]> writes:
> > Patches attached.
>
> +1 for 0001.
Cool, will push tomorrow.
> I'm still nervous about 0002. However, maybe the cases that we had trouble
> with are legacy issues that nobody cares about anymore in 2023. We can
> always look for another answer if we get complaints, I guess.
Yea, it's a patch that should be easily revertable, if it comes to that. I'll
add a note to the commit message about potentially needing to do that if
there's not easily addressed fallout.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpython vs _POSIX_C_SOURCE
2023-01-25 01:48 Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 02:32 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 04:28 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 04:37 ` Re: plpython vs _POSIX_C_SOURCE Tom Lane <[email protected]>
@ 2023-01-25 13:31 ` Robert Haas <[email protected]>
2023-01-25 21:26 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Robert Haas @ 2023-01-25 13:31 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andres Freund <[email protected]>; Noah Misch <[email protected]>; pgsql-hackers; Peter Eisentraut <[email protected]>; Andrew Dunstan <[email protected]>
On Tue, Jan 24, 2023 at 11:37 PM Tom Lane <[email protected]> wrote:
> Andres Freund <[email protected]> writes:
> > Patches attached.
>
> +1 for 0001. I'm still nervous about 0002. However, maybe the
> cases that we had trouble with are legacy issues that nobody cares
> about anymore in 2023. We can always look for another answer if
> we get complaints, I guess.
It feels like things are changing so fast these days that whatever was
happening 12 years ago is not likely to be relevant. Compilers change
enough to cause warnings and even errors in just a few years. A decade
is long enough for an entire platform to become irrelevant.
Plus, the cost of experimentation here seems very low. Sure, something
might break, but if it does, we can just change it back, or change it
again. That's not really a big deal. The thing that would be a big
deal, maybe, is if we released and only found out afterward that this
caused some subtle and horrible problem for which we had no
back-patchable fix, but that seems pretty unlikely.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: plpython vs _POSIX_C_SOURCE
2023-01-25 01:48 Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 02:32 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 04:28 ` Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 04:37 ` Re: plpython vs _POSIX_C_SOURCE Tom Lane <[email protected]>
2023-01-25 13:31 ` Re: plpython vs _POSIX_C_SOURCE Robert Haas <[email protected]>
@ 2023-01-25 21:26 ` Andres Freund <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Andres Freund @ 2023-01-25 21:26 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Noah Misch <[email protected]>; pgsql-hackers; Peter Eisentraut <[email protected]>; Andrew Dunstan <[email protected]>
Hi,
Pushed the patches. So far no fallout, and hoverfly recovered.
I just checked a few of the more odd animals (Illumos, Solaris, old OpenBSD,
AIX) that already ran without finding new warnings.
There's a few more animals to run before I'll fully relax though.
On 2023-01-25 08:31:23 -0500, Robert Haas wrote:
> Plus, the cost of experimentation here seems very low. Sure, something
> might break, but if it does, we can just change it back, or change it
> again. That's not really a big deal. The thing that would be a big
> deal, maybe, is if we released and only found out afterward that this
> caused some subtle and horrible problem for which we had no
> back-patchable fix, but that seems pretty unlikely.
Agreed.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v7 5/7] Row pattern recognition patch (docs).
@ 2023-09-22 04:53 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Tatsuo Ishii @ 2023-09-22 04:53 UTC (permalink / raw)
---
doc/src/sgml/advanced.sgml | 52 ++++++++++++++++++++++++++++++++++
doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++++++++++++++
doc/src/sgml/ref/select.sgml | 38 +++++++++++++++++++++++--
3 files changed, 142 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 755c9f1485..f39ec8f2d5 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -537,6 +537,58 @@ WHERE pos < 3;
<literal>rank</literal> less than 3.
</para>
+ <para>
+ Row pattern common syntax can be used with row pattern common syntax to
+ perform row pattern recognition in a query. Row pattern common syntax
+ includes two sub clauses. <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>. Moreover if the expression comprises a column
+ reference, it must be the argument of <function>rpr</function>. 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 price column in the previous
+ row if it's called in a context of row pattern recognition. So in the
+ second line means the definition variable "UP" is <literal>TRUE</literal>
+ when price column in the current row is greater than the price column in
+ the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when
+ 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". If a
+ sequence of rows found, rpr returns the column at the starting row.
+ Example of a <literal>SELECT</literal> using the <literal>DEFINE</literal>
+ and <literal>PATTERN</literal> clause is as follows.
+
+<programlisting>
+SELECT company, tdate, price, max(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 (LOWPRICE UP+ DOWN+)
+ DEFINE
+ LOWPRICE AS price <= 100,
+ UP AS price > PREV(price),
+ DOWN AS price < PREV(price)
+);
+</programlisting>
+ </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 24ad87f910..9c99dda4ae 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -21780,6 +21780,7 @@ SELECT count(*) FROM sometable;
returns <literal>NULL</literal> if there is no such row.
</para></entry>
</row>
+
</tbody>
</tgroup>
</table>
@@ -21819,6 +21820,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 0ee0cc7e64..056768b330 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -966,8 +966,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>
@@ -1074,6 +1074,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(Fri_Sep_22_14_16_40_2023_530)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v7-0006-Row-pattern-recognition-patch-tests.patch"
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-09-22 04:53 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-25 01:48 Re: plpython vs _POSIX_C_SOURCE Andres Freund <[email protected]>
2023-01-25 02:32 ` Andres Freund <[email protected]>
2023-01-25 04:28 ` Andres Freund <[email protected]>
2023-01-25 04:37 ` Tom Lane <[email protected]>
2023-01-25 08:52 ` Andres Freund <[email protected]>
2023-01-25 13:31 ` Robert Haas <[email protected]>
2023-01-25 21:26 ` Andres Freund <[email protected]>
2023-09-22 04:53 [PATCH v7 5/7] Row pattern recognition patch (docs). 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