Re: serialization errors

Hello,

On Tue, Jan 28, 2003 at 12:47:20PM -0500, Tom Lane wrote:
> > If I receive the legal error "duplicate key" error, how am I supposed to
> > detect if that error is due to a concurrent update, or because of some
> > other error elsewhere?
>
> What difference does it make if the other guy got there ten microseconds
> or ten years earlier? He inserted before you did. Whether it's
> "concurrent" or not shouldn't matter that I can see. Perhaps more to
> the point, there is no reason to expect that a duplicate-key failure
> will succeed if you retry the same insertion.

My application checks to make sure that the operation it is about to
perform wouldn't violates the constraints. (This is done right after
the transaction is started.)

If I get a "duplicate record" error, I want to know if it's because
of recently committed data, or because something else messed up.

Let me give a more concrete example, closer to what I'm doing:
create table tree_node (
node_id int primary key default nextval('seq_tree_node'),
parent_id int references tree_node(node_id),
node_order int not null,
unique(parent_id, node_order)
);

For adding a new node, I basically do this:
insert into tree_node(parent_id, node_order)
values(1, (select max(node_order)+1 from tree_node where parent_id = 1) );

Now, if I have to clients which do this simultaneously, then one will get a
duplicate record error, and I know to simply re-run the query.

However, with more complicated functionality (e.g. changing the tree
structure / order), I need to know why I received the error:
Did my version of the data get corrupted such that i'm running
completely invalid queries? (end result: force-quit)
(this also covers any bugs I may have introduced).
Did someone else update the database at the same time?
(end result: apply changes and if changes are compatible, then
re-run with new data, or tell user operation was aborted because
of what someone else did).

At the very least I would suggest adding a note to the manual, section 9.2.2
(serialization level isoloation). The note would mention that "Serialization"
errors are not generated by inserts that violate unique constraints.

Thanks!

Ryan

--
Ryan VanderBijl rvbijl(at)vanderbijlfamily(dot)com