Date: Thu, 5 Jan 2006 15:36:45 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: pgsql-patches@postgresql.org
Subject: Summary table trigger example race condition
Message-ID: <20060105213645.GY43311@pervasive.com>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="SkvwRMAIpAhPCcCJ"
Content-Disposition: inline
User-Agent: Mutt/1.5.11


--SkvwRMAIpAhPCcCJ
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

--SkvwRMAIpAhPCcCJ
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename=patch

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;
+            &lt;&lt;insert_update&gt;&gt;
+            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>
 

--SkvwRMAIpAhPCcCJ--