Foreign table with the option 'query' about firebird_fdw HOT 5 CLOSED

Some basic details would be useful:

• PostgreSQL version
• Firebird version
• firebird_fdw and libfq versions

To assist diagnosing the issue, please set client_min_messages to debug2, re-execute the query and attach the output.

from firebird_fdw.

Hello, thank you for replying.

Postgres version: PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
Firebird version: 2.5.9
Firebird_fdw: postgresql10-firebird_fdw-0.5.0-1.x86_64
Libfq: libfq-0.3.0-1.x86_64

debug.txt

from firebird_fdw.

Thanks for the info.

Unfortunately I can't reproduce the issue. Could you execute the query contained in the debug.txt output, i.e.

 SELECT COUNT(*) FROM (
    /* your query here */
 )

and report the output?

What is happening here is that the FDW is trying to fetch an estimate of the table's row count for the PostgreSQL planner. For foreign tables defined as a query, the only way of doing this is to execute the query wrapped in SELECT COUNT(*) FROM ..., which should theoretically only ever return one row. For some reason this is not happening, but the debug output doesn't say how many rows are being returned.

As a workaround, and something I've been meaning to do anyway, I've added a table option estimated_row_count, which makes it possible to specify the approximate number of rows the query would return, and side-step the potentially expensive SELECT COUNT(*) FROM .... This will be in the next release, which should be available in the near future.

It would still be useful to find out what is going on with the SELECT COUNT(*) FROM ... query.

from firebird_fdw.

As a workaround, and something I've been meaning to do anyway, I've added a table option estimated_row_count, which makes it possible to specify the approximate number of rows the query would return, and side-step the potentially expensive SELECT COUNT(*) FROM .... This will be in the next release, which should be available in the near future.

It would still be useful to find out what is going on with the SELECT COUNT(*) FROM ... query.

In the meantime I've made a new release (1.0.0) which contains this functionality, and also better logging of the issue you encountered; it would be helpful if you could try the query with this release and let me know the result.

from firebird_fdw.

Hello,

Thank you very much. Now with version 1.0.0 worked correctly, follow the debug log attachment
debug2.txt

from firebird_fdw.