Hi everybody,

We are experiencing the following problem and we would like to solicit your
help/guidance in our effort to solve it:

Sometimes when we attempt to restore our database gbak gives the following
error:

gbak: ERROR: attempt to store duplicate value (visible to active
transactions)
in unique index "RDB$PRIMARY177"
gbak: Index "RDB$PRIMARY177" failed to activate because:
gbak: The unique index has duplicate values or NULLs.
gbak: Delete or Update duplicate values or NULLs, and activate index with
gbak: ALTER INDEX "RDB$PRIMARY177" ACTIVE;
action cancelled by trigger (3) to preserve data integrity
-Cannot deactivate primary index

The error has happenned in many of our installations, where we use use IB
5.6 and a Windows OS (NT, 98, 2000, or XP). The error is very difficult to
track down because we are not able to reproduce it on purpose and it may
take days, weeks or months to happen again. After some effort we are always
able to get the database back online but we do not understand why the error
happens in the first place, i.e. why there is a duplicate value in the
primary index. It makes us very nervous because we never know if we can rely
on our backups. The index and table related to the error are always the same
ones and we have never removed the primary key constraint.

We have identified two posible causes of this error: the first one has to do
with the fact there is a date field included in the primary key of the
table; the second one has to do with an updatable view defined on the table.

Here is the definition of the table:

CREATE TABLE ART_ARTICULOS_HIST_PRECIOS (EMPRESA EMPRESAS,
CANAL CANALES,
ALMACEN ALMACENES,
FECHA FECHA_HOY,
ARTICULO ARTICULOS,
TIPO TIPOS_OPERACIONES,
N_MOV_STOCK CONTADORES,
UNIDADES CANTIDADES,
EXISTENCIAS CANTIDADES,
PMP CANTIDADES,
P_COSTE CANTIDADES,
P_VENTA CANTIDADES,
CONSTRAINT PK_ART_ARTICULOS_HIST_PRECIOS PRIMARY KEY (EMPRESA, CANAL,
ALMACEN, ARTICULO, FECHA, TIPO));

where the definitions of the domains of the primary key fields are

CREATE DOMAIN EMPRESAS AS SMALLINT
default 0
check (value>=0) NOT NULL;
CREATE DOMAIN CANALES AS SMALLINT
default 0
check (value >=0) NOT NULL;
CREATE DOMAIN ALMACENES AS CHAR(3)
default '000'
check (value=upper(value) and value not containing ' ') NOT NULL;
CREATE DOMAIN ARTICULOS AS VARCHAR(15)
default ''
check (value=upper(value)) NOT NULL;
CREATE DOMAIN FECHA_HOY AS DATE
default 'Today'
NOT NULL;
CREATE DOMAIN TIPOS_OPERACIONES AS CHAR(1)
default ''
check (value=upper(value)) NOT NULL;

As you can see there is date field included in the primary key. Some of us
are wondering (however stupidly) if this may be the cause of the problem,
sort of like double precision fields and their intrinsic inexactitude.
However, we are not sure of this.

Another probable cause is that the table has an updatable view based on it.
The view is defined as follows:

CREATE VIEW VER_ARTICULOS_HIST_PRECIOS (EMPRESA, CANAL, ALMACEN, FECHA,
ARTICULO, TIPO, N_MOV_STOCK, UNIDADES, EXISTENCIAS, PMP, P_COSTE, P_VENTA)
AS
select pre.empresa, pre.canal, pre.almacen, pre.fecha, pre.articulo,
pre.tipo, pre.n_mov_stock,
pre.unidades, pre.existencias, pre.pmp, pre.p_coste,pre.p_venta
from art_articulos_hist_precios pre

However, there is also a after update trigger on the view, in which the
table art_articulos_hist_precios is again updated. We realize that this
setup is a no-no and we have change it already, but we are left wondering if
this could cause the refernetial integrity problems we are having.

Anyway, thanks for reading this post to the end and we welcome any help or
guidance you can give us on this issue.

Thanks,

Javier Castro
El Priorato Marin, S.L.
priorato@...