Skip site navigation (1) Skip section navigation (2)

BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT

From: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT
Date: 2004-03-11 06:45:27
Message-ID: 20040311064527.BFAE6CF4D30@www.postgresql.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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



Responses

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2004-03-11 07:07:22
Subject: Re: BUG #1097: Make failure
Previous:From: Joseph ShraibmanDate: 2004-03-11 00:38:19
Subject: small bug in ADD CONSTRAINT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group