From: Amiri Barksdale <amiribarksdale@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Inconsistent Nested Set Moves
Date: Thu, 22 Apr 2010 17:52:00 -0700
Message-ID: <20100423005159.GA9885@akbuntu.socal.rr.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: amiribarksdale@gmail.com
Subject: Re: Inconsistent Nested Set Moves
In-Reply-To: <20100423005159.GA9885@akbuntu.socal.rr.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox