Обсуждение: Anyone have example C code for Asynchronous Command Processing?

I'm using libpq to dump some text files into a PostgreSQL database.
It's just a bunch of "INSERT INTO" calls, but they block one another
waiting for the INSERT command to return. I think I can speed things
up by using asynchronous command processing (e.g. PQsendQuery,
PQgetResult). It's hard to make sense of the asynchronous command
processing documentation without a concrete example.

Can anyone out there provide a simple C code snippet for how to use
the asynchronous command processing in libpq?

Thanks.
-Tony

reina_ga@hotmail.com (Tony Reina) writes:
> I'm using libpq to dump some text files into a PostgreSQL database.
> It's just a bunch of "INSERT INTO" calls, but they block one another
> waiting for the INSERT command to return. I think I can speed things
> up by using asynchronous command processing (e.g. PQsendQuery,
> PQgetResult).

No you can't, because you can't have multiple commands pending over a
single connection.

You would get significantly more speedup by using COPY, anyway.

            regards, tom lane

If the "INSERT INTO" calls are known previously, you can spawn several
connections at the same time.

It's argueable that it will finish sooner, but it may be worth trying.

> reina_ga@hotmail.com (Tony Reina) writes:
>> I'm using libpq to dump some text files into a PostgreSQL database.
>> It's just a bunch of "INSERT INTO" calls, but they block one another
>> waiting for the INSERT command to return. I think I can speed things
>> up by using asynchronous command processing (e.g. PQsendQuery,
>> PQgetResult).
>
> No you can't, because you can't have multiple commands pending over a
> single connection.
>
> You would get significantly more speedup by using COPY, anyway.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Yikes! I was hoping that by not having to wait for an acknowledgement that
the data had been successfully dumped I could speed up the inserts. I guess
I'll have to try to modify things to use COPY.

Just for kicks... you said that you can't have multiple connections pendings
over a single connection. Would there be any advantage for the same client
to make multiple connections in this case? For example, if I had 2 INSERT
queries ready to send, could I send them along 2 different connections
(PQconn conn1, conn2) from the same C code? Would the 2 connections execute
faster than running the 2 commands serial over the same connection?

-Tony

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Tony Reina" <reina_ga@hotmail.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Monday, March 08, 2004 4:51 PM
Subject: Re: [NOVICE] Anyone have example C code for Asynchronous Command
Processing?


> reina_ga@hotmail.com (Tony Reina) writes:
> > I'm using libpq to dump some text files into a PostgreSQL database.
> > It's just a bunch of "INSERT INTO" calls, but they block one another
> > waiting for the INSERT command to return. I think I can speed things
> > up by using asynchronous command processing (e.g. PQsendQuery,
> > PQgetResult).
>
> No you can't, because you can't have multiple commands pending over a
> single connection.
>
> You would get significantly more speedup by using COPY, anyway.
>
> regards, tom lane
>

"Tony and Bryn Reina" <reina_ga@hotmail.com> writes:
> Would the 2 connections execute
> faster than running the 2 commands serial over the same connection?

Perhaps, but you'd get much more win out of batching multiple inserts
into one COPY.  The overhead of parsing an INSERT command is relatively
high compared to what it actually gets done.  Running two in parallel
does nothing to improve the overhead situation; and if you are after
speed, cutting the overhead is where you *must* focus your attention.
(If you just gotta have the sex appeal of parallelism, try running two
COPY operations in parallel...)

            regards, tom lane

Thanks Tom. I'll stick with reforming things using a batch COPY command.

-Tony

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Tony and Bryn Reina" <reina_ga@hotmail.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, March 09, 2004 4:05 PM
Subject: Re: [NOVICE] Anyone have example C code for Asynchronous Command
Processing?


> "Tony and Bryn Reina" <reina_ga@hotmail.com> writes:
> > Would the 2 connections execute
> > faster than running the 2 commands serial over the same connection?
>
> Perhaps, but you'd get much more win out of batching multiple inserts
> into one COPY.  The overhead of parsing an INSERT command is relatively
> high compared to what it actually gets done.  Running two in parallel
> does nothing to improve the overhead situation; and if you are after
> speed, cutting the overhead is where you *must* focus your attention.
> (If you just gotta have the sex appeal of parallelism, try running two
> COPY operations in parallel...)
>
> regards, tom lane
>