Node-postgres: Not working with large SQL : Error: Connection terminated unexpectedly

[andrew-medvedev]

I'm trying to query a large SQL file (~65000 lines) and constantly getting Error: Connection terminated unexpectedly after 2 min of execution.

Here is code:

'use strict';

var fs = require('fs'),
    pg = require('pg'),
    Pool = pg.Pool;

(async () => {
    var pool = new Pool({
        database: 'some database',
        user: 'app',
        password: 'some password',
        host: 'some host',
        port: 9999,
        ssl: true,
        max: 20, // set pool max size to 20
        min: 4, // set min pool size to 4
        idleTimeoutMillis: 10000, // close idle clients after 1 second
        connectionTimeoutMillis: 10000, // return an error after 1 second if connection could not be established
    });

    var client = await pool.connect();

    pool.on('error', error => {
        console.error('error event on pool (%s)', error);
    });

    try {
        await client.query('BEGIN');


        const queryText = fs.readFileSync('./v2-game_data.sql') + '';


        const res = await client.query(queryText)


        await client.query('COMMIT')

    } catch (e) {
        await client.query('ROLLBACK')
        throw e
    } finally {
        client.release();
    }
})();

And here is error:

events.js:174
      throw er; // Unhandled 'error' event
      ^

Error: Connection terminated unexpectedly
    at Connection.con.once (C:\Users\ihatemicrosoft1\Desktop\backend\node_modules\pg\lib\client.js:252:9)
    at Object.onceWrapper (events.js:286:20)
    at Connection.emit (events.js:198:13)
    at TLSSocket.<anonymous> (C:\Users\ihatemicrosoft1\Desktop\backend\node_modules\pg\lib\connection.js:131:10)
    at TLSSocket.emit (events.js:203:15)
    at endReadableNT (_stream_readable.js:1145:12)
    at process._tickCallback (internal/process/next_tick.js:63:19)
Emitted 'error' event at:
    at connectedErrorHandler (C:\Users\ihatemicrosoft1\Desktop\backend\node_modules\pg\lib\client.js:199:10)
    at Connection.con.once (C:\Users\ihatemicrosoft1\Desktop\backend\node_modules\pg\lib\client.js:268:9)
    at Object.onceWrapper (events.js:286:20)
    [... lines matching original stack trace ...]
    at process._tickCallback (internal/process/next_tick.js:63:19)

UPD

With pg-native the error is:

events.js:174
      throw er; // Unhandled 'error' event
      ^

Error: SSL SYSCALL error: EOF detected

    at module.exports.Client._readError (C:\Users\ihatemicrosoft1\Desktop\backend\node_modules\pg-native\index.js:154:13)
    at module.exports.Client._read (C:\Users\ihatemicrosoft1\Desktop\backend\node_modules\pg-native\index.js:203:17)
    at PQ.emit (events.js:198:13)
Emitted 'error' event at:
    at module.exports.<anonymous> (C:\Users\ihatemicrosoft1\Desktop\backend\node_modules\pg\lib\native\client.js:101:14)
    at module.exports.emit (events.js:198:13)
    at module.exports.Client._readError (C:\Users\ihatemicrosoft1\Desktop\backend\node_modules\pg-native\index.js:155:8)
    at module.exports.Client._read (C:\Users\ihatemicrosoft1\Desktop\backend\node_modules\pg-native\index.js:203:17)
    at PQ.emit (events.js:198:13)

How to make it work?

[vitaly-t]

Why won't you run it through psql? That's what people use to execute large SQL dumps, not a driver for executing queries, because it's kind of pointless.

[andrew-medvedev]

I really believe that there is a vast amount of other methods of doing this job. And I can understand if it's pointless for you, but it doesn't mean that it pointless for me. I have tasks and they have to be accomplished in exact way. So my answer will be like because of reasons

[sehrope]

If the SQL file contains basic DML commands (ex: INSERT, UPDATE, etc) then unless you hit some kind of memory issue in node, it should work fine.

If the SQL file contains COPY commands with inline data this will not work. The COPY protocol requires special handling and changes the client protocol handlers to expect a copy data stream.

[vitaly-t]

@andrew-medvedev As sehrope stated, most likely you have commands in there that cannot be executed through a single stream, which means use of psql is inevitable.

Also, it is very unlikely that the size of the file got anything to do with the issue. You can easily check that - compress SQL through pg-minify, and execute the resulting (smaller) SQL. Most likely the result will be the same, which will then confirm the above.

[andrew-medvedev]

Btw the issue is just in not enough memory. Script ran against 1gb droplet and upgrading up to 2gb helped out.

So for everybody who will encounter similar proplem: try use pg-native - at least it will give you more informative error like Error: SSL SYSCALL error: EOF detected.