On Thu, 2004-06-17 at 19:45, Alvaro Herrera wrote:
On Thu, Jun 17, 2004 at 08:13:19PM +0200, Peter Eisentraut wrote:
Josh Berkus wrote:
My personal conjecture:

The "extended insert" is not on the Postgres TODO list because:
a) It's not ANSI SQL standard.
b) We have COPY, which is better.
Unfortunately that statement is mostly wrong:
- It is on the TODO list.
- It is standard.
- It is better than COPY.
I agree with all 3 of these, because:
- Yes, its on the TODO list
- It is a standard because it is in use by many people, even though I
strongly doubt its on the ANSI list.
- It is "better" than COPY in certain situations.
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).
Oracle's Array INSERT syntax allowed a very similar saving in
round-trips. MySQL's REPLACE command is also a simpler form of MERGE
command (UPDATE/INSERT), also designed to minimise number of round-trips
to the database. Note that by doing so they minimise locking time and by
doing so overcome difficulties with transaction isolation levels, both
of which are problems for them - and the cause of scalability issues for
their users.

Donnacha: are your associates aware of such issues with MySQL?

IMHO: Those MySQL two features illustrate the one thing that is great
about MySQL: they aren't afraid to break the rules AND argue that by
doing so they have actually improved things...great, but not good.

On the technical side, I think we should have both extended INSERT and
REPLACE on the TODO list...

Incidentally, discussing other approaches: Teradata uses "multiple
statement requests", which allow you to submit multiple otherwise
unrelated SQL statements in a single request packet to the database.

Best Regards, Simon Riggs

Search Discussions

Discussion Posts


Follow ups

Related Discussions



site design / logo © 2021 Grokbase