Skip site navigation (1) Skip section navigation (2)

Re: More thoughts about FE/BE protocol

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Barry Lind <blind(at)xythos(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: More thoughts about FE/BE protocol
Date: 2003-04-10 18:15:13
Message-ID: 5564.1049998513@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-interfaces
Barry Lind <blind(at)xythos(dot)com> writes:
> When an application needs to do a lot of the same thing (i.e 
> insert a thousand rows), the applicaiton tells the driver to insert a 
> 'batch' of 1000 rows instead of performing 1000 regular inserts. This 
> allows the driver to optimize this operation as one network roundtrip 
> instead of 1000 roundtrips.
> ... How could this be accomplished with the 
> new FE/BE protocol "extended query" facility?

Well, as far as network roundtrips go, it's always been true that you
don't really have to wait for the backend's response before sending the
next command.  The proposal to decouple SYNC from individual commands
should make this easier: you fire off N commands "blind", then a SYNC.
When the sync response comes back, it's done.  If any of the commands
fail, all else up to the SYNC will be ignored, so you don't have the
problem of commands executing against an unexpected state.

(I'm not sure it'd be bright to issue thousands of commands with no
SYNC, but certainly reasonable-size batches would be sensible.)

As for lots of instances of the same kind of command, you could PARSE
the SQL insert command itself just once (with parameter placeholders for
the data values), then repeat BIND/EXECUTE pairs as often as you want.
That's probably about as efficient as you're going to get without
switching to COPY mode.

Does that address your concern, or is there more to do?

			regards, tom lane


In response to

Responses

pgsql-hackers by date

Next:From: Ron PeacetreeDate: 2003-04-10 18:20:13
Subject: Re: Anyone working on better transaction locking?
Previous:From: Michael MeskesDate: 2003-04-10 17:20:04
Subject: Re: ECPG thread-safety

pgsql-interfaces by date

Next:From: William SuetholzDate: 2003-04-10 19:20:39
Subject: ECPG for ODBC?
Previous:From: Tim EllisDate: 2003-04-10 18:13:05
Subject: Re: PLEASE HELP ME URGENT about choosing only the ones

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group