Update view/table rule order of operations or race condition

From: Dan Fitzpatrick <dan(at)eparklabs(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Update view/table rule order of operations or race condition
Date: 2010-03-09 16:31:10
Message-ID: 000B2A59-BB3C-4848-83B6-F37CD5A565C4@eparklabs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Smith 2010-03-09 16:58:58 Re: kernel version impact on PostgreSQL performance
Previous Message Tom Lane 2010-03-09 16:06:43 Re: How many file descriptors does postgres need?