Re: Support for RANGE ... PRECEDING windows in OVER

I'm fine with moving the operators over to functions. I just don't want to
implement anything that is against best practice. If we are OK with that
direction, I'll go ahead and start on the new patch.

Ian

On Mon, Jul 1, 2013 at 9:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
> > On 07/02/2013 02:39 AM, Robert Haas wrote:
> >> I'm actually
> >> not clear that it would be all that bad to assume fixed operator
> >> names, as we apparently do in a few places despite the existence of
> >> operator classes. But if that is bad, then I don't know how using @+
> >> and @- instead helps anything.
>
> > Personally I'm not clear why it's bad to reserve certain fundamental
> > operators like '+' and '-', requiring that they have particular
> semantics.
>
> It is bad. It's against project policy, not least because we have
> assorted *existing* datatypes for which "obvious" operator names like
> "=" do not have all the properties you might expect.
>
> If you need a more concrete example of why that sort of thinking is
> bad, you might consider the difference between < and ~<~ for type text.
> If we hard-wired knowledge about operator behavior to operator names,
> it would be impossible for the system to understand that both of those
> operators represent sorting-related behaviors.
>
> Or to be even more concrete: if we allow RANGE to suppose that there's
> only one possible definition of "+" for a datatype, we're effectively
> supposing that there's only one possible sort ordering for that type.
> Which is already a wrong assumption, and has been since Postgres was
> still at Berkeley. If you go this way, you won't be able to support
> both WINDOW ... ORDER BY foo USING < RANGE ... and WINDOW ... ORDER BY
> foo USING ~<~ RANGE ... because you won't know which addition operator
> to apply.
>
> (And yeah, I'm aware that the SQL standard only expects RANGE to support
> sort keys that are of numeric, datetime, or interval type. I would hope
> that we have higher expectations than that. Even if we don't, it's not
> exactly hard to credit that people might have multiple ideas about how
> to sort interval values.)
>
> There are indeed still some places where we rely on operator names to
> mean something, but we need to get away from that idea not add more.
> Ideally, any property the system understands about an operator or
> function should be explicitly declared through opclass membership or
> some similar representation. We've made substantial progress in that
> direction in the last fifteen years. I don't want to reverse that
> progress in the name of minor expediencies, especially not ones that
> fail to support flexibility that has been in the system for a couple
> or three decades already.
>
> regards, tom lane
>