Re: Push down time-related SQLValue functions to foreign server

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru>
Cc: Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>, Zhihong Yu <zyu(at)yugabyte(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Push down time-related SQLValue functions to foreign server
Date: 2022-01-18 20:01:05
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

I wrote:
> Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru> writes:
>> This means we'll translate something like
>> explain select * from t where d > now() - '1 day'::interval;
>> to
>> select * from t where d > $1;

> Right.

After thinking about that a bit more, I see that this will result
in a major redefinition of what is "shippable". Right now, we do not
consider the above WHERE clause to be shippable, not only because of
now() but because the timestamptz-minus-interval operator is dependent
on the timezone setting, which might be different at the remote.
But if we evaluate that operator locally and send its result as a
parameter, the objection vanishes. In fact, I don't think we even
need to require the subexpression to contain only built-in functions.
Its result still has to be of a built-in type, but that's a much
weaker restriction.

So this is going to require significant restructuring of both
is_foreign_expr and deparse.c, which I realize may be more than
you bargained for. But if you want to tackle it, I think what
we want to do is divide potentially-shippable expressions into
three sorts of components:

1. Remote Vars, which obviously ship as-is.

2. Locally-evaluatable subexpressions, which must contain no
remote Vars, must be stable or immutable, and must have a result
type that we consider safe to ship. If such a subexpression
is just a Const, we ship it as a constant, otherwise we evaluate
the value at runtime and ship a parameter.

3. Superstructure, which consists of all expression nodes having
at least one remote Var below them. These nodes have to be
shippable according to the existing definition, so that we know
that the remote server will evaluate them just as we would.

If we keep the existing division of labor between is_foreign_expr
and deparse.c, I fear that there's going to be a lot of duplicated
code as well as duplicative planning effort. I wonder if it'd be
wise to combine those operations into a single expression-scanning
process that determines whether an expression is safe to ship and
produces the translated string immediately if it is.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Juan José Santamaría Flecha 2022-01-18 20:08:37 Re: [PATCH] allow src/tools/msvc/clean.bat script to be called from the root of the source tree
Previous Message Bossart, Nathan 2022-01-18 20:00:41 Re: O(n) tasks cause lengthy startups and checkpoints