Re: VACUUM/t_ctid bug (was Re: GiST concurrency commited)

> 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/