Re: self referencing tables/ nested sets etc...

On Thursday 25 March 2004 22:01, Manfred Koizar wrote:
> On Thu, 25 Mar 2004 20:56:35 +0100, Rob Hoopman <rob(at)tuna(dot)nl> wrote:
> >> > It appears that when adding more than 48 sub nodes
> >> >to any node in the tree, craps out because of an INT8 column
> >> > overflowing.
> >>
> >> AFAICS it doesn't depend on the number of siblings, but it fails when
> >> the sum of the numbers in dotted path notation exceeds 62.
> >
> >Maybe, but some of the intermediate steps are larger than the number that
> > gets stored in the end. Actually that's where this implementation broke
> > for me.
>
> Rob, do you still have the functions
Yes

> and the data
No, but I can reproduce. I wrote a function that let's me insert a number of
child nodes in the tree.

> that led to the
> overflow? If so, would you care to locate the parent of the node you
> failed to insert and this parent's last child. Then please post the
> output of
>
> SELECT pk, numer, denom, path(numer, denom)
> FROM yourtable
> WHERE pk = 'parentpk' OR pk = 'childpk';

Here you go:
test=> SELECT name, numer, denom, path(numer,denom)
test-> FROM emps
test-> WHERE name = 'Drone 1.1.10.8' OR name = 'Drone 1.1.10.8.29';
name | numer | denom | path
-------------------+-----------------+-----------------+--------------
Drone 1.1.10.8 | 1573379 | 1048576 | .1.1.10.8
Drone 1.1.10.8.29 | 844700881780739 | 562949953421312 | .1.1.10.8.29
(2 rows)

Have another:
test=> SELECT name, numer, denom, path(numer,denom)
test-> FROM emps
test-> WHERE name = 'KING' OR name = 'Drone 1.48';
name | numer | denom | path
------------+-----------------+-----------------+-------
KING | 3 | 2 | .1
Drone 1.48 | 562949953421315 | 562949953421312 | .1.48

test=>

So it seems that you are right, but the magic number seems to be 49

>
> I'd like to find out whether OMPM is flawed or my theory about it.
I wouldn't want to rule out the possibility of me screwing up somewhere in the
code. I had never done any plpgsl before yesterday.

Cheers,
Rob

Some more info:

The insert fails like this after enabling some debugging ( ni_insert_nodes is
the function that autogenerates childnodes ):
test2=> SELECT ni_insert_nodes( '1', 1 );
NOTICE: Current Child: 49
NOTICE: Last Child: 49
NOTICE: Inserting Drone 1.49
NOTICE: >>>> start child_number
NOTICE: num is: 1
NOTICE: den is: 1
NOTICE: child is: 1
NOTICE: <<<< end child_number
NOTICE: >>>> start child_number
NOTICE: num is: 3
NOTICE: den is: 2
NOTICE: child is: 49
NOTICE: <<<< end child_number
WARNING: Error occurred while executing PL/pgSQL function child_numer
WARNING: while casting return value to function's return type
ERROR: Bad int8 external representation "1.12589990684263e+15"

The function that fails:
CREATE FUNCTION child_numer( INT8, INT8, INT8) RETURNS INT8 AS'
DECLARE
num ALIAS FOR $1;
den ALIAS FOR $2;
child ALIAS FOR $3;
BEGIN
RAISE NOTICE '' >>>> start child_number'';
RAISE NOTICE ''num is: %'', num;
RAISE NOTICE ''den is: %'', den;
RAISE NOTICE ''child is: %'', child;

RAISE NOTICE '' <<<< end child_number'';
RETURN num*pow(2, child)+3-pow(2, child);
END
' LANGUAGE plpgsql;