[Qgis-user] updating postgres updateable view much slower from attribute table than using db manager

[Matthias Kuhn]

Hi Janneke,

The reason for individual statements are mainly that expressions (field
calculator) and SQL (table manager) are not completely equal. Some
functions are only supported by QGIS, some only by postgres/postgis. The
expressions should be supported by all data providers (different
database systems, non-database systems) and therefore QGIS plays safe
and just does the calculation always on its own.

Some background:
We have this summer started to be able to generate postgres SQL from
expressions (where possible) but this has only been applied to some
isolated areas (like filtering for rendering). It would theoretically be
possible to use it for provider side updates as well but that's an
ambitious task that will need some work by an experienced developer.

I'd propose looking into what makes updating your view so expensive
(probably easier) or consider to contribute towards provider side updates

Matthias

On 11/29/2015 10:03 PM, Janneke van Dijk wrote:
> Hi Jürgen,
>
> After your remarks I thought I would try the individual update
> statements (7000 of them ) directly on postgres. Indeed, that gives an
> out of memory error after 10 minutes. Not sure if that is entirely up
> to the expensive view or that there is something more going on. I also
> tried writing the update statement as an update on the view with the
> where clause being 'where id in (list of ids)', this again goes
> quickly (6 seconds).
>
> To sum it up: unless there is a way in QGIS to update selections using
> one update statement with a where clause (as opposed to individual
> update statements per record), it is not possible to work with larger
> selections on 'expensive' views in postgres. Would that be difficult
> to implement, or are there other reasons for issuing individual
> statements?
>
> Thanks again all for your thoughts,
> Janneke
>
> On 29/11/2015 22:36, Jürgen E. Fischer wrote:
>> Hi Janneke,
>>
>> On Sun, 29. Nov 2015 at 20:44:54 +0300, Janneke van Dijk wrote:
>>> Using the field calculator either from the attribute table or from main
>>> window with the attribute table closed both takes too long to be useful. Not
>>> the actual calculation, but the saving of the edits.  To be clear: I
>>> originally used the update expression bar to update the selection, but all
>>> three methods take too long to save.
>> That's because - as Matthias already said - the expression are evaluated for
>> each record and the changed attribute value is temporary stored in QGIS.  When
>> you commit the changed records are iterated and a separate UPDATE statements is
>> issued for each record.   And that's what takes long - although an expensive
>> view contributes to the time each UPDATE needs.
>>
>> The comment about the attribute table was just because for each change also the
>> attribute table(s) UI is updated.  So execution might be quicker if there are
>> no open attribute tables.  But the actual updates should perform the same from
>> wherever you invoke the feature calculator.
>>
>>
>> Jürgen
>>
>>
>>
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

-- 
Matthias Kuhn
OPENGIS.ch - https://www.opengis.ch
Spatial • (Q)GIS • PostGIS • Open Source

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20151130/7f74335d/attachment.html>