I found some time and I think I am up to speed now. I finally figured out how to add new operator strategies and made a little test operator for myself.

It seems pretty clear that assuming '+' and '-' are addition and subtraction is a bad idea. I don't think it would be too tricky to add support for new operator strategies. Andrew Gierth suggested calling these new strategies "offset -" and "offset +", which I think describes it pretty well. I assigned the operator itself to be "@+" and "@-" but that can obviously be changed. If this sounds like a good path to you guys, I will go ahead and implement the operators for the appropriate types. Please let me know if I am misunderstanding something - I am still figuring stuff out :) Aside from the opclass stuff, there were some other important issues mentioned with the original RANGE support. I think I will address those after the opclass stuff is done. Thanks! Ian On Sat, Jun 22, 2013 at 4:38 PM, ian link <i...@ilink.io> wrote: > Thanks Craig! That definitely does help. I probably still have some > questions but I think I will read through the rest of the code before > asking. Thanks again! > > Ian > > > Craig Ringer > > Friday, June 21, 2013 8:41 PM > > > > > On 06/22/2013 03:30 AM, ian link wrote: > >> > >> Forgive my ignorance, but I don't entirely understand the problem. What > >> does '+' and '-' refer to exactly? > > > > Consider "RANGE 4.5 PRECEDING'. > > > > You need to be able to test whether, for the current row 'b', any given > > row 'a' is within the range (b - 4.5) < a <= b . Not 100% sure about the > > < vs <= boundaries, but that's irrelevant for the example. > > > > To test that, you have to be able to do two things: you have to be able > > to test whether one value is greater than another, and you have to be > > able to add or subtract a constant from one of the values. > > > > Right now, the b-tree access method provides information on the ordering > > operators < <= = > >= <> , which provides half the answer. But these > > don't give any concept of *distance* - you can test ordinality but not > > cardinality. > > > > To implement the "different by 4.5" part, you have to be able to add 4.5 > > to one value or subtract it from the other. > > > > The obvious way to do that is to look up the function that implements > > the '+' or '-' operator, and do: > > > > ((OPERATOR(+))(a, 4.5)) > b AND (a <= b) > > > > or > > > > ((OPERATOR(-))(b, 4.5)) < a AND (a <= b); > > > > The problem outlined by Tom in prior discussion about this is that > > PostgreSQL tries really hard not to assume that particular operator > > names mean particular things. Rather than "knowing" that "+" is always > > "an operator that adds two values together; is transitive, symmetric and > > reflexive", PostgreSQL requires that you define an *operator class* that > > names the operator that has those properties. > > > > Or at least, it does for less-than, less-than-or-equals, equals, > > greater-than-or-equals, greater-than, and not-equals as part of the > > b-tree operator class, which *usually* defines these operators as < <= = > >> > >> = > <>, but you could use any operator names you wanted if you really > > > > liked. > > > > Right now (as far as I know) there's no operator class that lets you > > identify operators for addition and subtraction in a similar way. So > > it's necessary to either add such an operator class (in which case > > support has to be added for it for every type), extend the existing > > b-tree operator class to provide the info, or blindly assume that "+" > > and "-" are always addition and subtraction. > > > > For an example of why such assumptions are a bad idea, consider matrix > > multiplication. Normally, "a * b" = "b * a", but this isn't true for > > multiplication of matrices. Similarly, if someone defined a "+" operator > > as an alias for string concatenation (||), we'd be totally wrong to > > assume we could use that for doing range-offset windowing. > > > > So. Yeah. Operator classes required, unless we're going to change the > > rules and make certain operator names "special" in PostgreSQL, so that > > if you implement them they *must* have certain properties. This seems > > like a pretty poor reason to add such a big change. > > > > I hope this explanation (a) is actually correct and (b) is helpful. > > > > ian link > > Friday, June 21, 2013 12:30 PM > > > Forgive my ignorance, but I don't entirely understand the problem. What > does '+' and '-' refer to exactly? > > Thanks! > > > > > > > > Hitoshi Harada > > Friday, June 21, 2013 4:35 AM > > > > > > > > On 06/22/2013 03:30 AM, ian link wrote: > > Forgive my ignorance, but I don't entirely understand the problem. What > > does '+' and '-' refer to exactly? > > Consider "RANGE 4.5 PRECEDING'. > > You need to be able to test whether, for the current row 'b', any given > row 'a' is within the range (b - 4.5) < a <= b . Not 100% sure about the > < vs <= boundaries, but that's irrelevant for the example. > > To test that, you have to be able to do two things: you have to be able > to test whether one value is greater than another, and you have to be > able to add or subtract a constant from one of the values. > > Right now, the b-tree access method provides information on the ordering > operators < <= = > >= <> , which provides half the answer. But these > don't give any concept of *distance* - you can test ordinality but not > cardinality. > > To implement the "different by 4.5" part, you have to be able to add 4.5 > to one value or subtract it from the other. > > The obvious way to do that is to look up the function that implements > the '+' or '-' operator, and do: > > ((OPERATOR(+))(a, 4.5)) > b AND (a <= b) > > or > > ((OPERATOR(-))(b, 4.5)) < a AND (a <= b); > > The problem outlined by Tom in prior discussion about this is that > PostgreSQL tries really hard not to assume that particular operator > names mean particular things. Rather than "knowing" that "+" is always > "an operator that adds two values together; is transitive, symmetric and > reflexive", PostgreSQL requires that you define an *operator class* that > names the operator that has those properties. > > Or at least, it does for less-than, less-than-or-equals, equals, > greater-than-or-equals, greater-than, and not-equals as part of the > b-tree operator class, which *usually* defines these operators as < <= = > >= > <>, but you could use any operator names you wanted if you really > liked. > > Right now (as far as I know) there's no operator class that lets you > identify operators for addition and subtraction in a similar way. So > it's necessary to either add such an operator class (in which case > support has to be added for it for every type), extend the existing > b-tree operator class to provide the info, or blindly assume that "+" > and "-" are always addition and subtraction. > > For an example of why such assumptions are a bad idea, consider matrix > multiplication. Normally, "a * b" = "b * a", but this isn't true for > multiplication of matrices. Similarly, if someone defined a "+" operator > as an alias for string concatenation (||), we'd be totally wrong to > assume we could use that for doing range-offset windowing. > > So. Yeah. Operator classes required, unless we're going to change the > rules and make certain operator names "special" in PostgreSQL, so that > if you implement them they *must* have certain properties. This seems > like a pretty poor reason to add such a big change. > > I hope this explanation (a) is actually correct and (b) is helpful. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >