Обсуждение: BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT
BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT
От
"PostgreSQL Bugs List"
Дата:
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
Re: BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT
От
Richard Huxton
Дата:
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