Обсуждение: BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT

The following bug has been logged online:

Bug reference:      1098
Logged by:          Tim Burgess

Email address:      tim@queens.unimelb.edu.au

PostgreSQL version: 7.4

Operating system:   PostgreSQL 7.4.1 on i386-unknown-freebsd5.2, compiled by
GCC gcc (GCC) 3.3.3 [FreeBSD] 20031106
FreeBSD

Description:        Multiple ON INSERT rules not applied properly in the
case of INSERT...SELECT

Details:

We have a print charging system here based around a similar idea to a
bank....  We have a table with user balances and a table of transactions.
Every transaction is a transfer of funds between two entities that both have
balances.  There are INSERT rules on the transactions table that adjust the
balances.

\d quips_transactions

           Table "public.quips_transactions"
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
 user_from   | character varying(32)       | not null
 user_to     | character varying(32)       | not null
 amount      | numeric                     | not null
 timestamp   | timestamp without time zone | not null
 description | character varying(255)      |
Foreign-key constraints:
    "ri_users_quips_1" FOREIGN KEY (user_from) REFERENCES
users_quips(username) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
    "ri_users_quips_2" FOREIGN KEY (user_to) REFERENCES
users_quips(username) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
Rules:
    quips_transfer_from AS ON INSERT TO quips_transactions DO UPDATE
users_quips SET balance = (users_quips.balance - new.amount) WHERE
((users_quips.username)::text = (new.user_from)::text)
    quips_transfer_to AS ON INSERT TO quips_transactions DO UPDATE
users_quips SET balance = (users_quips.balance + new.amount) WHERE
((users_quips.username)::text = (new.user_to)::text)


Now the problem occurs when we do a bulk payment to all users (when giving
out the 'free print credit' at the start of the academic year).

I execute a query like this:

insert into quips_transactions select 'frontoffice_quips', member_username,
10, now(), 'Free Print Credit' from group_members where groupname =
'freshers_04';

And all the transactions are added, however the rules do not execute
properly.  In our case, the quips_transfer_to rule worked fine - all the
students had their balances credited.  However, the quips_transfer_from rule
was only applied once (the frontoffice_quips user had their balance lowered
by $10, not $2180 as they should have).

Now, we only do this once a year, so no biggie for us now that I know about
it...  But I imagine it could cause some major headaches for others if
unnoticed!

Cheers, and thanks for all your work.
Tim

On Thursday 11 March 2004 06:45, PostgreSQL Bugs List wrote:
> The following bug has been logged online:
>
> Bug reference:      1098
> Logged by:          Tim Burgess
> Email address:      tim@queens.unimelb.edu.au

> Description:        Multiple ON INSERT rules not applied properly in the
> case of INSERT...SELECT

> Rules:
>     quips_transfer_from AS ON INSERT TO quips_transactions DO UPDATE
> users_quips SET balance = (users_quips.balance - new.amount) WHERE
> ((users_quips.username)::text = (new.user_from)::text)
>     quips_transfer_to AS ON INSERT TO quips_transactions DO UPDATE
> users_quips SET balance = (users_quips.balance + new.amount) WHERE
> ((users_quips.username)::text = (new.user_to)::text)

> insert into quips_transactions select 'frontoffice_quips', member_username,
> 10, now(), 'Free Print Credit' from group_members where groupname =
> 'freshers_04';
>
> And all the transactions are added, however the rules do not execute
> properly.  In our case, the quips_transfer_to rule worked fine - all the
> students had their balances credited.  However, the quips_transfer_from
> rule was only applied once (the frontoffice_quips user had their balance
> lowered by $10, not $2180 as they should have).

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.

--
  Richard Huxton
  Archonet Ltd

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