Re: transaction safety

On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote:
> DaVinci <bombadil@wanadoo.es> writes:
> >     procedencia    int2 references procedencia,
> >     l�nea        int2 references l�nea,
> >     empresa        int2 references empresa,
>
> Depending on the data values you are working with, it could be that the
> foreign key references cause interlocks --- ie, one transaction has to
> wait to see if the other commits to know if it has a valid foreign key.
>
> However, you still have not shown us a complete example that would allow
> anyone else to reproduce your results.

 Good. Here is a script for psql that creates database:

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

------------------------------------------------------------------
-- David Espada 2000.
--
------------------------------------------------------------------

create database example;

\connect example

---------------------------
create table empresa (
    cod        serial primary key,
    descripcion    text
);

---------------------------
create table procedencia (
    cod        serial primary key,
    descripcion    text
);

---------------------------
create table calle (
    cod        serial primary key,
    nombre        text,
    v�a            text
);

---------------------------
create table provincia (
    cod        serial primary key,
    nombre        text
);

---------------------------
create table localidad (
    cod        serial primary key,
    nombre        text
);

---------------------------
create table l�nea (
    cod        serial primary key,
    nombre        text
);

---------------------------
create table forma_pago (
    cod        serial primary key,
    descripcion    text
);


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

-----------------------------
create table aviso (
    n�mero        serial primary key,
    fecha        timestamp default now(),
    procedencia    int2 references procedencia,
    l�nea        int2 references l�nea,
    empresa        int2 references empresa,
    urgente        bool    default 'f',
    externo        bool    default 'f',
    aceptado    bool,            -- El valor nulo implica 'pendiente'
    tmr        bool    default 'f',    -- Trabajo Mal Realizado
    detalle        text
);
create index avi_fecha_ndx on aviso (fecha);
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 index avi_aceptado_ndx on aviso (aceptado);
create index avi_tmr_ndx on aviso (tmr);
create index avi_externo_ndx on aviso (externo);

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

 With this, you only have to insert values in "procedencia", "l�nea" and
 "empresa" to satisfy referencial integrity and make experiment proposed in
 the other message with two psql:

 # insert into aviso(procedencia,l�nea,empresa,detalle) values
   (1,1,1,'Example');

 Greets.

                                                         David