goodybag
Queries keep being executed after transaction aborted
Hello, I have a problem handling errors in transactions.
The use case is:
- upload of image + meta data
- creation of digest of the image to be used as image id
- insert into postgres the image id,path and the metadata
the step number 3 require a set of insert/update that must be executed atomically.
The issues occurs when a query in the transaction fails
and transactions is aborted.
What happens is that even if an error occurs and the
transaction get closed, the remaining queries get executed
anyway and (as expected) they fail one after another
because the transaction is aborted.
At the end also the tx.commit() get executed.
here is the sample code:
var pg = require('pg');
var Transaction = require('pg-transaction')
var crypto = require('crypto');
var fs = require('fs');
/* .... */
exports.newPhoto = function(req, res, next) {
try{
var stuff = req.body.hasOwnProperty("stuff") ? req.body.stuff.split("|") : [];
var create_time = new Date(req.body.hasOwnProperty("time")? parseInt(req.body.time) : 0);
var ip_address = req.connection.remoteAddress;
var file = req.files.img;
// create file hash
var photohash = crypto.createHash('sha256');
var s = fs.ReadStream(file.path);
s.on('data', function(d) { photohash.update(d);});
s.on('error', function(err) {if(err) {return next(err); }});
s.on('end', function() {
var photo_id = photohash.digest('hex');
// create db client
var client = new pg.Client(conString);
client.connect()
var tx = new Transaction(client);
// error handling
// !!! this gets executed but queries keep going
tx.on("error", function(err){if(err){tx.rollback(); return next(err);} });
// start the transaction
tx.begin();
// insert values into database
// run all the queries
// ....
// all queries executed, commit the transaction
tx.commit(function(error){
console.log("error is: ");
console.log(error);
if(!error){
res.jsonp({"status":"OK"});
}
});
// is this necessary?
// client.end()
});
} catch (err){
console.error(err.stack);
res.send(500, 'KABOOOM!!');
}
}
And here there is the stack trace when I try to upload two times the same photo:
connect.multipart() will be removed in connect 3.0
visit https://github.com/senchalabs/connect/wiki/Connect-3.0 for alternatives
connect.limit() will be removed in connect 3.0
Listening on port 9871...
error: duplicate key value violates unique constraint "photos_pkey"
at Connection.parseE (/usr/local/lib/node_modules/pg/lib/connection.js:561:11)
at Connection.parseMessage (/usr/local/lib/node_modules/pg/lib/connection.js:390:17)
at null.<anonymous> (/usr/local/lib/node_modules/pg/lib/connection.js:98:18)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:746:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)
error: current transaction is aborted, commands ignored until end of transaction block
at Connection.parseE (/usr/local/lib/node_modules/pg/lib/connection.js:561:11)
at Connection.parseMessage (/usr/local/lib/node_modules/pg/lib/connection.js:390:17)
at null.<anonymous> (/usr/local/lib/node_modules/pg/lib/connection.js:92:20)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:746:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)
error: current transaction is aborted, commands ignored until end of transaction block
at Connection.parseE (/usr/local/lib/node_modules/pg/lib/connection.js:561:11)
at Connection.parseMessage (/usr/local/lib/node_modules/pg/lib/connection.js:390:17)
at null.<anonymous> (/usr/local/lib/node_modules/pg/lib/connection.js:92:20)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:746:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)
error: current transaction is aborted, commands ignored until end of transaction block
at Connection.parseE (/usr/local/lib/node_modules/pg/lib/connection.js:561:11)
at Connection.parseMessage (/usr/local/lib/node_modules/pg/lib/connection.js:390:17)
at null.<anonymous> (/usr/local/lib/node_modules/pg/lib/connection.js:92:20)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:746:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)
error: current transaction is aborted, commands ignored until end of transaction block
at Connection.parseE (/usr/local/lib/node_modules/pg/lib/connection.js:561:11)
at Connection.parseMessage (/usr/local/lib/node_modules/pg/lib/connection.js:390:17)
at null.<anonymous> (/usr/local/lib/node_modules/pg/lib/connection.js:92:20)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:746:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)
error: current transaction is aborted, commands ignored until end of transaction block
at Connection.parseE (/usr/local/lib/node_modules/pg/lib/connection.js:561:11)
at Connection.parseMessage (/usr/local/lib/node_modules/pg/lib/connection.js:390:17)
at null.<anonymous> (/usr/local/lib/node_modules/pg/lib/connection.js:92:20)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:746:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)
error is:
null
http.js:691
throw new Error('Can\'t set headers after they are sent.');
^
Error: Can't set headers after they are sent.
at ServerResponse.OutgoingMessage.setHeader (http.js:691:11)
at ServerResponse.res.setHeader (/usr/local/lib/node_modules/express/node_modules/connect/lib/patch.js:63:22)
at ServerResponse.res.set.res.header (/usr/local/lib/node_modules/express/lib/response.js:527:10)
at ServerResponse.res.jsonp (/usr/local/lib/node_modules/express/lib/response.js:238:8)
at /[LOCAL SCRITPT PATH]/myroute.js:132:25
at null.callback (/usr/local/lib/node_modules/pg-transaction/lib/index.js:77:26)
at Query.handleReadyForQuery (/usr/local/lib/node_modules/pg/lib/query.js:84:10)
at null.<anonymous> (/usr/local/lib/node_modules/pg/lib/client.js:159:19)
at EventEmitter.emit (events.js:117:20)
at null.<anonymous> (/usr/local/lib/node_modules/pg/lib/connection.js:97:12)
Package versions:
- node.js v0.10.26
- express.js v3.5.1
- pg v2.11.1
- pg-transaction v.1.0.2
Postgre v 9.3.1.0
I may be wrong here, but it looks like you're having a single error emitted from pg. Your issue I think is that you're looking for errors in two places and the way you're responding to them are at odds with each other.
tx.commit( function( err ){ /* ... */ } ) is looking for errors and sending data to the response stream and you've also got tx.on( 'error', function( err ){ /* ... */ } ) calling next( err ) which will also write to the response stream.
@lalitkapoor thoughts?
@noandrea Try managing all your error handling in just the tx.commit callback
Also to prevent executing queries after a bad one in a transaction I recommend using something like async or promises to help with error handling at each step. Take a look here: https://github.com/goodybag/node-pg-transaction/blob/master/test/index.js#L161
I'll think about a better way to manage this. Welcome any suggestions! :)