citusdata / citusdata/citus

Error in ALTER TABLE when data left on coordinator

Open
#2,889 2 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.