Use with replica sets

[ms1111]

Curious if anyone has been able to use mongo_fdw with a replica set and a list of seed servers, and if there's a place where connection error log messages are being written that I'm just not seeing.

• With no replica_set, if I set a single host address option in CREATE SERVER, and the host isn't reachable, a simple SELECT * FROM ... silently returns 0 results after about 500 ms, and there are no error messages written to postgres stdout or stderr.

• With no replica_set, if I set a comma-separated host address option, and the first host is invalid but the second host is reachable, a SELECT * FROM ... works, after about a 500 ms delay, as I'd probably expect.

• With a replica_set, regardless of the address option, whether it has one host or multiple hosts, I get 0 results after about 500 ms.

So it looks like replica_set doesn't work at all, and also connection errors result in silently returning no data rather than a hard failure.

I'm using PostgreSQL 12.0 and mongo_fdw 5.2.6 compiled with --with-master. I've compiled it into a container based on the standard Docker Hub postgres image, so I'm wondering if that messes up the logging somehow.

[esatterwhite]

Has this been confirmed?

[rajkumarraghuwanshi]

Thanks for reporting this. Mentioned issue is fixed with below commit.

`commit 8ce1225
Author: Jeevan Chalke [email protected]
Date: Tue Jul 7 19:36:39 2020 +0530

Check that the existing or new connection is reachable/active or not.

mongoc_client_new() function does not return NULL when it fails to
connect with the server.  So we need to ping the server to make sure
that connection is reachable with the parameters given.

Along the way, move the connection establishment logic from
MongoExecForeignInsert/Update/Delete functions to the
MongoBeginForeignModify() function so that we won't try to ping the
server for every insert/update/delete operations.

FDW-127, Vaibhav Dalvi, reviewed by Suraj Kharage.`

Also I have verified replica_set option with 2 mongo servers and it is working as expected. below is some use cases which I verified with replica_set

`Connect to Secondary Mongo Server and Perform select & DML. only select should pass as Secondary is read only.

edb=# create server ms foreign data wrapper mongo_fdw options (address 'localhost', port '27018');
CREATE SERVER
edb=# create user mapping for public server ms;
CREATE USER MAPPING
edb=# create foreign table ft2 (_id name, name varchar) server ms options (database 'edb', collection 'testrep');
CREATE FOREIGN TABLE
edb=# select * from ft2;
_id | name
--------------------------+--------------------------------
6059a1d7a4e2f62e482343e3 | data inserted on primary 27017
6059aa6b2757a54bc62b4038 | Insert via FDW in Primary
(2 rows)

edb=# insert into ft2 values ('2','Insert via FDW in Secondary');
ERROR: failed to insert row
HINT: Mongo error: "not master"
edb=# select * from ft2;
_id | name
--------------------------+--------------------------------
6059a1d7a4e2f62e482343e3 | data inserted on primary 27017
6059aa6b2757a54bc62b4038 | Insert via FDW in Primary
(2 rows)

Now connect to Secondary using replica_set option, as per ReadMe description DML should also pass now since using replica_set this will connect to Primary Server and run DML there.

edb=# create server msr foreign data wrapper mongo_fdw options (address 'localhost', port '27018', replica_set 'mongoreplication');
CREATE SERVER
edb=# create user mapping for public server msr;
CREATE USER MAPPING
edb=# create foreign table ft3 (_id name, name varchar) server msr options (database 'edb', collection 'testrep');
CREATE FOREIGN TABLE
edb=# select * from ft3;
_id | name
--------------------------+--------------------------------
6059a1d7a4e2f62e482343e3 | data inserted on primary 27017
6059aa6b2757a54bc62b4038 | Insert via FDW in Primary
(2 rows)

edb=# insert into ft3 values ('2','Insert via FDW in Secondary via replica_set');
INSERT 0 1
edb=# select * from ft3;
_id | name
--------------------------+---------------------------------------------
6059a1d7a4e2f62e482343e3 | data inserted on primary 27017
6059aa6b2757a54bc62b4038 | Insert via FDW in Primary
6059aaaf2757a54bc62b403c | Insert via FDW in Secondary via replica_set
(3 rows)
`