Re: [RFC] ASOF Join - Mailing list pgsql-hackers

From Ilya Anfimov
Subject Re: [RFC] ASOF Join
Date
Msg-id 20211123072929.GA908685@azor.tzirechnoy.ru
Whole thread Raw
In response to Re: [RFC] ASOF Join  (Alexander Kuzmenkov <akuzmenkov@timescale.com>)
Responses Re: [RFC] ASOF Join
List pgsql-hackers
On Mon, Nov 22, 2021 at 03:44:37PM +0300, Alexander Kuzmenkov wrote:
> On 21.11.2021 07:53, Ilya Anfimov wrote:
> > DISCLAIMER:  I  am both seeing this first time and I don't have a
> > good understanding of the PosgreSQL development practices.
> 
> > pure evil
> > ridiculous
> No worries, at least you got the etiquette just right.
> 
> 
>  There  are  two  points in your mail that I'd like to discuss.
> First, the ASOF grammar being bad because  it's  implicit.  I  do
> agree on the general idea that explicit is better UX than implic-
> it, especially when we're talking about SQL where you spend  half
> the  time  battling  the  query  planner already. However, in the
> grammar I proposed it's unambiguous which conditions are ASOF and
> which  are  not  -- all inequalities are ASOF, all equalities are

 I see at least two operators in postgres that implement ordering
while they are not being <= ( ~<=~ -- for text  compare  byte-by-
byte, and *<= for internal record compare)
 and  four  cases that are literally <= , but don't implement or-
dering -- box, lseg, path and circle are compared by  length  and
fuzzy floating-point comparision.

 Are  you sure an implementor and a programmer will easily decide
what is  just a boolean test, and what is an order?

 What's worse, preference of values doesn't have a lot in  common
with  filters  you want on them. Let's get your example of a time
matching: another reasonable business case is to match the  near-
est time point in any direction, within a reasonable time limit.
 Like timea BETWEEN timeb - '1s' AND timeb + '1s' ,
 and to choose something like min(@(timea-timeb)) among them (*We
strangely don't have an absolute value operator on interval,  but
I think you've got the point*).

> not, and there can be no other kinds of conditions for this  type
> of join. It can also support any number of ASOF conditions. Which
> grammar exactly do you suggest? Maybe something like this:




> 
> asks JOIN bids ON asks.instrument = bids.instrument ASOF asks.timestamp <= bids.timestamp

 I suggest JOIN bids ORDER BY asks.timestamp DESC LIMIT 1
                 ON asks.instrument = bids.instrument AND asks.timestamp <= bids.timestamp

 LIMIT 1 could also be implied.





pgsql-hackers by date:

Previous
From: vignesh C
Date:
Subject: Re: row filtering for logical replication
Next
From: "houzj.fnst@fujitsu.com"
Date:
Subject: RE: row filtering for logical replication