Editable views with postgis and qgis

Dear Community.
I want to create editable views in postgis and track the edit history on this view when editing with QGIS. In my example I want to track changes on my table test.vulkan that contains volcanoes of the world. The edits (delete, update, insert) shall be archived in table test.vulkan_update with additional infomation on date, ipaddress and type of transaction. The volcanoes are edited with an editable view (test.volcano_edit) and handled with rules so that data edited is transfered to the update table. So far my example worked on the QGIS 2.x branch but under QGIS 3.6 the insert rule does not work anymore. Below you find the create statements for the tables, the view definition and the rules to handle the editable view in QGIS. Somehow, the problem relates to the new insert widgets in QGIS 3.6 that does not support a null id that should be handled with postges on the basis of a sequence(select nextval(‘test.vulkan_id_seq’)). The sequence itself is shared between both tables vulkan and vulkan_update. I deaktivated under properties the enforce null and enforce integrity buttons but this did not help. I would welcome your help and support

CREATE TABLE test.vulkan
    id integer NOT NULL DEFAULT nextval('test.vulkan_id_seq'::regclass),
    geom geometry(1107468),
    name_ character varying(9) COLLATE pg_catalog."default",
    status character varying(9) COLLATE pg_catalog."default",
    objectid integer,
    CONSTRAINT vulkan_pkey PRIMARY KEY (id)

CREATE TABLE test.vulkan_update
     id integer DEFAULT nextval('test.vulkan_id_seq'::regclass),
     objectid integer,
     geom geometry,
     name_ character varying(20) COLLATE pg_catalog."default",
     status character varying(20) COLLATE pg_catalog."default",
	 type_ character varying(20) COLLATE pg_catalog."default",
     transact character varying(10),
	 address text,
	 datum date

create or replace view test.volcano_edit as
select id, objectid, geom, name_,type_,status from test.vulkan
  where objectid not in (select objectid from test.vulkan_update where objectid is not null)
  (select id, objectid, geom, name_,type_,status from test.vulkan_update
  where transact <> 'delete')

--delete rule
create or replace rule "delete_vulkan" as
on delete to test.volcano_edit
do instead
insert into test.vulkan_update (id,objectid,geom,name_,type_,status,transact,address,datum)
values((select nextval('test.vulkan_id_seq')),old.objectid,old.geom,old.name_,old.type_,old.status,'delete',(select inet_client_addr()),CURRENT_DATE);

--insert rule
create or replace rule "insert_vulkan" as
on insert to test.volcano_edit do instead
insert into test.vulkan_update (id,objectid,geom,name_,type_,status,transact,address,datum)
values((select nextval('test.vulkan_id_seq')),null,new.geom,new.name_,new.type_,new.status,'insert',(select inet_client_addr()),CURRENT_DATE);

--update rule
create or replace rule "update_vulkan" as
on update to test.volcano_edit do instead
insert into test.vulkan_update (objectid,geom,name_,type_,status,transact,address,datum)
values(old.objectid,new.geom,new.name_,new.type_,new.status,'update',(select inet_client_addr()),CURRENT_DATE);


Konnte Änderungen am Layer volcano_edit nicht festschreiben

Fehler: FEHLER: Ein Objekt nicht hinzugefügt.

PostGIS-Fehler beim Attributhinzufügen: ERROR: cannot perform INSERT RETURNING on relation “volcano_edit”
TIP: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

This is what is happening here with QGIS 3.8/ PostgreSQL 9.6/ PostGIS 2.3.x.

Just for information:
For versioning of geodata there is a QGIS-Extension pg_version which does some svn-like versioning based on databasefunktions with a QGIS-Toolbar as frontend. We use this here in production in a multiuser environement. Maybe this is helpful for you.

have a lot of fun!


Es klingt irgendwie kompliziert. Ich bin ein großer Freund von Views, aber falls er dazu da ist, die Update- und Verschiebelogik zu verstecken und immer die neuen Daten zu zeigen, dann wie gesagt klingt das kompliziert.
mein Ansatz wäre:

  • eine ganz normale Tabelle, die aktualisiert wird (und die ganz normal ihre letzten Daten anzeigt)
  • ein Trigger, der die Aktualisierungsvorgänge mitschreibt in eine Historietabelle

Einen View kann man trotzdem drüberlegen wie man mag, als Interface oder mehrere, wenn benötigt.
Wenn er richtig angelegt wird, bleibt er von allein editierbar.