Re: index unique

Cristal clear !
and it have to be the case as my test was done with some not so random data.

but this mean that we cannot put a bunch of datatypes in a PK, as soon as
it may be longer than 2701.
I know, its clearly stated in postgres doc about btree," The only
limitation is that an index entry cannot exceed approximately one-third of
a page (after TOAST compression, if applicable)."
but as this looks quite hard coded, it means that for long utf8 things the
data length is not so long before hitting the limit.

is there any plan to adress this ?
kind of index toast ?? :-)

thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Tue, Jun 8, 2021 at 8:42 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> On 2021-06-08 18:30:16 +0200, Marc Millas wrote:
> > the only pb observed is the size of the object accepted. if the geom is
> a bit
> > "big" then the index errors.about btree size of index object.
> > but if I create a table test_l with a text column blabla as a PK, and
> insert a
> > 100 000 character long string , no pb.
> > if I do an explain analyze select blabla from test_l order by blabla,
> postgres
> > tells me that it did an index only scan.
> > so, I wonder why Postgres is able to put a 100 000 long text in a btree
> index
> > and NOT a geom column which wkt is 10 000 bytes long.
>
> Good question. Maybe your texts compresses better than your geometries:
>
> hjp=> insert into t(t) values(repeat('a', 235327) || '1');
> INSERT 0 1
> Time: 60.057 ms
> hjp=> insert into t(t) values(repeat('a', 235328) || '1');
> ERROR: index row size 2720 exceeds maximum 2712 for index "t_pkey1"
> HINT: Values larger than 1/3 of a buffer page cannot be indexed.
> Consider a function index of an MD5 hash of the value, or use full text
> indexing.
> Time: 58.751 ms
>
> Note the difference between the length of the string I was trying to
> insert and the length of the row it complains about.
>
> hp
>
> --
> _ | Peter J. Holzer | Story must make more sense than reality.
> |_|_) | |
> | | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>