Re: how to auto GRANT custom ACL on a new table?

[Richard Huxton <dev(at)archonet(dot)com>]

Maciej Sieczka wrote:
> I'd like to prevent concurrent editing of a table. For example PostGIS 
> tables, which are going to be stored in the database too: once a user 
> starts editing the table in QGIS [1], it should be locked (for writing 
> at least), until he turns the edit mode off and commits changes. Only 
> then another user should be able edit the table too.
>
> Currently QGIS doesn't do this locking I guess - I have noticed that 
> multiple users can edit the same PostGIS table simultanously (ie. 
> add/delete/modify points/lines/polygons in a PostGIS table at the same 
> time) which can lead to overlapping and corrupted features. Thinking of 
> it now, maybe it is a defect in QGIS it allows that? In PG experts 
> opinion, could/should a PostGIS editing software lock table for writing 
> to prevent concurent editing?

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.

--
  Richard Huxton
  Archonet Ltd