Exclusion constraint with custom operator not working as expected

Hello,

The reason why constraint is not working is that GIST scan using your
operator does not return what you expect. Deeper debugging needed on your
side to fix that select to return the rows you need.

12:23:37 [kom] > explain select * from test_1 where g |*| 'LINESTRING(10
10,50 50)';
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐

│ QUERY PLAN
│
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤

│ Index Scan using test_1_g_excl on test_1 (cost=0.14..22.26 rows=635
width=36) │
│ Index Cond: (g |*|
'0102000000020000000000000000002440000000000000244000000000000049400000000000004940'::geometry)
│
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

(2 rows)

Time: 0,916 ms
12:23:41 [kom] > select * from test_1 where g |*| 'LINESTRING(10 10,50
50)';
┌─────┬───┐
│ fid │ g │
├─────┼───┤
└─────┴───┘
(0 rows)

Time: 0,638 ms

On Tue, Jun 22, 2021 at 11:30 PM Rhys A.D. Stewart <rhys.stewart@gmail.com>
wrote:

Greetings All,

Firstly, apologies for cross posting.
I would like to create a table which will contain postGIS geometries,
specifically linestrings. Each line string should be unique, unique in the
sense that no linestring should st_equals any other. (see
https://postgis.net/docs/manual-3.1/ST_Equals.html)

So, LINESTRING(10 10, 50 50) and LINESTRING(50 50, 10 10) are "st_equal".

I did the following:

BEGIN;

DROP OPERATOR IF EXISTS |*| (geometry, geometry) CASCADE;

CREATE OPERATOR |*| (
FUNCTION = st_equals,
LEFTARG = geometry,
RIGHTARG = geometry,
COMMUTATOR = |*|
);

CREATE OPERATOR CLASS my_ops FOR TYPE geometry
USING gist FAMILY gist_geometry_ops_2d AS
OPERATOR 99 |*| (geometry, geometry);

-- This returns True
SELECT 'LINESTRING(10 10, 50 50)'::geometry |*| 'LINESTRING(50 50, 10
10)'::geometry;

DROP TABLE IF EXISTS test_1 ;
CREATE TABLE test_1 (
fid integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
g geometry,
EXCLUDE USING GIST (g WITH |*|)
);

INSERT INTO test_1 (g) VALUES ('LINESTRING(10 10, 50 50)') ON CONFLICT DO
NOTHING;
INSERT INTO test_1 (g) VALUES ('LINESTRING(50 50, 10 10)') ON CONFLICT DO
NOTHING; -- This should do nothing;

SELECT fid, st_astext(g) FROM test_1; -- both rows returned, exclusion
doesn't work as I think it should.

ROLLBACK;

But where I expected the second insert to 'DO NOTHING', it very much did
something. So clearly I am missing something somewhere or my understanding
of exclusion constraints is lacking...or both. Any suggestions to get the
desired outcome? (Using a trigger doesn't count :-D )

But
Rhys
Peace & Love | Live Long & Prosper
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

--
Darafei "Komяpa" Praliaskouski
OSM BY Team - http://openstreetmap.by/