I'm attempting to create an inventory of trees. Here's a simplified,
sample table:
CREATE TABLE tree (
tree_id SERIAL PRIMARY KEY,
tree_species_id INT NOT NULL REFERENCES tree_species,
tree_location POINT NOT NULL,
tree_install_date DATE NOT NULL,
tree_removal_date DATE,
CHECK (tree_removal_date > tree_install_date)
);
I need to ensure that no two trees are located in the same place at
the
same time:
CREATE OR REPLACE FUNCTION check_unique_tree()
RETURNS trigger
AS $$
DECLARE
num_trees INT;
BEGIN
-- just to see what's going on
SELECT COUNT(tree_id) INTO num_trees FROM tree;
RAISE NOTICE '% % of new tree %, there are % trees.',
TG_WHEN, TG_OP, NEW, num_trees;
PERFORM tree_id
FROM tree
WHERE
-- first condition prevents updated tree from matching with itself
NEW.tree_id <> tree_id
AND NEW.tree_location ~= tree_location
AND NEW.tree_install_date <
COALESCE(tree_removal_date, timestamp 'infinity')
AND COALESCE(NEW.tree_removal_date, timestamp 'infinity') >
tree_install_date;
IF FOUND THEN
RAISE EXCEPTION 'Conflicting trees';
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;
CREATE TRIGGER check_unique_tree
AFTER INSERT OR UPDATE ON tree
FOR EACH ROW EXECUTE PROCEDURE check_unique_tree();
And yet, I'm able to do this:
=> INSERT INTO tree (tree_species_id, tree_location,
tree_install_date)
-> VALUES
-> (1, '(1,1)', 'today'),
-> (1, '(1,1)', 'today');
NOTICE: AFTER INSERT of new tree (20,1,"(1,1)",2009-05-21,), there
are 0
trees.
NOTICE: AFTER INSERT of new tree (21,1,"(1,1)",2009-05-21,), there
are 0
trees.
INSERT 0 2
As a sanity check (on a fresh, truncated table):
=> INSERT INTO tree (tree_species_id, tree_location,
tree_install_date)
-> VALUES (1, '(1,1)', 'today');
NOTICE: AFTER INSERT of new tree (22,1,"(1,1)",2009-05-21,), there
are 0
trees.
INSERT 0 1
=> INSERT INTO tree (tree_species_id, tree_location,
tree_install_date)
-> VALUES (1, '(1,1)', 'today');
NOTICE: AFTER INSERT of new tree (23,1,"(1,1)",2009-05-21,), there
are 1
trees.
ERROR: Conflicting trees
I notice the row count does not reflect the newly-inserted row, which
suggests that the trigger is not seeing changes made to the table.
This
seems to be exactly opposite of what's in the manual:
http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html
http://www.postgresql.org/docs/8.3/interactive/trigger-example.html
Am I doing something wrong here? Have I misunderstood the manual?
Have I
found a bug? Any help is greatly appreciated, as this check is pretty
key to what I'm trying to do.
Thanks.
Karl Nack
Futurity, Inc.
773-506-2007
