Re: Rule ON DELETE, to perform to DELETE querys !

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(at)unl(dot)edu(dot)ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------