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
