PostGIS tables locking for concurent editing needed

[Maciej Sieczka - old]

Hi

I'm about to deploy a server on which multiple users will be allowed to
edit same PostGIS tables. I have noticed that QGIS (trunk SVN r8182)
doesn't prevent multiple users from editing the same table at the same
time, which sooner or later results in overlapping features and other
surprises that ruin the party. I asked on Postgres ML whether PostGIS
client software should do locking while table is being edited [1], and
the answer is yes:

---
The application should either:

1. Take an advisory lock (see the functions/admin functions chapter) so
that it can use another table to indicate which parts of the GIS are in use.

2. Check to see if the data changed while the user was editing but
before committing (known as "optimistic locking"). Then give the user
the option to overwrite/rollback.

A last resort would be locking rows or the whole table, since a user
might click "edit" then go to lunch.

Certainly doing nothing isn't much use if you have multiple users editing.
---

Are there any chances QGIS could be fixed in this regard? That'd be great.

[1]http://archives.postgresql.org/pgsql-general/2008-02/msg01285.php

Best,
Maciek
_______________________________________________
Qgis-developer mailing list
[hidden email]
http://lists.qgis.org/cgi-bin/mailman/listinfo/qgis-developer

[Jürgen E. Fischer]

Hi Maciej,

On Tue, 26. Feb 2008 at 21:48:40 +0100, Maciej Sieczka wrote:
> The application should either:
>
> 1. Take an advisory lock (see the functions/admin functions chapter) so
> that it can use another table to indicate which parts of the GIS are in use.
 
> 2. Check to see if the data changed while the user was editing but
> before committing (known as "optimistic locking"). Then give the user
> the option to overwrite/rollback.

> A last resort would be locking rows or the whole table, since a user
> might click "edit" then go to lunch.
 
> Certainly doing nothing isn't much use if you have multiple users
> editing.

That doesn't only apply the postgis provider.

We probably need an infrastructure in QgsVectorLayer to do feature
locking when features are changed or deleted - ie. some method that
locks the feature when the before they enter mChangedGeometries,
mChangedAttributes or mDeletedFeaturesIds and releases the locks on
commitChanges/rollBack.

For the PostGIS case the provider should do an AddAuth on connect,
LockRow when a feature is about to be changed and UnlockRows on
commit or rollback.

How does that sound?


Jürgen

--
Jürgen E. Fischer         norBIT GmbH               Tel. [phone]
Dipl.-Inf. (FH)           Rheinstraße 13            Fax. [phone]
Software Engineer         D-26506 Norden               http://www.norbit.de

--
norBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme mbH
Rheinstrasse 13, 26506 Norden
GF: Jelto Buurman, HR: Amtsgericht Emden, HRB 5502

_______________________________________________
Qgis-developer mailing list
[hidden email]
http://lists.qgis.org/cgi-bin/mailman/listinfo/qgis-developer

[Maciej Sieczka - old]

Jürgen E. Fischer pisze:
> On Tue, 26. Feb 2008 at 21:48:40 +0100, Maciej Sieczka wrote:

>> The application should either:
>>
>> 1. Take an advisory lock (see the functions/admin functions chapter) so
>> that it can use another table to indicate which parts of the GIS are in use.
>>  
>> 2. Check to see if the data changed while the user was editing but
>> before committing (known as "optimistic locking"). Then give the user
>> the option to overwrite/rollback.
>>
>> A last resort would be locking rows or the whole table, since a user
>> might click "edit" then go to lunch.
>>  
>> Certainly doing nothing isn't much use if you have multiple users
>> editing.

> That doesn't only apply the postgis provider.
>
> We probably need an infrastructure in QgsVectorLayer to do feature
> locking when features are changed or deleted - ie. some method that
> locks the feature when the before they enter mChangedGeometries,
> mChangedAttributes or mDeletedFeaturesIds and releases the locks on
> commitChanges/rollBack.
>
> For the PostGIS case the provider should do an AddAuth on connect,
> LockRow when a feature is about to be changed and UnlockRows on
> commit or rollback.
>
> How does that sound?

Hi Jürgen

I can't speak as to technical details - I'm still a PgSQL woose. Those
word of wisdom above I quote after Richard Huxton from Psql ML.

I'm sure QGIS developers will choose optimal approach. All that matters
for a user is that as an effect the table is disabled for any other
editing while "open" in QGIS. Whether it could be still accessible
read-only in the meantime is questionable - I can see cons and pros for
that. But any writing should be disabled definitely.

Maciek
_______________________________________________
Qgis-developer mailing list
[hidden email]
http://lists.qgis.org/cgi-bin/mailman/listinfo/qgis-developer

[Hugentobler Marco]

Hi Jürgen,

That sounds very good. And yes, other providers could use that too (e.g. for
WFS-T).
Vector data providers could have a capability 'FeatureLevelLock' and provider
subclasses could implement the methods lockFeature(int id) and
unlockFeatures().
This has been on my mental long term (nirvana) wish list for quite some time.
If someone feels like adding the possibility for feature locks in a branch,
it would be really cool.

Regards,
Marco

Am Mittwoch 27 Februar 2008 20:30:29 schrieb Jürgen E. Fischer:

> That doesn't only apply the postgis provider.
>
> We probably need an infrastructure in QgsVectorLayer to do feature
> locking when features are changed or deleted - ie. some method that
> locks the feature when the before they enter mChangedGeometries,
> mChangedAttributes or mDeletedFeaturesIds and releases the locks on
> commitChanges/rollBack.
>
> For the PostGIS case the provider should do an AddAuth on connect,
> LockRow when a feature is about to be changed and UnlockRows on
> commit or rollback.
>
> How does that sound?
>
>
> Jürgen
>
> --
> Jürgen E. Fischer         norBIT GmbH               Tel. [phone]
> Dipl.-Inf. (FH)           Rheinstraße 13            Fax. [phone]
> Software Engineer         D-26506 Norden               http://www.norbit.de

--
Dr. Marco Hugentobler
Institute of Cartography
ETH Zurich
Technical Advisor QGIS Project Steering Committee
_______________________________________________
Qgis-developer mailing list
[hidden email]
http://lists.qgis.org/cgi-bin/mailman/listinfo/qgis-developer