pushdown doesn't work for 'where with timestamp'

[TheLazzziest]

Context

Hi there!

I was testing a connection between postgres and mysql, so I started experiencing some issues when I was trying to employ time conditions in a query, such as:

 SELECT id, content, updated FROM dev.reviews WHERE dev.reviews.updated BETWEEN CURRENT_TIMESTAMP - INTERVAL '1 day' AND CURRENT_TIMESTAMP LIMIT 100;

I got the following errors (one of) in a response:

• ERROR: failed to store the result
• server closed the connection unexpectedly
• Error writing file '/tmp/MYfd=219' (OS errno 28 - No space left on device)

It looks like the condition is not pushed to the mysql instance, so postgres is trying to fetch everything it can.

If I remove the condtion, everything works as expected with expected performance.

Is is an issue or I just misused the extension ?

Here are the snippet for the adding a remote server:

CREATE EXTENSION IF NOT EXISTS mysql_fdw;
CREATE SCHEMA dev AUTHORIZATION postgres;
CREATE SERVER dev FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'dev.db',port '3306',fetch_size '10000');
CREATE USER MAPPING FOR postgres SERVER dev OPTIONS (username 'root', password :'password');
GRANT USAGE ON FOREIGN SERVER dev TO postgres;
IMPORT FOREIGN SCHEMA dev LIMIT TO (reviews) FROM SERVER dev INTO dev OPTIONS (import_enum_as_text 'true');

[surajkharage19]

Hi @TheLazzziest,

Can you please share the explain verbose output of your query?

explain verbose  SELECT id, content, updated FROM dev.reviews WHERE dev.reviews.updated BETWEEN CURRENT_TIMESTAMP - INTERVAL '1 day' AND CURRENT_TIMESTAMP LIMIT 100;

It seems where clause pushdown is not happening and because of that Postgres is fetching all the rows from the MySQL side and result set is huge and failing with error your mentioned above.

Can you please share the number of records that exist in dev.reviews table and its size?

If I remove the condtion, everything works as expected with expected performance.

How the said query look like? Does it contain limit clause as well? If limit clause is there, then that is getting pushdown to MySQL, and only 100 records are getting fetched from MySQL instead of whole table.

[TheLazzziest]

Hi @surajkharage19 ! Thank you for your support 🙏

Can you please share the explain verbose output of your query?

Using between

lighthouse=# EXPLAIN VERBOSE SELECT id, content, updated FROM dev.reviews WHERE dev.reviews.updated BETWEEN CURRENT_TIMESTAMP - INTERVAL '1 day' AND CURRENT_TIMESTAMP LIMIT 100;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=25.00..125.00 rows=100 width=44)
   Output: id, content, updated
   ->  Foreign Scan on dev.reviews  (cost=25.00..1025.00 rows=1000 width=44)
         Output: id, content, updated
         Filter: ((reviews.updated <= CURRENT_TIMESTAMP) AND (reviews.updated >= (CURRENT_TIMESTAMP - '1 day'::interval)))
         Remote server startup cost: 25
         Remote query: SELECT `id`, `content`, `updated` FROM `dev`.`reviews`
 Query Identifier: 5941588392410086392
(8 rows)

Using where >= timestamp

lighthouse=# EXPLAIN VERBOSE SELECT id, content, updated FROM dev.reviews WHERE dev.reviews.updated >= '2025-04-01T00:00:00Z'::timestamp LIM
IT 100;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Limit  (cost=25.00..125.00 rows=100 width=44)
   Output: id, content, updated
   ->  Foreign Scan on dev.reviews  (cost=25.00..1025.00 rows=1000 width=44)
         Output: id, content, updated
         Filter: (reviews.updated >= '2025-04-01 00:00:00'::timestamp without time zone)
         Remote server startup cost: 25
         Remote query: SELECT `id`, `content`, `updated` FROM `dev`.`reviews`
 Query Identifier: 8148464489210689565
(8 rows)

without where

lighthouse=# EXPLAIN VERBOSE SELECT id, content, updated FROM dev.reviews ORDER BY updated DESC LIMIT 100;
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on dev.reviews  (cost=1.00..2.00 rows=1 width=44)
   Output: id, content, updated
   Remote server startup cost: 25
   Remote query: SELECT `id`, `content`, `updated` FROM `dev`.`reviews` ORDER BY `updated` IS NOT NULL, `updated` DESC LIMIT 100
 Query Identifier: -1371969428942665404
