Date: Fri, 06 Jan 2006 14:00:34 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Summary table trigger example race condition
In-reply-to: <20060105213645.GY43311@pervasive.com>
To: "Jim C. Nasby" <jnasby@pervasive.com>
Cc: pgsql-patches@postgresql.org
Message-id: <43BDC132.90007@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <20060105213645.GY43311@pervasive.com>

Jim C. Nasby wrote:
> http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
> example 36-4 has a race condition in the code that checks to see if a
> row exists. It should use the code from example 36-1. This patch fixes
> that. It also adds some commands to show what the summary table output
> looks like. Unfortunately gamke html is bombing with some kind of
> library error, so I can't verify that I didn't break the sgml.
> 
> BTW, should this have gone to -docs instead?

Your SGML builds fine for me.

However, I think the actual change is not quite right - after running 
the INSERT, DELETE, UPDATE sequence at the end I see:

ware=# SELECT * FROM sales_summary_bytime;
  time_key | amount_sold | units_sold | amount_cost
----------+-------------+------------+-------------
         1 |       30.00 |         13 |       50.00
         2 |       90.00 |         47 |      283.00
(2 rows)

ware=# select * from sales_fact;
  time_key | product_key | store_key | amount_sold | units_sold | 
amount_cost
----------+-------------+-----------+-------------+------------+-------------
         1 |           2 |         1 |       20.00 |         10 | 
35.00
         2 |           2 |         1 |       40.00 |         30 | 
135.00
         2 |           3 |         1 |       10.00 |          2 | 
13.00
(3 rows)

i.e - sales_summary_bytime and sales_fact do not agree with each other 
any more! I suspect that the loop does the update even if the insert is 
successful (so double counts).

BTW - Nice to see someone reading this... :-)


Best wishes

Mark