Re: bulk inserts

On Mon, Sep 28, 2009 at 08:33:45PM -0400, Martin Gainty wrote:
> INSERTS/UPDATES are historically slow especially with autocommit is
> on (implied autocommit on) the Database writer actually stops any
> processing and applies that one record to the database

That seems to be overstating the issue somewhat. Each connection is
still independent and (assuming no locks are taken) will carry on as
such.

> Most bulk operations such as import/export and copy are well worth
> their weight as they apply en-masse before any commit ..

?? I'm not sure what you're implying about the semantics here, but it
doesn't seem right. COPY doesn't somehow break out of ACID semantics,
it's only an *optimization* that allows you to get large quantities of
data into the database faster. The main reason it's faster is because
parsing CSV data is easier than parsing SQL.

At least I think that's the only difference; anybody know better?

> remember the DB actually stops flushes its buffers to Disk and then
> resumes..

The DB as a whole does not stop if you issue a commit; just your
session/connection.

> the only solution here is to disable autocommit but be wary
> you may have 100's of statements waiting to be commited and then
> someone does a quit on your session..all your work is lost

I'm not sure what you're saying here. These are normal transactional
semantics and are what all ACID databases are specified to do. You need
to issue a "COMMIT" for data to be committed.

--
Sam http://samason.me.uk/