[postgis-users] Storing geometries in variables

[Paul Ramsey]

On Sat, Aug 22, 2020 at 5:16 PM Simon Greener <simon at spatialdbadvisor.com>
wrote:
>
> Python, unless I am wrong, is a mid/client tier language only; plpgsql
can only be deployed in the data tier.

Python can be used in the client side or on the server side via PL/Python.

However:

The server-side Python binding (create extension plpythonu) is "untrusted"
which means it is frequently not available (for example, on AWS RDS)
because it represents a server-side security risk. That means that
developing large chunks of functionality in PL/Python risks making your
application limited in terms of the deployment environments you can use.

The downside of doing Python processing on the *client* side is that all
the data you process has to traverse from the database to the client, and
back, and that is frequently a non-trivial overhead.

All this speaks to doing processing in PL/PgSQL (which is universally
available, and runs local to the database) when it makes sense for you.

With respect to optimization, it makes sense to use full SQL statements
often. If you find yourself writing LOOPs in PL/PgSQL it's possible you've
done something wrong, logically. If you let your processing be defined in
SQL you increase the odds of the planner finding optimizations that you
might miss if you're trying to be smart and control execution.

That said, some multi-stage processes benefit from writing temporary
results into TEMP tables and building indexes on them, as you go from stage
to stage. YMMV, depends on the problem. Again though, avoiding pushing the
data back and forth over the wire is a good thing.

ATB,
P
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200824/bad82dac/attachment.html>