Thanks Thomas,
but, as stated after the first post, the need was for a PK as asked by postgres (ie. for tech needs, not for functionnal needs)
up to now, looks like we must create a PK (and so, the associated index) just to answer logical replication needs.(and qgis which also needs a PK)
that index (some kind of hash on the geom column + the other 2). have no meaning on a functionnal point of view, and there are chances that it will never be used by postgres in normal use..
as the hash will not help on any topological request...
that was the reason of the first mail: as we must create a PK, is there any way to make something useful and not this unuseful "thing" ?
Marc MILLAS
Senior Architect
+33607850334
Marc Millas schrieb am 03.06.2021 um 22:51:
> 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" ??
How do you define the "uniqueness" of the geometry?
GIST can support "uniqueness" through exclusion constraints.
It's not a primary key, so you can't create foreign keys referencing that table,
but it does ensure uniqueness (In fact the "normal" unique indexes are
essentially a special case of exclusion constraints)
create index on the_table using gist (int_column with =, text_col with =, geometry_col with &&);
Replace the && operator with whatever is appropriate for your use case.
Thomas
