Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption
От | Japin Li |
---|---|
Тема | Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption |
Дата | |
Msg-id | ME3P282MB31667682FE50B2DE40954487B6F12@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM обсуждение исходный текст |
Ответ на | Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption (Etsuro Fujita <etsuro.fujita@gmail.com>) |
Ответы |
Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption
(Önder Kalacı <onderkalaci@gmail.com>)
Re: BUG #18467: postgres_fdw (deparser) ignores LimitOption (Etsuro Fujita <etsuro.fujita@gmail.com>) |
Список | pgsql-bugs |
On Fri, 24 May 2024 at 16:32, Etsuro Fujita <etsuro.fujita@gmail.com> wrote: > Hi, > > On Thu, May 23, 2024 at 11:30 PM Japin Li <japinli@hotmail.com> wrote: >> On Thu, 23 May 2024 at 14:54, Önder Kalacı <onderkalaci@gmail.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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Waka RanaiДата:
Сообщение: Re: Bug report - pg_upgrade tool seems to have a race condition when trying to delete a pg_wal file
Следующее
От: "David G. Johnston"Дата:
Сообщение: Re: BUG #18482: The first data after paging is inconsistent with the actual first data