Thread: BUG #1781: result of cascading triggers not available until function exits.

The following bug has been logged online:

Bug reference:      1781
Logged by:          Andrew Smith
Email address:      andrew@asmith.id.au
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;

On Sat, 23 Jul 2005, Andrew Smith wrote:

> The following bug has been logged online:
>
> Bug reference:      1781
> Logged by:          Andrew Smith
> Email address:      andrew@asmith.id.au
> PostgreSQL version: 7.4.6
> Operating system:   Debian GNU/Linux 3.1
> Description:        result of cascading triggers not available until
> function exits.

I don't think the 8.0 changes are a candidate for patching to earlier
versions, so I think the only option is to upgrade if you want the newer
behavior.