|
|
 | | From: | amorati | | Subject: | Trigger changes visibility | | Date: | Mon, 17 Jan 2005 03:31:07 +0100 |
|
|
 | This is a multi-part message in MIME format.
--------------000807080700090201020401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
--------------000807080700090201020401
Content-Type: text/plain;
name="PostgreSQL 8 problem.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="PostgreSQL 8 problem.sql"
CREATE TABLE "public"."Padre" (
"IDPadre" SERIAL,
"DatoPadre" CHAR(18) NOT NULL,
CONSTRAINT "PKPadre" PRIMARY KEY("IDPadre")
) WITHOUT OIDS;
CREATE TABLE "public"."Hijo" (
"IDHijo" SERIAL,
"DatoHijo" CHAR(28) NOT NULL,
"IDPadre" INTEGER NOT NULL,
CONSTRAINT "PKHijo" PRIMARY KEY("IDHijo"),
CONSTRAINT "gg" FOREIGN KEY ("IDPadre")
REFERENCES "public"."Padre"("IDPadre")
MATCH FULL
ON DELETE RESTRICT
ON UPDATE CASCADE
DEFERRABLE
INITIALLY DEFERRED
) WITHOUT OIDS;
CREATE INDEX "fki_FKPadre-Hijo" ON "public"."Hijo"
USING btree ("IDPadre");
CREATE OR REPLACE FUNCTION "public"."compruebaenhijo" (identificador integer) RETURNS boolean AS
$body$
/* New function body */
DECLARE
temporal "Hijo"%ROWTYPE;
BEGIN
SELECT INTO temporal * FROM "Hijo" WHERE "Hijo"."IDPadre"=identificador;
IF NOT FOUND THEN
RETURN false;
END IF;
RETURN true;
END
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
ALTER TABLE "Padre" ADD CONSTRAINT "Padre_check0" CHECK (compruebaenhijo("IDPadre"));
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";
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"();
begin;
insert into "Hijo" values (1,'dd',1);
insert into "Hijo" values (2,'dd',1);
insert into "Hijo" values (3,'dd',1);
insert into "Padre" values (1,'pp');
commit;
-- the error comes here!!
-- for every row, the trigger gets executed,
-- but always returns that there are 3 rows,
-- the same that were before executing DELETE.
delete from "Hijo";
--------------000807080700090201020401
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--------------000807080700090201020401--
|
|
| | From: | Tom Lane | | Subject: | Re: Trigger changes visibility | | Date: | Mon, 17 Jan 2005 13:54:06 -0500 |
|
|
| amorati writes:
> CREATE OR REPLACE FUNCTION "public"."DisparadorHijoBorradoCMin1" ()
> RETURNS trigger AS
> ...
> LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
> 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.
Don't use an IMMUTABLE function as a trigger. I'm not sure if the
system should actually prohibit this, but the function is doing what
it's spec'd to, namely not seeing any side-effects of the calling query.
See
http://developer.postgresql.org/docs/postgres/xfunc-volatility.html
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
|
