Re: Does 'instead of delete' trigger support modification of OLD

On Thu, Nov 7, 2019 at 11:24:29AM +0200, Eugen Konkov wrote:
> Hello Eugen,
>
> Thursday, November 7, 2019, 11:20:32 AM, you wrote:
>
> >> I looked in the CREATE TRIGGER manual page and found this:
>
> >> https://www.postgresql.org/docs/12/sql-createtrigger.html
> >> If the trigger fires before or instead of the event, the trigger
> >> can skip the operation for the current row, or change the row
> >> being inserted (for INSERT and UPDATE operations only).
>
> >> I don't see the "(for INSERT and UPDATE operations only)" language in
> >> the main trigger documentation,
> >> https://www.postgresql.org/docs/current/trigger-definition.html. I have
> >> written the attached patch to fix that. Does that help?
>
> > No. If we document that PG does not allow to modify OLD at instead
> > of trigger, the we can not implement that. Probably we can put note
> > that "currently modification of the trigger row for RETURNING is not
> > implemented"
>
> sorry, typo. Please read:
> "currently modification of the trigger row for DELETE RETURNING is notimplemented"

In looking at the existing docs, the bullet above the quoted text says:

For row-level INSERT and UPDATE triggers only, the returned row becomes
----
the row that will be inserted or will replace the row being updated.
This allows the trigger function to modify the row being inserted or
updated.

First, notice "only", which was missing from the later sentence:

For <command>INSERT</command> and <command>UPDATE</command>
operations [only], the trigger may modify the
<varname>NEW</varname> row before returning it.

which I have now added with my applied patch to all supported releases.

The major use of modifying NEW is to modify the data that goes into the
database, and its use to modify data seen by later executed triggers, or
by RETURNING, is only a side-effect of its primary purpose. Therefore,
it is not surprising that, since DELETE does not modify any data, just
removes it, that the modification of OLD to appear in later triggers or
RETURNING is not supported.

> >> As far as allowing DELETE to modify the trigger row for RETURNING, I am
> >> not sure how much work it would take to allow that, but it seems like it
> >> is a valid requite, and if so, I can add it to the TODO list.
>
> > Yes, Add please into TODO the feature to "allowing DELETE to modify the trigger row
> > for RETURNING". Becuase, as I have described at first letter, without
> > this the RETURNING rows **does not correspond actually deleted data**
>
> > Thank you.

I have added a TODO item:

Allow DELETE triggers to modify rows, for use by RETURNING

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +