[BUGS] BUG #1781: result of cascading triggers not available until function exits.

[Andrew Smith]

The following bug has been logged online:

Bug reference:      1781
Logged by:          Andrew Smith
Email address:      [EMAIL PROTECTED]
PostgreSQL version: 7.4.6
Operating system:   Debian GNU/Linux 3.1
Description:        result of cascading triggers not available until
function exits.
Details: 

CREATE TABLE master(value INTEGER);
CREATE TABLE detail(value INTEGER,parent INTEGER);

CREATE FUNCTION aftermaster() RETURNS TRIGGER AS '
BEGIN
    INSERT INTO detail(value,parent)
        VALUES(10,NEW.value);
    INSERT INTO detail(value,parent)
        VALUES(20,NEW.value);
    RETURN NULL;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER aftermaster AFTER INSERT OR UPDATE ON master
FOR EACH ROW EXECUTE PROCEDURE aftermaster();

CREATE OR REPLACE FUNCTION dostuff() RETURNS VOID AS '
DECLARE
    counter INTEGER;
BEGIN
    INSERT INTO master(value) VALUES(1);
    SELECT COUNT(*) INTO counter FROM detail;
    RAISE NOTICE ''counter = %'',counter;
    RETURN NULL;
END
' LANGUAGE plpgsql;

/*
on PostgreSQL 8.0 log output is 'counter = 2'
but for version 7.4.6 log output is 'counter = 0'
*/

SELECT dostuff();
SELECT COUNT(*) FROM detail;

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster