Thread: Howto use "COPY FROM" with the native API?
Hi, We are working on an university project for network traffic accounting. We use ulogd-2 to log netfilter packets, however it creates for each packet a seperate transaction and also doesn't use prepared statements, which results in horrible performance. What we are looking for is a low-overhead way of inserting many rows (100-1000) into a table at once in one transaction. Is "COPY FROM STDIN" a good idea in this case? If so, are there some examples how to use "COPY FROM STDIN" with the native C API? I found some documentation, however as I am no C native some sample code would really help. Thank you in advance, Clemens PS: Sorry for the cross post, I accidentially sent it to the jdbc list before.
On Mon, 11 Apr 2011 11:21:16 -0400, Clemens Eisserer <linuxhippy@gmail.com> wrote: > Hi, > > We are working on an university project for network traffic accounting. > We use ulogd-2 to log netfilter packets, however it creates for each > packet a seperate transaction and also doesn't use prepared > statements, which results in horrible performance. > > What we are looking for is a low-overhead way of inserting many rows > (100-1000) into a table at once in one transaction. > Is "COPY FROM STDIN" a good idea in this case? For that many, you can probably get away with insert into foo values (1, 2, 3), (4, 5, 6) ..rather than having to figure out the "COPY FROM" API. Cheers, Andy
Hello 2011/4/11 Clemens Eisserer <linuxhippy@gmail.com>: > Hi, > > We are working on an university project for network traffic accounting. > We use ulogd-2 to log netfilter packets, however it creates for each > packet a seperate transaction and also doesn't use prepared > statements, which results in horrible performance. > > What we are looking for is a low-overhead way of inserting many rows > (100-1000) into a table at once in one transaction. > Is "COPY FROM STDIN" a good idea in this case? sure > > If so, are there some examples how to use "COPY FROM STDIN" with the > native C API? look to source code on \copy implementation in psql http://doxygen.postgresql.org/bin_2psql_2copy_8c.html http://www.postgresql.org/docs/8.1/static/libpq-copy.html Regards Pavel Stehule > I found some documentation, however as I am no C native some sample > code would really help. > > Thank you in advance, Clemens > > PS: Sorry for the cross post, I accidentially sent it to the jdbc list before. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hi Pavel, >> If so, are there some examples how to use "COPY FROM STDIN" with the >> native C API? > > look to source code on \copy implementation in psql > > http://doxygen.postgresql.org/bin_2psql_2copy_8c.html > > http://www.postgresql.org/docs/8.1/static/libpq-copy.html Thanks for the pointer, I also found some very old docs with a few examples. Works perfectly, inserting every ~128kb of text data postgresql produces less than 2% load for 2000 inserts/s :)ä Thanks, Clemens