[PROJ] PSC Motion: require SQLite 3.11 as minimum version

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

[PROJ] PSC Motion: require SQLite 3.11 as minimum version

Even Rouault-2
Hi,

It has been reported that PROJ 6.2.1 (also applies to master) runs super slow
if used with a too old SQLite version (https://github.com/OSGeo/PROJ/issues/
1718). It works fine with SQLite 3.11 (as found on Ubuntu 16.04 for example).
SQLite 3.11 was released on 2016-02-15, so meets our "older than 2 years"
rule.

Hence the following motion
~~
Require SQLite 3.11 for PROJ master
~~

+1 Even

PR implementing this ready in https://github.com/OSGeo/PROJ/pull/1721
Will apply for PROJ 6.3

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Howard Butler-3


> On Nov 7, 2019, at 8:15 AM, Even Rouault <[hidden email]> wrote:
>
> Hi,
>
> It has been reported that PROJ 6.2.1 (also applies to master) runs super slow
> if used with a too old SQLite version (https://github.com/OSGeo/PROJ/issues/
> 1718). It works fine with SQLite 3.11 (as found on Ubuntu 16.04 for example).
> SQLite 3.11 was released on 2016-02-15, so meets our "older than 2 years"
> rule.
>
> Hence the following motion
> ~~
> Require SQLite 3.11 for PROJ master
> ~~
>
> +1 Even
>
> PR implementing this ready in https://github.com/OSGeo/PROJ/pull/1721
> Will apply for PROJ 6.3
>
> --
> Spatialys - Geospatial professional services
> http://www.spatialys.com
> _______________________________________________
> PROJ mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/proj

_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Howard Butler-3
+0. I'd like to hear from the packagers on this. We're already making life complicated for them, and SQLite is a frequently referenced dependency. Does 3.11 pick up common RHEL/Centos installations? Android/iOS?

Howard

> On Nov 7, 2019, at 8:50 AM, Howard Butler <[hidden email]> wrote:
>
>
>
>> On Nov 7, 2019, at 8:15 AM, Even Rouault <[hidden email]> wrote:
>>
>> Hi,
>>
>> It has been reported that PROJ 6.2.1 (also applies to master) runs super slow
>> if used with a too old SQLite version (https://github.com/OSGeo/PROJ/issues/
>> 1718). It works fine with SQLite 3.11 (as found on Ubuntu 16.04 for example).
>> SQLite 3.11 was released on 2016-02-15, so meets our "older than 2 years"
>> rule.
>>
>> Hence the following motion
>> ~~
>> Require SQLite 3.11 for PROJ master
>> ~~
>>
>> +1 Even
>>
>> PR implementing this ready in https://github.com/OSGeo/PROJ/pull/1721
>> Will apply for PROJ 6.3
>>
>> --
>> Spatialys - Geospatial professional services
>> http://www.spatialys.com
>> _______________________________________________
>> PROJ mailing list
>> [hidden email]
>> https://lists.osgeo.org/mailman/listinfo/proj
>

_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Even Rouault-2
On jeudi 7 novembre 2019 08:52:48 CET Howard Butler wrote:
> +0. I'd like to hear from the packagers on this. We're already making life
> complicated for them, and SQLite is a frequently referenced dependency.
> Does 3.11 pick up common RHEL/Centos installations? Android/iOS?

This was triggered by someone trying on RHEL 7 that offers SQLite 3.7.17. So
the solution for them would also to have a backport of a more recent SQLite
version. As SQLite is quite backward compatible, I don't think this shouldn't
be an issue.

