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?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.83
diff -u -r1.83 plpgsql.sgml
--- doc/src/sgml/plpgsql.sgml 29 Dec 2005 04:02:32 -0000 1.83
+++ doc/src/sgml/plpgsql.sgml 5 Jan 2006 21:36:14 -0000
@@ -3017,31 +3017,33 @@
-- There might have been no row with this time_key (e.g new data!).
IF (NOT FOUND) THEN
- BEGIN
- INSERT INTO sales_summary_bytime (
- time_key,
- amount_sold,
- units_sold,
- amount_cost)
- VALUES (
- delta_time_key,
- delta_amount_sold,
- delta_units_sold,
- delta_amount_cost
- );
- EXCEPTION
- --
- -- Catch race condition when two transactions are adding data
- -- for a new time_key.
- --
- WHEN UNIQUE_VIOLATION THEN
- UPDATE sales_summary_bytime
- SET amount_sold = amount_sold + delta_amount_sold,
- units_sold = units_sold + delta_units_sold,
- amount_cost = amount_cost + delta_amount_cost
- WHERE time_key = delta_time_key;
-
- END;
+ <<insert_update>>
+ LOOP
+ UPDATE sales_summary_bytime
+ SET amount_sold = amount_sold + delta_amount_sold,
+ units_sold = units_sold + delta_units_sold,
+ amount_cost = amount_cost + delta_amount_cost
+ WHERE time_key = delta_time_key;
+
+ EXIT insert_update WHEN found;
+
+ BEGIN
+ INSERT INTO sales_summary_bytime (
+ time_key,
+ amount_sold,
+ units_sold,
+ amount_cost)
+ VALUES (
+ delta_time_key,
+ delta_amount_sold,
+ delta_units_sold,
+ delta_amount_cost
+ );
+ EXCEPTION
+ WHEN UNIQUE_VIOLATION THEN
+ -- do nothing
+ END;
+ END LOOP insert_update;
END IF;
RETURN NULL;
@@ -3051,6 +3053,16 @@
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
+
+INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
+INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
+INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
+INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
+SELECT * FROM sales_summary_bytime;
+DELETE FROM sales_fact WHERE product_key = 1;
+SELECT * FROM sales_summary_bytime;
+UPDATE sales_fact SET units_sold = units_sold * 2;
+SELECT * FROM sales_summary_bytime;
</programlisting>
</example>
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
