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

Search Discussions

Discussion Posts


Follow ups

Related Discussions



site design / logo © 2021 Grokbase