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.
我正在使用C中的libpq库来访问我的Postgresql数据库。应用程序插入从队列馈送的数据。当有大量数据并且插入速度很快时,它会随机插入并清空行。在我执行插入之前,我检查以确保插入的文本的长度大于1。这是随机发生的原因吗?当数据较少时,它不会发生。
*I'd like to note, that this does not happen on Mysql, only Postgresql
*我想注意,这不会发生在Mysql上,只有Postgresql
1 个解决方案
#1
See Milen A. Radev's comment. It should have been an answer. You should not allow empty rows.
见Milen A. Radev的评论。应该是一个答案。你不应该允许空行。
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.
您是使用准备好的参数化插入,还是每次构建SQL插入语句字符串并执行它?如果要构建要执行的SQL字符串,则必须确保使用libpq提供的例程正确引用字符/二进制字符串列。或者切换到另一种准备插入的方法,并将数据作为参数传递给libpq本身正确引用。这也可以提高性能。
#1
See Milen A. Radev's comment. It should have been an answer. You should not allow empty rows.
见Milen A. Radev的评论。应该是一个答案。你不应该允许空行。
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.
您是使用准备好的参数化插入,还是每次构建SQL插入语句字符串并执行它?如果要构建要执行的SQL字符串,则必须确保使用libpq提供的例程正确引用字符/二进制字符串列。或者切换到另一种准备插入的方法,并将数据作为参数传递给libpq本身正确引用。这也可以提高性能。