> We have queries that use ltree for sorting too, the sort looks like this:
> order by subpath(ltreefield, 0, nlevel(ltreefield) - 1)
>
> But concurrency leads to a bug, that results in an sql-error:
> ERROR: invalid positions
contrib_regression=# select nlevel(t), subpath(t, 0, nlevel(t)-1) from
text2ltree('1.2.3.4.5') as t; nlevel | subpath
--------+--------- 5 | 1.2.3.4
(1 row)
contrib_regression=# select nlevel(t), subpath(t, 0, nlevel(t)-1) from
text2ltree('1') as t; nlevel | subpath
--------+--------- 1 |
(1 row)
contrib_regression=# select nlevel(t), subpath(t, 0, nlevel(t)-1) from
text2ltree('') as t;
ERROR: invalid positions
contrib_regression=#
It's incorrect arguments for subpath().
>
> It seems the ltree length parameter is set to 0 in the tuples, the content
> itself is still there: Example:
> Say the tuple was before treefield='1.2.3.4.5'
> After the occurence of the error, I get: treefield='' (empty, but not null)
>
> Using a tool Tom Lane told me to use, I checked it, and on-disk I had still
> "1.2.3.4.5", but the length parameter of the ltree column was 0 (sorry, I was
> wrong in my first mail, on-disk was broken too.)
Interesting... But with some test suite or more information I'm helpless.
How often do updates/inserts of table and/or ltree column occurs? Vacuum?
>
> Might this be somehow related to the intarray bugs?
No, except case when you update your table something like to:
update tbl set ltreefield=... where ltreefield ...;
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/