Re: disabling autocommit

From: "Michael Kleiser" <mkl(at)webde-ag(dot)de>
To: "Matt Van Mater" <nutter_(at)hotmail(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: disabling autocommit
Date: 2004-08-12 07:24:57
Message-ID: 40AE2EEA89695342B4192094392579CB3B162D@exchange01.int.cinetic.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I had postet a similar question some time ago.
Someone answerded me, in the next PostgreSQL
it would be possible to use nested transactions.
Is is now implemented in version 8.0-beta

You can set a savepoint before the insert that possible fails.
http://developer.postgresql.org/docs/postgres/sql-savepoint.html

Probbaly the savepint also needs a roundtrip between client and server.
If so, this can decrease performance.
This can be avoided if you put the "savepoint, insert , rollback to savepoint and probably the insert in a stored procedure.

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]Im Auftrag von Matt Van Mater
Gesendet: Mittwoch, 11. August 2004 20:25
An: pgsql-general(at)postgresql(dot)org
Betreff: [GENERAL] disabling autocommit

I'm looking to get a little more performance out of my database, and saw in
the docs a section about disabling autocommit by using the BEGIN and COMMIT
keywords.

My problem is this: I enforce unique rows for all data, and occasionally
there is an error where I try to insert a duplicate entry. I expect to see
these duplicate entries and depend on the DB to enforce the row uniqueness.
When I just run the insert statements without the begin and commit keywords
the insert only fails for that single insert, but If I disable autocommit
then all the inserts fail because of one error.

As a test I ran about 1000 identical inserts with autocommit on and also
with it off. I get roughly a 33% speed increase with the autocommit off, so
it's definitely a good thing. The problem is, to parse the insert
statements and ensure there are no duplicates I feel like I would be losing
the advantage that disabling autocommit gives me, and simply spending the
cpu cycles somewhere else.

Is there a way for me to say 'only commit the successful commands and ignore
the unsuccessful ones'? I know that's the point behind using this type of
transaction/rollback statement but I was curious if there was a way I could
fix it.

Matt

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

Browse pgsql-general by date

  From Date Subject
Next Message Giannis Vrentzos 2004-08-12 07:45:38 case insensitive search with greek characters
Previous Message Mark Kirkwood 2004-08-12 06:23:54 Re: Coming soon: PG 7.4.4, 7.3.7, 7.2.5