BUG #13572: Foreign Key Corruption

From: herbertsilver(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13572: Foreign Key Corruption
Date: 2015-08-14 16:39:19
Message-ID: 20150814163919.2606.38687@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13572
Logged by: Herbert Silver
Email address: herbertsilver(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2015-08-14 21:07:42 Re: BUG #13572: Foreign Key Corruption
Previous Message Tom Lane 2015-08-14 14:23:10 Re: [BUGS] Re: BUG #13541: There is a visibility issue when run some DDL and Query. The time window is very shot