Hello! Problem I'm talking about in this letter is related to how we deal with extremely big amounts of data to be inserted into DB. Recently I switched to using buffers. The best approach to make a buffer I know (and the only I can use with my driver) is to create a long query (possibly made of some statements) like: "INSERT INTO test VALUES (1, 2), (3, 4) ... (N, M); INSERT INTO test VALUES (7, 8), (9, 10) ... (N2, M2);" ...and then pass it to query() function your driver has (no matter what driver you use, it should be quite fast). But what will happen if the connection to PostgreSQL is lost during the execution? There are two different things which may happen with such a buffer: 1) I've found that once PostgreSQL has started writing, connection can safely be lost without affecting the data passed. In this case all the data will be inserted, because PostgreSQL has the whole query to process. 2) Data can't be inserted in the case the connection is lost before PostgreSQL has started such a writing (i.e. during transmission of the query). Unfortunatelly, my driver can't say me at which stage the connection has been lost, so I don't know should I insert it again or not after reconnect. Way #1. One of the ways to solve this problem is to guarantee that query I execute fails completely when connection is lost no matter at which stage. In this case I can safely insert a buffer after reconnect without any checks. Is there a way to guarantee that? Way #2. Another way I see is to have additional table containing values that show which buffers were inserted successfully. For example, the last statement in my buffer could be: INSERT INTO insert_history VALUES (<BUFFER_ID>, <SUCCESSFULLY_INSERTED_FLAG>) After reconnect, I can check if buffer I'm dealing with has been inserted or not. Which way do you prefer and why? Is there a way to do #1 (it's a bit cleaner and simpler to implement)? Thanks. -- Sergey Samokhin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
