Thread: BUG #13572: Foreign Key Corruption

The following bug has been logged on the website:

Bug reference:      13572
Logged by:          Herbert Silver
Email address:      herbertsilver@gmail.com
PostgreSQL version: 9.4.4
Operating system:   Windows 8.1 x64
Description:

Hi there,

I'm having a bug here. I have a main table and another one referencing to
its primary key. I'm being able to delete records from the main table at the
same time postgres keeps the orphaneds rows on the second one.

I belive it's because of a rule I'm using that is causing the bug, although
I think it was not supposed to happen anyway.

Here's a simplified version of my db to reproduce the bug.

CREATE SCHEMA sch_test
  AUTHORIZATION postgres;
  GRANT ALL ON SCHEMA sch_test TO postgres;
  GRANT ALL ON SCHEMA sch_test TO public;


CREATE SEQUENCE sch_test.nume_agen_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;

CREATE TABLE sch_test.tab_agendas
(
  nume_agen INTEGER NOT NULL DEFAULT NEXTVAL('sch_test.nume_agen_seq'),
  stat_agen BOOLEAN NOT NULL DEFAULT TRUE,
  data_agen DATE NOT NULL,
  CONSTRAINT pkey_nume_agen PRIMARY KEY (nume_agen)
);


CREATE TABLE sch_test.tab_horariosagendas
(
  agen_hoag INTEGER NOT NULL DEFAULT CURRVAL('sch_test.nume_agen_seq'), --
FK
  hora_hoag TIME WITHOUT TIME ZONE NOT NULL,
  stat_hoag SMALLINT NOT NULL DEFAULT 1,
  enca_hoag BOOLEAN NOT NULL DEFAULT FALSE,
  obse_hoag VARCHAR(50) NOT NULL DEFAULT '',
  CONSTRAINT pkey_agen_hora_hoag PRIMARY KEY (agen_hoag,hora_hoag),
  CONSTRAINT fk_agen_hoag FOREIGN KEY (agen_hoag)
      REFERENCES sch_test.tab_agendas (nume_agen) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE OR REPLACE RULE rul_remover_horarios_agenda AS
  ON DELETE TO sch_test.tab_horariosagendas
  WHERE OLD.enca_hoag=FALSE
  DO INSTEAD UPDATE sch_test.tab_horariosagendas
  SET stat_hoag=1, obse_hoag=''
  WHERE agen_hoag=OLD.agen_hoag AND hora_hoag=OLD.hora_hoag;

Just add one row to the table tab_agendas, then one row to the
tab_horariosagendas referencing FK to PK, then delete the row of the first
table, there will be an orphaned row. A corrupted relation.

Thanks a lot.

PS: Sorry if my english.

herbertsilver@gmail.com writes:
> I'm having a bug here. I have a main table and another one referencing to
> its primary key. I'm being able to delete records from the main table at the
> same time postgres keeps the orphaneds rows on the second one.

> I belive it's because of a rule I'm using that is causing the bug, although
> I think it was not supposed to happen anyway.

Yup.  Rules act at a level lower than foreign keys, ie a rule can rewrite
the query that is trying to implement ON DELETE CASCADE.  And here you
have an ON DELETE DO INSTEAD rule on the target table.  This is not a bug,
it's just the way those two features work together.

            regards, tom lane