Обсуждение: Temp table + inde + FDW table on Redshift: MOVE BACKWARD ALL IN not supported

Поиск
Список
Период
Сортировка

Temp table + inde + FDW table on Redshift: MOVE BACKWARD ALL IN not supported

От
Wells Oliver
Дата:
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!

--

Re: Temp table + inde + FDW table on Redshift: MOVE BACKWARD ALL IN not supported

От
Tom Lane
Дата:
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