Oliver Ford <ojf...@gmail.com> writes: > [ 0001-window-frame-v6.patch ]
Generally speaking, Postgres tries hard to be an extensible-datatype system, going beyond the SQL standard's minimum requirements when necessary to make it so. The reason that we don't already have RANGE PRECEDING/FOLLOWING support is that nobody was satisfied with only making it work for integers and datetimes. There was, as I recall, code implementing more or less what you've got here in the original window function submission, and we pulled it out before committing because of that inadequacy. I don't think the fact that some years have gone by means that we should forget about keeping the feature extensible. One subsequent discussion about how we might make it work to project standards was here: https://www.postgresql.org/message-id/flat/51C3B952.60907%402ndquadrant.com Looking back at that, I notice that we all focused on the way to identify a suitable "+" or "-" operator, but now I'm thinking that that's not actually a good factorization, because it'd be subject to undesirable overflow hazards. That is, if we have an integer sequence like 2147483640 2147483641 2147483642 2147483643 2147483644 and we operate on this with "RANGE FOLLOWING 10", that approach results in an integer overflow when we try to calculate the limit values. But there's no real need for an overflow error. Ideally, if we try to form 2147483640 + 10 and notice it's overflowed, we'd treat the bound as +infinity, because every non-overflowed integer value must be within range. So the approach I'm imagining now is a datatype-specific support function along the lines of in_range(a, b, delta) returns bool which is supposed to return true if a <= b + delta, or something along that line --- exact details of the definition TBD --- with the proviso that if b + delta would overflow then the result is automatically true. We could probably also delegate the requirement of throwing an error for negative delta to this function, eliminating the need for the datatype-independent core code to know how to tell that, which is the other datatype-dependent behavior needed per spec. Likely there are two of these, one each for the PRECEDING and FOLLOWING cases. As suggested in the above-mentioned thread, we could attach such functions as support functions in the btree opclass that defines the sort order of the window frame's ordering column, and the core code could look it up from there. Extensibility would come from the fact that people can define new opclasses. Also, I believe we could support multiple such functions per opclass, allowing the potential to support "delta"s of different datatypes --- pg_amproc.amproclefttype would correspond to the common type of a and b, while pg_amproc.amprocrighttype would correspond to the data type of delta. We certainly need to allow delta to be a different type from a/b just to handle the spec's timestamp cases. I'm not sure if there's near-term value in multiple types of delta values, but it seems easy to allow in this framework. regards, tom lane
