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(at)queens(dot)unimelb(dot)edu(dot)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