[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Exclusion constraint with custom operator not working as expected
From: Darafei_"KomÑpa"_Praliaskouski <me () komzpa ! net>
Date: 2021-06-23 9:26:55
Message-ID: CAC8Q8tK=eo9bM7ExC=32GB-15dcHE=rYRaZEXK56k1acbOR2Cg () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
[Attachment #4 (text/plain)]
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/
[Attachment #5 (text/html)]
<div dir="ltr"><div>Hello,<br><br>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.<span \
style="font-family:monospace"><span \
style="color:rgb(178,104,24)"><br><br><br>12:23:37</span><span \
style="color:rgb(0,0,0)"> [</span><span \
style="font-weight:bold;color:rgb(255,84,84)">kom</span><span \
style="color:rgb(0,0,0)">] > explain select * from test_1 where g |*| \
'LINESTRING(10 10,50 50)'; \
</span><br>┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
<br>│ QUERY PLAN \
│ <br>├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
<br>│ Index Scan using test_1_g_excl on test_1 (cost=0.14..22.26 rows=635 width=36) \
│ <br>│ Index Cond: (g |*| \
'0102000000020000000000000000002440000000000000244000000000000049400000000000004940'::geometry) \
│ <br>└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
<br>(2 rows)
<br>
<br>Time: 0,916 ms
<br><span style="color:rgb(178,104,24)">12:23:41</span><span \
style="color:rgb(0,0,0)"> [</span><span \
style="font-weight:bold;color:rgb(255,84,84)">kom</span><span \
style="color:rgb(0,0,0)">] > select * from test_1 where g |*| 'LINESTRING(10 \
10,50 50)'; </span><br>┌─────┬───┐ <br>│ fid │ g │
<br>├─────┼───┤
<br>└─────┴───┘
<br>(0 rows)
<br>
<br>Time: 0,638 ms<br>
<br></span></div></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Tue, Jun 22, 2021 at 11:30 PM Rhys A.D. Stewart <<a \
href="mailto:rhys.stewart@gmail.com">rhys.stewart@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
dir="auto"><div dir="ltr"><div class="gmail_default" \
style="font-family:"trebuchet ms",sans-serif">Greetings All,</div><div \
class="gmail_default" style="font-family:"trebuchet \
ms",sans-serif"><br></div><div class="gmail_default" \
style="font-family:"trebuchet ms",sans-serif">Firstly, apologies for cross \
posting. </div><div class="gmail_default" style="font-family:"trebuchet \
ms",sans-serif" dir="auto">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 <a \
href="https://postgis.net/docs/manual-3.1/ST_Equals.html" \
target="_blank">https://postgis.net/docs/manual-3.1/ST_Equals.html</a>)</div><div \
class="gmail_default" style="font-family:"trebuchet ms",sans-serif" \
dir="auto"><br></div><div class="gmail_default" style="font-family:"trebuchet \
ms",sans-serif">So, LINESTRING(10 10, 50 50) and LINESTRING(50 50, 10 10) are \
"st_equal".</div><div class="gmail_default" \
style="font-family:"trebuchet ms",sans-serif"><br></div><div \
class="gmail_default" style="font-family:"trebuchet ms",sans-serif"> I did \
the following:<br></div><div class="gmail_default" style="font-family:"trebuchet \
ms",sans-serif"><br></div><div class="gmail_default" \
style="font-family:"trebuchet ms",sans-serif"></div><div \
class="gmail_default" style="font-family:"trebuchet ms",sans-serif">BEGIN; \
<br><br>DROP OPERATOR IF EXISTS |*| (geometry, geometry) CASCADE;<br><br>CREATE \
OPERATOR |*| (<br> FUNCTION = st_equals,<br> LEFTARG = geometry,<br> RIGHTARG = \
geometry,<br> COMMUTATOR = |*|<br> );<br><br>CREATE OPERATOR CLASS my_ops FOR TYPE \
geometry<br> USING gist FAMILY gist_geometry_ops_2d AS<br> OPERATOR 99 |*| (geometry, \
geometry);<br><br>-- This returns True<br>SELECT 'LINESTRING(10 10, 50 \
50)'::geometry |*| 'LINESTRING(50 50, 10 10)'::geometry;<br><br>DROP \
TABLE IF EXISTS test_1 ;<br>CREATE TABLE test_1 (<br> fid integer PRIMARY KEY \
GENERATED ALWAYS AS IDENTITY,<br> g geometry,<br> EXCLUDE USING GIST (g WITH \
|*|)<br>);<br><br>INSERT INTO test_1 (g) VALUES ('LINESTRING(10 10, 50 50)') \
ON CONFLICT DO NOTHING;<br>INSERT INTO test_1 (g) VALUES ('LINESTRING(50 50, 10 \
10)') ON CONFLICT DO NOTHING; -- This should do nothing; <br><br>SELECT fid, \
st_astext(g) FROM test_1; -- both rows returned, exclusion doesn't work as I \
think it should.<br><br>ROLLBACK;</div><div class="gmail_default" \
style="font-family:"trebuchet ms",sans-serif"><br></div><div \
class="gmail_default" style="font-family:"trebuchet ms",sans-serif">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 )<br></div><div class="gmail_default" \
style="font-family:"trebuchet ms",sans-serif"><br></div><div \
class="gmail_default" style="font-family:"trebuchet ms",sans-serif">But <br \
clear="all"></div><div><div dir="ltr"><div dir="ltr"><div><div \
dir="ltr"><div>Rhys</div>Peace & Love | Live Long & \
Prosper</div></div></div></div></div></div></div> </div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br> \
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" \
class="gmail_signature"><div dir="ltr">Darafei "Komяpa" \
Praliaskouski<br>OSM BY Team - <a href="http://openstreetmap.by/" \
target="_blank">http://openstreetmap.by/</a><br></div></div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic