Version editing for Postgresql 9.2.4 / postGIS 2

Question

Background: I am "building" a GIS with PostgreSQL 9.2.4 / PostGIS 2 as the database and QGIS 1.8.0 as the geographic application. There will be multiple users accessing, editing and viewing the data at the same time.

Question:

1. Is version editing automatically enforced in PostgreSQL 9.2.4 / PostGIS 2?
2. Is it possible to enable/enforce version editing and if yes how?

I have had a look at pgVersion for PostgreSQL 9.2.4 / PostGIS 2 and the QGIS pgVersion plugin. However, if I understand correctly (reading the help files and testing it), pgVersion only allows editing of cell values of existing columns and also no new geographic features can be added.

Answer 1

Yes it seems that PostgreSQL/PostGIS does allow multiple users to edit and view the same data at the same time automatically, whether it is viewed or edited in PostgreSQL/PostGIS or QGIS. However, if not properly managed this, apparently, may result in overlapping and/or corrupt features.

Here are the links from which I made my conclusions:

• http://www.postgresql.org/message-id/[email protected]
• http://www.postgresql.org/message-id/[email protected]
• http://trac.osgeo.org/qgis/ticket/972

Please note that the question and answers in the above links were asked/answered about 5 years ago, which mean they point to earlier program versions. However, I cannot imagine the newer versions of PostgreSQL/PostGIS not supporting multi-user editing (might even have been enhanced).

Update: For those using QGIS 1.8.0, under the tab 'Table' in DB Manager there is a Versioning option. However, I keep getting an error that states 'language "SQL" does not exist.'. Any suggestions on how to bypass or fix this error? Does anyone else have the same problem/error?

Answer 2

If you are using Qgis there is a plugin for versioning using a postgres database https://plugins.qgis.org/plugins/FastVersion/ The plugin also allows to administrate user permissions on the versions. You could set one user per version, and then consolidate the changes in only one version, with the post function.

Answer 3

Definely yes, you can do versioning by the use of triggers http://www.postgresql.org/docs/9.1/static/triggers.html on geographic tables.

I use this approach to save temporal data so I could issue queries that would create a snapshot of records at given time.

1. Versioning editing is not enforced by what I know.

2. Yes, this can be supported. There will be several responses based on the user needs. You could use the http://www.pgadmin.org/docs/1.16/pgagent.html and schedule a job to copy live data to versioned table on a daily window as a simple example.

Or you could use a shadowed table that records edits of records when those events happen.

Postgresql has all resources you need to do that but you will need skills with SQL language.