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

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

From: "Andrew Smith" <andrew(at)asmith(dot)id(dot)au>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1781: result of cascading triggers not available until function exits.
Date: 2005-07-23 09:59:48
Message-ID: 20050723095948.6353FF0B16@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1781
Logged by:          Andrew Smith
Email address:      andrew(at)asmith(dot)id(dot)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;

Responses

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2005-07-23 16:59:22
Subject: Re: BUG #1781: result of cascading triggers not available
Previous:From: Tom LaneDate: 2005-07-22 19:57:04
Subject: Re: abstime bug

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