Search Postgresql Archives

Update view/table rule order of operations or race condition

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;

-- View output
 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux