Обсуждение: Rules and missing inserts

Поиск
Список
Период
Сортировка

Rules and missing inserts

От
Martín Marqués
Дата:
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
-----------------------------------------------------------------