prepared statement cann't push down.

[digoal]

ONE error:

acmcoder=# analyze "wb_Cands";
ERROR:  XX000: could not retrieve document count for collection
HINT:  could not        collect statistics about foreign table
LOCATION:  MongoAnalyzeForeignTable, mongo_fdw.c:1887

another problem :

acmcoder=# prepare a (int) as select "confirmState" from "wb_Cands" where id=$1;
PREPARE
acmcoder=# explain (verbose,analyze,costs,timing,buffers) execute a(4392325);                                    
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public."wb_Cands"  (cost=0.00..0.00 rows=1000 width=4) (actual time=0.698..0.699 rows=1 loops=1)
   Output: "confirmState"
   Filter: ("wb_Cands".id = 4392325)
   Foreign Namespace: hello-world.Cands
 Execution time: 0.761 ms
(5 rows)
.....
begin the 5th : 
the query filter not push down?
acmcoder=# explain (verbose,analyze,costs,timing,buffers) execute a(4392325);
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public."wb_Cands"  (cost=0.00..0.00 rows=1000 width=4) (actual time=7333.681..7744.454 rows=1 loops=1)
   Output: "confirmState"
   Filter: ("wb_Cands".id = $1)
   Rows Removed by Filter: 371799
   Foreign Namespace: hello-world.Cands
 Execution time: 7744.525 ms
(6 rows)

[ahsanhadi]

Yeah this is a unsupported feature because mongodb doesn't support placeholders as far as we have looked. We will keep this item open as a feature request and revisit it later.

[lifubang]

Thank you, @digoal @ahsanhadi how to fix this problem as quickly as possible, because we use mongo_fdw in online systems now. If can't, can you give me a replace solution?

[lifubang]

@ahsanhadi why it works normally in the first 4 queries?

[digoal]

@lifubang because postgresql use custom scan for the first 4 queries.

[lifubang]

@digoal

because postgresql use custom scan for the first 4 queries.

It's postgresql's limit or mongo_fdw's limit? If "For the first 4 queries" defined in mongo_fdw, it'll be fixed more easier.

[lifubang]

@ahsanhadi , before you fix the problem, I have got a solution to avoid this bug.

Double r = Math.random();
String sql = String.format(" select \"confirmState\" from \"wb_Cands_Writeable\" where id=? and %#.18f=%#.18f", r, r);