[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Inconsistent behavior with ST_Within across versions
From: Imre Samu <pella.samu () gmail ! com>
Date: 2022-02-11 16:46:22
Message-ID: CAJnEWwnwOXjAQoDJRaozfAomTw=m04gR6bk1a=9-4XN9L9btPA () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
> I've updated my Trac ticket accordingly.
some other testing ideas *for the next week :*
1.) As I see the latest PostgreSQL patch release is available ( 2022-02-10 )
and some corrupted index problem has been fixed in the 14.2:
https://www.postgresql.org/docs/release/14.2/
*"However, some bugs have been found that may have resulted in corrupted
indexes, as explained in the first two changelog entries. *
* If any of those cases apply to you, it's recommended to reindex
possibly-affected indexes after updating."*
So, in theory, you should re-check your problem *the next week *- with
the newer "postgis/postgis" images ( based on 14.2; 13.6; 12.10; 11.15 )
2.) You can add some Debian-based docker images to your test cases
so we can check: it is a "musl" related problem or not.
3.) You can also test the "14-master" image ( near ~master postgis, geos,
gdal .. )
https://github.com/postgis/docker-postgis/blob/master/14-master/Dockerfile#L183_L204
Kind Regards,
Imre
Carsten Byrman <byrman+postgis@posteo.nl> ezt írta (időpont: 2022. febr.
11., P, 15:53):
> Thanks for your recommendations, Imre. I've updated my Trac ticket
> accordingly.
>
> Kind regards, Carsten
>
>
> On 11 Feb 2022, at 13:46, Imre Samu <pella.samu@gmail.com> wrote:
>
> Hi Carsten,
>
> > but am just not sure I chose the appropriate milestone/component/version
> > Could you please give me some guidance here?
>
> ( I am a docker-postgis contributor)
>
> I am so sorry, but not "all" docker postgis/postgis image updating
> regularly ( with minor postgres,postgis) patches, bug fixes
> Only as in this repo: https://github.com/postgis/docker-postgis
> (scheduled on every Monday )
>
> You can check the "age / created " of the local docker image
> $ docker images postgis/postgis
> postgis/postgis 11-2.5-alpine 10c7c19a572c 4 days ago 330MB
> postgis/postgis 11-3.2-alpine 0303403af49f 4 days ago 369MB
> postgis/postgis 11-3.1-alpine f8c3965c17b8 7 weeks ago 328MB <-- too old
> postgis/postgis 11-3.0-alpine 4605eb1b2223 14 months ago 289MB <-- extreme
> old
>
> So I am not recommending the current -3.0; -3.1 docker images ( or any
> images older than 1 month, like "12-2.5-alpine" )
> This is a technical limitation of the current docker image workflow. (
> probably it can be improved in the future )
>
> You can check the actual version with:
> SELECT version();
> SELECT PostGIS_Full_Version();
>
>
> The other important issue for debugging the alpine version ( 3.12 - 3.15 )
> and you can check with "cat /etc/os-release"
>
> $ docker run -t --rm postgis/postgis:11-3.0-alpine sh -c "cat
> /etc/os-release | grep VERSION"
> VERSION_ID=3.12.2
> $ docker run -t --rm postgis/postgis:11-3.2-alpine sh -c "cat
> /etc/os-release | grep VERSION"
> VERSION_ID=3.15.0
>
>
> So my recommendations:
> - please remove the "older docker" images from your test cases
> - and add postgres / postgis / alpine versions
>
> It is an interesting issue;
>
> Regards,
> Imre
>
>
> Carsten Byrman <byrman+postgis@posteo.nl> ezt írta (időpont: 2022. febr.
> 11., P, 7:41):
>
>> Hello,
>>
>> I noticed inconsistent behavior with ST_Within across different
>> PostgreSQL-PostGIS versions. Most notably, the outcome (not performance) of
>> my query is determined by the presence of an index, which strikes me as
>> odd. I filed my first bug report about 3 months ago and I expect no
>> solution any time soon, but am just not sure I chose the appropriate
>> milestone/component/version: https://trac.osgeo.org/postgis/ticket/5025.
>> Perhaps it's not even a PostGIS but a PostgreSQL issue. Could you please
>> give me some guidance here?
>>
>> Thanks, Carsten
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
<div dir="ltr">> I've updated my Trac ticket accordingly.<div><br></div><div>some \
other testing ideas <b><font color="#0000ff">for the next week \
:</font></b></div><div><br></div><div>1.) As I see the latest PostgreSQL patch \
release is available ( 2022-02-10 )</div><div> and some corrupted index problem \
has been fixed in the 14.2: <a \
href="https://www.postgresql.org/docs/release/14.2/">https://www.postgresql.org/docs/release/14.2/</a></div><blockquote \
style="margin:0 0 0 40px;border:none;padding:0px"><div><i><font \
color="#0000ff">"However, some bugs have been found that may have resulted in \
corrupted indexes, as explained in the first two changelog entries. \
</font></i></div><div><i><font color="#0000ff"> If any of those cases apply to you, \
it's recommended to reindex possibly-affected indexes after \
updating."</font></i></div></blockquote> So, in theory, you should re-check \
your problem <b>the next week </b>- with the newer "postgis/postgis" images \
( based on 14.2; 13.6; 12.10; 11.15 )<div><div><br></div><div>2.) You can add some \
Debian-based docker images to your test cases </div><div> so we can check: it \
is a "musl" related problem or not.</div><div><br></div><div>3.) You can \
also test the "14-master" image ( near ~master postgis, geos, gdal .. \
) </div><div> <a \
href="https://github.com/postgis/docker-postgis/blob/master/14-master/Dockerfile#L183_ \
L204">https://github.com/postgis/docker-postgis/blob/master/14-master/Dockerfile#L183_L204</a></div><div><br></div><div>Kind \
Regards,</div><div> Imre</div><div> \
<br></div><div><br><div><br></div><div><br><div><br><div><br></div></div></div></div></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">Carsten Byrman <<a \
href="mailto:byrman%2Bpostgis@posteo.nl">byrman+postgis@posteo.nl</a>> ezt írta \
(időpont: 2022. febr. 11., P, 15:53):<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 style="overflow-wrap: break-word;">Thanks for \
your recommendations, Imre. I've updated my Trac ticket \
accordingly.<div><br><div>Kind regards, Carsten</div><div><br><div><br><blockquote \
type="cite"><div>On 11 Feb 2022, at 13:46, Imre Samu <<a \
href="mailto:pella.samu@gmail.com" target="_blank">pella.samu@gmail.com</a>> \
wrote:</div><br><div><div dir="ltr">Hi Carsten,<div><br></div><div>> <span>but \
am just not sure I chose the appropriate \
milestone/component/version</span></div><div>> <span>Could you please give me \
some guidance here?</span></div><div><span><br></span></div><div>( I am a \
docker-postgis contributor) </div><div><br></div><div>I am so sorry, but not \
"all" docker <font face="monospace">postgis/postgis</font> image updating \
regularly ( with minor postgres,postgis) patches, bug fixes </div><div>Only as in \
this repo: <a href="https://github.com/postgis/docker-postgis" \
target="_blank">https://github.com/postgis/docker-postgis</a> (scheduled on every \
Monday )<br></div><div><br></div><div>You can check the "age / created " of \
the local docker image</div><div><div \
style="color:rgb(235,209,183);background-color:rgb(54,49,44);font-family:"Droid \
Sans Mono","monospace",monospace;font-size:14px;line-height:19px;white-space:pre-wrap"><div>$ \
docker images postgis/postgis</div><div>postgis/postgis 11-2.5-alpine \
10c7c19a572c 4 days ago 330MB</div><div>postgis/postgis 11-3.2-alpine \
0303403af49f 4 days ago 369MB</div><div>postgis/postgis 11-3.1-alpine \
f8c3965c17b8 7 weeks ago 328MB <-- too old </div><div>postgis/postgis \
11-3.0-alpine 4605eb1b2223 14 months ago 289MB <-- extreme \
old</div></div></div><div><br>So I am not recommending the current -3.0; -3.1 \
docker images ( or any images older than 1 month, like "12-2.5-alpine" \
)<br></div><div>This is a technical limitation of the current docker image workflow. \
( probably it can be improved in the future )</div><div><br></div><div>You can check \
the actual version with:</div><div><div \
style="color:rgb(235,209,183);background-color:rgb(54,49,44);font-family:"Droid \
Sans Mono","monospace",monospace;font-size:14px;line-height:19px;white-space:pre-wrap"><div>SELECT \
<span style="color:rgb(96,163,101)">version</span>();</div><div>SELECT <span \
style="color:rgb(96,163,101)">PostGIS_Full_Version</span>();</div></div></div><div><br></div><div><br></div><div>The \
other important issue for debugging the alpine version ( 3.12 - 3.15 )</div><div>and \
you can check with <font face="monospace">"cat \
/etc/os-release"</font></div><div><br></div><div><div \
style="color:rgb(235,209,183);background-color:rgb(54,49,44);font-family:"Droid \
Sans Mono","monospace",monospace;font-size:14px;line-height:19px;white-space:pre-wrap"><div>$ \
docker run -t --rm postgis/postgis:11-3.0-alpine sh -c <span \
style="color:rgb(248,187,57)">"cat /etc/os-release | grep \
VERSION"</span></div><div>VERSION_ID=3.12.2</div><div>$ docker run -t --rm \
postgis/postgis:11-3.2-alpine sh -c <span style="color:rgb(248,187,57)">"cat \
/etc/os-release | grep \
VERSION"</span></div><div>VERSION_ID=3.15.0</div><br></div></div><div><br></div><div>So \
my recommendations:</div><div>- please remove the "older docker" images \
from your test cases</div><div>- and add postgres / postgis / alpine \
versions</div><div><br></div><div>It is an interesting \
issue;</div><div><br></div><div>Regards,</div><div> Imre \
</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">Carsten Byrman <<a href="mailto:byrman%2Bpostgis@posteo.nl" \
target="_blank">byrman+postgis@posteo.nl</a>> ezt írta (időpont: 2022. febr. \
11., P, 7:41):<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><span>Hello,</span><div><br></div><div>I \
noticed inconsistent behavior with ST_Within across different PostgreSQL-PostGIS \
versions. Most notably, the outcome (not performance) of my query is determined by \
the presence of an index, which strikes me as odd. I filed my first bug report about \
3 months ago and I expect no solution any time soon, but am just not sure I chose the \
appropriate milestone/component/version: <a \
href="https://trac.osgeo.org/postgis/ticket/5025" \
target="_blank">https://trac.osgeo.org/postgis/ticket/5025</a>. Perhaps it's not even \
a PostGIS but a PostgreSQL issue. Could you please give me some guidance \
here?</div><div><br></div><div>Thanks, \
Carsten</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>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" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></div></ \
blockquote></div><br></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>
_______________________________________________
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