Re: Rows missing from table despite FK constraint

Le 07/01/2010 11:12, Konrad Garus a écrit :
> Hello,
>
> We use PG 8.3. We use pg_dump and pg_restore overnight to create
> copies of main database for reporting etc. One dump/restore runs at 9
> PM, another at 11 PM.
>
> Today I discovered that the restore at 11 PM failed to recreate a
> foreign key constraint, because one row from master table was missing.
> It is also missing from main database, but not from the 9 PM dump.
>
> The main database is in curious state: The row from master table is
> missing, the row referencing it from slave table is present, and
> finally the FK constraint on slave is in place.
>
> Do you have any ideas on how it could possibly happen?

Disabling trigger does this. Here is a quick (but long) example:

guillaume(at)laptop:~$ createdb b1

guillaume(at)laptop:~$ LANG=C psql b1
psql (8.5devel)
Type "help" for help.

b1=# create table t1 (c1 integer primary key, c2 integer);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey"
for table "t1"
CREATE TABLE

b1=# create table t2 (c1 integer, c1_t1 integer references t1(c1));
CREATE TABLE

b1=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer | not null
c2 | integer |
Indexes:
"t1_pkey" PRIMARY KEY, btree (c1)
Referenced by:
TABLE "t2" CONSTRAINT "t2_c1_t1_fkey" FOREIGN KEY (c1_t1) REFERENCES
t1(c1)

b1=# \d t2
Table "public.t2"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
c1_t1 | integer |
Foreign-key constraints:
"t2_c1_t1_fkey" FOREIGN KEY (c1_t1) REFERENCES t1(c1)

b1=# insert into t1 values (1, 100);
INSERT 0 1

b1=# insert into t1 values (2, 200);
INSERT 0 1

b1=# insert into t2 values (1, 1);
INSERT 0 1

b1=# insert into t2 values (1, 2);
INSERT 0 1

b1=# insert into t2 values (1, 3);
ERROR: insert or update on table "t2" violates foreign key constraint
"t2_c1_t1_fkey"
DÉTAIL : Key (c1_t1)=(3) is not present in table "t1".

Which is right. Now, we disable triggers:

b1=# alter table t2 disable trigger all;
ALTER TABLE
b1=# insert into t2 values (1, 3);
INSERT 0 1

The INSERT now works. The FK is not checked.

b1=# alter table t2 enable trigger all;
ALTER TABLE

Reenabling triggers won't alert you. Rows are inserted and will stay
that way.

b1=# select * from t1;
c1 | c2
----+-----
1 | 100
2 | 200
(2 lines)

b1=# select * from t2;
c1 | c1_t1
----+-------
1 | 1
1 | 2
1 | 3
(3 lines)

Despite my examples are on 8.5dev, you have the same issue with 8.3. See
http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html for
more details.

So, question is: did you disable triggers sometime on the referenced table?

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com