Forum OpenACS Q&A: Problem with connect by

Hi everybody,
I encountered this problem using the official connect by solution proposed by Dan W :

• when inserting a new row the insert trigger calls the next_sortkey function to get the next sortkey
• the next_sortkey function in turn uses the tree_encoding table to get the next value
• things work fine until you try to insert the 11th leaf to a node and all the rows after the 10th get the same sortkey, i.e.

  /00/07
  /00/08
  /00/09
  /00/0:
  /00/0:
  .....
  

The problem depends from this code fragment from the insert trigger:

select max(sortkey) into max_key
from sedi
where id_sede_super = new.id_sede_super;

because the sequence of the sortkey returned conflicts with the sequence of the tree_encoding table:

 
copy tree_encodings from stdin using delimiters '/' ;
0/0
1/1
2/2
3/3
4/4
5/5
6/6
7/7
8/8
9/9
10/:
11/;
...

Trying to insert the 11th row the select max(sortkey) always returns the value /00/09 instead of /00/0:

I'm posting this just to be sure that some workaround already exists before creating a new one.

TIA in advance,
Claudio Pasolini

Don,
really I don't know, because I used the Jonathan's RPM. The LANG environment variable is actually set to en_US and I found this in /var/lib/pgsql/i18n

LANG="en_US"
SUPPORTED="en_US:en"
SYSFONT="lat0-sun16"
SYSFONTACM="iso01"
export LANG LC_ALL LC_CTYPE LC_COLLATE LC_NUMERIC LC_CTYPE LC_TIME

There are some other threads about this in the OpenACS 4.x design
forum (try searching for "lang" and "collating" and "tree" or
something like that).

Out of the box OpenACS 4 works with LANG=C.  Among other things if
LANG != C then Postgres won't use indexes for "LIKE 'foo%'"-style
operators, which implies a horrible efficiency hit for any
hierarchical select using the sortkeys.

So, check the other threads on the issue, you'll find a fairly
complete discussion.  We don't have a solution that's satisfactory
for the case where LANG != C yet.