Re: transaction safety

On Tue, Feb 13, 2001 at 09:52:21AM -0500, Tom Lane wrote:
> DaVinci <bombadil(at)wanadoo(dot)es> writes:
> > procedencia int2 references procedencia,
> > lnea int2 references lnea,
> > 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,
va text
);

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

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

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

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

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

-----------------------------
create table aviso (
nmero serial primary key,
fecha timestamp default now(),
procedencia int2 references procedencia,
lnea int2 references lnea,
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 (lnea);
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", "lnea" and
"empresa" to satisfy referencial integrity and make experiment proposed in
the other message with two psql:

# insert into aviso(procedencia,lnea,empresa,detalle) values
(1,1,1,'Example');

Greets.

David