search

ibarwick / firebird_fdw

A PostgreSQL foreign data wrapper (FDW) for Firebird - latest version 1.4.0 (2024-05-11)
https://sql-info.de/postgresql/firebird-fdw/index.html
Other
36 stars 9 forks source link

Foreign table with the option 'query' #7

Closed ms3rgio closed 5 years ago

ms3rgio commented 5 years ago

When I create the foreign table with the option 'query' I get the error:    ERROR: Query returned unexpected number of rows Ps: The query returns thousands of rows.

ibarwick commented 5 years ago

Some basic details would be useful:

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

ms3rgio commented 5 years ago

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

ibarwick commented 5 years ago

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.

ibarwick commented 5 years ago

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.

ms3rgio commented 5 years ago

Hello,

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