Update view/table rule order of operations or race condition - Mailing list pgsql-general
| From | Dan Fitzpatrick |
|---|---|
| Subject | Update view/table rule order of operations or race condition |
| Date | |
| Msg-id | 000B2A59-BB3C-4848-83B6-F37CD5A565C4@eparklabs.com Whole thread Raw |
| Responses |
Re: Update view/table rule order of operations or race condition
(Dan Fitzpatrick <dan@eparklabs.com>)
|
| List | pgsql-general |
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
pgsql-general by date: