Exclusion constraint with custom operator not working as expected

Поиск
Список
Период
Сортировка
От Rhys A.D. Stewart
Тема Exclusion constraint with custom operator not working as expected
Дата
Msg-id CACg0vTmVJD64Tm1CPeJduAQfPzuBoZFS2zFXcv0YUxLkXfMYPg@mail.gmail.com
обсуждение исходный текст
Ответы Re: [postgis-users] Exclusion constraint with custom operator not working as expected
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Vijaykumar Jain
Дата:
Сообщение: Re: www.postgresql-archive.org content deleted
Следующее
От: Niels Jespersen
Дата:
Сообщение: SV: cpu-intensive immutable function and parallel scan