Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Copy command ends successfully before a constraint violation is raised

I'm using Vertica Analytic Database v7.2.3-0 and the node-vertica package (https://github.com/wvanbergen/node-vertica) in my node js project. 

 

I execute a copy command with code like:

var copy = connection.connection.copy(copyCommand, function (transfer, success, fail) {
stream.on('data', function (data) {
transfer(data);
});
stream.on('end', function (data) {
if (data) {
transfer(data);
}
success(function() {
connection.done();
});
asyncCallback(null, {"result": {"name": tableName, "status": "Data loaded successfully"}} );

});
stream.on('error', function (err) {
fail(err, function() {
connection.done();
});

var errorMsg = {"table": tableName, detail: err };
asyncCallback(errorMsg);
});
stream.write(new Buffer(data));
stream.end();
});

copy.on('error', function (err) {
var errorMsg = {"table": tableName, detail: err.message };
asyncCallback(errorMsg);
connection.done();
return;
});

And my copy command is (of course with the right substitutions):

copy   "<%=tableName%>"  ( <%=columnNames%> )  from stdin rejected data as table "<%=rejected%>" delimiter '|' skip 1  ENCLOSED BY  '"' ;

I noticed that when there's a primary key constraint violation, the 'end' event gets emitted first, then several milliseconds later, the "error" event gets emitted. The problem is, when the 'end' event is received by my code, it calls the callback and says everything went fine. When the 'error' event is emitted, it's the copy error event handler which catches it, but the callback has already been called and so the fact that the data did not get inserted is ignored.

 

The error that I get is:

[ Err { [Error: Duplicate key values: 'apiEventId=513490b0-6fee-4fa8-a562-ebbda13b6321' -- violates constraint 'events.worker_event.C_PRIMARY']#012  message: 'Duplicate key values: \'apiEventId=513490b0-6fee-4fa8-a562-ebbda13b6321\' -- violates constraint \'events.worker_event.C_PRIMARY\'',#012  fields: #012   { Severity: 'ERROR',#012     Code: '23505',#012     Message: 'Duplicate key values: \'apiEventId=513490b0-6fee-4fa8-a562-ebbda13b6321\' -- violates constraint \'events.worker_event.C_PRIMARY\'',#012     File: '/scratch_a/release/svrtar28745/vbuild/vertica/Session/ConstraintEnforcement.cpp',#012     Line: '1299',#012     Routine: 'raiseConstraintValidationError' },#012  code: '23505' }#012undefined ]

I've examined the node-vertica code, but I don't think it's causing the problem. 

 

My question is, is there something I need to do when I receive an 'end' event to verify that there are no errors? I tried putting a nextTick() in the 'end' listener, but it's not enough. I don't think waiting around a certain amount of time would be a good way to go either. Any suggestions?

 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.