Date: Thu, 5 Jan 2006 21:04:15 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Mark Kirkwood <markir@paradise.net.nz>
Cc: pgsql-patches@postgresql.org, pgsql-docs@postgresql.org
Subject: Re: [PATCHES] Summary table trigger example race condition
Message-ID: <20060106030415.GL43311@pervasive.com>
References: <20060105213645.GY43311@pervasive.com>
	<43BDC132.90007@paradise.net.nz>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="KDt/GgjP6HVcx58l"
Content-Disposition: inline
In-Reply-To: <43BDC132.90007@paradise.net.nz>
User-Agent: Mutt/1.5.11


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

On Fri, Jan 06, 2006 at 02:00:34PM +1300, Mark Kirkwood wrote:
> However, I think the actual change is not quite right - after running 

DOH! It would be good if doc/src had a better mechanism for handling
code; one that would allow for writing the code natively (so you don't
have to worry about translating < into &lt; and > into &gt;) and for
unit testing the different pieces of code.

Anyway, updated patch attached.
-- 
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

--KDt/GgjP6HVcx58l
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 -c -r1.83 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml	29 Dec 2005 04:02:32 -0000	1.83
--- doc/src/sgml/plpgsql.sgml	6 Jan 2006 03:03:11 -0000
***************
*** 3007,3022 ****
          END IF;
  
  
!         -- Update the summary row with the new values.
!         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;
  
  
-         -- 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, 
--- 3007,3023 ----
          END IF;
  
  
!         -- Insert or update the summary row with the new values.
!         &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, 
***************
*** 3029,3048 ****
                              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;
!         END IF;
          RETURN NULL;
  
      END;
--- 3030,3044 ----
                              delta_units_sold,
                              delta_amount_cost
                             );
+ 
+                 EXIT insert_update;
+ 
              EXCEPTION
                  WHEN UNIQUE_VIOLATION THEN
!                     -- do nothing
              END;
!         END LOOP insert_update;
! 
          RETURN NULL;
  
      END;
***************
*** 3051,3056 ****
--- 3047,3062 ----
  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>
  

--KDt/GgjP6HVcx58l--