Ryan VanderBijl <rvbijl@vanderbijlfamily.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.
regards, tom lane