public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jim C. Nasby <[email protected]>
To: [email protected]
Subject: Summary table trigger example race condition
Date: Thu, 5 Jan 2006 15:36:45 -0600
Message-ID: <[email protected]> (raw)
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>
Attachments:
[text/plain] patch (3.2K, 2-patch)
download | inline:
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>
view thread (14+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Summary table trigger example race condition
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox