Re: Batch API for After Triggers

On 17 June 2013 20:53, Kevin Grittner <kgrittn@ymail.com> wrote:
> Simon Riggs <simon@2ndQuadrant.com> wrote:
>> On 9 June 2013 12:58, Craig Ringer <craig@2ndquadrant.com> wrote:
>
>>> We don't currently have OLD and NEW relations so we're free to
>>> define how this works pretty freely.
>
>> I think the best way, if we did do this, would be to have a
>> number of different relations defined:
>>
>> OLD
>> NEW
>> INSERTED
>> DELETED
>> all of which would be defined same as main table
>>
>> and also one called
>> UPDATED
>> which would have two row vars called OLD and NEW
>> so you would access it like e.g. IF UPDATED.OLD.id = 7
>
> Well, there is the SQL standard, which has a couple paragraphs on
> the topic which we might want to heed.

Yes, I already did in my proposal above.
OLD and NEW are all we need to fulfill the standard.

> For a delete there is just
> an old table; for an insert just a new one.  For an update you have
> both, with the same cardinality.  The rows in the old and new
> tables have a correspondence, but that is only visible to FOR EACH
> ROW triggers.

Yes, those are the relevant parts. SQL:2008 4.38 is the paragraphs
that describe this (for later reference).

What the standard doesn't cover is recursive calls, that might
generate new events of different kinds. So an UPDATE statement might
have caused DELETEs etc.. So we'd need a way to get access to DELETED
rows even when the OLD relation covers only the UPDATED rows.

For row level triggers we support macros like TRIGGER_FIRED_BY_INSERT.
Having an INSERT relation is just the logical equivalent for statement
level triggers.

> For something like RI, why would you need to
> establish correspondence?  A row with the referenced key either
> exists after the statement completes, or it doesn't -- why would we
> care whether it is an updated version of the same row?

I wasn't doing this for RI specifically, I was looking at what we'd
need to provide a full facilitiy.

It's not very easy to see how we can support RI via statement level triggers.

By definiton, statement level triggers happen after all row level
triggers have fired. So implementing row level RI by using statement
level triggers that follow the standard isn't possible. The main
things we'd need to cope with would be recursive trigger calls, for
example DELETE cascades. The firing of the triggers generates more
trigger events which delete more rows etc.. If we want to implement RI
triggers using some form of set processing we would need to do that in
the middle of handling the after row events, i.e. execute a set, then
re-check for a new set of events and execute them.

Directly using the statement-level standard triggers isn't the way, I
conclude after some detailed thinking. But there could be some
intermediate form that makes sense.

> Syntax for how to refer to the these is defined by the standard.
>
> As usual, I don't object to adding capabilities as long as the
> standard syntax is also supported with standard semantics.

Agreed.

--Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services