The alternative to this would be to modify our implementation to work decently
with older SQLite versions. But on a quick look, this doesn't seem to be
doable without writing horrible SQL requests in the code. I'm not even sure
this is doable for all requests. Or perhaps it would require creating extra
materialized table(s) instead of requesting a view which is a union of several
tables. I'm not super enthusiastic about that.

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Sebastiaan Couwenberg
In reply to this post by Howard Butler-3
On 11/7/19 3:52 PM, Howard Butler wrote:
> +0. I'd like to hear from the packagers on this. We're already making life complicated for them, and SQLite is a frequently referenced dependency. Does 3.11 pick up common RHEL/Centos installations? Android/iOS?

Debian has 3.16.2 in oldstable.

Ubuntu has 3.11.0 in xenial.

CentOS 7 has 3.7.17.
CentOS 8 has 3.26.0.

Kind Regards,

Bas

--
 GPG Key ID: 4096R/6750F10AE88D4AF1
Fingerprint: 8182 DE41 7056 408D 6146  50D1 6750 F10A E88D 4AF1
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

jmckenna
Administrator
In reply to this post by Even Rouault-2
+1

As a Windows packager (6,000+ downloads per month for the MapServer
community) we upgraded to minimum SQLite 3.17.0 for MS4W releases, as
that SQLite release has reported 25% performance boost through R-tree
indexing improvements.

-jeff



On 2019-11-07 10:15 AM, Even Rouault wrote:

> Hi,
>
> It has been reported that PROJ 6.2.1 (also applies to master) runs super slow
> if used with a too old SQLite version (https://github.com/OSGeo/PROJ/issues/
> 1718). It works fine with SQLite 3.11 (as found on Ubuntu 16.04 for example).
> SQLite 3.11 was released on 2016-02-15, so meets our "older than 2 years"
> rule.
>
> Hence the following motion
> ~~
> Require SQLite 3.11 for PROJ master
> ~~
>
> +1 Even
>
> PR implementing this ready in https://github.com/OSGeo/PROJ/pull/1721
> Will apply for PROJ 6.3
>
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Paul Ramsey
In reply to this post by Howard Butler-3
Some packagers may now feel punished for getting on the Proj6 train. They’d have been better off just continuing with Proj4 and waiting out the transition for a couple more years.
This isn’t an optional requirement for Proj6, basically it’s an unusably bad performance regression, so using Proj6 requires a newer SQLite given current state. The Motion is sort of beside the point, it’s just a technical fact.

P.

> On Nov 7, 2019, at 6:52 AM, Howard Butler <[hidden email]> wrote:
>
> +0. I'd like to hear from the packagers on this. We're already making life complicated for them, and SQLite is a frequently referenced dependency. Does 3.11 pick up common RHEL/Centos installations? Android/iOS?
>
> Howard
>
>> On Nov 7, 2019, at 8:50 AM, Howard Butler <[hidden email]> wrote:
>>
>>
>>
>>> On Nov 7, 2019, at 8:15 AM, Even Rouault <[hidden email]> wrote:
>>>
>>> Hi,
>>>
>>> It has been reported that PROJ 6.2.1 (also applies to master) runs super slow
>>> if used with a too old SQLite version (https://github.com/OSGeo/PROJ/issues/
>>> 1718). It works fine with SQLite 3.11 (as found on Ubuntu 16.04 for example).
>>> SQLite 3.11 was released on 2016-02-15, so meets our "older than 2 years"
>>> rule.
>>>
>>> Hence the following motion
>>> ~~
>>> Require SQLite 3.11 for PROJ master
>>> ~~
>>>
>>> +1 Even
>>>
>>> PR implementing this ready in https://github.com/OSGeo/PROJ/pull/1721
>>> Will apply for PROJ 6.3
>>>
>>> --
>>> Spatialys - Geospatial professional services
>>> http://www.spatialys.com
>>> _______________________________________________
>>> PROJ mailing list
>>> [hidden email]
>>> https://lists.osgeo.org/mailman/listinfo/proj
>>
>
> _______________________________________________
> PROJ mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/proj

_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Even Rouault-2
> This isn’t an optional requirement for
> Proj6, basically it’s an unusably bad performance regression, so using
> Proj6 requires a newer SQLite given current state.

Agreed. This probably mostly affects RHEL 7 which, while not being antiquated
from the point of view of RedHat's release policies, still ships with outdated
components by todays' standards. So RHEL 7 users have two options: use PROJ
5.x or PROJ 6 + backported SQLite.

> The Motion is sort of
> beside the point, it’s just a technical fact.

This motion is mostly purely procedural to be in compliance with
"Changing the version requirements for a dependency needs to be approved by
the PSC."
of https://proj.org/community/rfc/rfc-3.html as Kristian reminded me.

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Greg Troxel-2
Even Rouault <[hidden email]> writes:

>> This isn’t an optional requirement for
>> Proj6, basically it’s an unusably bad performance regression, so using
>> Proj6 requires a newer SQLite given current state.
>
> Agreed. This probably mostly affects RHEL 7 which, while not being antiquated
> from the point of view of RedHat's release policies, still ships with outdated
> components by todays' standards. So RHEL 7 users have two options: use PROJ
> 5.x or PROJ 6 + backported SQLite.

This is a fundamental issue with LTS releases.  The idea of building
current versions of software using dependencies from the LTS release
basically does not work.  Taken to the extreme, supporting that means
that every package has to work with all versions of dependencies -- and
compilers -- that were released over 5 years ago.

As for RHEL 7, it sounds like the question is "build modern proj with
old sqlite3", and it seems reasonable to just say that isn't supported;
presumably somebody choosing an LTS release also wants old proj, old
qgis, etc.  And if they want new, they probably should build an entire
tree of modern versions that are intercompatible.

All that said,

  NetBSD 8 base system: 3.17.0
  pkgsrc 2019Q3: 3.29.0
  pkgsrc curent: 3.30.1

I don't have a NetBSD 7 system handy, or 9 (not released), but since
proj is in pkgsrc and not in base, it's trivial to make it depend on
pkgsrc sqlite3.

So I see no issue with requiring 3.11.

I geuss the question is

  If someone builds proj with sqlite3 so old that it will be slow, is it
  better to let it work and be slow or fail the build?

and I can see your point that it's better to fail.
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Thomas Knudsen
+1
(convinced by the evidence and reasoning provided by Greg and Bas, although it would be nice if we in any practical way could "suggest", rather than "require" >=3.11)

Den tor. 7. nov. 2019 kl. 17.37 skrev Greg Troxel <[hidden email]>:
Even Rouault <[hidden email]> writes:

>> This isn’t an optional requirement for
>> Proj6, basically it’s an unusably bad performance regression, so using
>> Proj6 requires a newer SQLite given current state.
>
> Agreed. This probably mostly affects RHEL 7 which, while not being antiquated
> from the point of view of RedHat's release policies, still ships with outdated
> components by todays' standards. So RHEL 7 users have two options: use PROJ
> 5.x or PROJ 6 + backported SQLite.

This is a fundamental issue with LTS releases.  The idea of building
current versions of software using dependencies from the LTS release
basically does not work.  Taken to the extreme, supporting that means
that every package has to work with all versions of dependencies -- and
compilers -- that were released over 5 years ago.

As for RHEL 7, it sounds like the question is "build modern proj with
old sqlite3", and it seems reasonable to just say that isn't supported;
presumably somebody choosing an LTS release also wants old proj, old
qgis, etc.  And if they want new, they probably should build an entire
tree of modern versions that are intercompatible.

All that said,

  NetBSD 8 base system:         3.17.0
  pkgsrc 2019Q3:                3.29.0
  pkgsrc curent:                3.30.1

I don't have a NetBSD 7 system handy, or 9 (not released), but since
proj is in pkgsrc and not in base, it's trivial to make it depend on
pkgsrc sqlite3.

So I see no issue with requiring 3.11.

I geuss the question is

  If someone builds proj with sqlite3 so old that it will be slow, is it
  better to let it work and be slow or fail the build?

