Delete cascade with three levels bug ? - Mailing list pgsql-hackers
From | Marcelo Costa |
---|---|
Subject | Delete cascade with three levels bug ? |
Date | |
Msg-id | c13f2d590910270705w6b4a2ac8m1a1d3f192cbe3261@mail.gmail.com Whole thread Raw |
Responses |
Re: Delete cascade with three levels bug ?
Re: Delete cascade with three levels bug ? |
List | pgsql-hackers |
Hi, hackers<br /><br /> I have a problem at PostgreSQL 8.3.5 (Slackware Server and Win 2003 Server) SO independent.<br /><br/>When run the scripts below I receive the error:<br /><br />---------------------------------------------------------------------------<br/>testes=# DELETE FROM pai WHERE co_pai =1;<br /> server closed the connection unexpectedly<br /> This probably means the server terminated abnormally<br/> before or while processing the request.<br />The connection to the server was lost. Attempting reset:Failed.<br /> ---------------------------------------------------------------------------<br /><br />The script createsthe father table, son and grandson. All conected with FK Cascade.<br /><br />I changed the comportment of the pg_triggerto postpone the validation of constraint<br /> (make compatible with 7.4 version because we migrate it).<br /><br/>When I delete the father register the next error ocourr.<br /><br />PS1.: On Windows need reinicialize the service.<br/> PS2.: On Linux all connection down but not is needed reinicialize the service.<br /> PS3.: I make a test on8.4 version and the same error ocourr.<br /><br /><br /> -------Scripts-------<br /> CREATE TABLE father<br /> (<br /> co_father double precision NOT NULL,<br /> no_description character varying(50) NOT NULL,<br /> CONSTRAINT pk_fatherPRIMARY KEY (co_father)<br /> )<br /> WITH (OIDS=TRUE);<br /> ALTER TABLE father OWNER TO postgres;<br /><br /><br/> CREATE TABLE son<br /> (<br /> co_son double precision NOT NULL,<br /> co_father double precision NOT NULL,<br/> no_description character varying(50) NOT NULL,<br /> CONSTRAINT pk_son PRIMARY KEY (co_son)<br /> )<br /> WITH(OIDS=TRUE);<br /> ALTER TABLE son OWNER TO postgres;<br /><br /> CREATE TABLE grandson<br /> (<br /> co_grandson doubleprecision NOT NULL,<br /> co_son double precision NOT NULL,<br /> no_description character varying(50) NOT NULL,<br/> CONSTRAINT pk_grandson PRIMARY KEY (co_grandson)<br /> )<br /> WITH (OIDS=TRUE);<br /> ALTER TABLE son OWNERTO postgres;<br /><br /> ALTER TABLE son<br /> ADD CONSTRAINT fk_son_father FOREIGN KEY (co_father)<br /> REFERENCESfather (co_father) MATCH SIMPLE<br /> ON UPDATE CASCADE ON DELETE CASCADE;<br /><br /> ALTER TABLE grandson<br/> ADD CONSTRAINT fk_grandson_son FOREIGN KEY (co_son)<br /> REFERENCES son (co_son) MATCH SIMPLE<br /> ON UPDATE CASCADE ON DELETE CASCADE;<br /><br /> UPDATE pg_constraint SET condeferred = TRUE, condeferrable = TRUE;<br/> UPDATE pg_trigger SET tgdeferrable = TRUE, tginitdeferred = TRUE;<br /><br /> INSERT INTO father VALUES(1, 'FATHER-1');<br/><br /> INSERT INTO son VALUES(1,1, 'Son FATHER-1');<br /> INSERT INTO son VALUES(2,1, 'Son FATHER-1');<br/> INSERT INTO son VALUES(3,1, 'Son FATHER-1');<br /><br /> INSERT INTO grandson VALUES(1,1, 'Grandson FATHER-1');<br/> INSERT INTO grandson VALUES(2,2, 'Grandson FATHER-1');<br /> INSERT INTO grandson VALUES(3,3, 'GrandsonFATHER-1');<br /><br /> DELETE FROM father WHERE co_father = 1<br /><br /><br />My LOGS -----------------------------------------------------------------------------------------------------------<br/><br />0LOG: 00000: server process (PID 23470) was terminated by signal 11: Segmentation fault<br /> 0LOCATION: LogChildExit,postmaster.c:2529<br />0LOG: 00000: terminating any other active server processes<br />0LOCATION: HandleChildCrash,postmaster.c:2374<br />0FATAL: 57P03: the database system is in recovery mode<br /> 0LOCATION: ProcessStartupPacket,postmaster.c:1648<br /> 0LOG: 00000: all server processes terminated; reinitializing<br />0LOCATION: PostmasterStateMachine, postmaster.c:2690<br />0LOG: 00000: database system was interrupted; last known upat 2009-10-27 11:43:37 BRST<br />0LOCATION: StartupXLOG, xlog.c:4836<br /> 0DEBUG: 00000: checkpoint record is at 1/1C865BD4<br/>0LOCATION: StartupXLOG, xlog.c:4906<br />0DEBUG: 00000: redo record is at 1/1C865BD4; shutdown TRUE<br />0LOCATION: StartupXLOG, xlog.c:4932<br />0DEBUG: 00000: next transaction ID: 0/27113; next OID: 67190<br /> 0LOCATION: StartupXLOG, xlog.c:4936<br />0DEBUG: 00000: next MultiXactId: 1; next MultiXactOffset: 0<br />0LOCATION: StartupXLOG,xlog.c:4939<br />0LOG: 00000: database system was not properly shut down; automatic recovery in progress<br/> 0LOCATION: StartupXLOG, xlog.c:5003<br />0LOG: 00000: record with zero length at 1/1C865C14<br />0LOCATION: ReadRecord, xlog.c:3126<br />0LOG: 00000: redo is not required<br />0LOCATION: StartupXLOG, xlog.c:5146<br/>0DEBUG: 00000: transaction ID wrap limit is 2147484026, limited by database "template1"<br /> 0LOCATION: SetTransactionIdLimit, varsup.c:283<br />0LOG: 00000: database system is ready to accept connections<br />0LOCATION: reaper, postmaster.c:2156<br /><br />-----------------------------------------------------------------------------------------------------------------------------<br clear="all"/><br />Has I know, the deletion cascade with three levels don't give this problem. The same think occourr ifI change the pg_trigger comportment to postpone validation. This is right?<br /><br />Sorry, but what wrap limit do ?<br/><br />Thanks in advanced.<br /><br />Sincerely,<br />-- <br />Marcelo Costa<br /><a href="http://www.marcelocosta.net"target="_blank"></a>-------------------------------------------------<br />“You can't alwaysget what want”, <br /><br />Doctor House in apology to Mike Jagger<br />
pgsql-hackers by date: