On Sun, Aug 25, 2013 at 3:15 AM, Korisk <korisk@yandex.ru> wrote:
Hi! I want quick insert into db a lot of data (in form of triplets). Data is formed dynamical so "COPY" is not suitable. I tried batch insert like this:
insert into triplets values (1,1,1); insert into triplets values (1,1,1), (3,2,5), (4,5,5); ... insert into triplets values (1,1,1), (3,2,5), (4,5,5) .... ;
The more triplets I use the quicker operation is. With preparation it looks like this:
res = PQprepare(conn, "qu", "insert into triplets values ($1::bigint, $2::bigint, $3::float);",3, NULL); ... res = PQprepare(conn, "qu", "insert into triplets values ($1::bigint, $2::bigint, $3::float), ($4::bigint, $5::bigint, $6::float), ($7::bigint, $8::bigint, $9::float), ($10::bigint, $11::bigint, $12::float);",12, NULL); ...
The question: Is there any way to prepare query with any number of triplets without casting such a long string?
Let every thread that is generating data create a file on disk (at some predetermined directory) to which the tread would write data in a format (such as CSV, TSV) that COPY can use.
Use some sort of counter that would be updated for each write of a record to this file.
Then provide a counter threshold which when the value of your counter reaches (surpasses) your thread will first generate a COPY command and send out the contents of this file via the COPY command to your DB. Now delete the contents of the file and write the record into it.
You may also write some code to do on demand writing of the contents of this file to the DB when some event such as an indication to terminate the application happens.
All the above steps are to be performed within each thread of you application in isolation.
You may also write some clean up code that would look for the existence of these files when you application starts and writes the the contents to the DB (followed by the deletion of the files), this is done to cater for situations where your application may not have gracefully shutdown.
