-- **** One-off set-up ****
/*
-- DROP TABLE IF EXISTS base_timeslice CASCADE;

CREATE TABLE base_timeslice
(
  timeslice_id integer NOT NULL,
  feature_id integer NOT NULL,
  valid_time_begin timestamp NOT NULL,
  interpretation text NOT NULL,
  sequence_number integer,
  CONSTRAINT pk_base_timeslice PRIMARY KEY (timeslice_id)
);

CREATE TABLE derived_timeslice
(
  timeslice_id integer NOT NULL,
  feature_id integer NOT NULL,
  name text NOT NULL,
  CONSTRAINT pk_derived_timeslice PRIMARY KEY (timeslice_id),
  CONSTRAINT uq_derived_timeslice_dup_time_ex EXCLUDE
    USING btree (feature_id WITH =, valid_time_begin WITH =, interpretation
WITH =, (COALESCE(sequence_number::integer, (-1))) WITH =)
    DEFERRABLE INITIALLY DEFERRED
)
INHERITS (base_timeslice);

INSERT INTO derived_timeslice (timeslice_id, feature_id, valid_time_begin,
interpretation, name)
VALUES (51, 1, '2015-01-01', 'X', 'Test');
*/

-- **** Repro ****

BEGIN;

-- Insert row that violates deferred constraint
INSERT INTO derived_timeslice (timeslice_id, feature_id, valid_time_begin,
interpretation, name)
VALUES (52, 1, '2015-01-01', 'X', 'Test');

-- Delete the old row - now there should be no more conflict
DELETE FROM derived_timeslice WHERE timeslice_id = 51;

-- Problem doesn't occur without an UPDATE statement
UPDATE derived_timeslice SET name = 'Updated' WHERE timeslice_id = 52;

-- This confirms there is only 1 row - no conflict
SELECT timeslice_id, valid_time_begin FROM derived_timeslice WHERE
feature_id = 1;

--COMMIT;
SET CONSTRAINTS ALL IMMEDIATE; -- Enfore constraint - error occurs here

ROLLBACK;