Re: [Qgis-developer] Grouping insert/update when sending to PostGIS
Hi Rémi, On Thu, 28. May 2015 at 17:07:32 +0200, Rémi Cura wrote: > this doesn't explain why each operations isn't grouped. > For instance all the addFeatures are grouped into one transaction, but not > into one statement. addFeatures does one prepared INSERT statement. Jürgen -- Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31 Dipl.-Inf. (FH) Rheinstraße 13 Fax. +49-4931-918175-50 Software Engineer D-26506 Norden http://www.norbit.de QGIS release manager (PSC) GermanyIRC: jef on FreeNode signature.asc Description: Digital signature ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Grouping insert/update when sending to PostGIS
Hey Jürgen, this doesn't explain why each operations isn't grouped. For instance all the addFeatures are grouped into one transaction, but not into one statement. Cheers, Rémi-C 2015-05-28 16:39 GMT+02:00 Jürgen E. : > Hi Rémi, > > On Thu, 28. May 2015 at 12:06:22 +0200, Rémi Cura wrote: > > When making several edit on the layer, then saving the edits. > > All the edits are send one by one to the database (though in the same > > transaction). > > That's because of the provider interface. For vector data providers > addFeatures, deleteFeatures, changeAttributeValues and > changeGeometryValues are > independant operations - which therefore are also done in separate > transactions. > > > Jürgen > > -- > Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31 > Dipl.-Inf. (FH) Rheinstraße 13 Fax. +49-4931-918175-50 > Software Engineer D-26506 Norden > http://www.norbit.de > QGIS release manager (PSC) GermanyIRC: jef on FreeNode > > ___ > Qgis-developer mailing list > Qgis-developer@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/qgis-developer > ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Grouping insert/update when sending to PostGIS
Hi Rémi, On Thu, 28. May 2015 at 12:06:22 +0200, Rémi Cura wrote: > When making several edit on the layer, then saving the edits. > All the edits are send one by one to the database (though in the same > transaction). That's because of the provider interface. For vector data providers addFeatures, deleteFeatures, changeAttributeValues and changeGeometryValues are independant operations - which therefore are also done in separate transactions. Jürgen -- Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31 Dipl.-Inf. (FH) Rheinstraße 13 Fax. +49-4931-918175-50 Software Engineer D-26506 Norden http://www.norbit.de QGIS release manager (PSC) GermanyIRC: jef on FreeNode signature.asc Description: Digital signature ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
[Qgis-developer] Grouping insert/update when sending to PostGIS
Hey dear list, I recently stumbled upon a strange behaviour of QGIS. With a simple point postgis layer. When making several edit on the layer, then saving the edits. All the edits are send one by one to the database (though in the same transaction). It would hold much benefits to group those insert/update per table before sending it, and it may be easy to do. A pure SQL solution : instead of doing for instance : - --adding stuff INSERT INTO my_table (geom) VALUES ('POINT(1,2)'); INSERT INTO my_table (geom) VALUES ('POINT(3,4)'); --updating stuff UPDATE my_table SET geom = 'POINT(5,6)' WHERE id = 1 ; UPDATE my_table SET geom = 'POINT(7,8)' WHERE id = 2 ; --deleting stuff DELETE FROM my_table WHERE id = 3 ; DELETE FROM my_table WHERE id = 4 ; -- We could do -- WITH inserting AS ( INSERT INTO my_table (geom) VALUES ('POINT(1,2)') ) ,to_update AS ( SELECT 1 AS id, 'POINT(5,6)' AS geom UNION ALL SELECT 2 , 'POINT(7,8)' ) , updating AS ( UPDATE my_table SET geom = t_o.geom FROM update AS t_o WHERE my_table.id = t_o.id RETURNING 1 ) , deleting AS ( DELETE FROM my_table WHERE id = ANY (ARRAY[3,4]) RETURNING 1 ) SELECT 1 FROM deleting ; Speed may benefit from it, but much more important, all the action would be in one statement, which would permit to use STATEMENT TRIGGER in postgis database. Cheers, Rémi-C ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer