Skip site navigation (1) Skip section navigation (2)

Re: High-Profile Advocacy Opportunity: Vbulletin

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
Cc: Peter Eisentraut <peter_e(at)gmx(dot)net>,Josh Berkus <josh(at)agliodbs(dot)com>,Donnacha Mac Gloinn <postgresql(dot)org(at)donnacha(dot)com>,pgsql-advocacy <pgsql-advocacy(at)postgresql(dot)org>
Subject: Re: High-Profile Advocacy Opportunity: Vbulletin
Date: 2004-06-22 19:51:07
Message-ID: 1087933867.12015.31502.camel@stromboli (view raw or flat)
Thread:
Lists: pgsql-advocacy
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




In response to

Responses

pgsql-advocacy by date

Next:From: Alvaro HerreraDate: 2004-06-22 20:05:37
Subject: Re: High-Profile Advocacy Opportunity: Vbulletin Forum
Previous:From: Larry RosenmanDate: 2004-06-21 01:52:58
Subject: Re: [HACKERS] SCO embraces MySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group