public inbox for [email protected]  

help / color / mirror / Atom feed

From: Eugen Konkov <[email protected]>
To: PostgreSQL-development <[email protected]>
Cc: [email protected]
Subject: Does 'instead of delete' trigger support modification of OLD
Date: Tue, 29 Oct 2019 17:54:36 +0200
Message-ID: <[email protected]> (raw)

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

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Does 'instead of delete' trigger support modification of OLD
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox