Richard Huxton <dev@archonet.com> writes:
> The rule should only be applied once. The rule system basically rewrites the
> insert you supply into two other insert queries (from/to). It will not
> generate one query for each row in quips_transactions (which is what you
> want). You probably need a trigger on the transactions table that issues
> separate queries for each row inserted.
The real issue is that since the multi-row insert command generates
multi-row update commands, user rows that are mentioned multiple times
in the "from" or "to" columns of the insert would have to be updated
multiple times in the same UPDATE command. We do not support that ---
an UPDATE can only update a given row once, because it does not see its
own output row versions as input candidates. This is annoying in this
scenario but it is correct and necessary in most other scenarios.
As an example, without this rule something like "UPDATE foo SET x = x + 1"
would likely go into an infinite loop, repeatedly seeing the new row
version it just created as fodder for another UPDATE cycle.
I concur with the suggestion that triggers would probably be the most
intuitive solution to the problem. I tried to think of a way to make
it work as a rule by aggregating all the updates affecting a single user
row into one row operation. However, since the UPDATE syntax has no
provision for GROUP BY there doesn't seem to be any good way to do that.
regards, tom lane
