Crash on select via JDBC connection

[brantg]

Working via a psql console works fantastically, but as soon as I connect programatically (or via Squirrel, so still JDBC), it crashes my PostgreSQL server, every time.

Server OS is:

Linux jupiter 3.13.0-46-generic #75-Ubuntu SMP Tue Feb 10 15:24:04 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

PostgreSQL version is:

PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

Commands prior:

create extension mongo_fdw;
create server mongo_server foreign data wrapper mongo_fdw options (address '127.0.0.1', port '27017');
create foreign table test (_id text, name text, role text) server mongo_server options (database 'edge', collection 'test');

Contents of the target mongo collection:

> db.test.find()
{ "_id" : ObjectId("550c497fb7e9de3274e2e3db"), "name" : "Brant", "role" : "Report Developer" }
{ "_id" : ObjectId("550c498ab7e9de3274e2e3dc"), "name" : "Mark", "role" : "Manager" }

Relevant log entries:

*** Error in `postgres: mmuser edge41 192.168.0.12(52272) SELECT': free(): invalid size: 0x00007f11614af9d0 ***
2015-03-20 14:21:36 CDT LOG:  server process (PID 32053) was terminated by signal 6: Aborted
2015-03-20 14:21:36 CDT DETAIL:  Failed process was running: select name, role from test
2015-03-20 14:21:36 CDT LOG:  terminating any other active server processes
2015-03-20 14:21:36 CDT WARNING:  terminating connection because of crash of another server process
2015-03-20 14:21:36 CDT 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.
2015-03-20 14:21:36 CDT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-03-20 14:21:36 CDT WARNING:  terminating connection because of crash of another server process
2015-03-20 14:21:36 CDT 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.
2015-03-20 14:21:36 CDT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-03-20 14:21:36 CDT LOG:  all server processes terminated; reinitializing
2015-03-20 14:21:36 CDT LOG:  database system was interrupted; last known up at 2015-03-20 13:55:01 CDT
2015-03-20 14:21:36 CDT LOG:  database system was not properly shut down; automatic recovery in progress
2015-03-20 14:21:36 CDT LOG:  redo starts at E/76537150
2015-03-20 14:21:36 CDT LOG:  record with zero length at E/76543750
2015-03-20 14:21:36 CDT LOG:  redo done at E/76543710
2015-03-20 14:21:36 CDT LOG:  last completed transaction was at log time 2015-03-20 14:20:20.65869-05
2015-03-20 14:21:36 CDT LOG:  database system is ready to accept connections
2015-03-20 14:21:36 CDT LOG:  autovacuum launcher started

Let me know if I can get more supporting info to assist.

[ahsanhadi]

Hi brant,

We will try and get to this issue in the next week or so. Is it possible for you to share your sample JDBC program that we can use to reproduce the crash?

-- Ahsan

[brantg]

Sure thing, here you go:

package com.mmodal.bi;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Application for testing out Foreign Data Wrapper driver on PGSQL.
 */
public class FDWApp {

  public static void main(String[] args) throws SQLException {
    Connection conn = DriverManager.getConnection(
        "jdbc:postgresql://db.servername.local/database", "username", "password");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select name, role from test;");

    while (rs.next()) {
      System.out.println(rs.getString("name") + ", " + rs.getString("role"));
    }
    rs.close();
    stmt.close();
  }
}

[ibrarahmad]

It works fine on my computer, can you please share the stack trace

postgres=# create extension mongo_fdw; CREATE EXTENSION

postgres=# create server mongo_server foreign data wrapper mongo_fdw options (address '127.0.0.1', port '27017'); CREATE SERVER

postgres=# create foreign table test (_id name ,name text, role text) server mongo_server options(database 'test');

postgres=# create user mapping for ibrar SERVER mongo_server; CREATE USER MAPPING

postgres=# INSERT INTO test VALUES(0, 'Brant', 'Report Developer'); postgres=# INSERT INTO test VALUES(0, 'Mark', 'Manager'); postgres=# \q ibrar@ibrar-virtual-machine:/usr/local/pgsql.94/bin$ java -cp .:postgresql-9.4-1201.jdbc41.jar FDWApp

Brant, Report Developer Mark, Manager

[brantg]

Sure, here is the stack trace:

user@host:~/Development/FDWStress$ java -jar FDWStress.jar
Started.
Exception in thread "main" org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:281)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:285)
        at com.mmodal.bi.FDWApp.main(FDWApp.java:25)
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:284)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1741)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        ... 4 more

And then you go look at PostgreSQL's logs and you get this:

*** Error in `postgres: mmuser edge41 192.168.1.110(51061) SELECT': free(): invalid size: 0x00007fcd3ed19f30 ***
2015-04-02 06:54:12 CDT LOG:  server process (PID 19974) was terminated by signal 6: Aborted
2015-04-02 06:54:12 CDT DETAIL:  Failed process was running: select name, role from test
2015-04-02 06:54:12 CDT LOG:  terminating any other active server processes

Next block repeats exactly 70 times:

2015-04-02 06:54:12 CDT WARNING:  terminating connection because of crash of another server process
2015-04-02 06:54:12 CDT 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.
2015-04-02 06:54:12 CDT HINT:  In a moment you should be able to reconnect to the database and repeat your command.

And then this:

