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é
