Batch API for After Triggers

While fiddling with FK tuning, Noah suggested batching trigger
executions together to avoid execution overhead.

It turns out there is no easy way to write triggers that can take
advantage of the knowledge that they are being executed as a set of
trigger executions. Some API is required to allow a trigger to
understand that there may be other related trigger executions in the
very near future, so it can attempt to amortise call overhead across
many invocations ("batching").

The attached patch adds two fields to the TriggerDesc trigger
functions are handed, allowing them to inspect (if they choose) the
additional fields and thus potentially use some form of batching.

This is backwards compatible with earlier trigger API.

Two fields are

int tg_tot_num_events;
int tg_event_num

So your trigger can work out it is e.g. number 3 of 56 invocations in
the current set of after triggers.

Going back to Noah's example, this would allow you to collect all 56
values and then execute a single statement with an array of 56 values
in it. Knowing there are 56 means you can wait until the 56th
invocation before executing the batched statement, without risking
skipping some checks because you've only got half a batch left.

If you don't do this, then you'd need to introduce the concept of a
"final function" similar to the way aggregates work. But that seems
much too complex to be real world useful.

This seemed a generally useful approach for any after trigger author,
not just for RI.

Comments please.

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

Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:

> Comments please.

How much of this problem space do you think could be addressed by
providing OLD and NEW *relations* to AFTER EACH STATEMENT triggers?

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

* Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> While fiddling with FK tuning, Noah suggested batching trigger
> executions together to avoid execution overhead.

I like the general idea, but I'd prefer a way to avoid having to queue
up tons of trigger events to be executed in the first place.
Aggregates do this by providing a way to store up information to be
processed by an eventual 'final' function. Another option, as Kevin
asked about, would be statement level triggers which are able to see
both the OLD and the NEW versions of the relation.

The per-row trigger option with a way to be called immediately and then
store what it cares about for a later final function strikes me as very
generalized and able to do things that the statement-level option
couldn't, but I'm not sure if there's a use-case that could solve which
the OLD/NEW statement trigger capability couldn't.

Thanks,

Stephen

On 8 June 2013 22:25, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>
>> Comments please.
>
> How much of this problem space do you think could be addressed by
> providing OLD and NEW *relations* to AFTER EACH STATEMENT triggers?

It's a reasonable question because those two things sound a little
like they might be related.

Providing the proposed additional info costs almost nothing since the
work to calculate that info is already performed. I've written this
patch since it was trivial to do so, while inspecting the code to see
if it was possible. As it now turns out, I'll be putting most effort
into the WHEN clause approach for FKs, but there's no reason why
others like Slony or pgmemcache wouldn't benefit here also - hence
posting the patch. The proposed API changes don't conflict in any way
with the feature you propose.

Providing the whole OLD and NEW sets as relations to a trigger would
require significant resources and wouldn't be done for performance
reasons AFAICS. 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.

Since my objective is performance, not standards, I don't see a reason
to work on that, yet. I might have time to work on it later, lets see.

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

On 9 June 2013 05:08, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
>> While fiddling with FK tuning, Noah suggested batching trigger
>> executions together to avoid execution overhead.
>
> I like the general idea, but I'd prefer a way to avoid having to queue
> up tons of trigger events to be executed in the first place.

There's already a thread on that exact topic, for FKs, which is what
spawned this thread.

> Aggregates do this by providing a way to store up information to be
> processed by an eventual 'final' function.

As I mentioned in my post, I did consider that and then chose not to
do that. However, having a final func is a major modification in the
way that we specify trigger functions. We'd also need to cope with
recursive trigger execution, which would mean the final func would get
called potentially many times, so there's no way of knowing if the
final func is actually the last call needed. That sounded complex and
confusing to me.

The proposed API allows you to do exactly that anyway, more easily, by
just waiting until tg_event_num == tg_tot_num_events.

> Another option, as Kevin
> asked about, would be statement level triggers which are able to see
> both the OLD and the NEW versions of the relation.
>
> The per-row trigger option with a way to be called immediately

... it already exists and is known as the WHEN clause. This is the
mechanism I expect to use to tune FKs

> and then
> store what it cares about for a later final function strikes me as very
> generalized and able to do things that the statement-level option
> couldn't,

> but I'm not sure if there's a use-case that could solve which
> the OLD/NEW statement trigger capability couldn't.

I think the two things are quite different, as I explained on a
separate post to Kevin.

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

On Sun, Jun 09, 2013 at 10:15:09AM +0100, Simon Riggs wrote:
> As I mentioned in my post, I did consider that and then chose not to
> do that. However, having a final func is a major modification in the
> way that we specify trigger functions. We'd also need to cope with
> recursive trigger execution, which would mean the final func would get
> called potentially many times, so there's no way of knowing if the
> final func is actually the last call needed. That sounded complex and
> confusing to me.
>
> The proposed API allows you to do exactly that anyway, more easily, by
> just waiting until tg_event_num == tg_tot_num_events.

