Trigger changes visibility

From: amorati <antonio(dot)cosas(at)terra(dot)es>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Trigger changes visibility
Date: 2005-01-17 02:31:07
Message-ID: 41EB236B.4040404@terra.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,

I'm having an unexpected behaviour when executing an 'after delete' trigger.

In PostgreSQL 8.0.0beta5 documentation, section 33.2 "Visibility of Data
Changes", it is said that "When a row-level after trigger is fired, all
data changes made by the outer command are already complete, and are
visible to the invoked trigger function".

In my case, when executing a DELETE sql statement, the next trigger is
executed:

===============================================

CREATE OR REPLACE FUNCTION "public"."DisparadorHijoBorradoCMin1" ()
RETURNS trigger AS
$body$
DECLARE
temporal INTEGER;

BEGIN

SELECT INTO temporal count(*) FROM "Hijo" WHERE
"Hijo"."IDPadre"=OLD."IDPadre" AND "Hijo"."IDHijo"!=OLD."IDHijo";
RAISE NOTICE 'number of Rows: %', temporal;

IF temporal < 1 THEN
RAISE EXCEPTION 'ERROR DE BORRADO';
RETURN NULL;
END IF;

RETURN NULL;
END
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE TRIGGER "BorradoCMin1" AFTER DELETE
ON "public"."Hijo" FOR EACH ROW
EXECUTE PROCEDURE "public"."DisparadorHijoBorradoCMin1"();

===============================================

The problem is that the row's count done by the trigger is the same
number of rows that appears in the table before de delete was executed.
There was no other user trigger in the database.

I've send the database script attached to this mail.

Thanks in advance.

Antonio

Attachment Content-Type Size
PostgreSQL 8 problem.sql text/plain 2.0 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2005-01-17 02:35:05 Re: Error in 8.0 rc5 with repeat calls to array operator
Previous Message Josh Berkus 2005-01-17 01:38:38 Re: Error in 8.0 rc5 with repeat calls to array operator