Postgres 16.4 bug

[ngkame]

Description:
We are experiencing a bug related to PostgreSQL when using Foreign Data Wrapper (FDW) tables that query ClickHouse. Specifically, when executing a query that includes a FDW table (e.g., ch_views.trds), if the query in the WITH clause takes longer than 20 seconds, it enters a cyclic retry loop.

How to Reproduce:
The issue can be reproduced with the following SQL query:

WITH trds AS (
    SELECT * FROM ch_views.trds
)
SELECT * FROM trds
INNER JOIN (
    SELECT *
    FROM views.some_dict
) dict_tbl USING (k_login);

If the query in the trds CTE takes longer than 20 seconds to execute, it results in a retry loop.

Proposed Solutions:

1. Create a Materialized View:
   To mitigate this issue, we can create a materialized view as follows:

   CREATE MATERIALIZED VIEW ch_views.trds_mv AS
   SELECT * FROM ch_views.trds;

   This materialized view can be refreshed as needed, which may help avoid the timeout issue.

2. Avoid Using FDW Tables in the WITH Clause:
   Alternatively, we can avoid placing FDW tables in the WITH clause. When the FDW table is not included in the WITH block, the query seems to execute normally without entering the retry loop.

Please let me know if you need any more information or if there are any updates regarding this issue.