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