postgresql libpq inserting empty row for no reason

Question

I'm using the libpq library in C for accessing my Postgresql database. The application inserts a piece of data fed from a queue. When there is a lot of data and it's inserting very quickly it randomly inserts and empty row into the table. Before I even perform the insert I check to make sure that the length of the text being inserted is greater then one. Is there a reason why this is randomly happening? It dosen't happen when there is less data.

*I'd like to note, that this does not happen on Mysql, only Postgresql

Answer 1

See Milen A. Radev's comment. It should have been an answer. You should not allow empty rows.

Surely at least one column can have a constraint that would cause the insert to fail. Then your app can print/log the error with enough diagnostics for you to figure out what's going on, and under what conditions.

Without retrofitting the above, determine if all data from your queue was inserted properly, or if some rows are missing. I.e., see if some rows are being translated into empty inserts. If you see that some data is causing this you can find what the problem data has in common.

Are you using prepared, parameterized inserts, or are you building a SQL insert statement string each time and executing that? If you're building SQL strings to execute then you must make sure you are quoting character/binary string columns properly with the routines provided by libpq. Or switch to the other method of preparing the insert and passing the data as parameters where it can be properly quoted by libpq itself. This may also improve performance.