and I can see your point that it's better to fail.
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj

_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Even Rouault-2
On jeudi 7 novembre 2019 17:49:26 CET Thomas Knudsen wrote:
> +1
> (convinced by the evidence and reasoning provided by Greg and Bas, although
> it would be nice if we in any practical way could "suggest", rather than
> "require" >=3.11)

No, this is really a functional requirement to get nominal behaviour of the
library. Otherwise runtime experience will be terrible (it is not like running
with older sqlite is 10% slower. It is 1 to 2 order of magnitude slower. see
the ticket) and people will complain. If people really want to run against
older versions, they can easily change configure.ac/CMakeLists.txt to remove
the check, but at least they will know that they're doing risky games and
hopefully they won't complain to community forums that it is slow.

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Kristian Evers-2
I am also +1 on this. I am just wondering if 3.11 is the lowest possible
version that is feasible to use with PROJ. From the linked issue I understand
that SQLite 3.8.x is too slow and that it works with 3.11. I take it that 3.11 was
chosen because it was readily available for Even. Would 3.9 or 3.10 work
as well? Or was there a specific change in 3.11 that we know is the one that
makes the difference?

/Kristian

> On 7 Nov 2019, at 18:14, Even Rouault <[hidden email]> wrote:
>
> On jeudi 7 novembre 2019 17:49:26 CET Thomas Knudsen wrote:
>> +1
>> (convinced by the evidence and reasoning provided by Greg and Bas, although
>> it would be nice if we in any practical way could "suggest", rather than
>> "require" >=3.11)
>
> No, this is really a functional requirement to get nominal behaviour of the
> library. Otherwise runtime experience will be terrible (it is not like running
> with older sqlite is 10% slower. It is 1 to 2 order of magnitude slower. see
> the ticket) and people will complain. If people really want to run against
> older versions, they can easily change configure.ac/CMakeLists.txt to remove
> the check, but at least they will know that they're doing risky games and
> hopefully they won't complain to community forums that it is slow.
>
> --
> Spatialys - Geospatial professional services
> http://www.spatialys.com
> _______________________________________________
> PROJ mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/proj

_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Even Rouault-2
On jeudi 7 novembre 2019 18:27:59 CET Kristian Evers wrote:
> I am also +1 on this. I am just wondering if 3.11 is the lowest possible
> version that is feasible to use with PROJ. From the linked issue I
> understand that SQLite 3.8.x is too slow and that it works with 3.11. I
> take it that 3.11 was chosen because it was readily available for Even.
> Would 3.9 or 3.10 work as well? Or was there a specific change in 3.11 that
> we know is the one that makes the difference?

3.11 is likely not the minimum version. I tried to look at the SQLite release
notes to find the right version, but nothing obvious struck me, so this would
require doing bisection to find out the minimum version. I don't think it
really matters to spot the exact version. From the feedback we got up to now,
the practical concern is with RHEL 7 that ships something too old. Other
environments have at least 3.11. And anyway, even if it did work for now with
something older than 3.11, I wouldn't test routinely against it, and our CI
doens't test that anymore (it is on Xenial on master, so using 3.11), so it
might break in the future.

On thinking, what has intriguied me is that on Travis, the 6.2 branch runs
with Ubuntu Trusty / SQLite 3.8.2. Looking at logs, I can indeed see that the
runtime of test_cpp_api reported there is abnormaly long (45s) but we didn't
notice. Locally, with a same -O2 build of the 6.2 branch, I get 5.2s with
sqlite 3.11 vs 32s with 3.8.2. So there might be some amortizing of the
penalty for runs in the same process / reusing the same database connection.

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Kurt Schwehr-2
In reply to this post by Kristian Evers-2
+1 Kurt

If sqlite 3.11.0 is a blocker for a distribution, should they really be talking about proj 6?

