I have found some kind of problems with a rule I have on one of my databases, 
and after some mailling with Tom, and re-checking my logs I find out that the 
inserts look like the are getting (if I look at the logs) but the data is not 
there!

This is the RULE:

CREATE RULE admin_insert AS ON 
INSERT TO admin_view
DO INSTEAD (
   INSERT INTO carrera 
	  (carrera,titulo,area,descripcion,incumbencia,director,
	  matricula,cupos,informes,nivel,requisitos,duracion,
	  categoria)
   VALUES 
	  (new.carrera,new.titulo,new.id_subarea,new.descripcion,
	  new.incumbencia,new.director,new.matricula,new.cupos,
	  new.informes,new.nivel,new.requisitos,new.duracion,
	  new.car_categ);

   INSERT INTO inscripcion
	  (carrera,fecha_ini,fecha_fin,lugar)
   VALUES
	  (currval('carrera_id_curso_seq'),new.fecha_ini,new.fecha_fin,
	  new.lugar);

   INSERT INTO resol
	  (carr,numero,year,fecha)
   VALUES
	  (currval('carrera_id_curso_seq'),new.numero,new.year,new.fecha);

   INSERT INTO log_carrera (accion,tabla,id_col) VALUES 
	  ('I','carrera',currval('carrera_id_curso_seq'));
);

As you can see, there is an insert to a log (log_carrera) table for each 
insert to the view. 
On the other hand, all inserts are done throught the view, so for each value 
of the column id_curso of carrera (see the sequence of the other 3 inserts) 
there should be a value in inscripcion.carrera, resol.carr and 
log_carrera.id_col. This is not true for inscripcion:

webunl=> select count(id_curso) from carrera where id_curso NOT IN
webunl-> (select carrera from inscripcion);
 count
-------
    38
(1 row)
 
webunl=>

And if I check the log_carrera table for some of the values found by the last 
query (obviously not the count but the id_curso):

webunl=> select * from log_carrera where id_col IN
webunl-> (87,88,90,92) AND tabla='carrera';
 id_log | usuario |           horario            | accion |  tabla  | id_col
--------+---------+------------------------------+--------+---------+--------
    259 | mariana | Mon 24 Sep 20:21:42 2001 GMT | I      | carrera |     87
    262 | mariana | Mon 24 Sep 20:36:26 2001 GMT | I      | carrera |     88
    269 | mariana | Mon 24 Sep 21:37:25 2001 GMT | I      | carrera |     90
    275 | mariana | Mon 24 Sep 21:53:38 2001 GMT | I      | carrera |     92
(4 rows)
 
webunl=>

In this case 92 is the only one of all four that is OK. The other 3 didn't 
make the inscripcion insert.

And the most courious thing i what the logs say:

2001-09-24 17:21:42 DEBUG:  StartTransactionCommand
2001-09-24 17:21:42 DEBUG:  query: INSERT INTO admin_view 
(titulo,id_subarea,descripcion,nivel,requisitos,duracion,numero,year,fecha,fecha_ini,fecha_fin,lugar,informes 
,carrera,director) VALUES ('Especialista y Magister en Gesti<F3>n 
Urbana',1,'Fase 1 Especializaci<F3>n: 3 m<F3>dulos; Introductorio; Contenidos 
Espec
<ED>ficos; Problem<E1>ticas particularizadas. Fase 2 Maestr<ED>a: Se 
profundizar<E1> en aspectos vinculados al desarrollo de la de la Tesis del 
Magister.',4,'Dr. Homero 
Rondina',12,93,2001,'24/9/2001','01/02/2002','30/03/2002','Facultad de 
Arquitectura, Dise<F1>o y Urbanismo.','1','Especializaci<F3>n y Maestr<ED>a 
en Gesti<F3>n Urbana, municipal y Comunal','Ser graduado Universitario' )
2001-09-24 17:21:42 DEBUG:  ProcessQuery
INSERT @ 0/17879624: prev 0/17879584; xprev 0/0; xid 36118: Heap - insert: 
node 102203/102530; tid 19/1
INSERT @ 0/17880096: prev 0/17879624; xprev 0/17879624; xid 36118: Btree - 
insert: node 102203/102573; tid 1/87
INSERT @ 0/17880160: prev 0/17880096; xprev 0/17880096; xid 36118: Btree - 
insert: node 102203/102600; tid 1/1
INSERT @ 0/17880224: prev 0/17880160; xprev 0/17880160; xid 36118: Btree - 
insert: node 102203/102603; tid 1/86
INSERT @ 0/17880288: prev 0/17880224; xprev 0/17880224; xid 36118: Btree - 
insert: node 102203/102606; tid 1/1
2001-09-24 17:21:42 DEBUG:  ProcessQuery
INSERT @ 0/17880352: prev 0/17880288; xprev 0/17880288; xid 36118: Heap - 
insert: node 102203/102724; tid 1/22
INSERT @ 0/17880472: prev 0/17880352; xprev 0/17880352; xid 36118: Btree - 
insert: node 102203/102743; tid 1/87
2001-09-24 17:21:42 DEBUG:  ProcessQuery
INSERT @ 0/17880536: prev 0/17880472; xprev 0/17880472; xid 36118: Heap - 
insert: node 102203/102635; tid 0/87
INSERT @ 0/17880608: prev 0/17880536; xprev 0/17880536; xid 36118: Btree - 
insert: node 102203/102654; tid 1/87
2001-09-24 17:21:42 DEBUG:  ProcessQuery
INSERT @ 0/17880672: prev 0/17880608; xprev 0/17880608; xid 36118: Heap - 
insert: node 102203/102902; tid 2/71
INSERT @ 0/17880776: prev 0/17880672; xprev 0/17880672; xid 36118: Btree - 
insert: node 102203/102922; tid 1/259
2001-09-24 17:21:42 DEBUG:  CommitTransactionCommand

Looks like the 4 inserts went OK, but I can't understand why they didn't get 
to the database?

webunl=> select version();
                             version
------------------------------------------------------------------
 PostgreSQL 7.1.3 on sparc-sun-solaris2.7, compiled by GCC 2.95.2


TIA!


-- 
Porquï¿½ usar una base de datos relacional cualquiera,
si podï¿½s usar PostgreSQL?
-----------------------------------------------------------------
Martï¿½n Marquï¿½s                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------

Fri, 5 Oct 2001 17:59:51 -0300
=?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= <martin@bugs.unl.edu.ar>
Rules and missing inserts