Re: How should I deal with disconnects during insert?

On Wed, May 20, 2009 at 01:41:33PM -0700, Sergey Samokhin wrote:
> 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.

In general I'm not sure how it would ever know; the easiest way is to do
as you're suggesting and retry the insert on any failure.

> 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.

PRIMARY KEYs (or unique constraints in general) and transactions are
your friend here; you can check things if you want or just try dumping
data in and let it fail if it's already there.

Not sure what the "flag" is for; surely if there's a row in there
matching that buffer id then things are good to go. You could have
a foreign key on this from the data (the "test" table above) and the
database will check that you're only inserting data into it when you say
you are. Transactions will ensure that either everything happens or
nothing does.

--
Sam http://samason.me.uk/