public inbox for [email protected]help / color / mirror / Atom feed
Re: memory leak in trigger handling (since PG12) 9+ messages / 4 participants [nested] [flat]
* Re: memory leak in trigger handling (since PG12) @ 2023-05-23 21:26 Tomas Vondra <[email protected]> 2023-05-23 21:39 ` Re: memory leak in trigger handling (since PG12) Tom Lane <[email protected]> 2023-05-24 18:14 ` Re: memory leak in trigger handling (since PG12) Andres Freund <[email protected]> 0 siblings, 2 replies; 9+ messages in thread From: Tomas Vondra @ 2023-05-23 21:26 UTC (permalink / raw) To: Andres Freund <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On 5/23/23 19:14, Andres Freund wrote: > Hi, > > On 2023-05-23 18:23:00 +0200, Tomas Vondra wrote: >> This means that for an UPDATE with triggers, we may end up calling this >> for each row, possibly multiple bitmaps. And those bitmaps are allocated >> in ExecutorState, so won't be freed until the end of the query :-( > > Ugh. > > > I've wondered about some form of instrumentation to detect such issues > before. It's obviously a problem that we can have fairly large leaks, like the > one you just discovered, without detecting it for a couple years. It's kinda > made worse by the memory context infrastructure, because it hides such issues. > > Could it help to have a mode where the executor shutdown hook checks how much > memory is allocated in ExecutorState and warns if its too much? There's IIRC a > few places that allocate large things directly in it, but most of those > probably should be dedicated contexts anyway. Something similar could be > useful for some other long-lived contexts. > Not sure such simple instrumentation would help much, unfortunately :-( We only discovered this because the user reported OOM crashes, which means the executor didn't get to the shutdown hook at all. Yeah, maybe if we had such instrumentation it'd get triggered for milder cases, but I'd bet the amount of noise is going to be significant. For example, there's a nearby thread about hashjoin allocating buffiles etc. in ExecutorState - we ended up moving that to a separate context, but surely there are more such cases (not just for ExecutorState). The really hard thing was determining what causes the memory leak - the simple instrumentation doesn't help with that at all. It tells you there might be a leak, but you don't know where did the allocations came from. What I ended up doing is a simple gdb script that sets breakpoints on all palloc/pfree variants, and prints info (including the backtrace) for each call on ExecutorState. And then a script that aggregate those to identify which backtraces allocated most chunks that were not freed. This was super slow (definitely useless outside development environment) but it made it super obvious where the root cause is. I experimented with instrumenting the code a bit (as alternative to gdb breakpoints) - still slow, but much faster than gdb. But perhaps useful for special (valgrind-like) testing mode ... Would require testing with more data, though. I doubt we'd find much with our regression tests, which have tiny data sets. > ... > >> Attached is a patch, restoring the pre-12 behavior for me. > > Hm. Somehow this doesn't seem quite right. Shouldn't we try to use a shorter > lived memory context instead? Otherwise we'll just end up with the same > problem in a few years. > I agree using a shorter lived memory context would be more elegant, and more in line with how we do things. But it's not clear to me why we'd end up with the same problem in a few years with what the patch does. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: memory leak in trigger handling (since PG12) 2023-05-23 21:26 Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> @ 2023-05-23 21:39 ` Tom Lane <[email protected]> 2023-05-24 08:49 ` Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: Tom Lane @ 2023-05-23 21:39 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> Tomas Vondra <[email protected]> writes: > The really hard thing was determining what causes the memory leak - the > simple instrumentation doesn't help with that at all. It tells you there > might be a leak, but you don't know where did the allocations came from. > What I ended up doing is a simple gdb script that sets breakpoints on > all palloc/pfree variants, and prints info (including the backtrace) for > each call on ExecutorState. And then a script that aggregate those to > identify which backtraces allocated most chunks that were not freed. FWIW, I've had some success localizing palloc memory leaks with valgrind's leak detection mode. The trick is to ask it for a report before the context gets destroyed. Beats writing your own infrastructure ... > Would require testing with more data, though. I doubt we'd find much > with our regression tests, which have tiny data sets. Yeah, it's not clear whether we could make the still-hypothetical check sensitive enough to find leaks using small test cases without getting an unworkable number of false positives. Still, might be worth trying. It might be an acceptable tradeoff to have stricter rules for what can be allocated in ExecutorState in order to make this sort of problem more easily detectable. regards, tom lane ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: memory leak in trigger handling (since PG12) 2023-05-23 21:26 Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 2023-05-23 21:39 ` Re: memory leak in trigger handling (since PG12) Tom Lane <[email protected]> @ 2023-05-24 08:49 ` Tomas Vondra <[email protected]> 2023-05-24 15:57 ` Re: memory leak in trigger handling (since PG12) Tom Lane <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tomas Vondra @ 2023-05-24 08:49 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> On 5/23/23 23:39, Tom Lane wrote: > Tomas Vondra <[email protected]> writes: >> The really hard thing was determining what causes the memory leak - the >> simple instrumentation doesn't help with that at all. It tells you there >> might be a leak, but you don't know where did the allocations came from. > >> What I ended up doing is a simple gdb script that sets breakpoints on >> all palloc/pfree variants, and prints info (including the backtrace) for >> each call on ExecutorState. And then a script that aggregate those to >> identify which backtraces allocated most chunks that were not freed. > > FWIW, I've had some success localizing palloc memory leaks with valgrind's > leak detection mode. The trick is to ask it for a report before the > context gets destroyed. Beats writing your own infrastructure ... > I haven't tried valgrind, so can't compare. Would it be possible to filter which memory contexts to track? Say we know the leak is in ExecutorState, so we don't need to track allocations in other contexts. That was a huge speedup for me, maybe it'd help valgrind too. Also, how did you ask for the report before context gets destroyed? >> Would require testing with more data, though. I doubt we'd find much >> with our regression tests, which have tiny data sets. > > Yeah, it's not clear whether we could make the still-hypothetical check > sensitive enough to find leaks using small test cases without getting an > unworkable number of false positives. Still, might be worth trying. I'm not against experimenting with that. Were you thinking about something that'd be cheap enough to just be enabled always/everywhere, or something we'd enable during testing? This reminded me a strangeloop talk [1] [2] about the Scalene memory profiler from UMass. That's for Python, but they did some smart tricks to reduce the cost of profiling - maybe we could do something similar, possibly by extending the memory contexts a bit. [1] https://youtu.be/vVUnCXKuNOg?t=1405 [2] https://youtu.be/vVUnCXKuNOg?t=1706 > It might be an acceptable tradeoff to have stricter rules for what can > be allocated in ExecutorState in order to make this sort of problem > more easily detectable. > Would these rules be just customary, or defined/enforced in the code somehow? I can't quite imagine how would that work, TBH. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: memory leak in trigger handling (since PG12) 2023-05-23 21:26 Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 2023-05-23 21:39 ` Re: memory leak in trigger handling (since PG12) Tom Lane <[email protected]> 2023-05-24 08:49 ` Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> @ 2023-05-24 15:57 ` Tom Lane <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Tom Lane @ 2023-05-24 15:57 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Andres Freund <[email protected]>; PostgreSQL Hackers <[email protected]> Tomas Vondra <[email protected]> writes: > On 5/23/23 23:39, Tom Lane wrote: >> FWIW, I've had some success localizing palloc memory leaks with valgrind's >> leak detection mode. The trick is to ask it for a report before the >> context gets destroyed. Beats writing your own infrastructure ... > I haven't tried valgrind, so can't compare. > Would it be possible to filter which memory contexts to track? Say we > know the leak is in ExecutorState, so we don't need to track allocations > in other contexts. That was a huge speedup for me, maybe it'd help > valgrind too. I don't think valgrind has a way to do that, but this'd be something you set up specially in any case. > Also, how did you ask for the report before context gets destroyed? There are several valgrind client requests that could be helpful: /* Do a full memory leak check (like --leak-check=full) mid-execution. */ #define VALGRIND_DO_LEAK_CHECK \ VALGRIND_DO_CLIENT_REQUEST_STMT(VG_USERREQ__DO_LEAK_CHECK, \ 0, 0, 0, 0, 0) /* Same as VALGRIND_DO_LEAK_CHECK but only showing the entries for which there was an increase in leaked bytes or leaked nr of blocks since the previous leak search. */ #define VALGRIND_DO_ADDED_LEAK_CHECK \ VALGRIND_DO_CLIENT_REQUEST_STMT(VG_USERREQ__DO_LEAK_CHECK, \ 0, 1, 0, 0, 0) /* Return number of leaked, dubious, reachable and suppressed bytes found by all previous leak checks. They must be lvalues. */ #define VALGRIND_COUNT_LEAK_BLOCKS(leaked, dubious, reachable, suppressed) \ Putting VALGRIND_DO_ADDED_LEAK_CHECK someplace in the executor loop would help narrow things down pretty quickly, assuming you had a self-contained example demonstrating the leak. I don't recall exactly how I used these but it was something along that line. >> Yeah, it's not clear whether we could make the still-hypothetical check >> sensitive enough to find leaks using small test cases without getting an >> unworkable number of false positives. Still, might be worth trying. > I'm not against experimenting with that. Were you thinking about > something that'd be cheap enough to just be enabled always/everywhere, > or something we'd enable during testing? We seem to have already paid the overhead of counting all palloc allocations, so I don't think it'd be too expensive to occasionally check the ExecutorState's mem_allocated and see if it's growing (especially if we only do so in assert-enabled builds). The trick is to define the rules for what's worth reporting. >> It might be an acceptable tradeoff to have stricter rules for what can >> be allocated in ExecutorState in order to make this sort of problem >> more easily detectable. > Would these rules be just customary, or defined/enforced in the code > somehow? I can't quite imagine how would that work, TBH. If the check bleated "WARNING: possible executor memory leak" during regression testing, people would soon become conditioned to doing whatever they have to do to avoid it ;-) regards, tom lane ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: memory leak in trigger handling (since PG12) 2023-05-23 21:26 Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> @ 2023-05-24 18:14 ` Andres Freund <[email protected]> 2023-05-24 19:56 ` Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: Andres Freund @ 2023-05-24 18:14 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> Hi, On 2023-05-23 23:26:42 +0200, Tomas Vondra wrote: > On 5/23/23 19:14, Andres Freund wrote: > > Hi, > > > > On 2023-05-23 18:23:00 +0200, Tomas Vondra wrote: > >> This means that for an UPDATE with triggers, we may end up calling this > >> for each row, possibly multiple bitmaps. And those bitmaps are allocated > >> in ExecutorState, so won't be freed until the end of the query :-( > > > > Ugh. > > > > > > I've wondered about some form of instrumentation to detect such issues > > before. It's obviously a problem that we can have fairly large leaks, like the > > one you just discovered, without detecting it for a couple years. It's kinda > > made worse by the memory context infrastructure, because it hides such issues. > > > > Could it help to have a mode where the executor shutdown hook checks how much > > memory is allocated in ExecutorState and warns if its too much? There's IIRC a > > few places that allocate large things directly in it, but most of those > > probably should be dedicated contexts anyway. Something similar could be > > useful for some other long-lived contexts. > > > > Not sure such simple instrumentation would help much, unfortunately :-( > > We only discovered this because the user reported OOM crashes, which > means the executor didn't get to the shutdown hook at all. Yeah, maybe > if we had such instrumentation it'd get triggered for milder cases, but > I'd bet the amount of noise is going to be significant. > > For example, there's a nearby thread about hashjoin allocating buffiles > etc. in ExecutorState - we ended up moving that to a separate context, > but surely there are more such cases (not just for ExecutorState). Yes, that's why I said that we would have to more of those into dedicated contexts - which is a good idea independent of this issue. > The really hard thing was determining what causes the memory leak - the > simple instrumentation doesn't help with that at all. It tells you there > might be a leak, but you don't know where did the allocations came from. > > What I ended up doing is a simple gdb script that sets breakpoints on > all palloc/pfree variants, and prints info (including the backtrace) for > each call on ExecutorState. And then a script that aggregate those to > identify which backtraces allocated most chunks that were not freed. FWIW, for things like this I found "heaptrack" to be extremely helpful. E.g. for a reproducer of the problem here, it gave me the attach "flame graph" of the peak memory usage - after attaching to a running backend and running an UPDATE triggering the leak.. Because the view I screenshotted shows the stacks contributing to peak memory usage, it works nicely to find "temporary leaks", even if memory is actually freed after all etc. > > Hm. Somehow this doesn't seem quite right. Shouldn't we try to use a shorter > > lived memory context instead? Otherwise we'll just end up with the same > > problem in a few years. > > > > I agree using a shorter lived memory context would be more elegant, and > more in line with how we do things. But it's not clear to me why we'd > end up with the same problem in a few years with what the patch does. Because it sets up the pattern of manual memory management and continues to run the relevant code within a query-lifetime context. Greetings, Andres Freund Attachments: [image/png] 2023-05-24T11:05:24,383491003-07:00.png (88.6K, ../../[email protected]/2-2023-05-24T11:05:24%2C383491003-07:00.png) download | view image ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: memory leak in trigger handling (since PG12) 2023-05-23 21:26 Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 2023-05-24 18:14 ` Re: memory leak in trigger handling (since PG12) Andres Freund <[email protected]> @ 2023-05-24 19:56 ` Tomas Vondra <[email protected]> 2023-05-24 20:22 ` Re: memory leak in trigger handling (since PG12) Andres Freund <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tomas Vondra @ 2023-05-24 19:56 UTC (permalink / raw) To: Andres Freund <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On 5/24/23 20:14, Andres Freund wrote: > Hi, > > On 2023-05-23 23:26:42 +0200, Tomas Vondra wrote: >> On 5/23/23 19:14, Andres Freund wrote: >>> Hi, >>> >>> On 2023-05-23 18:23:00 +0200, Tomas Vondra wrote: >>>> This means that for an UPDATE with triggers, we may end up calling this >>>> for each row, possibly multiple bitmaps. And those bitmaps are allocated >>>> in ExecutorState, so won't be freed until the end of the query :-( >>> >>> Ugh. >>> >>> >>> I've wondered about some form of instrumentation to detect such issues >>> before. It's obviously a problem that we can have fairly large leaks, like the >>> one you just discovered, without detecting it for a couple years. It's kinda >>> made worse by the memory context infrastructure, because it hides such issues. >>> >>> Could it help to have a mode where the executor shutdown hook checks how much >>> memory is allocated in ExecutorState and warns if its too much? There's IIRC a >>> few places that allocate large things directly in it, but most of those >>> probably should be dedicated contexts anyway. Something similar could be >>> useful for some other long-lived contexts. >>> >> >> Not sure such simple instrumentation would help much, unfortunately :-( >> >> We only discovered this because the user reported OOM crashes, which >> means the executor didn't get to the shutdown hook at all. Yeah, maybe >> if we had such instrumentation it'd get triggered for milder cases, but >> I'd bet the amount of noise is going to be significant. >> >> For example, there's a nearby thread about hashjoin allocating buffiles >> etc. in ExecutorState - we ended up moving that to a separate context, >> but surely there are more such cases (not just for ExecutorState). > > Yes, that's why I said that we would have to more of those into dedicated > contexts - which is a good idea independent of this issue. > Yeah, I think that's a good idea in principle. > >> The really hard thing was determining what causes the memory leak - the >> simple instrumentation doesn't help with that at all. It tells you there >> might be a leak, but you don't know where did the allocations came from. >> >> What I ended up doing is a simple gdb script that sets breakpoints on >> all palloc/pfree variants, and prints info (including the backtrace) for >> each call on ExecutorState. And then a script that aggregate those to >> identify which backtraces allocated most chunks that were not freed. > > FWIW, for things like this I found "heaptrack" to be extremely helpful. > > E.g. for a reproducer of the problem here, it gave me the attach "flame graph" > of the peak memory usage - after attaching to a running backend and running an > UPDATE triggering the leak.. > > Because the view I screenshotted shows the stacks contributing to peak memory > usage, it works nicely to find "temporary leaks", even if memory is actually > freed after all etc. > That's a nice visualization, but isn't that useful only once you determine there's a memory leak? Which I think is the hard problem. > > >>> Hm. Somehow this doesn't seem quite right. Shouldn't we try to use a shorter >>> lived memory context instead? Otherwise we'll just end up with the same >>> problem in a few years. >>> >> >> I agree using a shorter lived memory context would be more elegant, and >> more in line with how we do things. But it's not clear to me why we'd >> end up with the same problem in a few years with what the patch does. > > Because it sets up the pattern of manual memory management and continues to > run the relevant code within a query-lifetime context. > Oh, you mean someone might add new allocations to this code (or into one of the functions executed from it), and that'd leak again? Yeah, true. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: memory leak in trigger handling (since PG12) 2023-05-23 21:26 Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 2023-05-24 18:14 ` Re: memory leak in trigger handling (since PG12) Andres Freund <[email protected]> 2023-05-24 19:56 ` Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> @ 2023-05-24 20:22 ` Andres Freund <[email protected]> 2023-05-25 14:27 ` Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Andres Freund @ 2023-05-24 20:22 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> Hi, On 2023-05-24 21:56:22 +0200, Tomas Vondra wrote: > >> The really hard thing was determining what causes the memory leak - the > >> simple instrumentation doesn't help with that at all. It tells you there > >> might be a leak, but you don't know where did the allocations came from. > >> > >> What I ended up doing is a simple gdb script that sets breakpoints on > >> all palloc/pfree variants, and prints info (including the backtrace) for > >> each call on ExecutorState. And then a script that aggregate those to > >> identify which backtraces allocated most chunks that were not freed. > > > > FWIW, for things like this I found "heaptrack" to be extremely helpful. > > > > E.g. for a reproducer of the problem here, it gave me the attach "flame graph" > > of the peak memory usage - after attaching to a running backend and running an > > UPDATE triggering the leak.. > > > > Because the view I screenshotted shows the stacks contributing to peak memory > > usage, it works nicely to find "temporary leaks", even if memory is actually > > freed after all etc. > > > > That's a nice visualization, but isn't that useful only once you > determine there's a memory leak? Which I think is the hard problem. So is your gdb approach, unless I am misunderstanding? The view I screenshotted shows the "peak" allocated memory, if you have a potential leak, you can see where most of the allocated memory was allocated. Which at least provides you with a good idea of where to look for a problem in more detail. > >>> Hm. Somehow this doesn't seem quite right. Shouldn't we try to use a shorter > >>> lived memory context instead? Otherwise we'll just end up with the same > >>> problem in a few years. > >>> > >> > >> I agree using a shorter lived memory context would be more elegant, and > >> more in line with how we do things. But it's not clear to me why we'd > >> end up with the same problem in a few years with what the patch does. > > > > Because it sets up the pattern of manual memory management and continues to > > run the relevant code within a query-lifetime context. > > > > Oh, you mean someone might add new allocations to this code (or into one > of the functions executed from it), and that'd leak again? Yeah, true. Yes. It's certainly not obvious this far down that we are called in a semi-long-lived memory context. Greetings, Andres Freund ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: memory leak in trigger handling (since PG12) 2023-05-23 21:26 Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 2023-05-24 18:14 ` Re: memory leak in trigger handling (since PG12) Andres Freund <[email protected]> 2023-05-24 19:56 ` Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 2023-05-24 20:22 ` Re: memory leak in trigger handling (since PG12) Andres Freund <[email protected]> @ 2023-05-25 14:27 ` Tomas Vondra <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Tomas Vondra @ 2023-05-25 14:27 UTC (permalink / raw) To: Andres Freund <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On 5/24/23 22:22, Andres Freund wrote: > Hi, > > On 2023-05-24 21:56:22 +0200, Tomas Vondra wrote: >>>> The really hard thing was determining what causes the memory leak - the >>>> simple instrumentation doesn't help with that at all. It tells you there >>>> might be a leak, but you don't know where did the allocations came from. >>>> >>>> What I ended up doing is a simple gdb script that sets breakpoints on >>>> all palloc/pfree variants, and prints info (including the backtrace) for >>>> each call on ExecutorState. And then a script that aggregate those to >>>> identify which backtraces allocated most chunks that were not freed. >>> >>> FWIW, for things like this I found "heaptrack" to be extremely helpful. >>> >>> E.g. for a reproducer of the problem here, it gave me the attach "flame graph" >>> of the peak memory usage - after attaching to a running backend and running an >>> UPDATE triggering the leak.. >>> >>> Because the view I screenshotted shows the stacks contributing to peak memory >>> usage, it works nicely to find "temporary leaks", even if memory is actually >>> freed after all etc. >>> >> >> That's a nice visualization, but isn't that useful only once you >> determine there's a memory leak? Which I think is the hard problem. > > So is your gdb approach, unless I am misunderstanding? The view I > screenshotted shows the "peak" allocated memory, if you have a potential leak, > you can see where most of the allocated memory was allocated. Which at least > provides you with a good idea of where to look for a problem in more detail. > Right, it wasn't my ambition to detect memory leaks but to source of the leak if there's one. I got a bit distracted by the discussion detecting leaks etc. > >>>>> Hm. Somehow this doesn't seem quite right. Shouldn't we try to use a shorter >>>>> lived memory context instead? Otherwise we'll just end up with the same >>>>> problem in a few years. >>>>> >>>> >>>> I agree using a shorter lived memory context would be more elegant, and >>>> more in line with how we do things. But it's not clear to me why we'd >>>> end up with the same problem in a few years with what the patch does. >>> >>> Because it sets up the pattern of manual memory management and continues to >>> run the relevant code within a query-lifetime context. >>> >> >> Oh, you mean someone might add new allocations to this code (or into one >> of the functions executed from it), and that'd leak again? Yeah, true. > > Yes. It's certainly not obvious this far down that we are called in a > semi-long-lived memory context. > That's true, but I don't see how adding a ExecGetAllUpdatedCols() variant that allocates stuff in a short-lived context improves this. That'll only cover memory allocated in ExecGetAllUpdatedCols() and nothing else. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v11 5/7] Row pattern recognition patch (docs). @ 2023-11-08 06:57 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Tatsuo Ishii @ 2023-11-08 06:57 UTC (permalink / raw) --- doc/src/sgml/advanced.sgml | 80 ++++++++++++++++++++++++++++++++++++ doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++ doc/src/sgml/ref/select.sgml | 38 ++++++++++++++++- 3 files changed, 170 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 755c9f1485..cf18dd887e 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -537,6 +537,86 @@ WHERE pos < 3; <literal>rank</literal> less than 3. </para> + <para> + Row pattern common syntax can be used to perform row pattern recognition + in a query. Row pattern common syntax includes two sub + clauses: <literal>DEFINE</literal> + and <literal>PATTERN</literal>. <literal>DEFINE</literal> defines + definition variables along with an expression. The expression must be a + logical expression, which means it must + return <literal>TRUE</literal>, <literal>FALSE</literal> + or <literal>NULL</literal>. The expression may comprise column references + and functions. Window functions, aggregate functions and subqueries are + not allowed. An example of <literal>DEFINE</literal> is as follows. + +<programlisting> +DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +</programlisting> + + Note that <function>PREV</function> returns the price column in the + previous row if it's called in a context of row pattern recognition. So in + the second line the definition variable "UP" is <literal>TRUE</literal> + when the price column in the current row is greater than the price column + in the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when + the price column in the current row is lower than the price column in the + previous row. + </para> + <para> + Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be + used. <literal>PATTERN</literal> defines a sequence of rows that satisfies + certain conditions. For example following <literal>PATTERN</literal> + defines that a row starts with the condition "LOWPRICE", then one or more + rows satisfy "UP" and finally one or more rows satisfy "DOWN". Note that + "+" means one or more matches. Also you can use "*", which means zero or + more matches. If a sequence of rows which satisfies the PATTERN is found, + in the starting row of the sequence of rows all window functions and + aggregates are shown in the target list. Note that aggregations only look + into the matched rows, rather than whole frame. In the second or + subsequent rows all window functions and aggregates are NULL. For rows + that do not match the PATTERN, all window functions and aggregates are + shown AS NULL too, except count which shows 0. This is because the + unmatched rows are in an empty frame. Example of + a <literal>SELECT</literal> using the <literal>DEFINE</literal> + and <literal>PATTERN</literal> clause is as follows. + +<programlisting> +SELECT company, tdate, price, + first_value(price) OVER w, + max(price) OVER w, + count(price) OVER w +FROM stock, + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +</programlisting> +<screen> + company | tdate | price | first_value | max | count +----------+------------+-------+-------------+-----+------- + company1 | 2023-07-01 | 100 | 100 | 200 | 4 + company1 | 2023-07-02 | 200 | | | + company1 | 2023-07-03 | 150 | | | + company1 | 2023-07-04 | 140 | | | + company1 | 2023-07-05 | 150 | | | 0 + company1 | 2023-07-06 | 90 | 90 | 130 | 4 + company1 | 2023-07-07 | 110 | | | + company1 | 2023-07-08 | 130 | | | + company1 | 2023-07-09 | 120 | | | + company1 | 2023-07-10 | 130 | | | 0 +</screen> + </para> + <para> When a query involves multiple window functions, it is possible to write out each one with a separate <literal>OVER</literal> clause, but this is diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index d963f0a0a0..c3a8167c8e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -21933,6 +21933,7 @@ SELECT count(*) FROM sometable; returns <literal>NULL</literal> if there is no such row. </para></entry> </row> + </tbody> </tgroup> </table> @@ -21972,6 +21973,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 42d78913cf..522ad9dd70 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(Wed_Nov__8_16_37_05_2023_872)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v11-0006-Row-pattern-recognition-patch-tests.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-11-08 06:57 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-05-23 21:26 Re: memory leak in trigger handling (since PG12) Tomas Vondra <[email protected]> 2023-05-23 21:39 ` Tom Lane <[email protected]> 2023-05-24 08:49 ` Tomas Vondra <[email protected]> 2023-05-24 15:57 ` Tom Lane <[email protected]> 2023-05-24 18:14 ` Andres Freund <[email protected]> 2023-05-24 19:56 ` Tomas Vondra <[email protected]> 2023-05-24 20:22 ` Andres Freund <[email protected]> 2023-05-25 14:27 ` Tomas Vondra <[email protected]> 2023-11-08 06:57 [PATCH v11 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