Re: delta relations in AFTER triggers - Mailing list pgsql-hackers

On Tue, Jun 17, 2014 at 04:07:55PM -0400, Robert Haas wrote:
> On Sat, Jun 14, 2014 at 7:56 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> > I looked at the standard, and initially tried to implement the
> > standard syntax for this; however, it appeared that the reasons
> > given for not using standard syntax for the row variables also
> > apply to the transition relations (the term used by the standard).
> > There isn't an obvious way to tie that in to all the PLs we
> > support.  It could be done, but it seems like it would intolerably
> > ugly, and more fragile than what we have done so far.
> 
> I'm not too familiar with this area.  Can you describe what the
> standard syntax for the row variables is, as opposed to our syntax?
> Also, what's the standard syntax for the the transition relations?

The good:   - Generating the tuplestores.  Yay!

The bad:   - Generating them exactly and only for AFTER triggers   - Requiring that the tuplestores both be generated
ornot at all.     There are real use cases described below where only one would be relevant.   - Generating the
tuplestoresunconditionally.
 

The ugly:   - Attaching tuplestore generation to tables rather than callers (triggers, DML, etc.)

The SQL standard says:

<trigger definition> ::=CREATE TRIGGER <trigger name> <trigger action time> <trigger event>   ON <table name> [
REFERENCING<transition table or variable list> ]   <triggered action>
 

<trigger action time> ::=BEFORE   | AFTER   | INSTEAD OF

<trigger event> ::=INSERT   | DELETE   | UPDATE [ OF <trigger column list> ]

<trigger column list> ::=<column name list>

<triggered action> ::=[ FOR EACH { ROW | STATEMENT } ]   [ <triggered when clause> ]   <triggered SQL statement>

<triggered when clause> ::=WHEN <left paren> <search condition> <right paren>

<triggered SQL statement> ::=<SQL procedure statement>   | BEGIN ATOMIC { <SQL procedure statement> <semicolon> }...
END

<transition table or variable list> ::=<transition table or variable>...

<transition table or variable> ::=     OLD [ ROW ] [ AS ] <old transition variable name>   | NEW [ ROW ] [ AS ] <new
transitionvariable name>   | OLD TABLE [ AS ] <old transition table name>   | NEW TABLE [ AS ] <new transition table
name>

<old transition table name> ::=<transition table name>
<new transition table name> ::=<transition table name>

<transition table name> ::=<identifier>

<old transition variable name> ::=<correlation name>

<new transition variable name> ::=<correlation name>

Sorry that was a little verbose, but what it does do is give us what we need at
trigger definition time.  I'd say it's pilot error if a trigger
definition says "make these tuplestores" and the trigger body then
does nothing with them, which goes to Robert's point below re:
unconditional overhead.

> > Some things which I *did* follow from the standard: these new
> > relations are only allowed within AFTER triggers, but are available
> > in both AFTER STATEMENT and AFTER ROW triggers.  That is, an AFTER
> > UPDATE ... FOR EACH ROW trigger could use both the OLD and NEW row
> > variables as well as the delta relations (under whatever names we
> > pick).  That probably won't be used very often, but I can imagine
> > some cases where it might be useful.  I expect that these will
> > normally be used in FOR EACH STATEMENT triggers.
> 
> I'm concerned about the performance implications of capturing the
> delta relations unconditionally.

Along that same line, we don't always need to capture both the before
tuplestores and the after ones.  Two examples of this come to mind:

- BEFORE STATEMENT triggers accessing rows, where there is no after part to use, and
- DML (RETURNING BEFORE, e.g.) which only touches one of them.  This applies both to extant use cases of RETURNING and
toplanned ones.
 

I'm sure if I can think of two, there are more.

In summary, I'd like to propose that the tuplestores be generated
separately in general and attached to callers. We can optimize this by
not generating redundant tuplestores.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate