Inserting multiple data items can trigger failure loop

[awrichar]

I'm seeing a failure loop while FireFly is attempting to insert data items. The batch insert fails because one of the data items was already received before, and this causes the database transaction to be rolled back. FireFly then tries to fall back to individual upserts, but they fail because the transaction is already aborted.

[2023-02-28T02:34:57.160Z] ERROR SQL insert failed (conflictEmptyRequested=false): pq: duplicate key value violates unique constraint "data_id" sql=[ INSERT INTO data (id,validator,namespace,datatype_name,datatype_version,hash,created,blob_hash,blob_public,blob_name,blob_size,public,value_size,value) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)  RETURNING seq ]: pq: duplicate key value violates unique constraint "data_id" dbtx=ESmXsgzC ns=HDWalletNamespace pid=1 role=event-manager
[2023-02-28T02:34:57.160Z] DEBUG Batch data insert optimization failed for batch '180002a9-94aa-4372-8858-4cd45504166c': FF00177: Database insert failed: pq: duplicate key value violates unique constraint "data_id" dbtx=ESmXsgzC ns=HDWalletNamespace pid=1 role=event-manager
[2023-02-28T02:34:57.161Z] DEBUG SQL-> update data dbtx=ESmXsgzC ns=HDWalletNamespace pid=1 role=event-manager
[2023-02-28T02:34:57.161Z] ERROR SQL update failed: pq: current transaction is aborted, commands ignored until end of transaction block sql=[ UPDATE data SET validator = $1, datatype_name = $2, datatype_version = $3, hash = $4, created = $5, blob_hash = $6, blob_public = $7, blob_name = $8, blob_size = $9, public = $10, value_size = $11, value = $12 WHERE hash = $13 AND id = $14 AND namespace = $15 ] dbtx=ESmXsgzC ns=HDWalletNamespace pid=1 role=event-manager

I do see that when InsertDataArray() is called on a database that does not support MultiRowInsert, the code branch that is triggered specifically passes false for requestConflictEmptyResult (meaning that any row which fails to insert due to a key conflict should trigger a failure and rollback of the database transaction):

firefly/internal/database/sqlcommon/data_sql.go

Line 215 in 091bc22

_, err := s.attemptDataInsert(ctx, tx, data, false)

This would trigger the behavior above, and seems like a bug (ie we should likely pass requestConflictEmptyResult = true here).

However, I'm using Postgres here, which should support MultiRowInsert, and therefore should be using the branch that already (correctly) sets requestConflictEmptyResult = true... so there's a second layer of mystery.

[awrichar]

#1198 fixes the root issue here (the fact that the transaction is aborted when the optimized query fails, instead of allowing a graceful fallback)

#1197 actually addresses the related issue that I've filed separately as #1199 (the fact that Postgres databases are currently misconfigured)