pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: postgres_fdw: suppress casts on constants in limited cases.
Date: Fri, 12 Nov 2021 16:50:56 +0000
Message-ID: <E1mlZlQ-0008Mt-05@gemulon.postgresql.org> (raw)

postgres_fdw: suppress casts on constants in limited cases.

When deparsing an expression of the form "remote_var OP constant",
we'd normally apply a cast to the constant to make sure that the
remote parser thinks it's of the same type we do.  However, doing
so is often not necessary, and it causes problems if the user has
intentionally declared the local column as being of a different
type than the remote column.  A plausible use-case for that is
using text to represent a type that's an enum on the remote side.
A comparison on such a column will get shipped as "var = 'foo'::text",
which blows up on the remote side because there's no enum = text
operator.  But if we simply leave off the explicit cast, the
comparison will do exactly what the user wants.

It's possible to do this without major risk of semantic problems, by
relying on the longstanding parser heuristic that "if one operand of
an operator is of type unknown, while the other one has a known type,
assume that the unknown operand is also of that type".  Hence, this
patch leaves off the cast only if (a) the operator inputs have the same
type locally; (b) the constant will print as a string literal or NULL,
both of which are initially taken as type unknown; and (c) the non-Const
input is a plain foreign Var.  Rule (c) guarantees that the remote
parser will know the type of the non-Const input; moreover, it means
that if this cast-omission does cause any semantic surprises, that can
only happen in cases where the local column has a different type than
the remote column.  That wasn't guaranteed to work anyway, and this
patch should represent a net usability gain for such cases.

One point that I (tgl) remain slightly uncomfortable with is that we
will ignore an implicit RelabelType when deciding if the non-Const input
is a plain Var.  That makes it a little squishy to argue that the remote
should resolve the Const as being of the same type as its Var, because
then our Const is not the same type as our Var.  However, if we don't do
that, then this hack won't work as desired if the user chooses to use
varchar rather than text to represent some remote column.  That seems
useful, so do it like this for now.  We might have to give up the
RelabelType-ignoring bit if any problems surface.

Dian Fay, with review and kibitzing by me

Discussion: https://postgr.es/m/C9LU294V7K4F.34LRRDU449O45@lamia

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/f8abb0f5e114d8c309239f0faa277b97f696d829

Modified Files
--------------
contrib/postgres_fdw/deparse.c                 | 116 +++++++++++++++++++++--
contrib/postgres_fdw/expected/postgres_fdw.out | 125 +++++++++++++++++--------
contrib/postgres_fdw/sql/postgres_fdw.sql      |  18 ++++
3 files changed, 212 insertions(+), 47 deletions(-)



Message-ID: <E1mlZlQ-0008Mt-05@gemulon.postgresql.org>
Permalink:  ../E1mlZlQ-0008Mt-05@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1mlZlQ-0008Mt-05@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: tgl@sss.pgh.pa.us
  Subject: Re: pgsql: postgres_fdw: suppress casts on constants in limited cases.
  In-Reply-To: <E1mlZlQ-0008Mt-05@gemulon.postgresql.org>
  References: <E1mlZlQ-0008Mt-05@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox