Strange AFTER UPDATE trigger behavior

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