Re: serialization errors

On Wednesday 29 January 2003 22:39, Ryan VanderBijl wrote:
> On Wed, Jan 29, 2003 at 12:59:10AM -0500, Tom Lane wrote:
> > Ryan VanderBijl <rvbijl(at)vanderbijlfamily(dot)com> writes:
> > > 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) );
> >
> > That "select max()+1" makes me itch ;-) ... that's as perfect a recipe
> > for concurrency problems as I can imagine.
> >
> > At first glance it seems that all this is doing is assigning sequential
> > node_order values to the children of any particular parent. Is it
> > really necessary that those node_order values be consecutive? If they
> > only need to be strictly increasing, you could use a sequence to
> > generate them. (Actually, I think you could dispense with node_order
> > entirely, and use the node_id field instead.)
> >
> > In any case, I'd suggest some careful thought about what your data
> > structure really is, and how you could avoid creating serialization
> > bottlenecks like this one.

> I'm open to suggestions of a better way to store tree structures in a
> database ...

Not a better way, per se, but a different way:
http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html

If you only have two levels, the the adjacency list model will work very
well. It works well when a maximum depth can be specified.

The order field might be better expressed as a non-primary key column.

> However, back to serialization issue ... i'm going to take one [snip]
> more stab at convincing you a uniqueconstraint error should be flagged
> as a serial error (a "serializable error: unique constraint
> violated"?)

No it shouldn't. You have attempted to insert duplicate primary keys.
When you use select max with concurrent transactions, both transactions
will receive the same value for select max. The second one to insert
will be inserting a duplicate primary key.

PostgreSQL should say; "unique constraint violated", which it does.

Alan Gutierrez - ajglist(at)izzy(dot)net
http://khtml-win32.sourceforge.net/ - KHTML on Windows