Re: Summary table trigger example race condition

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