Error in ALTER TABLE when data left on coordinator
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
If we create a table with a nullable column, insert and then distribute. Changing the NOT NULL fails even after updating the columns.
```-- Cleanup from previous runs, if any.
--
DROP TABLE IF EXISTS test_coord;
DROP TABLE IF EXISTS test_dist;
DROP TABLE IF EXISTS test_ref;
-- Setup coordinator, distributed, and reference tables each with NULL
-- in some cells.
--
CREATE TABLE test_dist (id bigserial PRIMARY KEY, foo integer );
CREATE TABLE test_ref (id bigserial PRIMARY KEY, foo integer );
INSERT INTO test_dist (foo) VALUES (1),(NULL),(3),(NULL),(5);
INSERT INTO test_ref (foo) VALUES (1),(NULL),(3),(NULL),(5);
SELECT create_distributed_table('test_dist','id');
SELECT create_reference_table( 'test_ref');
-- Update the tables so there are no more NULL cells.
--
UPDATE test_dist SET foo = 0 WHERE foo IS NULL;
UPDATE test_ref SET foo = 0 WHERE foo IS NULL;
SELECT count(*) FROM test_coord WHERE foo IS NULL; -- reports count 0
SELECT count(*) FROM test_dist WHERE foo IS NULL; -- reports count 0
SELECT count(*) FROM test_ref WHERE foo IS NULL; -- reports count 0
-- This ALTER TABLE on a distributed table fails with:
--
-- ERROR: column "foo" contains null values
--
ALTER TABLE test_dist ALTER COLUMN foo SET NOT NULL;
-- This ALTER TABLE on a reference table fails with:
--
-- ERROR: column "foo" contains null values
--
ALTER TABLE test_ref ALTER COLUMN foo SET NOT NULL;
```
It looks like it comes from the fact that there is data on the coordinator not updated. As if we change the order, the error isn't raised.
```
CREATE TABLE test_coord (id bigserial PRIMARY KEY, foo integer );
CREATE TABLE test_dist (id bigserial PRIMARY KEY, foo integer );
CREATE TABLE test_ref (id bigserial PRIMARY KEY, foo integer );
SELECT create_distributed_table('test_dist','id');
SELECT create_reference_table( 'test_ref');
INSERT INTO test_coord (foo) VALUES (1),(NULL),(3),(NULL),(5);
INSERT INTO test_dist (foo) VALUES (1),(NULL),(3),(NULL),(5);
INSERT INTO test_ref (foo) VALUES (1),(NULL),(3),(NULL),(5);
UPDATE test_coord SET foo = 0 WHERE foo IS NULL;
UPDATE test_dist SET foo = 0 WHERE foo IS NULL;
UPDATE test_ref SET foo = 0 WHERE foo IS NULL;
ALTER TABLE test_dist ALTER COLUMN foo SET NOT NULL;
ALTER TABLE test_ref ALTER COLUMN foo SET NOT NULL;
```
Right now we gave a workaround of doing
```
SET citus.enable_ddl_propagation to off; -- this will disable the propagation to the workers
TRUNCATE test_dist ;
SET citus.enable_ddl_propagation to on;
SELECT * FROM test_dist; -- data is in the workers
ALTER TABLE test_dist ALTER COLUMN foo SET NOT NULL;
```
Which is obviously terrifying for users.
Contributor guide
Assessment
This issue has not been assessed yet.