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

Add check constraint to your table where appropriate and wait for the (inevitable) error from the DB. — Milen A. Radev
– Milen A. Radev, Commented May 19, 2009 at 21:44
Is this multi-threaded? Do you use asynchronous pg calls? Are you reusing your input buffers? — Nikolai Fetissov
– Nikolai Fetissov, Commented May 20, 2009 at 2:17

dwc · Accepted Answer · 2009-05-24 16:14:38Z

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.

answered May 24, 2009 at 16:14

dwc

25k7 gold badges47 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

whatWhat Over a year ago

I'm building the SQL strings each time and executing, I'm also using the string escaping function provided by libpq. Do you know if it's possible to programatically add a constraint via libpq?

dwc Over a year ago

It's possible, but you may want to put on your DBA hat and do it outside of your code. Either way, see postgresql.org/docs/current/static/sql-altertable.html for ALTER TABLE syntax, which often refers to postgresql.org/docs/current/static/sql-createtable.html. Search for "constraint" on either/both pages.

Collectives™ on Stack Overflow

postgresql libpq inserting empty row for no reason

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related