Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1s7eRD-006ezm-KY
	for pgsql-bugs@arkaria.postgresql.org; Thu, 16 May 2024 16:58:41 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1s7eRD-00C7V0-KU
	for pgsql-bugs@arkaria.postgresql.org; Thu, 16 May 2024 16:58:39 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1s7eRD-00C7Us-CN
	for pgsql-bugs@lists.postgresql.org; Thu, 16 May 2024 16:58:39 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1s7eRB-000WgZ-2P
	for pgsql-bugs@lists.postgresql.org; Thu, 16 May 2024 16:58:38 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 44GGwTt42114797;
	Thu, 16 May 2024 12:58:29 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
cc: Japin Li <japinli@hotmail.com>, Etsuro Fujita <etsuro.fujita@gmail.com>,
        onderkalaci@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption
In-reply-to: <202405161527.2rs7jfxkxl6m@alvherre.pgsql>
References: <202405161527.2rs7jfxkxl6m@alvherre.pgsql>
Comments: In-reply-to Alvaro Herrera <alvherre@alvh.no-ip.org>
	message dated "Thu, 16 May 2024 17:27:18 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2114795.1715878709.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 16 May 2024 12:58:29 -0400
Message-ID: <2114796.1715878709@sss.pgh.pa.us>
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/2114796.1715878709%40sss.pgh.pa.us>
Precedence: bulk

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2024-May-16, Japin Li wrote:
>> I find that the FETCH FIRST ... ROWS WITH TIES does not support type ca=
sting.

> Why do you need this?  The standard says

> <fetch first clause> ::=3D FETCH { FIRST | NEXT } [ <fetch first quantit=
y> ] { ROW | ROWS } { ONLY | WITH TIES }
> <fetch first quantity> ::=3D <fetch first row count> | <fetch first perc=
entage>
> <offset row count> ::=3D <simple value specification>
> <fetch first row count> ::=3D <simple value specification>
> which doesn't seem to leave room for a cast.

Exactly.  "LIMIT 42::int8" is accepted, but the equivalent
with FETCH is not, so that there's a hazard of the remote
rejecting the query depending on how deparseExpr chooses
to print the limit expression.  Ordinarily it hasn't the
slightest hesitation about affixing casts to constants,
so I suspect there's a live problem there.


I think that this approach to a fix might be the wrong thing
altogether, and that it might be better to refuse to send WITH_TIES
clauses to the remote at all.  There are two things that are scaring
me about that:

1. The remote might have a different idea of equality than we do,
leading to different results.  Admittedly that's a little bit
far-fetched, but with things like nondeterministic collations floating
around, it's by no means impossible.

2. If the remote is older than v13 the query will fail entirely
for lack of support for WITH TIES.

At the very least we need a remote-version check before deciding
that it'll be OK to ship such a clause.  But if there are other
gotchas, as it seems there are, let's just cut our losses and
not do it.

			regards, tom lane