(5 rows)

Can you please share the number of records that exist in dev.reviews table and its size?

Amount of rows

lighthouse=# SELECT COUNT(*) AS total_count FROM dev.reviews;
-------------
 total_count 
-------------
    27671279
(1 row)

The table size

SELECT 
  table_name AS "Table",
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE TABLE_NAME = 'reviews';
----------------------
Table     |Size (MB)|
----------+---------+
reviews| 12233.89|

[surajkharage19]

Thanks @TheLazzziest for your reply.

It seems that your foreign table has a timestamptz column, and you are type-casting the value to timestamp in your first two queries.
Can you please type caste it with timestamptz and check the second query?
for e.g.

EXPLAIN VERBOSE SELECT id, content, updated FROM dev.reviews WHERE dev.reviews.updated >= '2025-04-01T00:00:00Z'::timestamptz LIMIT 100;

The where clause from first query will not get pushdown because it is using 'current_timestamp' which is not pushable on the remote side.
Your third query is working fine because it is not fetching all result set from MySQL server and the limit clause pushdown is happening.

[TheLazzziest]

Thank you @surajkharage19 !

Overall, I figured out how to overcome some issue with query limitations. However, I am puzzled with current_timestamp, current_date, etc.

The where clause from first query will not get pushdown because it is using 'current_timestamp' which is not pushable on the remote side.

Can you suggest a way of how to overcome the issue with current_timestamp , current_date etc ?

[TheLazzziest]

It looks like I narrowed down the problem. When I am trying to run the following query

SELECT
     id,
     created
 FROM
     reviews
 WHERE
     created >= '2025-04-06 09:20:59';

I see the error

 Incorrect TIMESTAMP value: '2025-04-06 09:20:59+00'

It seems like FDW is trying to setup a timezone for the passed timestamp, but the target mysql instance doesn't recognize such a format. I was able to reproduce on a mysql instance. Moreover, when I am trying to pass just a timestamp, pushdown overall doesn't work.

Foreign Scan on reviews  (cost=25.00..1025.00 rows=1000 width=68)
   Output: id, created
   Filter: (reviews.created >= '2025-04-06 09:20:59'::timestamp without time zone)
   Remote server startup cost: 25
   Remote query: SELECT `id`, `created` FROM `remote`.`reviews`
 Query Identifier: 4737410178226414810

I checked pushdown configurations overall here. I hope I am right in terms of my understing as I am still badly aware of internal configurations of FDW framework overall.

It seems that timestamp must be supported as well, but I am missing something.

[surajkharage19]

Hi @TheLazzziest,

Can you please check what is the datestyle in Postgres and MySQL? Those should be the same.
I have tested at my end and it is working fine. Please check the below example -

MySQL:
mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2025-04-20 10:17:07 |
+---------------------+
1 row in set (0.00 sec)


Postgres:
edb@542169=#set datestyle to 'ISO, MDY';
SET
edb@542169=#select now();
               now                
----------------------------------
 2025-04-20 10:16:15.059925+05:30
(1 row)

edb@542169=#\d f1
                              Foreign table "public.f1"
 Column |            Type             | Collation | Nullable | Default | FDW options 
--------+-----------------------------+-----------+----------+---------+-------------
 c1     | integer                     |           |          |         | 
 c2     | timestamp without time zone |           |          |         | 
Server: mysql_server
FDW options: (dbname 'edb', table_name 'a')

edb@542169=#select * from f1 where c2 > '2025-04-06 09:20:59';
 c1 |         c2          
----+---------------------
  1 | 2025-04-07 08:49:00
  1 | 2025-04-07 08:49:00
(2 rows)

edb@542169=#explain (verbose) select * from f1 where c2 > '2025-04-06 09:20:59';
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Foreign Scan on public.f1  (cost=10.00..1010.00 rows=1000 width=12)
   Output: c1, c2
   Local server startup cost: 10
   Remote query: SELECT `c1`, `c2` FROM `edb`.`a` WHERE ((`c2` > '2025-04-06 09:20:59'))
(4 rows)

If you want >(timestamp with time zone,timestamp without time zone) operator to get pushdown to the remote server then please add that entry in mysql_fdw_pushdown.config entry.

Hope this helps.