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(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/