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
