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 (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 -- 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
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?
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. 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
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 (save&release). 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 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
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?
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: 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
[GENERAL] 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 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