Thread: Help: a rare problem with backing and restoring

 Hi all.

 I am desesperated, because I want to incorporate old data in a new
 database schema with referential integrity rules.

 In order to get this I do:

     pg_dump -aD -f data.sql database

 Then i have a file with a collection of inserts. Drop old database
 and make new. Both are equal, except in referential integrity rules.

 And next... the problem.

 Look at this tables:

----------------------------------------------
create table aviso (
    número        serial primary key,
    fecha        date default CURRENT_DATE,
    hora        time default CURRENT_TIME,
    usuario        int4 references empleado(cod),
    procedencia    int4 references procedencia,
    línea        int4 references línea,
    empresa        int4 references empresa,
    urgente        bool    default 'f',
    estado        int4 references estado,
    fecha_terminado    date,
    orden        int4,
    detalle        text
);
create index avi_fecha_ndx on aviso (fecha);
create index avi_hora_ndx on aviso (hora);
create index avi_usuario_ndx on aviso (usuario);
create index avi_procedencia_ndx on aviso (procedencia);
create index avi_linea_ndx on aviso (línea);
create index avi_empresa_ndx on aviso (empresa);
create index avi_urgente_ndx on aviso (urgente);
create unique index avi_orden_ndx on aviso (orden);
create index avi_estado_ndx on aviso (estado);
create index avi_fecha_aceptado_ndx on aviso (fecha_aceptado);
create index avi_fecha_terminado_ndx on aviso (fecha_terminado);

-----------------------------------
create table técnico_aviso (
    cod        serial primary key,
    aviso        int4 not null references aviso(número) on delete cascade,
    empleado    int4 not null references empleado(cod),
    fecha        date not null,
    hora        time not null,
    duración    interval,
    comentario    text
);
create unique index tec_aviso_unico_ndx on técnico_aviso (aviso, empleado, fecha, hora);
create index tec_avi_hora_ndx on técnico_aviso (hora);
create index tec_avi_empleado_ndx on técnico_aviso (empleado);
create index tec_avi_fecha_ndx on técnico_aviso (fecha);

---------------------------------

 A little example of all my database.

 técnico_aviso(aviso) references aviso(número). In file data.sql I
 have lines of type:

     INSERT INTO "técnico_aviso"
    ("cod","aviso","empleado","fecha","hora","duración","comentario")
    VALUES (1,0,17,'20/09/2000','17:00:00',NULL,NULL);

    INSERT INTO "técnico_aviso"
    ("cod","aviso","empleado","fecha","hora","duración","comentario")
    VALUES (2,5,8,'05/07/1999','17:00:00',NULL,NULL);

    INSERT INTO "técnico_aviso"
    ("cod","aviso","empleado","fecha","hora","duración","comentario")
    VALUES (3,6,16,'29/06/1999','20:34:00',NULL,NULL);

 First line is illegal for referential integrity because of
 técnico_aviso[aviso] = 0 (aviso[número] = 0 doesn't exist),
 but the other two are perfectly legal. If I insert then from psql, I
 get no error. But when I execute data.sql script none of them are
 inserted in técnico_aviso...

 I don't understand this situation. What can be the problem?. It have
 no sense and I am going crazy 8P

 Please, a light?... I use Postgresql 7.2.1 in Debian unestable (Sid).

 Thanks.

                                 David

On Thu, 2002-04-18 at 13:50, bombadil@wanadoo.es wrote:
...
>  First line is illegal for referential integrity because of
>  tcnico_aviso[aviso] = 0 (aviso[nmero] = 0 doesn't exist),
>  but the other two are perfectly legal. If I insert then from psql, I
>  get no error. But when I execute data.sql script none of them are
>  inserted in tcnico_aviso...
>
>  I don't understand this situation. What can be the problem?. It have
>  no sense and I am going crazy

You need to show the script.  All I can think of is that your script is
including the illegal insert inside a transaction; if one command in a
transaction fails, the whole transaction is rolled back, so none of the
inserts would be done.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "For by grace are ye saved through faith; and that not
      of yourselves; it is the gift of God, not of works,
      lest any man should boast."   Ephesians 2:8,9