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  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Re: Deferred foreign key and two updates block ALTER TABLE  (David Rowley <david.rowley@2ndquadrant.com>)
Список 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 по дате отправления:

Предыдущее
От: Alexander Lakhin
Дата:
Сообщение: Re: BUG #15599: Segfault at MakeExpandedObjectReadOnlyInternal
Следующее
От: Andrew Gierth
Дата:
Сообщение: Re: Deferred foreign key and two updates block ALTER TABLE