Thread: After each row trigger NOT seeing data changes?
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
Karl Nack <karlnack@futurityinc.com> writes: > 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 The reason is that you've declared the function STABLE, which causes it to use the calling query's starting snapshot. So it cannot see any in-progress changes of the calling query. Declare it VOLATILE (or let it default to that) and it will act as you expect. I'm not sure if the cited portions of the manual ought to contain notes about this or not. It seems a bit off-topic for them, but if other people have been bit by this, then maybe ... comments anyone? regards, tom lane
you have to change RETURN NULL; with RETURN NEW; On 05/21/2009 04:57 PM, Karl Nack wrote: > 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 >
On 21/05/2009 21:36, Tom Lane wrote: > Karl Nack <karlnack@futurityinc.com> writes: >> http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html >> http://www.postgresql.org/docs/8.3/interactive/trigger-example.html > I'm not sure if the cited portions of the manual ought to contain notes > about this or not. It seems a bit off-topic for them, but if other > people have been bit by this, then maybe ... comments anyone? Maybe just insert a brief reference to relevant section(s) in the manual on STABLE and family? - e.g. "See the following topics on data visibility..." or something like that. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
> The reason is that you've declared the function STABLE Yes, that did it! > I'm not sure if the cited portions of the manual ought to contain notes > about this or not. It seems a bit off-topic for them, but if other > people have been bit by this, then maybe ... comments anyone? Perhaps this should be documented on the page describing CREATE FUNCTION (which is what I referenced when I wrote the function)? In particular, the wording describing IMMUTABLE, STABLE VOLATILE doesn't seem entirely accurate: "STABLE ... is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc." Apparently not the case for after-update triggers that need to reference the just-updated table. Regardless, thank you very much for the help! Karl Nack Futurity, Inc. 773-506-2007
On May 21, 2009, at 10:31 PM, Rodrigo Gonzalez wrote: > you have to change RETURN NULL; with RETURN NEW; No he doesn't, it's an AFTER insert trigger; the data has already been inserted at that point, so the return value is not relevant. And don't top-post, please. > On 05/21/2009 04:57 PM, Karl Nack wrote: >> 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > > Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a16764110091025167268!
Tom Lane wrote: > Karl Nack <karlnack@futurityinc.com> writes: > > 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 > > The reason is that you've declared the function STABLE, which causes it > to use the calling query's starting snapshot. So it cannot see any > in-progress changes of the calling query. Declare it VOLATILE (or > let it default to that) and it will act as you expect. > > I'm not sure if the cited portions of the manual ought to contain notes > about this or not. It seems a bit off-topic for them, but if other > people have been bit by this, then maybe ... comments anyone? I have applied the attached documentation patch to subtly mention this issue. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/create_function.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v retrieving revision 1.90 diff -c -c -r1.90 create_function.sgml *** doc/src/sgml/ref/create_function.sgml 14 Feb 2010 01:01:35 -0000 1.90 --- doc/src/sgml/ref/create_function.sgml 25 Feb 2010 22:21:23 -0000 *************** *** 289,295 **** return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, ! parameter variables (such as the current time zone), etc. Also note that the <function>current_timestamp</> family of functions qualify as stable, since their values do not change within a transaction. </para> --- 289,297 ---- return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, ! parameter variables (such as the current time zone), etc. (It is ! inappropriate for <literal>AFTER</> triggers that wish to ! query rows modified by the current command.) Also note that the <function>current_timestamp</> family of functions qualify as stable, since their values do not change within a transaction. </para>