On Tue, 2004-06-22 at 21:05, Alvaro Herrera wrote:
> On Tue, Jun 22, 2004 at 08:51:07PM +0100, Simon Riggs wrote:
>
> > > Why is it better than COPY?
>
> > COPY is designed for bulk data loading from files etc. Extended INSERTs
> > are used to minimise the number of round-trips to the database when
> > issuing a few number of similar INSERTs, as when you do an
> > Order/Order-Line (i.e. Master and many similar Details records).
>
> Huh? I think you should be able to issue a single multivalued insert to
> a single table -- two queries in your Order/Order-Line example, not one.
>
Better explained, that would be:
**If you're doing an Order/Order-Line insert you'll do 1 INSERT to Order
and then multiple similar INSERTs to Order-Line: the multiple INSERTs
into Order-Line are when you'd use the extended INSERT feature.**
> You can do the same in Postgres issuing several commands separated with
> semicolons. You can even use a single transaction by using BEGIN and
> COMMIT at the start and end of the string.
>
> I think the real gain of multivalued insert will come from using batch
> heap and index insertions, not roundtrip to the application. At least,
> the roundtrip savings can be had with today's Postgres.
>
Yes, there are PostgreSQL ways of doing things, not my point, I was
explaining the MySQL rationale as I saw it.
> > On the technical side, I think we should have both extended INSERT and
> > REPLACE on the TODO list...
>
> I don't think REPLACE is a good idea (useless non standard MySQL-ism).
> MERGE and multivalued INSERT are. But they depend on someone actually
> doing the work. Both are on the TODO list, I think.
I think doing REPLACE is a trivial subset of MERGE, so should be easy
enough to pull off once MERGE gets done.
Regards, Simon Riggs
