473,322 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

problems with transaction blocks

I want to do a series of inserts within a single transaction block, but with
postgresql if one insert fails, the whole block is aborted. Is there any
way to get around this behavior so that postgresql won't abort the entire
transaction if a single insert returns an error?

Chris
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #1
6 2743
Transactions are atomic. What you are asking to do violates the whole
concept of a transaction.

You can, however, do these inserts outside of the transaction block.

Best Wishes,
Chris Travers
----- Original Message -----
From: "Chris Ochs" <ch***@paymentonline.com>
To: <pg***********@postgresql.org>
Sent: Wednesday, January 07, 2004 7:52 AM
Subject: [GENERAL] problems with transaction blocks

I want to do a series of inserts within a single transaction block, but with postgresql if one insert fails, the whole block is aborted. Is there any
way to get around this behavior so that postgresql won't abort the entire
transaction if a single insert returns an error?

Chris
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2
On Tue, Jan 06, 2004 at 16:52:12 -0800,
Chris Ochs <ch***@paymentonline.com> wrote:
I want to do a series of inserts within a single transaction block, but with
postgresql if one insert fails, the whole block is aborted. Is there any
way to get around this behavior so that postgresql won't abort the entire
transaction if a single insert returns an error?


Currently there is no provision for recovery from error by the application
inside a transaction. What you can do is have the application check for
problems before trying the insert. Depending on the problems you expect,
you might be able to avoid trying an insert which will fail in almost
all cases.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3
Another good way to handle this is to put a trigger on the table that
diverts inserts that would fail to a holding table. While this will slow
down the inserts, it will allow you to insert large lists of dubious
quality and worry about the bad rows later.

My preference is to fix the data feed, or pre-process it with PHP/Perl to
split it into two files ahead of time, but I'm more of a coder than a dba.
I get a lot of data to import from other sources at work, and it's often
easier to make the sources fix their data feeds than it is to try and
massage them each and every time.

On Wed, 7 Jan 2004, Chris Travers wrote:
Transactions are atomic. What you are asking to do violates the whole
concept of a transaction.

You can, however, do these inserts outside of the transaction block.

Best Wishes,
Chris Travers
----- Original Message -----
From: "Chris Ochs" <ch***@paymentonline.com>
To: <pg***********@postgresql.org>
Sent: Wednesday, January 07, 2004 7:52 AM
Subject: [GENERAL] problems with transaction blocks

I want to do a series of inserts within a single transaction block, but

with
postgresql if one insert fails, the whole block is aborted. Is there any
way to get around this behavior so that postgresql won't abort the entire
transaction if a single insert returns an error?

Chris
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #4
Another way is break the transaction. Instead of consisting of many
inserts, each insert is a transaction itself.
Do a Begin and a Commit (or rollback) circling the insert statement.
HTH
Rodrigo Malara
Em Qua, 2004-01-07 às 14:41, scott.marlowe escreveu:
Another good way to handle this is to put a trigger on the table that
diverts inserts that would fail to a holding table. While this will slow
down the inserts, it will allow you to insert large lists of dubious
quality and worry about the bad rows later.

My preference is to fix the data feed, or pre-process it with PHP/Perl to
split it into two files ahead of time, but I'm more of a coder than a dba.
I get a lot of data to import from other sources at work, and it's often
easier to make the sources fix their data feeds than it is to try and
massage them each and every time.

On Wed, 7 Jan 2004, Chris Travers wrote:
Transactions are atomic. What you are asking to do violates the whole
concept of a transaction.

You can, however, do these inserts outside of the transaction block.

Best Wishes,
Chris Travers
----- Original Message -----
From: "Chris Ochs" <ch***@paymentonline.com>
To: <pg***********@postgresql.org>
Sent: Wednesday, January 07, 2004 7:52 AM
Subject: [GENERAL] problems with transaction blocks

I want to do a series of inserts within a single transaction block, but

with
postgresql if one insert fails, the whole block is aborted. Is there any
way to get around this behavior so that postgresql won't abort the entire
transaction if a single insert returns an error?

Chris
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #5
>>>>> "RM" == Rodrigo Malara <ro***********@yahoo.com.br> writes:

RM> Another way is break the transaction. Instead of consisting of many
RM> inserts, each insert is a transaction itself.
RM> Do a Begin and a Commit (or rollback) circling the insert statement.

that will destroy your performance if you do it a lot. and there's no
point in doing an explicit begin/commit since PG does that implicitly
for each statement not inside a transaction already.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #6
>>>>> "BW" == Bruno Wolff, <Bruno> writes:

BW> Currently there is no provision for recovery from error by the application
BW> inside a transaction. What you can do is have the application check for
BW> problems before trying the insert. Depending on the problems you expect,

I have an application that does this. First it attempts to do a mass
number of inserts based on some external events all inside a
transaction. If we abort due to referential integrity constraint
violation, we restart the whole process but before each insert a check
is done to see if the required FK's are satisfied.

This gives us the benefit of 99% of the time when the FKs are ok we
zip along pretty darned fast, and for the 1% of the time when some
stale data is re-injected into the stream, we just restart that batch
and pay the penalty for it.

The benefit of being able to do all the inserts within a single
begin/end cannot be understated.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Karl Pech | last post by:
Hi all, I'm trying to write a program which can read in files in the following format: sos_encoded.txt: --- begin-base64 644 sos.txt UGxlYXNlLCBoZWxwIG1lIQ== ---
2
by: Tim Callaghan | last post by:
We have an inhouse replication process which is causing us headaches when we try to run more than one copy of it against the same target database (we support unlimited remote databases so we may...
1
by: Rittercorp | last post by:
I am debugging an app which blocks many processes in a SQL7 server DB. The app log writes every transaction "open" and "close". The weird thing is : when the app logfile says the transaction is...
1
by: Rick | last post by:
I'm having problems with EnterpriseServices transactions running against Oracle 9iR2. I am inconsistently getting Oracle ORA-24761: Transaction Rolled Back results mid-transaction. If I start...
1
by: Jamal | last post by:
I am working on binary files of struct ACTIONS I have a recursive qsort/mergesort hybrid that 1) i'm not a 100% sure works correctly 2) would like to convert to iteration Any comments or...
6
by: Jamal | last post by:
I am working on binary files of struct ACTIONS I have a recursive qsort/mergesort hybrid that 1) i'm not a 100% sure works correctly 2) would like to convert to iteration Any comments or...
2
by: John Lee | last post by:
Hi, I have few questions related to .NET 2.0 TransactionScope class behavior: 1. Check Transaction.Current.TransactionInformation.DistributedIdentifier to identify if distributed transaction...
15
by: Zeng | last post by:
Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried)...
2
by: Phil Endecott | last post by:
Dear All, Within a transaction, now() and current_timestamp are constant and give the time that the transaction started. This is normally what you want. But I have a case where I actually need...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.