Quantcast

Howto use "COPY FROM" with the native API?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Howto use "COPY FROM" with the native API?

Clemens Eisserer
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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Howto use "COPY FROM" with the native API?

Andy Chambers
On Mon, 11 Apr 2011 11:21:16 -0400, Clemens Eisserer  
<[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Howto use "COPY FROM" with the native API?

Pavel Stehule
In reply to this post by Clemens Eisserer
Hello

2011/4/11 Clemens Eisserer <[hidden email]>:

> 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 ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Howto use "COPY FROM" with the native API?

Clemens Eisserer
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...