Howto use "COPY FROM" with the native API?

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

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