Search Postgresql Archives

After each row trigger NOT seeing data changes?

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

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[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