Re: [Qgis-developer] Strange behaviour in feature ids when editing PostGIS layer
Hello, Le samedi 28 février 2015, 04:43:53 Régis Haubourg a écrit : > Hi, > well pgadmin sets table view in readonly mode if no Primary Key is defined.. > That is more than a good practice from a DBA point of view, I wouldn't be > against such a constraint for tables. We should raise some user warning > when connecting to PG, so that users don't get confuzed > Another point, what about views that do not have PK (but may be editable) ? If you talk about auto-updateable views, you can track back the original table and check it for PK, as the PostgreSQL doc says : "The view must have exactly one entry in its FROM list, which must be a table or another updatable view." There could still be a problem though if the PK from the original table is not selected in the view. This could be checked too. More generally, it would be great to be able to override this behaviour if the user provides a unique and not null column name. There are cases when triggers, rules and other mechanisms would require this and are not easily automatically detectable. Vincent > Cheers > Régis > > > > -- > View this message in context: > http://osgeo-org.1560.x6.nabble.com/Strange-behaviour-in-feature-ids-when-e > diting-PostGIS-layer-tp5185929p5190614.html Sent from the Quantum GIS - > Developer mailing list archive at Nabble.com. > ___ > Qgis-developer mailing list > Qgis-developer@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/qgis-developer -- Vincent Picavet - Gérant Oslandia www.oslandia.com ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Strange behaviour in feature ids when editing PostGIS layer
Hi, well pgadmin sets table view in readonly mode if no Primary Key is defined.. That is more than a good practice from a DBA point of view, I wouldn't be against such a constraint for tables. We should raise some user warning when connecting to PG, so that users don't get confuzed Another point, what about views that do not have PK (but may be editable) ? Cheers Régis -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Strange-behaviour-in-feature-ids-when-editing-PostGIS-layer-tp5185929p5190614.html Sent from the Quantum GIS - Developer mailing list archive at Nabble.com. ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Strange behaviour in feature ids when editing PostGIS layer
On Fri, Feb 27, 2015 at 08:37:11PM +0100, Jürgen E. Fischer wrote: > Hi Sandro, > > On Fri, 27. Feb 2015 at 17:35:37 +0100, Sandro Santilli wrote: > > In any case the reported behavior does sound like a bug to me, so > > I still think it should get a ticket, don't you ? > > Well, ctid is not a reliable key. The ctid will also change if something > else > modifies the row. Maybe postgis layers without any key should be readonly. Agreed, least surprise. --strk; ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Strange behaviour in feature ids when editing PostGIS layer
Hi Sandro, On Fri, 27. Feb 2015 at 17:35:37 +0100, Sandro Santilli wrote: > In any case the reported behavior does sound like a bug to me, so > I still think it should get a ticket, don't you ? Well, ctid is not a reliable key. The ctid will also change if something else modifies the row. Maybe postgis layers without any key should be readonly. That would also help with Victor's problem, but probably not in a desired way ;) 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] Strange behaviour in feature ids when editing PostGIS layer
On Fri, Feb 27, 2015 at 04:57:39PM +0100, Jürgen E. Fischer wrote: > Hi Sandro, > > On Fri, 27. Feb 2015 at 16:43:23 +0100, Sandro Santilli wrote: > > Victor, I've been thinking of this problem overnight and thought > > that maybe it could be fixed by having QGIS use UPDATE..RETURNING > > to update the associated key. > > And return it where? In the current vector provider interface only > ::addFeatures gets QgsFeature (and already updates their id), but > changeAttributeValues and changeGeometryValues only get attributes or geometry > along with the original feature id. Sorry, I hadn't looked at the actual code, was just an idea. Didn't know there was support for auto-updating IDs already (great!). In any case the reported behavior does sound like a bug to me, so I still think it should get a ticket, don't you ? --strk; ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Strange behaviour in feature ids when editing PostGIS layer
Hi Sandro, On Fri, 27. Feb 2015 at 16:43:23 +0100, Sandro Santilli wrote: > Victor, I've been thinking of this problem overnight and thought > that maybe it could be fixed by having QGIS use UPDATE..RETURNING > to update the associated key. And return it where? In the current vector provider interface only ::addFeatures gets QgsFeature (and already updates their id), but changeAttributeValues and changeGeometryValues only get attributes or geometry along with the original feature id. 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] Strange behaviour in feature ids when editing PostGIS layer
On Fri, Feb 06, 2015 at 12:54:20PM +0100, Victor Olaya wrote: > Correct, no PK in my table. What you say sounds feasible. I will try > with another table that has a PK, and see if I can reproduce the error > or not. > > I cannot expect to have a PK in the user's table, but this is at least > a clue :-) Victor, I've been thinking of this problem overnight and thought that maybe it could be fixed by having QGIS use UPDATE..RETURNING to update the associated key. Actually, the same approach could be used for keys that are subject to change due to triggers. If you file a ticket for the problem it will be easier to deal with it, or did you already ? --strk; > 2015-02-06 12:48 GMT+01:00 Martin Dobias : > > Hi Victor > > > > On Fri, Feb 6, 2015 at 5:39 PM, Victor Olaya wrote: > >> > >> > >> In the case of a PostGIS layer, when the editingStopped signal is > >> emitted, the query will return no features. The feature with id 1 is > >> no longer there, and instead there will be a feature with id equal to > >> 4. So basically it seems that a modification of a feature is really a > >> removal and then an addition, and the added feature has a different id > >> than the removed one, > > > > > > Does you table have a primary key? What is the table definition? > > > > If the postgres provider can't find a primary key, it will try to use ctid > > as the ID. Then I think what you say can happen - when updating a row, > > postgres removes the old row and appends a new one and the ctid will change. > > > > Cheers > > Martin > > > ___ > Qgis-developer mailing list > Qgis-developer@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/qgis-developer -- () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Strange behaviour in feature ids when editing PostGIS layer
Correct, no PK in my table. What you say sounds feasible. I will try with another table that has a PK, and see if I can reproduce the error or not. I cannot expect to have a PK in the user's table, but this is at least a clue :-) Thanks! 2015-02-06 12:48 GMT+01:00 Martin Dobias : > Hi Victor > > On Fri, Feb 6, 2015 at 5:39 PM, Victor Olaya wrote: >> >> >> In the case of a PostGIS layer, when the editingStopped signal is >> emitted, the query will return no features. The feature with id 1 is >> no longer there, and instead there will be a feature with id equal to >> 4. So basically it seems that a modification of a feature is really a >> removal and then an addition, and the added feature has a different id >> than the removed one, > > > Does you table have a primary key? What is the table definition? > > If the postgres provider can't find a primary key, it will try to use ctid > as the ID. Then I think what you say can happen - when updating a row, > postgres removes the old row and appends a new one and the ctid will change. > > Cheers > Martin > ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
Re: [Qgis-developer] Strange behaviour in feature ids when editing PostGIS layer
Hi Victor On Fri, Feb 6, 2015 at 5:39 PM, Victor Olaya wrote: > > In the case of a PostGIS layer, when the editingStopped signal is > emitted, the query will return no features. The feature with id 1 is > no longer there, and instead there will be a feature with id equal to > 4. So basically it seems that a modification of a feature is really a > removal and then an addition, and the added feature has a different id > than the removed one, > Does you table have a primary key? What is the table definition? If the postgres provider can't find a primary key, it will try to use ctid as the ID. Then I think what you say can happen - when updating a row, postgres removes the old row and appends a new one and the ctid will change. Cheers Martin ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
[Qgis-developer] Strange behaviour in feature ids when editing PostGIS layer
Hi all, I am seeing a behaviour that surprised me when editing a PostGIS layer. HEre's a detailed explanation Let's say I have a layer with 3 features with ids 1,2 and 3. In a layer such as a shapefile, if you move the feature with the id 1, the geometryChanged signal is fired, with the feature id and the new geometry as parameters. When the editingStopped signal is emitted after editing is finished, you can query the layer to get the feature with that id 1, and it will be there, with the modified geometry. In the case of a PostGIS layer, when the editingStopped signal is emitted, the query will return no features. The feature with id 1 is no longer there, and instead there will be a feature with id equal to 4. So basically it seems that a modification of a feature is really a removal and then an addition, and the added feature has a different id than the removed one, I am sure there must be an explanation for that, based on how PostGIS works, or how the corresponding provider handles the transactions and the id, but this seems to me rather confusing. In this situation (and in case this is not a bug that can't be fixed), how would you implement a mechanism for, whenever a layer is modified, do something later with the modified features?. Storing the ids of the modified features and then using them on the method that is connected to edittingStopped() won't work in this case. Thanks in advance. ___ Qgis-developer mailing list Qgis-developer@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-developer
