Deferred foreign key and two updates block ALTER TABLE
От | André Hänsel |
---|---|
Тема | Deferred foreign key and two updates block ALTER TABLE |
Дата | |
Msg-id | 04ab01d4b128$fa63f510$ef2bdf30$@webkr.de обсуждение исходный текст |
Ответы |
Re: Deferred foreign key and two updates block ALTER TABLE
Re: Deferred foreign key and two updates block ALTER TABLE |
Список | 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é
В списке pgsql-bugs по дате отправления: