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