Can you signal partial completion? For example, if a trigger know that
blocks of 10,000 are optimal and it sees tg_tot_num_events == 1,000,000
that it could do work every 10,000 entries, as in when:

(tg_event_num % 10000) == 0 || tg_event_num == tg_tot_num_events

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer

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.)

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

Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> On 8 June 2013 22:25, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>> Simon Riggs <simon(at)2ndQuadrant(dot)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.

> Since my objective is performance, not standards, I don't see a reason
> to work on that, yet. I might have time to work on it later, lets see.

This seems like it has some overlap with the delta relations I will
need to generate for incremental maintenance of materialized views,
so we should coordinate on those efforts if they happen to occur
around the same time.

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

On Sat, Jun 8, 2013 at 5:00 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> While fiddling with FK tuning, Noah suggested batching trigger
> executions together to avoid execution overhead.
>
> It turns out there is no easy way to write triggers that can take
> advantage of the knowledge that they are being executed as a set of
> trigger executions. Some API is required to allow a trigger to
> understand that there may be other related trigger executions in the
> very near future, so it can attempt to amortise call overhead across
> many invocations ("batching").
>
> The attached patch adds two fields to the TriggerDesc trigger
> functions are handed, allowing them to inspect (if they choose) the
> additional fields and thus potentially use some form of batching.

I'm unclear how this could be used in practice. Are the events in a
"batch" guaranteed to, say, all be related to the same relation?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 10 June 2013 22:50, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sat, Jun 8, 2013 at 5:00 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> While fiddling with FK tuning, Noah suggested batching trigger
>> executions together to avoid execution overhead.
>>
>> It turns out there is no easy way to write triggers that can take
>> advantage of the knowledge that they are being executed as a set of
>> trigger executions. Some API is required to allow a trigger to
>> understand that there may be other related trigger executions in the
>> very near future, so it can attempt to amortise call overhead across
>> many invocations ("batching").
>>
>> The attached patch adds two fields to the TriggerDesc trigger
>> functions are handed, allowing them to inspect (if they choose) the
>> additional fields and thus potentially use some form of batching.
>
> I'm unclear how this could be used in practice.

As described, you can use it to prepare batches.

> Are the events in a
> "batch" guaranteed to, say, all be related to the same relation?

Good point. I was too focused on the single large statements I was
considering. There would need to some logic to look at relations as
well.

For individual statements it can work well, since all or at least the
majority of events are for a particular relation and we can know which
one that is.

It probably wouldn't work that well for deferred trigger events that
covered a spread of different relations.

Perhaps that's an argument for a final function after all.

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

On 9 June 2013 14:56, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>> On 8 June 2013 22:25, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>> Simon Riggs <simon(at)2ndQuadrant(dot)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?

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.

>> Since my objective is performance, not standards, I don't see a reason
>> to work on that, yet. I might have time to work on it later, lets see.
>
> This seems like it has some overlap with the delta relations I will
> need to generate for incremental maintenance of materialized views,
> so we should coordinate on those efforts if they happen to occur
> around the same time.

IMHO you should use Andres' logical changeset extraction for
incremental maintenance for mat views. Doing mat view maintenance
using triggers would be choosing the lower performance option.

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

Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> On 9 June 2013 14:56, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>>> On 8 June 2013 22:25, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>>> Simon Riggs <simon(at)2ndQuadrant(dot)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

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

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

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

Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
> On 9 June 2013 12:58, Craig Ringer <craig(at)2ndquadrant(dot)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.  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.  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?

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.

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

On 06/18/2013 01:25 AM, Pavel Stehule wrote:
>> > 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
Much better naming than OLD_AND_NEW.

I'm not so sure about

OLD
NEW
INSERTED
DELETED

in that I imagine we'd want to pick one pair and stick with it. Since
using "INSERTED" / "DELETED" makes "UPDATED" make sense, and since "OLD"
and "NEW" are already used to refer to the magic variables of those
names in for each row triggers, I think INSERTED / UPDATED / DELETED is
the way to go.

INSERTED and UPDATED could just be views of the same data as UPDATED
that show only the OLD or only the NEW composite type fields. That'd
allow you to write a trigger without TG_OP tests in many cases, as
UPDATED would always contain what you wanted. It seems slightly weird to
have INSERTED and DELETED populated for an UPDATE, but when an UPDATE is
logically an INSERT+DELETE anyway...

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

On 17 June 2013 20:53, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>> On 9 June 2013 12:58, Craig Ringer <craig(at)2ndquadrant(dot)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

On 17 June 2013 23:30, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> INSERTED and UPDATED could just be views...

Yes, that would be my suggestion.

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