Skip site navigation (1) Skip section navigation (2)

Strange AFTER UPDATE trigger behavior

From: "Mason" <mason(at)vanten(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Strange AFTER UPDATE trigger behavior
Date: 2004-07-22 03:14:41
Message-ID: 026d01c46f9a$136db4a0$22dca8c0@coldpizza (view raw or flat)
Thread:
Lists: pgsql-bugs
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name : mason
Your email address : mason (at) vanten.com

System Configuration
---------------------
Architecture (example: Intel Pentium) : AMD, multi proc, not sure exactly

Operating System (example: Linux 2.0.26 ELF) : NetBSD

PostgreSQL version (example: PostgreSQL-7.3.4): PostgreSQL-7.3.4


Please enter a FULL description of your problem:
------------------------------------------------ 

The following scenerio is the expected behavior:

A function performs two operations:

1) updates a column on a table, casing a trigger to fire which will insert a
row into a new table.
2) The original fuction then updates that newly inserted row with some
additional data.

Actual behavior:

If the trigger is placed BEFORE UPDATE on the table with the changing
column, this works as planned.
If the trigger is instead placed AFTER UPDATE on the table then the function
cannot find the newly inserted row to update after the trigger has fired
even though the row is very clearly in the table.


Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
---------------------------------------------------------------------- 

To see the two types of behavior just switch the word AFTER with BEFORE and
run again.

CREATE TABLE foo (

    id  serial PRIMARY KEY

) WITHOUT OIDS;

CREATE TABLE bar (

    id  serial PRIMARY KEY REFERENCES foo,
    data        text

) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION insert_rec()
RETURNS TRIGGER
AS '
BEGIN
    INSERT INTO bar
 VALUES (NEW.id, ''text'');
    RETURN NEW;
END;
' LANGUAGE 'plpgsql' SECURITY DEFINER;

CREATE TRIGGER foo_trigger
AFTER UPDATE ON foo FOR EACH ROW
EXECUTE PROCEDURE insert_rec();

INSERT INTO foo VALUES (1);
INSERT INTO foo VALUES (2);
INSERT INTO foo VALUES (3);
INSERT INTO foo VALUES (4);

CREATE OR REPLACE FUNCTION test()
RETURNS boolean
AS '
BEGIN
    UPDATE foo SET id = 10 WHERE id = 1;
    UPDATE bar SET data = ''changed'' WHERE id = 10;
    UPDATE foo SET id = 20 WHERE id = 2;
    UPDATE bar SET data = ''changed'' WHERE id = 20;
    RETURN true;
END;
' LANGUAGE 'plpgsql' SECURITY DEFINER;

SELECT test();

SELECT * FROM foo;
SELECT * FROM bar;

DROP TABLE foo CASCADE;
DROP TABLE bar CASCADE;


If you know how this problem might be fixed, list the solution below:
--------------------------------------------------------------------- 

??

-- 
Mason Glaves, Senior Programmer, Vanten K.K.
mason(at)vanten(dot)com         Tel: 03-5919-0266
http://www.vanten.com   Fax: 03-5919-0267




pgsql-bugs by date

Next:From: Frank van VugtDate: 2004-07-22 13:50:46
Subject: adding a primary key column to a temporary table fails (v7.4.3)
Previous:From: Tom LaneDate: 2004-07-22 00:11:20
Subject: Re: Bug in concat operator for Char? -- More Info

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group