Date: Thu, 22 Apr 2010 17:52:00 -0700
From: Amiri Barksdale <amiribarksdale@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Inconsistent Nested Set Moves
Message-ID: <20100423005159.GA9885@akbuntu.socal.rr.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.20 (2009-06-14)

Hi folks:

I am working on a nested set implementation using some of theadvice I
found in the archives, namely in this thread:

http://archives.postgresql.org/pgsql-sql/2002-11/msg00358.php

However, I don't seem to be able to get consistent results. More than
half the time I get a duplicate primary key error. Sometimes the update
goes through though.

Here is my schema:

   CREATE TABLE nested_set (
       id integer NOT NULL,
       lft integer NOT NULL,
       rgt integer NOT NULL,
       title character varying,
       text text
   );
   ALTER TABLE ONLY nested_set
       ADD CONSTRAINT nested_set_pkey PRIMARY KEY (lft, rgt);


And here is my sproc:

   CREATE FUNCTION pg_move_tree(integer, integer) RETURNS integer
       LANGUAGE plpgsql
       AS $_$

   -- Moves part of a nested set tree to another part.
   -- Pass in the left of the child (from) and the left of the parent (to)

   DECLARE

   cleft INTEGER;     cright INTEGER;
   pleft INTEGER;     pright INTEGER;
   leftbound INTEGER; rightbound INTEGER;
   treeshift INTEGER; cwidth INTEGER;
   lrange INTEGER; rrange INTEGER;

   BEGIN

   SELECT lft, rgt FROM nested_set WHERE lft = $1 INTO cleft, cright;
   SELECT lft, rgt FROM nested_set WHERE lft = $2 INTO pleft, pright;

   -- Make sure the child exists
   IF cleft IS NULL THEN
   RETURN 0;
   END IF;

   -- Make sure the parent exists
   IF pleft IS NULL THEN
   RETURN 0;
   END IF;

   -- Self-move makes no sense
   IF cleft = pleft THEN
   RETURN 0;
   END IF;

   -- Parent cannot be underneath the child
   IF pleft BETWEEN cleft AND cright THEN
   RETURN 0;
   END IF;

   -- Child may already be in the proper place
   IF cleft = pleft+1 THEN
   RETURN 1;
   END IF;

   IF cleft > pleft THEN 
   treeshift  := pleft - cleft + 1;
   leftbound  := pleft+1;
   rightbound := cleft-1;
   cwidth     := cright-cleft+1;
   lrange      :=  cright;
   rrange     :=  pleft;
   ELSE
   treeshift  := pleft - cright;
   leftbound  := cright + 1;
   rightbound := pleft;
   cwidth     := cleft-cright-1;
   lrange      :=  pleft + 1;
   rrange     :=  cleft;
   END IF;


   UPDATE nested_set
   SET lft = CASE
   WHEN lft BETWEEN leftbound AND rightbound THEN lft + cwidth
   WHEN lft BETWEEN cleft AND cright THEN lft + treeshift
   ELSE lft END,
   rgt = CASE
   WHEN rgt BETWEEN leftbound AND rightbound THEN rgt + cwidth
   WHEN rgt BETWEEN cleft AND cright THEN rgt + treeshift
   ELSE rgt END;
   --            WHERE lft < lrange OR rgt > rrange;
   RETURN 1;

   END;
   $_$;

Can someone help me debug this? It seems all fine. I realize this recipe
is 8 years old, but it should still work, no?

Amiri