Deferred foreign key and two updates block ALTER TABLE

From: André Hänsel <andre(at)webkr(dot)de>
To: <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Deferred foreign key and two updates block ALTER TABLE
Date: 2019-01-21 01:31:04
Message-ID: 04ab01d4b128$fa63f510$ef2bdf30$@webkr.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi list,

this looks like a bug to me, forgive me if it's expected behavior:

BEGIN;

DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS test_other;

CREATE TABLE test(
some_column integer NOT NULL,
other_id integer NOT NULL
);
CREATE TABLE test_other(
other_id integer NOT NULL
);
ALTER TABLE test_other ADD PRIMARY KEY (other_id);
ALTER TABLE test ADD FOREIGN KEY (other_id) REFERENCES test_other
DEFERRABLE;

INSERT INTO test_other (other_id) VALUES (1);
INSERT INTO test (some_column, other_id) VALUES (0, 1);

COMMIT;

BEGIN;

SET CONSTRAINTS ALL DEFERRED;

UPDATE test SET some_column = 1 WHERE some_column = 0;
UPDATE test SET some_column = 2 WHERE some_column = 1;
ALTER TABLE test ALTER COLUMN some_column TYPE smallint;

COMMIT;

Expected result: Both transactions can be committed without error

Actual result: ERROR: cannot ALTER TABLE "test" because it has pending
trigger events SQL state: 55006

Notes:
- It doesn't matter if the UPDATEs are on the same or different colums that
are altered, there is still an error
- There is no error if there is just one UPDATE instead of two (this makes
me think this might be a bug)
- There is no error if both UPDATEs lead to the same result (when the second
UPDATE is a no-op, like setting some_column = 1 again)
- There is no error if the foreign key is non-deferrable or currently
immediate

Regards,
André

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Gierth 2019-01-21 02:17:17 Re: Deferred foreign key and two updates block ALTER TABLE
Previous Message Alexander Lakhin 2019-01-19 10:57:15 Re: BUG #15599: Segfault at MakeExpandedObjectReadOnlyInternal