Thread: Update view/table rule order of operations or race condition
I have a table with a trigger that inserts records into a second table on insert. I have a view that has a rule that inserts a record into the first table then updates the records that the first table's trigger inserted into the second table. From what I can see, when the view rule executes, the records in the second table are not yet inserted so they do not get updated. Please see code below (simplified for readability). Any ideas on why this is or another solution would be appreciated. Thanks.
-- Tables
CREATE TABLE a (a_id SERIAL, name VARCHAR(10), type_id INT);
CREATE TABLE item_type (item_type_id SERIAL, a_type_id INT, type_name VARCHAR(10));CREATE TABLE a_item (a_item_id SERIAL, a_id INT, type_id INT, val NUMERIC);
-- Add some type data
insert into item_type values (default,6,'quantity');
insert into item_type values (default,6,'price');
insert into item_type values (default,6,'discount');
-- Show type data
select * from item_type;
-- item_type_id | a_type_id | type_name
----------------+-----------+-----------
-- 1 | 6 | quantity
-- 2 | 6 | price
-- 3 | 6 | discount
--(3 rows)
-- Trigger function to add items
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION add_items()
RETURNS "trigger" AS
$BODY$BEGIN
INSERT INTO a_item (a_id, type_id)
SELECT a.a_id,
item_type.item_type_id
FROM a
JOIN item_type
ON a.type_id = item_type.a_type_id
WHERE a.a_id = NEW.a_id;
RETURN NULL;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-- Create trigger
CREATE TRIGGER insert_a_items
AFTER INSERT
ON a
FOR EACH ROW
EXECUTE PROCEDURE add_items();
-- Insert an "a" record
insert into a values (default,'Test',6);
-- Trigger works
select * from a_item;
-- a_item_id | a_id | type_id | val
-------------+------+---------+-----
-- 1 | 1 | 1 |
-- 2 | 1 | 2 |
-- 3 | 1 | 3 |
--(3 rows)
-- The view is a flattened version of the "a" and "a_item" table for a specific case
CREATE OR REPLACE VIEW options AS
SELECT a.a_id AS options_id, a.name AS options_name,
(SELECT a_item.val
FROM a_item
WHERE a_item.a_id = a.a_id AND a_item.type_id = 1) AS quantity,
(SELECT a_item.val
FROM a_item
WHERE a_item.a_id = a.a_id AND a_item.type_id = 2) AS price,
(SELECT a_item.val
FROM a_item
WHERE a_item.a_id = a.a_id AND a_item.type_id = 3) AS discount
FROM a
WHERE a.type_id = 6;
select * from options;
-- options_id | options_name | quantity | price | discount
--------------+--------------+----------+-------+----------
-- 1 | Test | | |
--(1 row)
-- The rule inserts into the "a" table and then updates the "a_item"
-- table with the records that the trigger is supposed
-- to execute before the next commands are run.
CREATE OR REPLACE RULE insert_options AS
ON INSERT TO options DO INSTEAD
-- Insert into table "a"
(INSERT INTO a (a_id, type_id, name)
VALUES (new.options_id, 6, new.options_name);
-- Update 3 records in table "a_item"
-- This doesn't work because the records do not appear to exist yet?
UPDATE a_item SET val = new.quantity
WHERE a_item.a_id = new.options_id AND a_item.type_id = 1;
UPDATE a_item SET val = new.price
WHERE a_item.a_id = new.options_id AND a_item.type_id = 2;
UPDATE a_item SET val = new.discount
WHERE a_item.a_id = new.options_id AND a_item.type_id = 3;
);
-- Insert into the view
insert into options values (nextval('a_a_id_seq'),'Test 2',1,2,3);
-- View data
select * from options;
options_id | options_name | quantity | price | discount
------------+--------------+----------+-------+----------
1 | Test | | |
2 | Test 2 | | |
(2 rows)
If I change the insert_options rule to insert into a_item, then I get 6 records in a_item (3 from insert_a_items and 3 from insert_options). The first 3 have null vals and the second 3 have the correct vals.
It should be:
options_id | options_name | quantity | price | discount
------------+--------------+----------+-------+----------
1 | Test | | |
2 | Test 2 | 1 | 2 | 3
Any ideas why this is or if there is another approach?
Thanks,
Dan
I think I found the problem with this. The rule:
CREATE OR REPLACE RULE insert_options AS
ON INSERT TO options DO INSTEAD
(INSERT INTO a (a_id, type_id, name)
VALUES (new.options_id, 6, new.options_name);
UPDATE a_item SET val = new.quantity
WHERE a_item.a_id = new.options_id AND a_item.type_id = 1;
UPDATE a_item SET val = new.price
WHERE a_item.a_id = new.options_id AND a_item.type_id = 2;
UPDATE a_item SET val = new.discount
WHERE a_item.a_id = new.options_id AND a_item.type_id = 3;
);
Is creating a new value from the sequence a_a_id_seq for
"new.options_id" on each UPDATE call. How do I use the variable
new.options_id in the three update statements without incrementing the
sequence again?
Dan Fitzpatrick <dan@eparklabs.com> writes:
> The rule is creating a new value from the sequence a_a_id_seq for
> "new.options_id" on each UPDATE call. How do I use the variable
> new.options_id in the three update statements without incrementing the
> sequence again?
You don't. This is one of the major disadvantages of rules: they're
macros, not functions, and as such don't have any real local variables.
You should probably look into whether you can do what you want with a
trigger instead of a rule.
regards, tom lane
On Mar 9, 2010, at 11:00 AM, Tom Lane wrote: > Dan Fitzpatrick <dan@eparklabs.com> writes: >> The rule is creating a new value from the sequence a_a_id_seq for >> "new.options_id" on each UPDATE call. How do I use the variable >> new.options_id in the three update statements without incrementing >> the >> sequence again? > > You don't. This is one of the major disadvantages of rules: they're > macros, not functions, and as such don't have any real local > variables. > You should probably look into whether you can do what you want with a > trigger instead of a rule. > > regards, tom lane Can you have a trigger on a view? I thought you can only use rules with a view. As another option, can you pass "NEW" (the incoming data record) as a variable to a function or does each field in NEW have to be explicitly passed to the function?