Editable views in QGIS - update affects the wrong row

Question

Updating views from QGIS doesn't work as I expect it to work.

I've got the following (simplified) tables, views and triggers:

    CREATE TABLE public.parcels
    (
      id serial,
      geom geometry(MultiPolygon,27700),
      "Land Registry Reference" character varying(30),
      type integer,
      area double precision,
      "Design Issues" text,
      sys_period tstzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp with time zone),
      CONSTRAINT parcels_pkey PRIMARY KEY (id)
    )
    WITH (
      OIDS=FALSE
    );

    CREATE TABLE public.owners
    (
      id integer,
      "Title Number" character varying,
      "Proprietor Name" character varying,
      sys_period tstzrange
    )
    WITH (
      OIDS=FALSE
    );

    drop view if exists from_two;

    CREATE VIEW from_two as(
      select "parcels"."id" AS "id",
      "parcels"."Land Registry Reference",
      "parcels"."geom",
      "owners"."Proprietor Name",
      "parcels"."Design Issues",
       ROW_NUMBER() over() AS uid
    FROM "public"."parcels"
    LEFT OUTER JOIN "owners" ON "parcels"."Land Registry Reference" = "owners"."Title Number"
    );

    CREATE OR REPLACE function update_from_two()
      returns trigger
      AS $f$
      BEGIN
        UPDATE public.parcels
        SET
        id = NEW.id,
        "Land Registry Reference" = NEW."Land Registry Reference",
        geom = NEW.geom,
        "Design Issues" = NEW."Design Issues"
        WHERE id = OLD.id;

      UPDATE owners
      SET "Proprietor Name" = NEW."Proprietor Name"
      WHERE "Proprietor Name" = OLD."Proprietor Name";

    return NEW;
    END;
    $f$ LANGUAGE plpgsql;

    DROP trigger if exists tg_fromtwo on from_two;

    CREATE  TRIGGER  tg_fromtwo
    INSTEAD OF UPDATE
    ON from_two
    FOR EACH ROW
      EXECUTE PROCEDURE update_from_two();

it all works fine and when I update the view in pgadmin with

UPDATE from_two
SET 
  id = 244,
  "Design Issues" = 'None'
WHERE id = 244;

I also can open this view in Qgis, but when I edit data and save it, the edited data ends up in the wrong table row. Do I have to do something different to be able to update correctly in Qgis?

I'm using Postgres 9.5, Postgis 2.2.2 and QGIS 2.16.2

Answer 1

In the end I found out what went wrong, the view needs to be sorted, so that the order is always the same. To make clear where all data comes from, I also created an combined ID:

    CREATE VIEW from_two as(
      select
        concat("parcels"."id"::text, '-', "owners"."id"::text) as id,
        "parcels"."id" as parcels_id,
        "owners"."id" as owners_id,
        "parcels"."Land Registry Reference",
        "parcels"."geom",
        "owners"."Proprietor Name",
        "parcels"."Design Issues",
        ROW_NUMBER() over() AS uid
      FROM "public"."parcels"
      LEFT OUTER JOIN "owners" ON "parcels"."Land Registry Reference" = 
                                                        "owners"."Title Number"
      ORDER BY id);