Date: Tue, 29 Oct 2019 17:54:36 +0200
From: Eugen Konkov <kes-kes@yandex.ru>
Message-ID: <919823407.20191029175436@yandex.ru>
Return-Receipt-To: kes-kes@yandex.ru
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
CC: pgsql-docs@lists.postgresql.org
Subject: Does 'instead of delete' trigger support modification of OLD
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi.

This is not clear from doc, so I have asked on IRC too.

from the DOC: https://www.postgresql.org/docs/current/trigger-definition.html
In the case of  INSTEAD OF triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger

I modify OLD row, thus I expect to get modified version when run next query: 

    WITH t1 AS( delete from abc returning *)
    select * from t1;

fiddle: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=637730305f66bf531794edb09a462c95

> https://www.postgresql.org/docs/current/trigger-definition.html
A row-level INSTEAD OF trigger should either return NULL to indicate that it did not modify any data from the view's underlying base tables,
or it should return the view row that was passed in (the NEW row for INSERT and UPDATE operations, or the OLD row for DELETE operations).
A nonnull return value is used to signal that the trigger performed the necessary data modifications in the view.
This will cause the count of the number of rows affected by the command to be incremented. For INSERT and UPDATE operations, the trigger may
modify the NEW row before returning it. This will change the data returned by INSERT RETURNING or UPDATE RETURNING,
and is useful when the view will not show exactly the same data that was provided.

But I still does not understand. Doc explicitly do not prohibit modification of OLD and has no examples for DELETE RETURNING case

So I want to ask clarify doc a bit.
If this prohibited, why this is prohibited? have any discussion on this?
If not prohibited, does this is not implemented for DELETE RETURNING queries? if so, is it left for later?

I have next use case.
I am implementing Bi-Temporal tables. The table have columns: id, app_period, value
for example I have next data: 7, '[2019-01-01, 2020-01-01)', 130
You can imagine this as having value 7 for each day of the year.
Now I want to delete this value for May month. I setup special variable to period: '[2019-05-01,2019-06-01)' and then delete:

    select app_period( '[2019-05-01,2019-06-01)' );
    WITH t1 AS( delete from abc returning *)
    select * from t1;

Algorithm of deletion is next:
1. Deactivate target row
   7, '[2019-01-01, 2020-01-01)', 130
2. If target row has wider app_period then we insert record that data back:
      NOT '[2019-05-01,2019-06-01)' @> '[2019-01-01, 2020-01-01)'
    INSERT INTO abc ( id, app_period, value ) values 
        ( 7, '[2019-01-01,2019-05-01)', 130 ),
        ( 7, '[2019-06-01,2020-01-01)', 130 ),
3. OLD.app_period = OLD.app_period * app_period(); 
    '[2019-01-01, 2020-01-01)' * '[2019-05-01,2019-06-01)' --> '[2019-05-01,2019-06-01)'

Because only 130 value is deleted from specified period I expect next result for the query above:
     ( 7, '[2019-05-01,2019-06-01)', 130 )

But despite on OLD was modified, actual result is:
     ( 7, '[2019-01-01,2020-01-01)', 130 )
You can see that this is original data.

So, does INSTEAD OF DELETE support modification of row?

-- 
Best regards,
Eugen Konkov