Nothing is jumping out at me looking at https://sqlite.org/changes.html as to the version, but 3.11.0 seems safe.

On Thu, Nov 7, 2019 at 10:28 AM Kristian Evers <[hidden email]> wrote:
I am also +1 on this. I am just wondering if 3.11 is the lowest possible
version that is feasible to use with PROJ. From the linked issue I understand
that SQLite 3.8.x is too slow and that it works with 3.11. I take it that 3.11 was
chosen because it was readily available for Even. Would 3.9 or 3.10 work
as well? Or was there a specific change in 3.11 that we know is the one that
makes the difference?

/Kristian

> On 7 Nov 2019, at 18:14, Even Rouault <[hidden email]> wrote:
>
> On jeudi 7 novembre 2019 17:49:26 CET Thomas Knudsen wrote:
>> +1
>> (convinced by the evidence and reasoning provided by Greg and Bas, although
>> it would be nice if we in any practical way could "suggest", rather than
>> "require" >=3.11)
>
> No, this is really a functional requirement to get nominal behaviour of the
> library. Otherwise runtime experience will be terrible (it is not like running
> with older sqlite is 10% slower. It is 1 to 2 order of magnitude slower. see
> the ticket) and people will complain. If people really want to run against
> older versions, they can easily change configure.ac/CMakeLists.txt to remove
> the check, but at least they will know that they're doing risky games and
> hopefully they won't complain to community forums that it is slow.
>
> --
> Spatialys - Geospatial professional services
> http://www.spatialys.com
> _______________________________________________
> PROJ mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/proj

_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj

_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Greg Troxel-2
Kurt Schwehr <[hidden email]> writes:

> +1 Kurt
>
> If sqlite 3.11.0 is a blocker for a distribution, should they really be
> talking about proj 6?

Of course not.  I think this is 100% about people wanting to run LTS and
then build new proj.
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Mike Taves
In reply to this post by Even Rouault-2
On Fri, 8 Nov 2019 at 07:51, Even Rouault <[hidden email]> wrote:
> 3.11 is likely not the minimum version. I tried to look at the SQLite release
> notes to find the right version, but nothing obvious struck me, so this would
> require doing bisection to find out the minimum version. I don't think it
> really matters to spot the exact version.

I found a bit of time to do a few bisection experiments, and I found
the exact version:

version-3.8.0    # 1.82user
version-3.8.10.2 # 0.78user
version-3.8.11   # 0.06user
version-3.8.11.1 # 0.07user
version-3.9.0    # 0.06user
version-3.9.2    # 0.07user
version-3.30.1   # 0.05user

SQLite version 3.8.11 released on 2015-07-27 should be the minimum version.

The closest item that I can see in the release notes [1] is
"Miscellaneous micro-optimizations"

[1] https://www.sqlite.org/changes.html#version_3_8_11
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Even Rouault-2
In reply to this post by Howard Butler-3
> > Hence the following motion
> > ~~
> > Require SQLite 3.11 for PROJ master
> > ~~
> >

I declare this motion passed with +1 from PSC members Thomas, Kristian, Kurt
and myself and +0 from Howard.

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj
Reply | Threaded
Open this post in threaded view
|

Re: PSC Motion: require SQLite 3.11 as minimum version

Even Rouault-2
On samedi 9 novembre 2019 11:33:54 CET Even Rouault wrote:
> > > Hence the following motion
> > > ~~
> > > Require SQLite 3.11 for PROJ master
> > > ~~
>
> I declare this motion passed with +1 from PSC members Thomas, Kristian, Kurt
> and myself and +0 from Howard.

For RHEL 7 users, Howard just pointed me to this backport package of sqlite 3.30.1
that Devrim Gündüz has just done:
https://git.postgresql.org/gitweb/?p=pgrpms.git;a=commit;h=6955796a5a02a794b483551f3336b6af05b3d6b8

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
PROJ mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/proj