Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sat, Jan 18, 2014 at 10:17 AM, Florian Pflug wrote: > On Jan17, 2014, at 20:34 , David Rowley wrote: > > On Fri, Jan 17, 2014 at 3:05 PM, Florian Pflug wrote: > > > >> I've now shuffled things around so that we can use inverse transition > functions > >> even if only some aggregates provide

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan17, 2014, at 20:34 , David Rowley wrote: > On Fri, Jan 17, 2014 at 3:05 PM, Florian Pflug wrote: > >> I've now shuffled things around so that we can use inverse transition >> functions >> even if only some aggregates provide them, and to allow inverse transition >> functions to force a re

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, Jan 17, 2014 at 3:05 PM, Florian Pflug wrote: > > I've now shuffled things around so that we can use inverse transition > functions > even if only some aggregates provide them, and to allow inverse transition > functions to force a restart by returning NULL. The rules regarding NULL > han

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan17, 2014, at 15:14 , David Rowley wrote: > If you make any more changes would it be possible for you to base them on the > attached patch instead of the last one you sent? Sure! The only reason I didn't rebase my last patch onto yours was that having the numeric stuff in there meant poten

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, Jan 17, 2014 at 3:05 PM, Florian Pflug wrote: > I had some more fun with this, the result is v2.5 of the patch (attached). > Changes are explained below. > > Looks like you've been busy on this! Thank you for implementing all the changes you talked about. I've now started working the 2.5

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

I had some more fun with this, the result is v2.5 of the patch (attached). Changes are explained below. On Jan16, 2014, at 19:10 , Florian Pflug wrote: > On Jan16, 2014, at 09:07 , David Rowley wrote: >> On Wed, Jan 15, 2014 at 5:39 AM, Florian Pflug wrote: >>> The notnullcount machinery seems

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Andres Freund writes: > On 2014-01-16 14:23:47 -0500, Tom Lane wrote: >> Dunno, I think that a transition state containing both an int64 and >> a (presumably separately palloc'd) numeric will be a real PITA. > Yea, not sure myself. I just dislike the idea of having a good part of a > 128bit math

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 2014-01-16 14:23:47 -0500, Tom Lane wrote: > > Maybe it's instead sufficient to just have flag indicating that you're > > working with a state that hasn't overflowed so far and just plain int8 > > math as long as that's the case, and entirely fall back to the current > > path once overflowed. Th

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Andres Freund writes: > You'll have to handle adding negative values and underflow as > well. Right. > Maybe it's instead sufficient to just have flag indicating that you're > working with a state that hasn't overflowed so far and just plain int8 > math as long as that's the case, and entirely f

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 2014-01-16 21:07:33 +0200, Heikki Linnakangas wrote: > On 01/16/2014 08:59 PM, Tom Lane wrote: > >Heikki Linnakangas writes: > >>I propose that we reimplement sum(bigint) in a more efficient way: For > >>the internal state, let's use an int8 and a numeric overflow field. The > >>transition func

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 01/16/2014 08:59 PM, Tom Lane wrote: Heikki Linnakangas writes: I propose that we reimplement sum(bigint) in a more efficient way: For the internal state, let's use an int8 and a numeric overflow field. The transition function adds to the int8 variable, and checks for overflow. On overflow,

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Heikki Linnakangas writes: > I propose that we reimplement sum(bigint) in a more efficient way: For > the internal state, let's use an int8 and a numeric overflow field. The > transition function adds to the int8 variable, and checks for overflow. > On overflow, increment the numeric field by o

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 01/16/2014 01:02 PM, David Rowley wrote: sum(bigint) became a bit weird as it uses numeric types internally, so I had to keep the do_numeric_discard() function to support it. It's pretty weird that we have implemented sum(bigint) that way. I understand that the result is a numeric so that i

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan16, 2014, at 09:07 , David Rowley wrote: > On Wed, Jan 15, 2014 at 5:39 AM, Florian Pflug wrote: >> The notnullcount machinery seems to apply to both STRICT and non-STRICT >> transfer function pairs. Shouldn't that be constrained to STRICT transfer >> function pairs? For non-STRICT pairs,

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Thu, Jan 16, 2014 at 9:45 AM, Florian Pflug wrote: > I'm currently thinking the best way forward is to get a basic patch > without any NUMERIC stuff committed, and to revisit this after that's done. > > Attached is a version to that effect. The number of inverse transition functions is down to

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Wed, Jan 15, 2014 at 5:39 AM, Florian Pflug wrote: The notnullcount machinery seems to apply to both STRICT and non-STRICT > transfer function pairs. Shouldn't that be constrained to STRICT transfer > function pairs? For non-STRICT pairs, it's up to the transfer functions to > deal with NULL i

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Jan 14, 2014 at 2:29 PM, Tom Lane wrote: > > One reason I'm thinking this is that whatever we do to ameliorate > the semantic issues is going to slow down the forward transition > function --- to no benefit unless the aggregate is being used as a > window function in a moving window. So

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Thu, Jan 16, 2014 at 9:45 AM, Florian Pflug wrote: > BTW, AVG() and STDDEV() have the same issue. The problem is just partially > masked by the division by N (or N-1) at the end, because we always emit as > least 16 fractional digits when dividing. So you have to have an input > value with a l

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Thu, Jan 16, 2014 at 3:47 PM, Florian Pflug wrote: > BTW, as it stands, the patch currently uses the inverse transition > function only when *all* the aggregates belonging to one window clause > provide one. After working with the code a bit, I think that a bit of > reshuffling would allow tha

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan16, 2014, at 02:32 , Florian Pflug wrote: > On Jan14, 2014, at 17:39 , Florian Pflug wrote: >> On Jan14, 2014, at 11:06 , David Rowley wrote: >>> Here's a patch which removes sum(numeric) and changes the documents a >>> little to remove a reference to using sum(numeric) to workaround the

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan14, 2014, at 17:39 , Florian Pflug wrote: > On Jan14, 2014, at 11:06 , David Rowley wrote: >> Here's a patch which removes sum(numeric) and changes the documents a little >> to remove a reference to using sum(numeric) to workaround the fact that >> there's no inverse transitions for sum(f

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Florian Pflug writes: > I'm currently thinking the best way forward is to get a basic patch > without any NUMERIC stuff committed, and to revisit this after that's done. +1. I liked Robert's suggestion that the "fast" aggregates be implemented as a contrib module rather than directly in core, to

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan15, 2014, at 19:56 , Robert Haas wrote: > It strikes me that for numeric what you really need is to just tell > the sum operation, whether through a parameter or otherwise, how many > decimal places to show in the output. Obviously that's not a > practical change for sum() itself, but if we

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Jan 14, 2014 at 4:16 AM, David Rowley wrote: > On Tue, Jan 14, 2014 at 9:09 PM, David Rowley wrote: >> >> I think unless anyone has some objections I'm going to remove the inverse >> transition for SUM(numeric) and modify the documents to tell the user how to >> build their own FAST_SUM(n

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 1/13/14, 7:41 PM, Gavin Flower wrote: On 14/01/14 14:29, Tom Lane wrote: [...] (2) the float and numeric variants should be implemented under nondefault names (I'm thinking FAST_SUM(), but bikeshed away). People who need extra speed and don't mind the slightly different results can alter th

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan14, 2014, at 11:06 , David Rowley wrote: > Here's a patch which removes sum(numeric) and changes the documents a little > to remove a reference to using sum(numeric) to workaround the fact that > there's no inverse transitions for sum(float). I also made a small change in > the aggregates

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Jan 14, 2014 at 9:09 PM, David Rowley wrote: > I think unless anyone has some objections I'm going to remove the inverse > transition for SUM(numeric) and modify the documents to tell the user how > to build their own FAST_SUM(numeric) using the built in functions to do it. > I'm starting

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Jan 14, 2014 at 9:09 PM, David Rowley wrote: > I think unless anyone has some objections I'm going to remove the inverse > transition for SUM(numeric) and modify the documents to tell the user how > to build their own FAST_SUM(numeric) using the built in functions to do it. > I'm starting

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Jan 14, 2014 at 2:29 PM, Tom Lane wrote: > Florian Pflug writes: > > I think it'd be worthwile to get this into 9.4, if that's still an > option, > > even if we only support COUNT. > > My thought is > > (1) we can certainly implement inverse transitions for COUNT() and the > integer vari

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Jan 14, 2014 at 2:00 PM, Florian Pflug wrote: > On Jan10, 2014, at 22:27 , David Rowley wrote: > > As the patch stands at the moment, I currently have a regression test > > which currently fails due to these extra zeros after the decimal point: > > > > -- This test currently fails due ex

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 14/01/14 14:29, Tom Lane wrote: [...] (2) the float and numeric variants should be implemented under nondefault names (I'm thinking FAST_SUM(), but bikeshed away). People who need extra speed and don't mind the slightly different results can alter their queries to use these variants. One rea

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Florian Pflug writes: > I think it'd be worthwile to get this into 9.4, if that's still an option, > even if we only support COUNT. My thought is (1) we can certainly implement inverse transitions for COUNT() and the integer variants of SUM(), and that alone would be a killer feature for some pe

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan10, 2014, at 22:27 , David Rowley wrote: > As the patch stands at the moment, I currently have a regression test > which currently fails due to these extra zeros after the decimal point: > > -- This test currently fails due extra trailing 0 digits. > SELECT SUM(n::numeric) OVER (ORDER BY i

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, Jan 10, 2014 at 2:07 PM, Tom Lane wrote: > Florian Pflug writes: >> On Jan10, 2014, at 19:08 , Tom Lane wrote: >>> Although, having said that ... maybe "build your own aggregate" would >>> be a reasonable suggestion for people who need this? I grant that >>> it's going to be a minority

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan11, 2014, at 01:24 , Jim Nasby wrote: > On 1/10/14, 1:07 PM, Tom Lane wrote: >> Florian Pflug writes: >>> >On Jan10, 2014, at 19:08 , Tom Lane wrote: >>Although, having said that ... maybe "build your own aggregate" would >>be a reasonable suggestion for people who need this? I

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan10, 2014, at 17:46 , Tom Lane wrote: > Florian Pflug writes: >> On Jan10, 2014, at 15:49 , Tom Lane wrote: >>> Also, it might be reasonable for both the regular and the inverse >>> transition functions to be strict. If a null entering the window >>> does not matter, then a null exiting

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 1/10/14, 1:07 PM, Tom Lane wrote: Florian Pflug writes: >On Jan10, 2014, at 19:08 , Tom Lane wrote: >>Although, having said that ... maybe "build your own aggregate" would >>be a reasonable suggestion for people who need this? I grant that >>it's going to be a minority requirement, maybe

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sat, Jan 11, 2014 at 7:08 AM, Tom Lane wrote: > Although, having said that ... maybe "build your own aggregate" would > be a reasonable suggestion for people who need this? I grant that > it's going to be a minority requirement, maybe even a small minority > requirement. People who have the

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

FYI, I'm using the verb "rewind" to talk about using the negative transition aggregation function to get a prior value.  I don't know if this is the right verb. Conceptually, when aggregating over floating point numbers, there is some infinitely precise theoretical value, and the computation is

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Florian Pflug writes: > On Jan10, 2014, at 19:08 , Tom Lane wrote: >> Although, having said that ... maybe "build your own aggregate" would >> be a reasonable suggestion for people who need this? I grant that >> it's going to be a minority requirement, maybe even a small minority >> requirement.

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan10, 2014, at 19:08 , Tom Lane wrote: > I wrote: >> Kevin Grittner writes: >>> The real issue here is that if you are using an approximate data type >>> and expecting exact answers, you will have problems. > >> That's a canard. People who know what they're doing (admittedly a >> minority)

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

I wrote: > Kevin Grittner writes: >> The real issue here is that if you are using an approximate data type >> and expecting exact answers, you will have problems. > That's a canard. People who know what they're doing (admittedly a > minority) do not expect exact answers, but they do expect to be

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan10, 2014, at 18:14 , Kevin Grittner wrote: > Given that this is already the case with aggregates on floating > point approximate numbers, why should we rule out an optimization > which only makes rounding errors more likely to be visible? The > real issue here is that if you are using an ap

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Kevin Grittner writes: > Aggregates on approximate (floating-point) numbers are not nearly > as consistent as many people probably assume.  Picture for a minute > a table where a column contains positive floating point numbers > happen to be located in the heap in increasing order, perhaps as > th

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Florian Pflug wrote: > Tom Lane wrote: >> Florian Pflug writes: >>> For float 4 and float8, wasn't the consensus that the potential >>> lossy-ness of addition makes this impossible anyway, even >>> without the NaN issue? But... >> >> Well, that was my opinion, I'm not sure if it was consensus ;-

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Florian Pflug writes: > On Jan10, 2014, at 15:49 , Tom Lane wrote: >> Also, it might be reasonable for both the regular and the inverse >> transition functions to be strict. If a null entering the window >> does not matter, then a null exiting the window doesn't either, no? > That's not true, I

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan10, 2014, at 15:49 , Tom Lane wrote: > Florian Pflug writes: >> Looking at the code it seems that for quite a few existing aggregates, >> the state remains NULL until the first non-NULL input is processed. But >> that doesn't hurt much - those aggregates can remain as they are until >> some

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Florian Pflug writes: > On Jan10, 2014, at 09:34 , David Rowley wrote: >> I just don't quite know yet the base way for the inverse transition function >> to communicate this to the caller yet. If you have any ideas on the best way >> to do this then I'd really like to hear them. > Could they m

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan10, 2014, at 09:34 , David Rowley wrote: > I just don't quite know yet the base way for the inverse transition function > to communicate this to the caller yet. If you have any ideas on the best way > to do this then I'd really like to hear them. Could they maybe just return NULL as the n

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 10 January 2014 08:12, David Rowley wrote: > On Fri, Jan 10, 2014 at 4:09 AM, Dean Rasheed > wrote: >> >> Hi, >> >> Reading over this, I realised that there is a problem with NaN >> handling --- once the state becomes NaN, it can never recover. So the >> results using the inverse transition fu

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

> > > bool_or() > FALSE can be removed, removing TRUE requires a rescan. Could be made > fully invertible by counting the number of TRUE and FALSE values, > similar to my suggestion for how to handle NaN for sum(numeric). > Same works for bool_and(). > > bit_or() > Like boo_or(), 0 can be

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, Jan 10, 2014 at 5:15 AM, Tom Lane wrote: > Dean Rasheed writes: > > Reading over this, I realised that there is a problem with NaN > > handling --- once the state becomes NaN, it can never recover. So the > > results using the inverse transition function don't match HEAD in > > cases lik

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, Jan 10, 2014 at 4:09 AM, Dean Rasheed wrote: > Hi, > > Reading over this, I realised that there is a problem with NaN > handling --- once the state becomes NaN, it can never recover. So the > results using the inverse transition function don't match HEAD in > cases like this: > > create ta

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan9, 2014, at 18:09 , Tom Lane wrote: > Florian Pflug writes: >> For float 4 and float8, wasn't the consensus that the potential >> lossy-ness of addition makes this impossible anyway, even without the >> NaN issue? But... > > Well, that was my opinion, I'm not sure if it was consensus ;-).

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Florian Pflug writes: > For float 4 and float8, wasn't the consensus that the potential > lossy-ness of addition makes this impossible anyway, even without the > NaN issue? But... Well, that was my opinion, I'm not sure if it was consensus ;-). But NaN is an orthogonal problem I think. I'm not s

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan9, 2014, at 17:15 , Tom Lane wrote: > Dean Rasheed writes: >> Reading over this, I realised that there is a problem with NaN >> handling --- once the state becomes NaN, it can never recover. So the >> results using the inverse transition function don't match HEAD in >> cases like this: > >

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Dean Rasheed writes: > Reading over this, I realised that there is a problem with NaN > handling --- once the state becomes NaN, it can never recover. So the > results using the inverse transition function don't match HEAD in > cases like this: Ouch! That takes out numeric, float4, and float8 in

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 15 December 2013 01:57, Tom Lane wrote: > Josh Berkus writes: >> I think even the FLOAT case deserves some consideration. What's the >> worst-case drift? > > Complete loss of all significant digits. > > The case I was considering earlier of single-row windows could be made > safe (I think) if

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, Jan 3, 2014 at 12:23 PM, Erik Rijkers wrote: > On Fri, January 3, 2014 00:09, Erik Rijkers wrote: > > > > connection to server was lost > > > > So, to repeat, this runs fine on a server compiled for speed. > > > > I forgot to append the log messages: > > 2014-01-03 00:19:17.073 CET 14054

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, January 3, 2014 00:09, Erik Rijkers wrote: > connection to server was lost > > So, to repeat, this runs fine on a server compiled for speed. > I forgot to append the log messages: 2014-01-03 00:19:17.073 CET 14054 LOG: database system is ready to accept connections TRAP: FailedAsserti

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Thu, January 2, 2014 17:33, Erik Rijkers wrote: > On Thu, January 2, 2014 13:36, Erik Rijkers wrote: >> On Thu, January 2, 2014 13:05, David Rowley wrote: >>> here's a slightly updated patch >>> [inverse_transition_functions_v1.8.patch.gz ] >> >> patch applies, and compiles (although with new wa

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, Jan 3, 2014 at 5:33 AM, Erik Rijkers wrote: > *** /var/data1/pg_stuff/pg_sandbox/pgsql.inverse/src/ > test/regress/expected/window.out 2014-01-02 16:19:48.0 +0100 > --- > /var/data1/pg_stuff/pg_sandbox/pgsql.inverse/src/test/regress/results/window.out >2014-01-02 16:21:43.00

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

"Erik Rijkers" writes: > The TRACE_POSTGRESQL_SORT_DONE warnings were not from your patch; sorry about > that. They occur on HEAD too (with a debug > compile). > tuplesort.c:935:44: warning: comparison between pointer and integer [enabled > by default] > TRACE_POSTGRESQL_SORT_DONE(state->tape

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Thu, January 2, 2014 13:36, Erik Rijkers wrote: > On Thu, January 2, 2014 13:05, David Rowley wrote: >> here's a slightly updated patch >> [inverse_transition_functions_v1.8.patch.gz ] > > patch applies, and compiles (although with new warnings). > But make check complains loudly: see attached.

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

2013/12/15 David Rowley : > I've been working on speeding up aggregate functions when used in the > context of a window's with non fixed frame heads. > 1. Fully implement negative transition functions for SUM and AVG. I would like to mention that this functionality is also extremely useful to ha

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Fri, Dec 27, 2013 at 1:36 AM, David Rowley wrote: > From what I can tell adding an inverse transition function to support AVG > for numeric does not affect the number of trailing zeros in the results, so > I've attached a patch which now has inverse transition functions to support > numeric ty

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 15, 2013 at 2:27 PM, Josh Berkus wrote: > On 12/14/2013 05:00 PM, Tom Lane wrote: > > This consideration also makes me question whether we should apply the > > method for NUMERIC. Although in principle numeric addition/subtraction > > is exact, such a sequence could leave us with a d

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

"Erik Rijkers" writes: > I have a 200 GB dev database running under 9.4devel that I thought I could > now, for test purposes, compile a patched > postgres binary for (i.e.: a HEAD + > inverse_transition_functions_v1.5.patch.gz binary), so as to avoid an initdb > and use > the existing 200 GB d

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Wed, December 25, 2013 14:49, David Rowley wrote: > [ inverse_transition_functions_v1.5.patch.gz ] I ran into the following problem which is, I think, NOT a problem with your patch but with my setup. Still, if anyone can enlighten me on its cause I'd be thankful (it shows up every now and th

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 22, 2013 at 2:42 AM, David Rowley wrote: > On Sun, Dec 22, 2013 at 1:01 AM, Erik Rijkers wrote: > >> On Sat, December 21, 2013 12:52, David Rowley wrote: >> > On Sun, Dec 22, 2013 at 12:49 AM, Erik Rijkers wrote: >> > >> >> On Sat, December 21, 2013 12:38, David Rowley wrote: >> >>

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 22, 2013 at 1:01 AM, Erik Rijkers wrote: > On Sat, December 21, 2013 12:52, David Rowley wrote: > > On Sun, Dec 22, 2013 at 12:49 AM, Erik Rijkers wrote: > > > >> On Sat, December 21, 2013 12:38, David Rowley wrote: > >> > [ inverse_transition_functions_v1.2.patch.gz ] > >> > Please

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sat, December 21, 2013 12:52, David Rowley wrote: > On Sun, Dec 22, 2013 at 12:49 AM, Erik Rijkers wrote: > >> On Sat, December 21, 2013 12:38, David Rowley wrote: >> > [ inverse_transition_functions_v1.2.patch.gz ] >> > Please find attached an updated patch which should remove the duplicate >

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 22, 2013 at 12:49 AM, Erik Rijkers wrote: > On Sat, December 21, 2013 12:38, David Rowley wrote: > > [ inverse_transition_functions_v1.2.patch.gz ] > > Please find attached an updated patch which should remove the duplicate > OID > > problem you saw. > > That fixes it, thanks > > The

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sat, December 21, 2013 12:38, David Rowley wrote: > [ inverse_transition_functions_v1.2.patch.gz ] > Please find attached an updated patch which should remove the duplicate OID > problem you saw. That fixes it, thanks There is 1 of 141 failed tests: window ... FAILED b

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 22, 2013 at 12:12 AM, David Rowley wrote: > On Sat, Dec 21, 2013 at 11:47 PM, Erik Rijkers wrote: > >> On Sat, December 21, 2013 10:53, David Rowley wrote: >> >> > [inverse_transition_functions_v1.1.patch.gz ] >> >> Hi, >> >> I know, $subject says "WIP", but I assumed it's supposed

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sat, Dec 21, 2013 at 11:47 PM, Erik Rijkers wrote: > On Sat, December 21, 2013 10:53, David Rowley wrote: > > > [inverse_transition_functions_v1.1.patch.gz ] > > Hi, > > I know, $subject says "WIP", but I assumed it's supposed to compile, so I > tried to get this to run on linux (Centos 5.0).

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sat, December 21, 2013 10:53, David Rowley wrote: > [inverse_transition_functions_v1.1.patch.gz ] Hi, I know, $subject says "WIP", but I assumed it's supposed to compile, so I tried to get this to run on linux (Centos 5.0). gcc 4.8.2, with ./configure --prefix=/home/aardvark/pg_stuff/pg_i

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Dec 17, 2013 at 10:51 PM, David Rowley wrote: > On Mon, Dec 16, 2013 at 9:36 PM, Hannu Krosing wrote: > >> On 12/16/2013 08:39 AM, David Rowley wrote: >> >> >> Any other ideas or +1's for any of the existing ones? >> >> +1, inverse good :) >> >> > In the attached patch I've renamed nega

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Mon, Dec 16, 2013 at 9:36 PM, Hannu Krosing wrote: > On 12/16/2013 08:39 AM, David Rowley wrote: > > > Any other ideas or +1's for any of the existing ones? > > +1, inverse good :) > > In the attached patch I've renamed negative to inverse. I've also disabled the inverse functions when an exp

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Dec 17, 2013 at 11:06 AM, David Rowley wrote: > On Tue, Dec 17, 2013 at 1:18 AM, Tom Lane wrote: > >> Once again: this patch has no business changing any user-visible behavior. >> That would include not changing the number of evaluations of volatile >> functions. The planner is full of

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Tue, Dec 17, 2013 at 1:18 AM, Tom Lane wrote: > Once again: this patch has no business changing any user-visible behavior. > That would include not changing the number of evaluations of volatile > functions. The planner is full of places where optimizations are disabled > for volatile subexpr

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sat, Dec 14, 2013 at 8:00 PM, Tom Lane wrote: > Greg Stark writes: >> On 14 Dec 2013 15:40, "Tom Lane" wrote: >>> I think you *can't* cover them for the float types; roundoff error >>> would mean you don't get the same answers as before. > >> I was going to say the same thing. But then I star

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

David Rowley writes: >> One thing that is currently on my mind is what to do when passing volatile >> functions to the aggregate. Since the number of times we execute a volatile >> function will much depend on the window frame options, I think we should >> include some sort of warning in the docum

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 15, 2013 at 12:17 PM, David Rowley wrote: > > One thing that is currently on my mind is what to do when passing volatile > functions to the aggregate. Since the number of times we execute a volatile > function will much depend on the window frame options, I think we should > include s

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Mon, Dec 16, 2013 at 9:39 PM, Heikki Linnakangas wrote: > > There's another technique we could use which doesn't need a negative > transition function, assuming the order you feed the values to the aggreate > function doesn't matter: keep subtotals. For example, if the window first > contains

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 12/15/2013 03:57 AM, Tom Lane wrote: Josh Berkus writes: I think even the FLOAT case deserves some consideration. What's the worst-case drift? Complete loss of all significant digits. The case I was considering earlier of single-row windows could be made safe (I think) if we apply the ne

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 12/16/2013 08:39 AM, David Rowley wrote: > On Mon, Dec 16, 2013 at 6:00 AM, Ants Aasma > wrote: > > On Dec 15, 2013 6:44 PM, "Tom Lane" > wrote: > > David Rowley > writes: > > > I've attach

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Mon, Dec 16, 2013 at 08:39:33PM +1300, David Rowley wrote: > On Mon, Dec 16, 2013 at 6:00 AM, Ants Aasma wrote: > > > On Dec 15, 2013 6:44 PM, "Tom Lane" wrote: > > > David Rowley writes: > > > > I've attached an updated patch which includes some documentation. > > > > I've also added suppor

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Mon, Dec 16, 2013 at 6:00 AM, Ants Aasma wrote: > On Dec 15, 2013 6:44 PM, "Tom Lane" wrote: > > David Rowley writes: > > > I've attached an updated patch which includes some documentation. > > > I've also added support for negfunc in CREATE AGGREGATE. Hopefully > that's > > > an ok name for

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Dec 15, 2013 6:44 PM, "Tom Lane" wrote: > David Rowley writes: > > I've attached an updated patch which includes some documentation. > > I've also added support for negfunc in CREATE AGGREGATE. Hopefully that's > > an ok name for the option, but if anyone has any better ideas please let > > th

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

David Rowley writes: > I've attached an updated patch which includes some documentation. > I've also added support for negfunc in CREATE AGGREGATE. Hopefully that's > an ok name for the option, but if anyone has any better ideas please let > them be known. I'd be a bit inclined to build the termi

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 15, 2013 at 9:29 PM, Tom Lane wrote: > David Rowley writes: > > I guess the answer for the people that complain about slowness could be > > that they create their own aggregate function which implements float4pl > as > > the trans function and float4mi as the negative trans function.

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 15, 2013 at 3:08 PM, Tom Lane wrote: > I wrote: > > It's not so good with two-row windows though: > > Actually, carrying that example a bit further makes the point even more > forcefully: > > Table correct sum of negative-transition > this + next val

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

David Rowley writes: > I guess the answer for the people that complain about slowness could be > that they create their own aggregate function which implements float4pl as > the trans function and float4mi as the negative trans function. They can > call it SUMFASTBUTWRONG() if they like. Perhaps i

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 15, 2013 at 3:08 PM, Tom Lane wrote: > I wrote: > > It's not so good with two-row windows though: > > Actually, carrying that example a bit further makes the point even more > forcefully: > > Table correct sum of negative-transition > this + next val

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

I wrote: > It's not so good with two-row windows though: Actually, carrying that example a bit further makes the point even more forcefully: Table correct sum of negative-transition this + next value result 1e201e201e20 + 1

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

David Rowley writes: > On Sun, Dec 15, 2013 at 2:27 PM, Josh Berkus wrote: >> If we're going to disqualify NUMERIC too, we might as well bounce the >> feature. Without a fast FLOAT or NUMERIC, you've lost most of the >> target audience. > I think the feature is worth it alone if we could improv

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

Josh Berkus writes: > I think even the FLOAT case deserves some consideration. What's the > worst-case drift? Complete loss of all significant digits. The case I was considering earlier of single-row windows could be made safe (I think) if we apply the negative transition function first, before

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On Sun, Dec 15, 2013 at 2:27 PM, Josh Berkus wrote: > If we're going to disqualify NUMERIC too, we might as well bounce the > feature. Without a fast FLOAT or NUMERIC, you've lost most of the > target audience. > > I don't agree with this. I'm going with the opinion that the more types and aggre

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 12/14/2013 05:00 PM, Tom Lane wrote: > This consideration also makes me question whether we should apply the > method for NUMERIC. Although in principle numeric addition/subtraction > is exact, such a sequence could leave us with a different dscale than > is returned by the existing code. I'm

<    1   2   3   >