[postgis-users] Exclusion constraint with custom operator not working as expected

[Darafei "Komяpa" Praliaskouski]

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 at 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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>


-- 
Darafei "Komяpa" Praliaskouski
OSM BY Team - http://openstreetmap.by/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210623/7f57460b/attachment.html>