Thread: Mutating table (urgent)
Hi,
I'm having a big throuble with postgreSQL, called "mutating table"
This problem also happen with Oracle,
but we found a soluction creating packagers.
but we found a soluction creating packagers.
In first.
I would like to explain that this exemple is just a exemple.
Don't try to understand its utility.
I would like to explain that this exemple is just a exemple.
Don't try to understand its utility.
Step to emulation the error:
1) Creating a principal table and a child table
CREATE TABLE PAI(CODPAI INTEGER PRIMARY KEY, NOMPAI VARCHAR(30));
CREATE TABLE FILHO(CODPAI INTEGER, CODFIL SERIAL, NOMFIL VARCHAR(30), PRIMARY KEY (CODPAI, CODFIL));
CREATE TABLE PAI(CODPAI INTEGER PRIMARY KEY, NOMPAI VARCHAR(30));
CREATE TABLE FILHO(CODPAI INTEGER, CODFIL SERIAL, NOMFIL VARCHAR(30), PRIMARY KEY (CODPAI, CODFIL));
2) Creating constraint between principal and child table
ALTER TABLE FILHO ADD CONSTRAINT FK_PAI_FILHO FOREIGN KEY (CODPAI) REFERENCES PAI (CODPAI);
ALTER TABLE FILHO ADD CONSTRAINT FK_PAI_FILHO FOREIGN KEY (CODPAI) REFERENCES PAI (CODPAI);
3) Creating trigger on principal table
CREATE OR REPLACE FUNCTION TR_AIPAI() RETURNS OPAQUE AS '
BEGIN
INSERT INTO FILHO (CODPAI, NOMFIL) VALUES (NEW.CODPAI, ''FILHO DO PAI''||NEW.CODPAI);
RETURN NULL;
END;
' language 'plpgsql';
CREATE OR REPLACE FUNCTION TR_AIPAI() RETURNS OPAQUE AS '
BEGIN
INSERT INTO FILHO (CODPAI, NOMFIL) VALUES (NEW.CODPAI, ''FILHO DO PAI''||NEW.CODPAI);
RETURN NULL;
END;
' language 'plpgsql';
CREATE TRIGGER AIPAI AFTER INSERT ON PAI FOR EACH ROW EXECUTE PROCEDURE TR_AIPAI();
If we taking a insert on principal table it will insert on child table,
until here, thats all right...
until here, thats all right...
4) Create a trigger on child table that make a select on principal table
CREATE OR REPLACE FUNCTION TR_SELECT_PAI() RETURNS OPAQUE AS'
DECLARE I INTEGER;
BEGIN
SELECT COUNT(*) INTO I FROM PAI;
RETURN NULL;
END;
'language 'plpgsql';
CREATE OR REPLACE FUNCTION TR_SELECT_PAI() RETURNS OPAQUE AS'
DECLARE I INTEGER;
BEGIN
SELECT COUNT(*) INTO I FROM PAI;
RETURN NULL;
END;
'language 'plpgsql';
CREATE TRIGGER BIFILHO BEFORE INSERT ON FILHO FOR EACH ROW EXECUTE PROCEDURE TR_SELECT_PAI();
In this moment when we insert a register on principal table
it don't insert the register on child table, neither send any error message.
it don't insert the register on child table, neither send any error message.
If anybody can help me, I'll be thanks for ever.
Cristian Custodio
On Fri, 21 Feb 2003, Cristian Custodio wrote: > 3) Creating trigger on principal table > CREATE OR REPLACE FUNCTION TR_AIPAI() RETURNS OPAQUE AS ' > BEGIN > INSERT INTO FILHO (CODPAI, NOMFIL) VALUES (NEW.CODPAI, ''FILHO DO PAI''||NEW.CODPAI); > RETURN NULL; > END; > ' language 'plpgsql'; > > CREATE TRIGGER AIPAI AFTER INSERT ON PAI FOR EACH ROW EXECUTE PROCEDURE TR_AIPAI(); > > If we taking a insert on principal table it will insert on child table, > until here, thats all right... > > 4) Create a trigger on child table that make a select on principal table > CREATE OR REPLACE FUNCTION TR_SELECT_PAI() RETURNS OPAQUE AS' > DECLARE I INTEGER; > BEGIN > SELECT COUNT(*) INTO I FROM PAI; > RETURN NULL; > END; > 'language 'plpgsql'; > > CREATE TRIGGER BIFILHO BEFORE INSERT ON FILHO FOR EACH ROW EXECUTE PROCEDURE TR_SELECT_PAI(); By returning NULL in a BEFORE trigger you're saying to drop the request on the floor.
It´s true, but why dont it trigger any error message?
It should raise and rollback, right?
It is seeming a bug, what do you think?
Cristian
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Cristian Custodio" <crstian@terra.com.br>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, February 21, 2003 3:33 PM
Subject: Re: [GENERAL] Mutating table (urgent)
On Fri, 21 Feb 2003, Cristian Custodio wrote:
> 3) Creating trigger on principal table
> CREATE OR REPLACE FUNCTION TR_AIPAI() RETURNS OPAQUE AS '
> BEGIN
> INSERT INTO FILHO (CODPAI, NOMFIL) VALUES (NEW.CODPAI, ''FILHO DO
PAI''||NEW.CODPAI);
> RETURN NULL;
> END;
> ' language 'plpgsql';
>
> CREATE TRIGGER AIPAI AFTER INSERT ON PAI FOR EACH ROW EXECUTE
PROCEDURE TR_AIPAI();
>
> If we taking a insert on principal table it will insert on child table,
> until here, thats all right...
>
> 4) Create a trigger on child table that make a select on principal table
> CREATE OR REPLACE FUNCTION TR_SELECT_PAI() RETURNS OPAQUE AS'
> DECLARE I INTEGER;
> BEGIN
> SELECT COUNT(*) INTO I FROM PAI;
> RETURN NULL;
> END;
> 'language 'plpgsql';
>
> CREATE TRIGGER BIFILHO BEFORE INSERT ON FILHO FOR EACH ROW EXECUTE
PROCEDURE TR_SELECT_PAI();
By returning NULL in a BEFORE trigger you're saying to drop the request on
the floor.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Fri, 21 Feb 2003, Cristian Custodio wrote: > It�s true, but why dont it trigger any error message? Why? I must be missing the error condition. The foreign key is fine AFAICS and I don't see anything else that would cause it to error.
Because whether postgresql don't send me the error. How Can I know when I must use commit and when I must use the rollback? Cristian ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> To: "Cristian Custodio" <crstian@terra.com.br> Cc: <pgsql-general@postgresql.org> Sent: Friday, February 21, 2003 4:13 PM Subject: Re: [GENERAL] Mutating table (urgent) On Fri, 21 Feb 2003, Cristian Custodio wrote: > It´s true, but why dont it trigger any error message? Why? I must be missing the error condition. The foreign key is fine AFAICS and I don't see anything else that would cause it to error. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Fri, 21 Feb 2003, Cristian Custodio wrote: > Because whether postgresql don't send me the error. But AFAICS there isn't any error to send you. Maybe I'm missing something, but it seems to me the sequence is: Do an insert on PAI Run after trigger which tries an insert on FILHO Run before trigger on FILHO Ignore insert No constraints seem to be violated, no errors are occurring. You've told the system that inserts to FILHO are silently ignored (by returning NULL from the trigger), so it seems to be doing exactly what you asked for.
Sorry, Now I'm undestandy you. I thinked RETURN NULL was obrigated for all triggers functions. I change for RETURN NEW and then it work fine. Thanks, Cristian ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> To: "Cristian Custodio" <crstian@terra.com.br> Cc: <pgsql-general@postgresql.org> Sent: Friday, February 21, 2003 5:46 PM Subject: Re: [GENERAL] Mutating table (urgent) On Fri, 21 Feb 2003, Cristian Custodio wrote: > Because whether postgresql don't send me the error. But AFAICS there isn't any error to send you. Maybe I'm missing something, but it seems to me the sequence is: Do an insert on PAI Run after trigger which tries an insert on FILHO Run before trigger on FILHO Ignore insert No constraints seem to be violated, no errors are occurring. You've told the system that inserts to FILHO are silently ignored (by returning NULL from the trigger), so it seems to be doing exactly what you asked for. ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org