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é
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 |