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

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

From: Richard Huxton <dev(at)archonet(dot)com>
To: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
Cc: tim(at)queens(dot)unimelb(dot)edu(dot)au
Subject: Re: BUG #1098: Multiple ON INSERT rules not applied properly in the case of INSERT...SELECT
Date: 2004-03-11 08:55:16
Message-ID: 200403110855.16101.dev@archonet.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)queens(dot)unimelb(dot)edu(dot)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

In response to

Responses

pgsql-bugs by date

Next:From: PostgreSQL Bugs ListDate: 2004-03-11 14:41:39
Subject: BUG #1099: bad syntax error localisation
Previous:From: Peter EisentrautDate: 2004-03-11 07:07:22
Subject: Re: BUG #1097: Make failure

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