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 | [EMAIL PROTECTED]
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
