pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Temp table + inde + FDW table on Redshift: MOVE BACKWARD ALL IN not supported 2+ messages / 2 participants [nested] [flat]
* Temp table + inde + FDW table on Redshift: MOVE BACKWARD ALL IN not supported @ 2024-02-10 23:35 Wells Oliver <wells.oliver@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Wells Oliver @ 2024-02-10 23:35 UTC (permalink / raw) To: pgsql-admin Hi all. This might be a Redshift-specific issue and if so, I apologize for posting here, but there might be something more general with FDW, indexes, and temp tables going on too, so I thought I'd post. We create a local temp table and then create an index on it. We then select from that temp table and join a FDW table using indexed (locally, temporarily) columns, this table is actually on a Redshift remote database. It looks a little like this: SELECT game_id, play_id INTO TEMPORARY TABLE b FROM games JOIN pitches USING (game_id); CREATE INDEX ON b (game_id, play_id) SELECT * FROM b JOIN my_fdw_table f ON b.game_id = f.game_id AND f.target_id = 1001; Kinda simple enough. The query runs for a split second, you can see some results, and then bombs: ERROR: SQL command "MOVE BACKWARD ALL IN c1" not supported. CONTEXT: remote SQL command: MOVE BACKWARD ALL IN c1 Without the index on the temp table, the SELECT and JOIN work fine. Wondering what's going on, thanks! -- Wells Oliver wells.oliver@gmail.com <wellsoliver@gmail.com> ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Temp table + inde + FDW table on Redshift: MOVE BACKWARD ALL IN not supported @ 2024-02-11 00:20 Tom Lane <tgl@sss.pgh.pa.us> parent: Wells Oliver <wells.oliver@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2024-02-11 00:20 UTC (permalink / raw) To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin Wells Oliver <wells.oliver@gmail.com> writes: > Hi all. This might be a Redshift-specific issue and if so, I apologize for > posting here, but there might be something more general with FDW, indexes, > and temp tables going on too, so I thought I'd post. > ... > The query runs for a split second, you can see some results, and then bombs: > ERROR: SQL command "MOVE BACKWARD ALL IN c1" not supported. > CONTEXT: remote SQL command: MOVE BACKWARD ALL IN c1 You didn't show an EXPLAIN, but it looks like the FDW is expecting the remote server to support something it doesn't. I'd guess that you need a Redshift-specific FDW to make this work reliably. You could possibly work around it by forcing the planner to use a different join method. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-02-11 00:20 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-02-10 23:35 Temp table + inde + FDW table on Redshift: MOVE BACKWARD ALL IN not supported Wells Oliver <wells.oliver@gmail.com> 2024-02-11 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox