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