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
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@xxxxxxxxx> 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 )ButRhysPeace & Love | Live Long & Prosper
postgis-users mailing list
postgis-users@xxxxxxxxxxxxxxx
https://lists.osgeo.org/mailman/listinfo/postgis-users
