Thread: Possible to prevent transaction abort?
Hello all, Is it possible to prevent Postgre from aborting the transaction upon a constraint violation? Using JDBC if I catch the constraint violation and try another statement I get: /ERROR: current transaction is aborted, commands ignored until end of transaction block/ I realize that I could set a save-point before every INSERT but that nearly doubles the processing time. Since our application INSERTS many thousands of rows at a time we need maximum efficiency. On Mysql (where this limitation doesn't exist) it's already only barely fast enough. If we have to use savepoints with Postgre it might prevent us from making the switch. Is there some mode flag I could set, either database or server wide? I've found Postgre to be wonderfully configurable so I'm crossing my fingers... Much Thanks. - Adam Videx Inc. 1105 N. E. Circle Blvd. Corvallis OR 97330 (541) 758-0521 CONFIDENTIAL COMMUNICATION: The email message and any attachments are intended only for the addressee. They may be privileged,confidential, and protected from disclosure. If you are not the intended recipient, any dissemination, distribution,or copying is expressly prohibited. If you received this email message in error, please notify the sender immediatelyby replying to this e-mail message or by telephone
Adam B wrote:
> Hello all,
>
> Is it possible to prevent Postgre from aborting the transaction upon a
> constraint violation?
From the help files maybe the following could get you on the right track:
This example uses exception handling to perform either UPDATE or INSERT,
as appropriate:
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
HTH,
Johan Nel
Pretoria, South Africa.
Adam B wrote on 01.05.2009 19:50: > I realize that I could set a save-point before every INSERT but that > nearly doubles the processing time. That's interesting. I did a quick test with JDBC inserting 500,000 rows and the time when using a savepoint for each INSERT was not really different to the one when not using a savepoint (less than a second which could well be caused by other things in the system). I tested this locally so no real network traffic involved, which might change the timing as more stuff is sent over to the server when using the savepoint. Thomas
Perhaps I'm doing something wrong. I'm consistently taking over 20s for
the following test case. (Without savepoints it takes under 10s)
CREATE TABLE lots2
(
lid serial NOT NULL,
"name" character varying(64),
CONSTRAINT lots2pk PRIMARY KEY (lid),
CONSTRAINT lots2_unique_name UNIQUE (name)
)
Java code:
Connection con =
DriverManager.getConnection("jdbc:postgresql://localhost/driver_test",
"postgres", "*****");
Statement st = con.createStatement();
st.executeUpdate("DELETE FROM lots2");
st.close();
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement("INSERT INTO
lots2 (name) VALUES (?)");
long start = System.currentTimeMillis();
for (int i = 0; i < 100000; i++)
{
ps.setString(1, "number " + i);
Savepoint saved = con.setSavepoint();
ps.executeUpdate();
con.releaseSavepoint(saved);
}
con.setAutoCommit(true);
long stop = System.currentTimeMillis();
System.out.println((stop - start) + "ms");
Thomas Kellerer wrote:
> Adam B wrote on 01.05.2009 19:50:
>> I realize that I could set a save-point before every INSERT but that
>> nearly doubles the processing time.
>
> That's interesting.
>
> I did a quick test with JDBC inserting 500,000 rows and the time when
> using a savepoint for each INSERT was not really different to the one
> when not using a savepoint (less than a second which could well be
> caused by other things in the system).
>
> I tested this locally so no real network traffic involved, which might
> change the timing as more stuff is sent over to the server when using
> the savepoint.
>
> Thomas
>
>
Videx Inc. 1105 N. E. Circle Blvd. Corvallis OR 97330 (541) 758-0521
CONFIDENTIAL COMMUNICATION: The email message and any attachments are intended only for the addressee. They may be
privileged,confidential, and protected from disclosure. If you are not the intended recipient, any dissemination,
distribution,or copying is expressly prohibited. If you received this email message in error, please notify the sender
immediatelyby replying to this e-mail message or by telephone
Adam B wrote on 01.05.2009 22:59: > Perhaps I'm doing something wrong. I'm consistently taking over 20s for > the following test case. (Without savepoints it takes under 10s) > That's really strange. I can reproduce your results on my computer (25 vs. 65 seconds). When running my import program against your table, I don't see a big difference between the savepoint solution and the one without (I added a row to the import file that would fail to make sure I was really using savepoints) My import program is doing more or less the same thing as your code, so I have no idea what's going on here. There was one strange thing though: I had one run where it took a lot longer with the savepoint than without. But I could not reproduce that, all other tests where approx. the same runtime with or without savepoints. Very strange. Might be worth posting to the JDBC list, to see if this is a driver issue.... Thomas
Strange indeed. Perhaps there's some background stuff happening that messes with the results (auto VACUUM?).<br /><br />In my mind, however, it makes sense that it would take longer: 2 extra operations against the server (save&release).<br/><br /> Thomas Kellerer wrote: <blockquote cite="mid:gtfs47$9j3$1@ger.gmane.org" type="cite">AdamB wrote on 01.05.2009 22:59: <br /><blockquote type="cite">Perhaps I'm doing something wrong. I'm consistentlytaking over 20s for the following test case. (Without savepoints it takes under 10s) <br /><br /></blockquote><br/> That's really strange. I can reproduce your results on my computer (25 vs. 65 seconds). <br /><br />When running my import program against your table, I don't see a big difference between the savepoint solution and theone without (I added a row to the import file that would fail to make sure I was really using savepoints) <br /><br />My import program is doing more or less the same thing as your code, so I have <br /> no idea what's going on here. <br/><br /> There was one strange thing though: I had one run where it took a lot longer with the savepoint than without.But I could not reproduce that, all other tests where approx. the same runtime with or without savepoints. <br /><br/> Very strange. <br /><br /> Might be worth posting to the JDBC list, to see if this is a driver issue.... <br /><br/> Thomas <br /><br /><br /></blockquote><br /><br /><br /><hr /> Videx Inc. 1105 N. E. Circle Blvd. Corvallis OR 97330 (541) 758-0521<br/> CONFIDENTIAL COMMUNICATION: The email message and any attachments are intended only for the addressee. They may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, any dissemination, distribution, or copying is expressly prohibited. If you received this email message in error, please notify the sender immediately by replying to this e-mail message or by telephone<br />
I'm intrigued by this solution, Johan.=C2=A0 It might be just the ticket!= =C2=A0 I'll do some benchmarks when I have time in a week or so. Johan Nel wrote: Adam B wrote: Hello all, Is it possible to prevent Postgre from aborting the transaction upon a constraint violation? =46rom the help files maybe the following could get you on the right track: This example uses exception handling to perform either UPDATE or INSERT, as appropriate: CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN =C2=A0=C2=A0=C2=A0 LOOP =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- first try to update the key =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 UPDATE db SET b =3D data WHERE a= =3D key; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 IF found THEN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 RETURN; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 END IF; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- not there, so try to insert t= he key =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- if someone else inserts the s= ame key concurrently, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- we could get a unique-key fai= lure =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 BEGIN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 INSERT I= NTO db(a,b) VALUES (key, data); =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 RETURN; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 EXCEPTION WHEN unique_violation = THEN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- do no= thing, and loop to try the UPDATE again =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 END; =C2=A0=C2=A0=C2=A0 END LOOP; END; $$ LANGUAGE plpgsql; HTH, Johan Nel Pretoria, South Africa. Videx Inc. 1105 N. E. Circle Blvd. Corva= llis OR 97330 (541) 758-0521 CONFIDENTIAL COMMUNICATION: The email message and&= nbsp;any attachments are intended only for th= e addressee. They may be privileged, co= nfidential, and protected from disclosure. If = ;you are not the intended recipient, any = ;dissemination, distribution, or copying is expres= sly prohibited. If you received this em= ail message in error, please notify the = sender immediately by replying to this e-mail= message or by telephone
Adam B wrote: > Hello all, > > Is it possible to prevent Postgre from aborting the transaction upon a > constraint violation? Not without use of savepoints. What I like to do is bulk-insert the suspect data into a temp table without constraints, then INSERT INTO ... SELECT it into the target table with appropriate WHERE constraints to prevent attempts to insert invalid values. Another alternative is to constrain each INSERT statement with an appropriate WHERE clause after rephrasing it in INSERT INTO ... SELECT form . -- Craig Ringer
Adam B wrote on 02.05.2009 00:48: > Strange indeed. Perhaps there's some background stuff happening that > messes with the results (auto VACUUM?). > > In my mind, however, it makes sense that it would take longer: 2 extra > operations against the server (save&release). Typical case of "problem sits between keyboard and chair". My import program had an additional parameter which was needed to enable the savepoint. If that is set, the results are comparable to your test program (and reproducable). So my fast results were not using savepoints. Sorry for the confusion. Thomas