Re: [GENERAL] Possible to prevent transaction abort?
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 (saverelease). 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible to prevent transaction abort?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible to prevent transaction abort?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible to prevent transaction abort?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible to prevent transaction abort?
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 (saverelease). Thomas Kellerer wrote: 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 VidexInc.1105N.E.CircleBlvd.CorvallisOR97330(541)758-0521 CONFIDENTIALCOMMUNICATION:Theemailmessageandanyattachmentsareintendedonlyfortheaddressee.Theymaybeprivileged,confidential,andprotectedfromdisclosure.Ifyouarenottheintendedrecipient,anydissemination,distribution,orcopyingisexpresslyprohibited.Ifyoureceivedthisemailmessageinerror,pleasenotifythesenderimmediatelybyreplyingtothise-mailmessageorbytelephone
Re: [GENERAL] Possible to prevent transaction abort?
I'm intrigued by this solution, Johan. It might be just the ticket! 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? >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. VidexInc.1105N.E.CircleBlvd.CorvallisOR97330(541)758-0521 CONFIDENTIALCOMMUNICATION:Theemailmessageandanyattachmentsareintendedonlyfortheaddressee.Theymaybeprivileged,confidential,andprotectedfromdisclosure.Ifyouarenottheintendedrecipient,anydissemination,distribution,orcopyingisexpresslyprohibited.Ifyoureceivedthisemailmessageinerror,pleasenotifythesenderimmediatelybyreplyingtothise-mailmessageorbytelephone
Re: [GENERAL] Possible to prevent transaction abort?
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 10; 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 immediately by replying to this
e-mail message or by telephone
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible to prevent transaction abort?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
