pg使用mongo_fdw外部表时，如果外部mongo重启，则pg无法自动重连mongo，pg也必须重启。

lifubang commented 8 years ago

mongo重启前，各种外部表查询都正常。

root@937118f50889:~# psql -U postgres -d acmcoder -h 127.0.0.1 -p 5432
Password for user postgres: 
psql (9.3.4)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

acmcoder=# SELECT * FROM testmongo;
           _id            | id | v 
--------------------------+----+---
 5770ed26a65e4cd5c9bf5ffc |  1 | 1
 5770f0c8b6fc52594ac90541 |  2 | 2
 5770f6a690a120b540fb759e |  6 | 3
(3 rows)

kill mongo服务器进程后，则无法查询，这个正常：

acmcoder=# SELECT * FROM testmongo;
 _id | id | v 
-----+----+---
(0 rows)

关键是mongo服务器重新启动后，查询还不正常：

acmcoder=# SELECT * FROM testmongo;
 _id | id | v 
-----+----+---
(0 rows)

pg的日志如下：

2016-06-28 01:18:33 UTC LOG: database system was interrupted; last known up at 2016-06-27 12:53:32 UTC 2016-06-28 01:18:33 UTC LOG: database system was not properly shut down; automatic recovery in progress 2016-06-28 01:18:33 UTC LOG: record with zero length at 0/3514218 2016-06-28 01:18:33 UTC LOG: redo is not required 2016-06-28 01:18:33 UTC LOG: database system is ready to accept connections 2016-06-28 01:18:33 UTC LOG: autovacuum launcher started 2016-06-28 01:19:23 UTC LOG: could not receive data from client: Connection reset by peer 2016-06-28 01:19:23 UTC LOG: could not receive data from client: Connection reset by peer 2016-06-28 01:19:23 UTC LOG: could not receive data from client: Connection reset by peer 2016-06-28 01:19:29 UTC LOG: could not receive data from client: Connection reset by peer WARNING: mongo_connect() is deprecated, please use mongo_client() 2016-06-28 01:20:21 UTC LOG: could not receive data from client: Connection reset by peer 2016-06-28 01:20:21 UTC LOG: could not receive data from client: Connection reset by peer

如果此时pg服务器也重启，则查询又正常了：

acmcoder=# SELECT * FROM testmongo;
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Succeeded.
acmcoder=# SELECT * FROM testmongo;
           _id            | id | v 
--------------------------+----+---
 5770ed26a65e4cd5c9bf5ffc |  1 | 1
 5770f0c8b6fc52594ac90541 |  2 | 2
 5770f6a690a120b540fb759e |  6 | 3
(3 rows)

acmcoder=#

https://yq.aliyun.com/ask/31615?spm=5176.8067842.tagmain.15.au081j

ahsanhadi commented 8 years ago

So if you restart mongo_db, the PG server also needs to restarted or you don't get any results from mongo_fdw tables? I haven't run into that issue before, i will try and reproduce the issue and try and get a fix prioritised asap.

lifubang commented 8 years ago

yes, you are right. thanks. I will also try to fix this problem if I can.

EnterpriseDB / mongo_fdw