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!
--