Re: [HACKERS] foreign table batch inserts

2016-06-01 Thread Etsuro Fujita
On 2016/05/31 14:53, Amit Langote wrote: On 2016/05/30 22:59, Craig Ringer wrote: On 30 May 2016 at 16:17, Etsuro Fujita wrote: That's a good point, but the basic idea is to send the local query almost-as-is to the remote server if possible. For example, if the

Re: [HACKERS] foreign table batch inserts

2016-05-30 Thread Amit Langote
On 2016/05/30 22:59, Craig Ringer wrote: > On 30 May 2016 at 16:17, Etsuro Fujita wrote: >> >> That's a good point, but the basic idea is to send the local query >> almost-as-is to the remote server if possible. For example, if the local >> query is "INSERT INTO

Re: [HACKERS] foreign table batch inserts

2016-05-30 Thread Craig Ringer
On 30 May 2016 at 16:17, Etsuro Fujita wrote: > > That's a good point, but the basic idea is to send the local query > almost-as-is to the remote server if possible. For example, if the local > query is "INSERT INTO foreign_table(a,b,c) VALUES (1, 2, 3), (4, 5,

Re: [HACKERS] foreign table batch inserts

2016-05-30 Thread Etsuro Fujita
On 2016/05/27 8:49, Michael Paquier wrote: On Thu, May 26, 2016 at 4:25 AM, Etsuro Fujita wrote: Honestly, I didn't have any idea for executing such an insert efficiently, but I was thinking to execute an insert into a foreign table efficiently, by sending the

Re: [HACKERS] foreign table batch inserts

2016-05-26 Thread Michael Paquier
On Thu, May 26, 2016 at 4:25 AM, Etsuro Fujita wrote: > On 2016/05/18 7:08, Michael Paquier wrote: >> >> On Wed, May 18, 2016 at 6:00 AM, Manuel Kniep wrote: >>> >>> I realized that inserts into foreign tables are only done row by row. >>> Consider

Re: [HACKERS] foreign table batch inserts

2016-05-26 Thread Etsuro Fujita
On 2016/05/18 7:08, Michael Paquier wrote: On Wed, May 18, 2016 at 6:00 AM, Manuel Kniep wrote: I realized that inserts into foreign tables are only done row by row. Consider copying data from one local table to a foreign table with INSERT INTO foreign_table(a,b,c) SELECT

Re: [HACKERS] foreign table batch inserts

2016-05-23 Thread Craig Ringer
On 20 May 2016 at 23:18, Craig Ringer wrote: > On 20 May 2016 at 15:35, Craig Ringer wrote: > > >> >> You can, however, omit Sync from between messages and send a series of >> protocol messages, like >> >>

Re: [HACKERS] foreign table batch inserts

2016-05-20 Thread Craig Ringer
On 20 May 2016 at 15:35, Craig Ringer wrote: > > You can, however, omit Sync from between messages and send a series of > protocol messages, like > > Parse/Bind/Execute/Bind/Execute/Bind/Execute/Sync > > to avoid round-trip overheads. > > I implemented what I think is a

Re: [HACKERS] foreign table batch inserts

2016-05-20 Thread Craig Ringer
On 20 May 2016 at 08:47, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-ow...@postgresql.org [mailto: > pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer > > Well, there's FE/BE level batching/pipelining already. Just no access to > it from libpq. >

Re: [HACKERS] foreign table batch inserts

2016-05-19 Thread Tsunakawa, Takayuki
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer Well, there's FE/BE level batching/pipelining already. Just no access to it from libpq. Oh, really. The Bind ('B') appears to take one set of parameter values, not multiple sets

Re: [HACKERS] foreign table batch inserts

2016-05-19 Thread Craig Ringer
On 19 May 2016 at 14:08, Tsunakawa, Takayuki wrote: > > > Yes, I want FE-BE protocol-level batch inserts/updates/deletes, too. I > was just about to start thinking of how to implement it because of recent > user question in pgsql-odbc. The OP uses Microsoft

Re: [HACKERS] foreign table batch inserts

2016-05-19 Thread Tsunakawa, Takayuki
From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Craig Ringer On 19 May 2016 at 01:39, Michael Paquier wrote: On Wed, May 18, 2016 at 12:27 PM, Craig Ringer wrote: > On 18 May 2016 at 06:08,

Re: [HACKERS] foreign table batch inserts

2016-05-18 Thread Craig Ringer
On 19 May 2016 at 01:39, Michael Paquier wrote: > On Wed, May 18, 2016 at 12:27 PM, Craig Ringer > wrote: > > On 18 May 2016 at 06:08, Michael Paquier > wrote: > >> > Wouldn’t it make sense to do the insert batch wise

Re: [HACKERS] foreign table batch inserts

2016-05-18 Thread Michael Paquier
On Wed, May 18, 2016 at 12:27 PM, Craig Ringer wrote: > On 18 May 2016 at 06:08, Michael Paquier wrote: >> > Wouldn’t it make sense to do the insert batch wise e.g. 100 rows ? >> >> Using a single query string with multiple values, perhaps, but

Re: [HACKERS] foreign table batch inserts

2016-05-18 Thread Craig Ringer
On 18 May 2016 at 06:08, Michael Paquier wrote: > > Wouldn’t it make sense to do the insert batch wise e.g. 100 rows ? > > Using a single query string with multiple values, perhaps, but after > that comes into consideration query string limit particularly for > large

Re: [HACKERS] foreign table batch inserts

2016-05-17 Thread Michael Paquier
On Wed, May 18, 2016 at 6:00 AM, Manuel Kniep wrote: > I realized that inserts into foreign tables are only done row by row. > Consider copying data from one local table to a foreign table with > > INSERT INTO foreign_table(a,b,c) SELECT a,b,c FROM local_table; > > When the

[HACKERS] foreign table batch inserts

2016-05-17 Thread Manuel Kniep
Hi, I realized that inserts into foreign tables are only done row by row. Consider copying data from one local table to a foreign table with INSERT INTO foreign_table(a,b,c) SELECT a,b,c FROM local_table; When the foreign server is for example in another datacenter with long latency, this as