On Tue, 2004-06-22 at 21:05, Alvaro Herrera wrote:
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.
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 INSERTsWhy is it better than COPY?
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).
a single table -- two queries in your Order/Order-Line example, not one.
**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 wassemicolons. 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.
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).REPLACE on the TODO list...
MERGE and multivalued INSERT are. But they depend on someone actually
doing the work. Both are on the TODO list, I think.
enough to pull off once MERGE gets done.
Regards, Simon Riggs
