Delete cascade with three levels bug ?

Started by Marcelo Costaabout 16 years ago6 messages

marcelojscosta@gmail.com

about 16 years ago

Hi, hackers

I have a problem at PostgreSQL 8.3.5 (Slackware Server and Win 2003 Server)
SO independent.

When run the scripts below I receive the error:

---------------------------------------------------------------------------
testes=# DELETE FROM pai WHERE co_pai = 1;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
---------------------------------------------------------------------------

The script creates the father table, son and grandson. All conected with FK
Cascade.

I changed the comportment of the pg_trigger to postpone the validation of
constraint
(make compatible with 7.4 version because we migrate it).

When I delete the father register the next error ocourr.

PS1.: On Windows need reinicialize the service.
PS2.: On Linux all connection down but not is needed reinicialize the
service.
PS3.: I make a test on 8.4 version and the same error ocourr.

-------Scripts-------
CREATE TABLE father
(
co_father double precision NOT NULL,
no_description character varying(50) NOT NULL,
CONSTRAINT pk_father PRIMARY KEY (co_father)
)
WITH (OIDS=TRUE);
ALTER TABLE father OWNER TO postgres;

CREATE TABLE son
(
co_son double precision NOT NULL,
co_father double precision NOT NULL,
no_description character varying(50) NOT NULL,
CONSTRAINT pk_son PRIMARY KEY (co_son)
)
WITH (OIDS=TRUE);
ALTER TABLE son OWNER TO postgres;

CREATE TABLE grandson
(
co_grandson double precision NOT NULL,
co_son double precision NOT NULL,
no_description character varying(50) NOT NULL,
CONSTRAINT pk_grandson PRIMARY KEY (co_grandson)
)
WITH (OIDS=TRUE);
ALTER TABLE son OWNER TO postgres;

ALTER TABLE son
ADD CONSTRAINT fk_son_father FOREIGN KEY (co_father)
REFERENCES father (co_father) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE grandson
ADD CONSTRAINT fk_grandson_son FOREIGN KEY (co_son)
REFERENCES son (co_son) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE;

UPDATE pg_constraint SET condeferred = TRUE, condeferrable = TRUE;
UPDATE pg_trigger SET tgdeferrable = TRUE, tginitdeferred = TRUE;

INSERT INTO father VALUES(1, 'FATHER-1');

INSERT INTO son VALUES(1,1, 'Son FATHER-1');
INSERT INTO son VALUES(2,1, 'Son FATHER-1');
INSERT INTO son VALUES(3,1, 'Son FATHER-1');

INSERT INTO grandson VALUES(1,1, 'Grandson FATHER-1');
INSERT INTO grandson VALUES(2,2, 'Grandson FATHER-1');
INSERT INTO grandson VALUES(3,3, 'Grandson FATHER-1');

DELETE FROM father WHERE co_father = 1

My LOGS
-----------------------------------------------------------------------------------------------------------

0LOG: 00000: server process (PID 23470) was terminated by signal 11:
Segmentation fault
0LOCATION: LogChildExit, postmaster.c:2529
0LOG: 00000: terminating any other active server processes
0LOCATION: HandleChildCrash, postmaster.c:2374
0FATAL: 57P03: the database system is in recovery mode
0LOCATION: ProcessStartupPacket, postmaster.c:1648
0LOG: 00000: all server processes terminated; reinitializing
0LOCATION: PostmasterStateMachine, postmaster.c:2690
0LOG: 00000: database system was interrupted; last known up at 2009-10-27
11:43:37 BRST
0LOCATION: StartupXLOG, xlog.c:4836
0DEBUG: 00000: checkpoint record is at 1/1C865BD4
0LOCATION: StartupXLOG, xlog.c:4906
0DEBUG: 00000: redo record is at 1/1C865BD4; shutdown TRUE
0LOCATION: StartupXLOG, xlog.c:4932
0DEBUG: 00000: next transaction ID: 0/27113; next OID: 67190
0LOCATION: StartupXLOG, xlog.c:4936
0DEBUG: 00000: next MultiXactId: 1; next MultiXactOffset: 0
0LOCATION: StartupXLOG, xlog.c:4939
0LOG: 00000: database system was not properly shut down; automatic recovery
in progress
0LOCATION: StartupXLOG, xlog.c:5003
0LOG: 00000: record with zero length at 1/1C865C14
0LOCATION: ReadRecord, xlog.c:3126
0LOG: 00000: redo is not required
0LOCATION: StartupXLOG, xlog.c:5146
0DEBUG: 00000: transaction ID wrap limit is 2147484026, limited by database
"template1"
0LOCATION: SetTransactionIdLimit, varsup.c:283
0LOG: 00000: database system is ready to accept connections
0LOCATION: reaper, postmaster.c:2156

-----------------------------------------------------------------------------------------------------------------------------

Has I know, the deletion cascade with three levels don't give this problem.
The same think occourr if I change the pg_trigger comportment to postpone
validation. This is right?

Sorry, but what wrap limit do ?

Thanks in advanced.

Sincerely,
--
Marcelo Costa
<http://www.marcelocosta.net>
-------------------------------------------------
“You can't always get what want”,

Doctor House in apology to Mike Jagger