postgresqlcitus

CitusDB: modifying the partition value of rows is not allowed

i want to update or delete data on the test database use citus based on postgressql, it note me this info: modifying the partition value of rows is not allowed citus:6.0 postgresql:9.6 how can i udpate or delete data when i use citus?

Solution

  • I think the error message is a bit confusing in this case. It is not allowed to update the distribution key value itself, however, it is allowed to update/delete rows with proving the distribution key value. See the example below:

    CREATE TABLE test_table (key int, value text);

-- distribute the table
SELECT create_distributed_table('test_table', 'key');

-- insert a row
INSERT INTO test_table VALUES (1, 'some test');

-- get the inserted row
SELECT * FROM test_table WHERE key = 1;

-- now, update the row
UPDATE test_table SET value = 'some another text' WHERE key = 1;

-- get the updated row
SELECT * FROM test_table WHERE key = 1;

-- now delete the row
DELETE FROM test_table WHERE key = 1;

-- see that the row is delete
SELECT * FROM test_table WHERE key = 1;

    Now, let me give an example of what is not allowed. It is not allowed to update the distribution key value, given that the row is already placed on a shard based on that value and updating the value may end up with the row not being in that shard anymore.

    -- insert the row again
INSERT INTO test_table VALUES (1, 'some test');

-- now, try to update the distribution key value
UPDATE test_table SET key = 2 WHERE key = 1;
ERROR:  modifying the partition value of rows is not allowed

    Hope this helps.