2015-04-02 06:54:12 CDT LOG:  all server processes terminated; reinitializing
2015-04-02 06:54:12 CDT LOG:  database system was interrupted; last known up at 2015-04-02 06:34:52 CDT
2015-04-02 06:54:12 CDT LOG:  database system was not properly shut down; automatic recovery in progress
2015-04-02 06:54:12 CDT LOG:  record with zero length at E/7F21B5B8
2015-04-02 06:54:12 CDT LOG:  redo is not required
2015-04-02 06:54:12 CDT LOG:  autovacuum launcher started
2015-04-02 06:54:12 CDT LOG:  database system is ready to accept connections

[ahsanhadi]

It will be great if can share you entire test case. Are you inserting data directory into mongo or using the mongodb_fdw?

I will also try and reproduce on my machine.

On Thu, Apr 2, 2015 at 5:06 PM, brantg notifications@github.com wrote:

Sure, here is the stack trace:

user@host:~/Development/FDWStress$ java -jar FDWStress.jar Started. Exception in thread "main" org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend. at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:281) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:285) at com.mmodal.bi.FDWApp.main(FDWApp.java:25) Caused by: java.io.EOFException at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:284) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1741) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ... 4 more

And then you go look at PostgreSQL's logs and you get this:

* Error in `postgres: mmuser edge41 192.168.1.110(51061) SELECT': free(): invalid size: 0x00007fcd3ed19f30 * 2015-04-02 06:54:12 CDT LOG: server process (PID 19974) was terminated by signal 6: Aborted 2015-04-02 06:54:12 CDT DETAIL: Failed process was running: select name, role from test 2015-04-02 06:54:12 CDT LOG: terminating any other active server processes

Next block repeats exactly 70 times:

2015-04-02 06:54:12 CDT WARNING: terminating connection because of crash of another server process 2015-04-02 06:54:12 CDT 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. 2015-04-02 06:54:12 CDT HINT: In a moment you should be able to reconnect to the database and repeat your command.

And then this:

2015-04-02 06:54:12 CDT LOG: all server processes terminated; reinitializing 2015-04-02 06:54:12 CDT LOG: database system was interrupted; last known up at 2015-04-02 06:34:52 CDT 2015-04-02 06:54:12 CDT LOG: database system was not properly shut down; automatic recovery in progress 2015-04-02 06:54:12 CDT LOG: record with zero length at E/7F21B5B8 2015-04-02 06:54:12 CDT LOG: redo is not required 2015-04-02 06:54:12 CDT LOG: autovacuum launcher started 2015-04-02 06:54:12 CDT LOG: database system is ready to accept connections

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mongo_fdw/issues/27#issuecomment-88879476 .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

[brantg]

Not sure what you mean, the above code is the only test case I've tried since it didn't work. It crashes my PostgreSQL server literally every time I try the code.

To answer your question I inserted the test data directly into Mongo using the mongo shell. SELECT works for me in the psql shell:

edge41=# \d test
       Foreign table "public.test"
 Column | Type | Modifiers | FDW Options 
--------+------+-----------+-------------
 _id    | text |           | 
 name   | text |           | 
 role   | text |           | 
Server: mongo_server
FDW Options: (database 'edge', collection 'test')

edge41=# select * from test;
 _id | name  |       role       
-----+-------+------------------
     | Brant | Report Developer
     | Mark  | Manager
(2 rows)

...but INSERT does not:

edge41=# insert into test (name,role) values ('Test','Test');
ERROR:  cannot insert into foreign table "test"

...so I did not use mongdb_fdw for data insertion.

Let me know if I can get anything else for you.

[brantg]

It occurs to me I never gave my Mongo version which I imagine is important:

> version()
3.0.1

[ibrarahmad]

Where you have downloaded the mongo_fdw, from git site or PGXN?. I think you are using older version downloaded from PGXN.

[brantg]

Well, I had thought it was from git but the filename doesn't match (my download is mongo_fdw-3.0.tar.gz) so I have to assume you are correct.

All right, when I have time I will back everything out and start over. Thanks for the assistance, I appreciate it.

[ahsanhadi]

The EnterpriseDB mongodb_fdw on github includes support for writing to mongodb foreign table so this error that you posted earlier doesn't make sense..

edge41=# insert into test (name,role) values ('Test','Test'); ERROR: cannot insert into foreign table "test"

On Thu, Apr 2, 2015 at 6:24 PM, brantg notifications@github.com wrote:

Well, I had thought it was from git but the filename doesn't match (my download is mongo_fdw-3.0.tar.gz) so I have to assume you are correct.

All right, when I have time I will back everything out and start over. Thanks for the assistance, I appreciate it.

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mongo_fdw/issues/27#issuecomment-88895502 .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

[brantg]

Yeah, that puzzled me, too. Ibrarahmad's suggestion that I've managed to get the wrong "3.0" copy must be correct.

[brantg]

Okay, I'm confused now. I downloaded the tar.gz here, from github and the contents are what I installed. I re-checked all the files and read through the README and as far as I can tell I did follow the procedures properly for installing it.

Clearly I've missed something, what can I provide to help sort this out?

[ibrarahmad]

Please try new release REL-4_0_0

On Thu, Apr 2, 2015 at 8:57 PM, brantg notifications@github.com wrote:

Okay, I'm confused now. I downloaded the tar.gz here, from github and the contents are what I installed. I re-checked all the files and read through the README and as far as I can tell I did follow the procedures properly for installing it.

Clearly I've missed something, what can I provide to help sort this out?

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mongo_fdw/issues/27#issuecomment-88958010 .

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

[ahsanhadi]

Please let us know if the issue is reproducible with the latest release.

[brantg]

My apologies for the lack of response, I will test with the latest release and update this thread with my results when I am able. Thank you.