Duplicate key values error is not thrown as exception and is getting ignored

[flyninjacode]

I'm trying to capture 'Duplicate key values error ' message if there is any insert statement that is violating the primary key constraint. In the following code I created a 'demo' table with 'demo_id' as the primary_key and tried to insert the same row twice. Example: `connection = v.connect() cursor = connection.cursor() cursor.execute(""" DROP TABLE IF EXISTS demo; CREATE TABLE demo ( demo_id integer primary key enabled, code varchar(20), quantity smallint, amount money, product_id int, demo_date datetime); """) cursor.execute("""
INSERT INTO demo SELECT 1,'RT0132',1,100,10023,now();

""") cursor.execute(""" INSERT INTO demo SELECT 1,'RT0132',1,100,10023,now();

""") connection.commit()` For the above code I would expect it to throw an Exception like "Message: b"Duplicate key values: 'demo_id=1' -- violates constraint", but it doesn't. Is there an existing solution to handle this case or can this issue be resolved by doing some quick workaround ?

I'm using Python 3.5.2 and vertica_python 0.8.2 versions.

[sitingren]

@flyninjacode Thanks for reporting this problem. I don't know if there is an existing solution, but the quick workaround I think is to call cursor.fetchall() after your second INSERT execution. I'll do further investigation and let you know if there is an update.

[sitingren]

Here are protocol messages that are sending(=>) and receiving(<=) during those two INSERT executions:

=> Query: First "INSERT INTO demo SELECT 1,'RT0132',1,100,10023,now();"
<= RowDescription
<= ParameterStatus
<= ParameterStatus
<= ParameterStatus
<= DataRow: the number of accepted rows = 1  NOTE: cursor.execute() returned at here
<= CommandComplete
<= ReadyForQuery

=> Query: Second "INSERT INTO demo SELECT 1,'RT0132',1,100,10023,now();"
<= RowDescription
<= ParameterStatus
<= ParameterStatus
<= ParameterStatus
<= DataRow: the number of accepted rows = 1  NOTE: cursor.execute() returned at here
<= ErrorResponse: "Duplicate key values: 'demo_id=1' -- violates constraint 'public.demo.C_PRIMARY'"
<= ReadyForQuery

The logic of cursor.execute() makes the function stop reading remaining protocol messages if it receives a DataRow message. But since the client doesn't receive a CommandComplete message, that means the server is still running the query and errors can be thrown afterward.

When you run cursor.execute(), the client will read and ignore all remaining protocol messages of the previous command cycle, even an ErrorResponse. If you run cursor.fetchall(), you can iterate through all remaining protocol messages and catch those ErrorResponse.

So your expected behavior need a change of cursor.execute() to read a few more messages after a DataRow message is received. But this would also affect the logic of cursor.fetch*(). We'll see if it is easy to fix.

[roveo]

You can do this

cursor.execute(query)
cursor.fetchall()
while cursor.nextset():
    cursor.fetchall()

It will raise an exception for each error associated with a result set (tested only with user-generated error).

EDIT: tested with .fetchall() instead of .fetchone() and it raises the expected exception. Updated the code.