return=representation returns old values of updatable view

[MadMartigan]

Environment

• PostgreSQL version: 14
• PostgREST version: 9.0.0
• Operating system: Mac O/S
• Request Heder: Prefer: return=representation

Description of issue

Postgrest returns old values of query parameters

Sample Request

PATCH /test?row_id=eq.1&version_id=eq.1

{ “name”: “new_name” }

Expected behavior:

Postgrest returns accurate representation of all table fields

Cause of behavior

Database trigger updates version_id field on row update.

[steve-chavez]

Please share the response you expect. Also a curl snippet would be helpful.

[MadMartigan]

It appears that this situation only occurs when updating a view. When I update a table I get the response I expect. My apologies for not finding this out earlier. In case this still might be of interest...

This view has a table with a before update trigger that sets NEW.vid = OLD.vid + 1;

Expected response: {id: 1, vid: 2, some_field: "new_value"}

curl "http://server.local:9200/api/updateable_view?id=eq.1"
-X PATCH
-H "Content-Type: application/json"
-H "Prefer: return=representation"
-H "Authorization: Bearer xxx...."
-d '{ "some_field": "some_value" }'

[MadMartigan]

This seems to be standard Postgres behavior. This is definitely not a postgREST issue or bug.

TLDR:

Updatable views that contain tables with triggers may return unexpected results

Setup:

1. Create a before update trigger on a table that increments an integer field. example: NEW.field = OLD.field + 1
2. Create an updatable view that contains that table. example: INSTEAD OF UPDATE ON table...
3. Perform an update on both the table and view and return the results. example: UPDATE table|view ... RETURNING *

Behavior:

• Table update will return incremented value
• View update will return non-incremented value

[steve-chavez]

Hm, maybe we should document that behavior(here). Do you have a reproducible example?(DDL)

[MadMartigan]

Here is a tested and reproducible example. I'm not sure how many people will run into this issue but it took me a while to figure it out. If you think this needs to documented I will be happy to give it a go or help in any way I can.

--  create a test tables
CREATE TABLE t1 (
    id integer PRIMARY KEY,
    xid integer,
    vid integer
);

--
CREATE TABLE t2 (
    --id serial PRIMARY KEY,
    t1_id integer PRIMARY KEY REFERENCES t1,
    txt text
);

-- Insert test data
INSERT INTO t1
    VALUES (1, 1, 1);

INSERT INTO t2
    VALUES (1, 'test1');

-- Create a view
CREATE OR REPLACE VIEW t1_view AS
SELECT
    t1.id,
    t1.vid,
    t1.xid,
    t2.txt
FROM
    t1
    LEFT JOIN t2 ON t2.t1_id = t1.id;

-- Create a function to make the view updatable
CREATE OR REPLACE FUNCTION t1_view_update ()
    RETURNS TRIGGER
    AS $$
BEGIN
    --
    UPDATE
        t1
    SET
        xid = NEW.xid
    WHERE
        id = NEW.id;
    --
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

--  Attach the function to the table
CREATE OR REPLACE TRIGGER t1_view_update_trg 
  INSTEAD OF UPDATE ON t1_view 
  FOR EACH ROW EXECUTE PROCEDURE t1_view_update ();

-- Create a trigger that incremnts vid + 1 on update
CREATE OR REPLACE FUNCTION vid_increment ()
    RETURNS TRIGGER
    AS $vid_increment$
DECLARE
BEGIN
    NEW.vid = OLD.vid + 1;
    RETURN NEW;
END;
$vid_increment$
LANGUAGE plpgsql;

-- Attach the trigger to t1
CREATE OR REPLACE TRIGGER vid_increment 
  BEFORE UPDATE ON data.t1 
  FOR EACH ROW EXECUTE PROCEDURE vid_increment ();

-- -----
-- TESTS
-- -----
-- xid and vid should always be the same value
SELECT * FROM t1_view;

-- Update the table directly.  xid == vid (correct)
UPDATE t1 SET xid = xid + 1 WHERE id = 1 RETURNING *;

-- Update the view xid != vid  (incorrect)
UPDATE t1_view SET xid = xid + 1 WHERE id = 1 RETURNING *;


-- RESET --
-- DROP TABLE IF EXISTS data.t1 CASCADE;
-- DROP TABLE IF EXISTS data.t2 CASCADE;

[wolfgangwalther]

I use the following to solve this, i.e. always return the newly computed row from the view trigger, instead of NEW directly:

-- Create a function to make the view updatable
CREATE OR REPLACE FUNCTION t1_view_update ()
    RETURNS TRIGGER
    AS $$
BEGIN
    --
    UPDATE
        t1
    SET
        xid = NEW.xid
    WHERE
        id = NEW.id;
    --
    RETURN t1_view
    FROM t1_view
    WHERE id = NEW.id;
END;
$$
LANGUAGE plpgsql;