Re: Batch API for After Triggers

Simon Riggs <simon@2ndQuadrant.com> wrote:
> On 9 June 2013 14:56, Kevin Grittner <kgrittn@ymail.com> wrote:
>> Simon Riggs <simon@2ndQuadrant.com> wrote:
>>> On 8 June 2013 22:25, Kevin Grittner <kgrittn@ymail.com> wrote:
>>>> Simon Riggs <simon@2ndQuadrant.com> 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. So we'd need to invent some semantics for a
>>> "linking identifier" of some description. Which once we've done it
>>> would be used by people to join them back together again, which is
>>> what we already had in the first place. So my take is that it sounds
>>> expressive, but definitely not performant.
>>
>> I have used a feature like this in other database products, and can
>> say from experience that these relations in a statement trigger can
>> be very useful without the linkage you propose.  I can see how the
>> linkage could potentially be useful, but if that is the only
>> hang-up, we would be adding a powerful feature without it.
>
> What I'm trying to do is tune FKs, which are currently implemented as
> after row triggers. Hence why I'm looking at ways to speed up after
> row triggers.
>
> Suggesting I work on after statement triggers would imply you think
> that FKs should/could be rewritten as after statement triggers. Is
> that what you mean?

Well, I didn't make any suggestion -- I was asking questions, and
then when you commented on a hypothetical feature I responded.

That said, I was asking the question because I used SQL Server for
years when it had triggers but no foreign key definitions.  There
was a pattern for enforcing foreign key relationships in AFTER EACH
STATEMENT triggers using the old and new relations (with no linkage
between particular updated rows) which we used so heavily I could
write the triggers for a given foreign key mechanically in just a
minute or two.  This technique had good performance and seems to
have all the semantics you're looking for, so I was wondering
whether that might be a better approach to this problem.  It sure
seems like it would have fewer moving parts.  I know it was very
reliable with S2PL concurrency control, but there may be problems
with adapting it to MVCC that I'm not seeing without a deeper look.

If you're interested, I could try to prod those areas of my memory
to recall the pattern, or find examples of it somewhere.

> Doing it that way would mean rewriting a lot of code and would still
> have problems 2 and 3 identified above. I can't imagine anybody would
> go for that, but if you have a sketch of how it might work we can
> consider it.

I don't, but if there is interest I could probably sketch the outlines.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company