Re: Batch API for After Triggers

2013/6/17 Simon Riggs <simon(at)2ndquadrant(dot)com>:
> On 9 June 2013 12:58, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>> On 06/09/2013 04:58 PM, Simon Riggs wrote:
>>> There are also difficulties in semantics, since when
>>> we have OLD and NEW at row level we know we are discussing the same
>>> row. With sets of OLD and NEW we'd need to be able to link the
>>> relations back together somehow, which couldn't be done by PK since
>>> that could change.
>>
>> We don't currently have OLD and NEW relations so we're free to define
>> how this works pretty freely.
>>
>> Rather than having OLD and NEW as separate relations, we could just have
>> one OLD_AND_NEW relation. In that relation we exploit Pg's composite
>> types to nest the old and new tuples in a single outer change record.
>>
>> OLD_AND_NEW would look to PL/PgSQL as if it were:
>>
>> CREATE TEMPORARY TABLE OLD_AND_NEW (
>> OLD tabletype NOT NULL,
>> NEW tabletype NOT NULL
>> );
>>
>> ...though presumably without the ability to create indexes on it and the
>> other things you can do to a real temp table. Though I can see cases
>> where that'd be awfully handy too.
>>
>> For DELETE and INSERT we'd either provide different relations named OLD
>> and NEW respectively, or we'd use OLD_AND_NEW with one field or the
>> other blank. I'm not sure which would be best.
>>
>> Alternately, we could break the usual rules for relations and define OLD
>> and NEW as ordered, so lock-step iteration would always return matching
>> pairs of rows. That's useless in SQL since there's no way to achieve
>> lock-step iteration, but if we provide a
>> "for_each_changed_row('some_function'::regproc)" that scans them in
>> lock-step and invokes `some_function` for each one...? (I haven't yet
>> done enough in the core to have any idea if this approach is completely
>> and absurdly impossible, or just ugly. Figured I'd throw it out there
>> anyway.)
>
>
> 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
>

nice idea

+1

Pavel

> --
> Simon Riggs http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers