Hi Paul,
obviously the bounding box is not a perfect choice. we also think of one of the "centers" point, but quite similar non uniqueness.
so, if no "tech" solution, we continue to work with the business to try to find an appropriate PK
thanks
On Thu, Jun 3, 2021 at 10:57 PM Paul Ramsey <pramsey@xxxxxxxxxxxxxxxxx> wrote:
Primary key is going to be a BTREE index. I'm surprised you require the geometry in order to achieve uniqueness?
You can't put the geometry into a BTREE because it's too large.
You could add a column and stick the MD5 hash of the geometry there, and use that as the last piece of uniqueness?
If the bbox of the geometry is "good enough" for your key, that kind of speaks to the idea that maybe your geometry doesn't need to be part of the PK? It's possible for different geometries to have the same bbox...
ATB,
P
> On Jun 3, 2021, at 1:51 PM, Marc Millas <marc.millas@xxxxxxxxxx> wrote:
>
> Hi,
> postgres 12 with postgis.
> on a table we need a primary key and to get a unique combinaison, we need 3 columns of that table:
> 1 of type integer,
> 1 of type text,
> 1 of type geometry
>
> creating the PK constraint doesn work: (even with our current small data set)
> ERROR: index row size 6072 exceeds btree version 4 maximum 2704 for index "xxx_spkey"
> DETAIL: Index row references tuple (32,1) in relation "xxx".
> 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.
>
> ok. we can do this.
> but if so, we need to create a gist index on the geometry column to do any topology request.
> so 2 indexes containing this single column.
>
> if we install extension btree_gist, no pb to create an index on all 3 columns.
> but as gist does not support unicity, this index cannot be used for the PK.
>
> OK, we may try to use a function to get the bounding box around the geometry objects and use the result into a btree index........
>
> Any idea (I mean: another idea !) to tackle this ?
> Or any critic on the "solution" ??
>
> thanks,
>
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
