Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption

On Fri, 24 May 2024 at 16:32, Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> Hi,
>
> On Thu, May 23, 2024 at 11:30 PM Japin Li <japinli(at)hotmail(dot)com> wrote:
>> On Thu, 23 May 2024 at 14:54, Önder Kalacı <onderkalaci(at)gmail(dot)com> wrote:
>> > I'm also not good at wording, but I have a minor suggestions like the
>> > following :
>> >
>> > /*
>> > * Also, the FETCH FIRST/NEXT ... ROW/ROWS WITH TIES clause cannot be
>> > pushed down
>> > * because:
>> > * a) The remote system may have a different understanding of equality,
>> > which can
>> > * result in varying results, such as non-deterministic collations.
>> > * b) We do not have knowledge of the remote server's version
>> > * as this clause is only supported for PG13 and above.
>> > */
>
>> Thanks for your review! Fixed in v5 patch.
>
> I think it is reasonable to refuse to send WITH TIES, but I am
> confused about the comments above. Do we really need to care about a)
> here in add_foreign_final_paths()? If the query has WITH TIES, 1) it
> must have ORDER BY as well, which determines what additional rows tie
> for the last place in the result set, and 2) ORDER BY must already
> have been determined to be safe to push down before we get here. So
> in that case, if getting here, we can consider that WITH TIES is also
> safe to push down (if the remote is v13 or later). No?
>
> Anyway, thank you for working on this issue!
>

Sorry for the late reply. I'm not familiar with this. However, after some
tests, the COLLATE may influence the result; see the example below.

[local]:535513 postgres=# CREATE TABLE t01 (a text);
CREATE TABLE
[local]:535513 postgres=# CREATE FOREIGN TABLE ft01 (a text) SERVER loopback OPTIONS (table_name 't01');
CREATE FOREIGN TABLE
[local]:535513 postgres=# SELECT * FROM ft01 ORDER BY a COLLATE "en_US" FETCH FIRST 2 ROWS WITH TIES;
a
-------
hello
hello
hello
(3 rows)

[local]:535513 postgres=# EXPLAIN (verbose) SELECT * FROM ft01 ORDER BY a COLLATE "en_US" FETCH FIRST 2 ROWS WITH TIES;
QUERY PLAN
-----------------------------------------------------------------------------------
Limit (cost=446.26..446.27 rows=2 width=64)
Output: a, ((a)::text)
-> Sort (cost=446.26..449.92 rows=1462 width=64)
Output: a, ((a)::text)
Sort Key: ft01.a COLLATE "en_US"
-> Foreign Scan on public.ft01 (cost=100.00..431.64 rows=1462 width=64)
Output: a, a
Remote SQL: SELECT a FROM public.t01
(8 rows)

[local]:535513 postgres=# SELECT * FROM ft01 ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
a
-------
hello
hello
(2 rows)

[local]:535513 postgres=# EXPLAIN (verbose) SELECT * FROM ft01 ORDER BY a FETCH FIRST 2 ROWS WITH TIES;
QUERY PLAN
----------------------------------------------------------------------------------
Foreign Scan on public.ft01 (cost=100.00..100.44 rows=2 width=32)
Output: a
Remote SQL: SELECT a FROM public.t01 ORDER BY a ASC NULLS LAST LIMIT 2::bigint
(3 rows)

--
Regards,
Japin Li