public inbox for [email protected]
help / color / mirror / Atom feedRe: Move defaults toward ICU in 16?
6+ messages / 4 participants
[nested] [flat]
* Re: Move defaults toward ICU in 16?
@ 2023-02-17 17:27 Pavel Stehule <[email protected]>
2023-02-17 20:43 ` Re: Move defaults toward ICU in 16? Jeff Davis <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2023-02-17 17:27 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: Andres Freund <[email protected]>; Tom Lane <[email protected]>; Thomas Munro <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers
pá 17. 2. 2023 v 18:02 odesílatel Jeff Davis <[email protected]> napsal:
> On Fri, 2023-02-17 at 00:06 -0800, Jeff Davis wrote:
> > On Tue, 2023-02-14 at 09:59 -0800, Andres Freund wrote:
> > > I am saying that pg_upgrade should be able to deal with the
> > > difference. The
> > > details of how to implement that, don't matter that much.
> >
> > To clarify, you're saying that pg_upgrade should simply update
> > pg_database to set the new databases' collation fields equal to that
> > of
> > the old cluster?
>
> Thinking about this more, it's not clear to me if this would be in
> scope for pg_upgrade or not. If pg_upgrade is fixing up the new cluster
> rather than checking for compatibility, why doesn't it just take over
> and do the initdb for the new cluster itself? That would be less
> confusing for users, and avoid some weirdness (like, if you drop the
> database "postgres" on the original, why does it reappear after an
> upgrade?).
>
> Someone might want to do something interesting to the new cluster
> before the upgrade, but it's not clear from the docs what would be both
> useful and safe.
>
Today I tested icu for Czech sorting. It is a little bit slower, but not
too much, but it produces partially different results.
select row_number() over (order by nazev collate "cs-x-icu"), nazev from
obce
except select row_number() over (order by nazev collate "cs_CZ"), nazev
from obce;
returns a not empty set. So minimally for Czech collate, an index rebuild
is necessary
Regards
Pavel
>
> Regards,
> Jeff Davis
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move defaults toward ICU in 16?
2023-02-17 17:27 Re: Move defaults toward ICU in 16? Pavel Stehule <[email protected]>
@ 2023-02-17 20:43 ` Jeff Davis <[email protected]>
2023-02-18 04:52 ` Re: Move defaults toward ICU in 16? Pavel Stehule <[email protected]>
2023-02-20 14:55 ` Re: Move defaults toward ICU in 16? Peter Eisentraut <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Jeff Davis @ 2023-02-17 20:43 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: Andres Freund <[email protected]>; Tom Lane <[email protected]>; Thomas Munro <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers
On Fri, 2023-02-17 at 18:27 +0100, Pavel Stehule wrote:
> Today I tested icu for Czech sorting. It is a little bit slower, but
> not too much, but it produces partially different results.
Thank you for trying it.
If it's a significant slowdown, can you please send more information?
ICU version, libc version, and testcase?
> select row_number() over (order by nazev collate "cs-x-icu"), nazev
> from obce
> except select row_number() over (order by nazev collate "cs_CZ"),
> nazev from obce;
>
> returns a not empty set. So minimally for Czech collate, an index
> rebuild is necessary
Yes, that's true of any locale change, provider change, or even
provider version change.
--
Jeff Davis
PostgreSQL Contributor Team - AWS
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move defaults toward ICU in 16?
2023-02-17 17:27 Re: Move defaults toward ICU in 16? Pavel Stehule <[email protected]>
2023-02-17 20:43 ` Re: Move defaults toward ICU in 16? Jeff Davis <[email protected]>
@ 2023-02-18 04:52 ` Pavel Stehule <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2023-02-18 04:52 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: Andres Freund <[email protected]>; Tom Lane <[email protected]>; Thomas Munro <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers
pá 17. 2. 2023 v 21:43 odesílatel Jeff Davis <[email protected]> napsal:
> On Fri, 2023-02-17 at 18:27 +0100, Pavel Stehule wrote:
> > Today I tested icu for Czech sorting. It is a little bit slower, but
> > not too much, but it produces partially different results.
>
> Thank you for trying it.
>
> If it's a significant slowdown, can you please send more information?
> ICU version, libc version, and testcase?
>
no - this slowdown is not significant - although 1% can looks too much -
but it is just two ms
It looks so libicu has little bit more expensive initialization, but the
execution is little bit faster
But when I try to repeat the measurements, the results are very unstable on
my desktop :-/
SELECT * FROM obce ORDER BY nazev LIMIT 10 // is faster with glibc little
bit
SELECT * FROM obce ORDER BY nazev // is faster with libicu
You can download dataset https://pgsql.cz/files/obce.sql
It is table of municipalities in czech republic (real names) - about 6000
rows
I use fedora 37 - so libicu 71.1, glibc 2.36
Regards
Pavel
>
> > select row_number() over (order by nazev collate "cs-x-icu"), nazev
> > from obce
> > except select row_number() over (order by nazev collate "cs_CZ"),
> > nazev from obce;
> >
> > returns a not empty set. So minimally for Czech collate, an index
> > rebuild is necessary
>
> Yes, that's true of any locale change, provider change, or even
> provider version change.
>
>
> --
> Jeff Davis
> PostgreSQL Contributor Team - AWS
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move defaults toward ICU in 16?
2023-02-17 17:27 Re: Move defaults toward ICU in 16? Pavel Stehule <[email protected]>
2023-02-17 20:43 ` Re: Move defaults toward ICU in 16? Jeff Davis <[email protected]>
@ 2023-02-20 14:55 ` Peter Eisentraut <[email protected]>
2023-02-20 19:41 ` Re: Move defaults toward ICU in 16? Jeff Davis <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2023-02-20 14:55 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; Pavel Stehule <[email protected]>; +Cc: Andres Freund <[email protected]>; Tom Lane <[email protected]>; Thomas Munro <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers
On 17.02.23 21:43, Jeff Davis wrote:
>> select row_number() over (order by nazev collate "cs-x-icu"), nazev
>> from obce
>> except select row_number() over (order by nazev collate "cs_CZ"),
>> nazev from obce;
>>
>> returns a not empty set. So minimally for Czech collate, an index
>> rebuild is necessary
> Yes, that's true of any locale change, provider change, or even
> provider version change.
I'm confused. We are not going to try to change existing databases to a
different collation provider during pg_upgrade, are we?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move defaults toward ICU in 16?
2023-02-17 17:27 Re: Move defaults toward ICU in 16? Pavel Stehule <[email protected]>
2023-02-17 20:43 ` Re: Move defaults toward ICU in 16? Jeff Davis <[email protected]>
2023-02-20 14:55 ` Re: Move defaults toward ICU in 16? Peter Eisentraut <[email protected]>
@ 2023-02-20 19:41 ` Jeff Davis <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Davis @ 2023-02-20 19:41 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; Pavel Stehule <[email protected]>; +Cc: Andres Freund <[email protected]>; Tom Lane <[email protected]>; Thomas Munro <[email protected]>; Robert Haas <[email protected]>; pgsql-hackers
On Mon, 2023-02-20 at 15:55 +0100, Peter Eisentraut wrote:
> I'm confused. We are not going to try to change existing databases
> to a
> different collation provider during pg_upgrade, are we?
No, certainly not.
I interpreted Pavel's comments as a comparison of ICU and libc in
general and not specific to this patch. Changing providers obviously
requires an index rebuild to be safe.
--
Jeff Davis
PostgreSQL Contributor Team - AWS
^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v18 6/8] Row pattern recognition patch (docs).
@ 2024-05-11 07:11 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Tatsuo Ishii @ 2024-05-11 07:11 UTC (permalink / raw)
---
doc/src/sgml/advanced.sgml | 82 ++++++++++++++++++++++++++++++++++++
doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++
doc/src/sgml/ref/select.sgml | 38 ++++++++++++++++-
3 files changed, 172 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 755c9f1485..b0b1d1c51e 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -537,6 +537,88 @@ 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. The 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. Thus 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. On the second or
+ subsequent rows all window functions are NULL. Aggregates are NULL or 0
+ (count case) depending on its aggregation definition. For rows that do not
+ match on the PATTERN, all window functions and aggregates are shown AS
+ NULL too, except count showing 0. This is because the rows do not match,
+ thus they 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 | | | 0
+ company1 | 2023-07-03 | 150 | | | 0
+ company1 | 2023-07-04 | 140 | | | 0
+ company1 | 2023-07-05 | 150 | | | 0
+ company1 | 2023-07-06 | 90 | 90 | 130 | 4
+ company1 | 2023-07-07 | 110 | | | 0
+ company1 | 2023-07-08 | 130 | | | 0
+ company1 | 2023-07-09 | 120 | | | 0
+ company1 | 2023-07-10 | 130 | | | 0
+(10 rows)
+</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 17c44bc338..8dbab31300 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23124,6 +23124,7 @@ SELECT count(*) FROM sometable;
returns <literal>NULL</literal> if there is no such row.
</para></entry>
</row>
+
</tbody>
</tgroup>
</table>
@@ -23163,6 +23164,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(Sat_May_11_16_23_07_2024_789)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v18-0007-Row-pattern-recognition-patch-tests.patch"
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-05-11 07:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-17 17:27 Re: Move defaults toward ICU in 16? Pavel Stehule <[email protected]>
2023-02-17 20:43 ` Jeff Davis <[email protected]>
2023-02-18 04:52 ` Pavel Stehule <[email protected]>
2023-02-20 14:55 ` Peter Eisentraut <[email protected]>
2023-02-20 19:41 ` Jeff Davis <[email protected]>
2024-05-11 07:11 [PATCH v18 6/8] 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