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 www.mokadb.com On Tue, Jun 8, 2021 at 10:51 PM Thomas Kellerer <sham...@gmx.net> wrote: > 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 > > >
