After each row trigger NOT seeing data changes?

Started by Karl Nackalmost 17 years ago7 messagesgeneral

karlnack@futurityinc.com

almost 17 years ago

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

Rodrigo Gonzalez

rjgonzale@estrads.com.ar

almost 17 years ago

In reply to: Karl Nack (#1)

Re: After each row trigger NOT seeing data changes?

you have to change RETURN NULL; with RETURN NEW;

Show quoted text

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

tgl@sss.pgh.pa.us

almost 17 years ago

In reply to: Karl Nack (#1)

Re: After each row trigger NOT seeing data changes?

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

Raymond O'Donnell

rod@iol.ie

almost 17 years ago

In reply to: Tom Lane (#3)

Re: After each row trigger NOT seeing data changes?

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

karlnack@futurityinc.com

almost 17 years ago

In reply to: Tom Lane (#3)

Re: After each row trigger NOT seeing data changes?

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

dalroi@solfertje.student.utwente.nl

almost 17 years ago

In reply to: Rodrigo Gonzalez (#2)

Re: After each row trigger NOT seeing data changes?

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!

bruce@momjian.us

about 16 years ago

In reply to: Tom Lane (#3)

Re: After each row trigger NOT seeing data changes?

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