Re: Rule ON DELETE, to perform to DELETE querys ! - Mailing list pgsql-sql

On Vie 08 Jun 2001 18:28, you wrote:
> I have a view over a join of tables and when it's performed over the
> view a delete i want to delete records in two different tables. The code
> that i wrote was:
>
>     CREATE RULE "deletetables" AS ON DELETE TO "tables"
>          DO INSTEAD (
>          DELETE FROM table2
>                 WHERE id = OLD.id;
>          DELETE FROM table1
>                 WHERE id=OLD.id
>            );

Remember that name is the identifier of the column in old.<name>. You can't 
have two id.

> table2 references table1 by the field id only for update. I don't them
> to be referenced by delete !

The references are there for everything! Not only for updates, or select, etc.

What is the structure of those tables?

> When i execute: DELETE FROM tables WHERE id=1; i got these message from
> postgres:
>
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>
> The records exists in both tables !!!!!!!
>
> This already happen with some of you ???? How can i do this ????

Did you check the SQL log file?
Ok, it happened to me also, and this is what I got from the log:


2001-06-08 16:28:05 DEBUG:  StartTransactionCommand
2001-06-08 16:28:05 DEBUG:  query: DELETE FROM tabla WHERE id=1;
2001-06-08 16:28:05 DEBUG:  ProcessQuery
INSERT @ 0/50216360: prev 0/50216320; xprev 0/0; xid 14917; bkpb 1: Heap - 
delete: node 19416/240357; tid 0/1
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 20666 exited with status 138
Server process (pid 20666) exited with status 138 at Fri Jun  8 16:28:06 2001
Terminating any active server processes...
Server processes were terminated at Fri Jun  8 16:28:06 2001
Reinitializing shared memory and semaphores
invoking IpcMemoryCreate(size=1245184)
2001-06-08 16:28:06 DEBUG:  database system was interrupted at 2001-06-08 
16:26:02 GMT
2001-06-08 16:28:06 DEBUG:  CheckPoint record at (0, 50165256)
2001-06-08 16:28:06 DEBUG:  Redo record at (0, 50165256); Undo record at (0, 
0); Shutdown TRUE
2001-06-08 16:28:06 DEBUG:  NextTransactionId: 14913; NextOid: 247500
2001-06-08 16:28:06 DEBUG:  database system was not properly shut down; 
automatic recovery in progress...
2001-06-08 16:28:06 DEBUG:  redo starts at (0, 50165320)
REDO @ 0/50165320; LSN 0/50173600: prev 0/50165256; xprev 0/0; xid 14915; 
bkpb 1: Heap - delete: node 19416/17058; tid 1/1
REDO @ 0/50173600; LSN 0/50181888: prev 0/50165320; xprev 0/50165320; xid 
14915; bkpb 1: Heap - update: node 19416/1259; tid 1/60; new 1/61
REDO @ 0/50181888; LSN 0/50190168: prev 0/50173600; xprev 0/50173600; xid 
14915; bkpb 1: Btree - insert: node 19416/17121; tid 1/111
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading 6
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading 6
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading 6
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling reading 6
The Data Base System is starting up
/usr/local/pgsql/bin/postmaster: ServerLoop:            handling writing 6
REDO @ 0/50190168; LSN 0/50198448: prev 0/50181888; xprev 0/50181888; xid 
14915; bkpb 1: Btree - insert: node 19416/17118; tid 1/122
REDO @ 0/50198448; LSN 0/50198488: prev 0/50190168; xprev 0/50190168; xid 
14915: Transaction - commit: 2001-06-08 16:26:59
REDO @ 0/50198488; LSN 0/50198528: prev 0/50198448; xprev 0/0; xid 14916: 
XLOG - nextOid: 255692
REDO @ 0/50198528; LSN 0/50199456: prev 0/50198488; xprev 0/0; xid 14916: 
Heap - insert: node 19416/17058; tid 1/2
REDO @ 0/50199456; LSN 0/50207736: prev 0/50198528; xprev 0/50198528; xid 
14916; bkpb 1: Btree - insert: node 19416/17175; tid 1/8
REDO @ 0/50207736; LSN 0/50216016: prev 0/50199456; xprev 0/50199456; xid 
14916; bkpb 1: Btree - insert: node 19416/17178; tid 1/7
REDO @ 0/50216016; LSN 0/50216168: prev 0/50207736; xprev 0/50207736; xid 
14916: Heap - update: node 19416/1259; tid 1/61; new 1/62
REDO @ 0/50216168; LSN 0/50216256: prev 0/50216016; xprev 0/50216016; xid 
14916: Btree - insert: node 19416/17121; tid 1/111
REDO @ 0/50216256; LSN 0/50216320: prev 0/50216168; xprev 0/50216168; xid 
14916: Btree - insert: node 19416/17118; tid 1/122
REDO @ 0/50216320; LSN 0/50216360: prev 0/50216256; xprev 0/50216256; xid 
14916: Transaction - commit: 2001-06-08 16:27:34
2001-06-08 16:28:06 DEBUG:  ReadRecord: record with zero len at (0, 50216360)
2001-06-08 16:28:06 DEBUG:  redo done at (0, 50216320)
XLogFlush: rqst 0/50199456; wrt 0/50216360; flsh 0/50216360
XLogFlush: rqst 0/50216168; wrt 0/50216360; flsh 0/50216360
XLogFlush: rqst 0/50216256; wrt 0/50216360; flsh 0/50216360
XLogFlush: rqst 0/50216320; wrt 0/50216360; flsh 0/50216360
XLogFlush: rqst 0/0; wrt 0/50216360; flsh 0/50216360
XLogFlush: rqst 0/50207736; wrt 0/50216360; flsh 0/50216360
XLogFlush: rqst 0/50216016; wrt 0/50216360; flsh 0/50216360
INSERT @ 0/50216360: prev 0/50216320; xprev 0/0; xid 0: XLOG - checkpoint: 
redo 0/50216360; undo 0/0; sui 36; xid 14917; oid 255692; shutdown
XLogFlush: rqst 0/50216424; wrt 0/50216360; flsh 0/50216360
2001-06-08 16:28:09 DEBUG:  database system is in production state
2001-06-08 16:28:09 DEBUG:  proc_exit(0)
2001-06-08 16:28:09 DEBUG:  shmem_exit(0)
2001-06-08 16:28:09 DEBUG:  exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...


This is strange. The database server stopped and then started again. Now why 
did it stop?

Good question. :-)

-- 
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica                      Universidad Nacional
        del Litoral
 
-----------------------------------------------------------------