Re: self referencing tables/ nested sets etc... - Mailing list pgsql-general

On Thursday 25 March 2004 22:01, Manfred Koizar wrote:
> On Thu, 25 Mar 2004 20:56:35 +0100, Rob Hoopman <rob@tuna.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;