Troubleshooting
Problem
How do I update a table's distribution column?
Symptom
If one attempts to perform a SQL Update on a table's distribution column, the following error is generated:
"ERROR: Attempt to UPDATE a distribution column"
Diagnosing The Problem
-- create new table with a new distribution key -- alter the privileges on the new table to be the same as the old table
A suggested workaround would be based upon the size of the table you are redistributing and the amount of space available on the SPUs. For example, if you have a relatively small table, with ample space on the SPUs the approach to use would be as follows:
1. Copy the original table to a new table with the revised distribution
2. Copy the privileges set on the original table to the newly created table
3. Generate statistics on the newly created table
4. Rename (or drop) the original table
5. Rename the newly created table to replace the original table
For example
-- Review current distribution key of original table
MYDATABASE(ADMIN)=> \d table1
Table "TABLE1"
Attribute | Type | Modifier | Default Value
-----------+---------+----------+---------------
INT1 | INTEGER | |
INT2 | INTEGER | |
Distributed on hash: "INT1"
MYDATABASE(ADMIN)=> create table table2 as select * from table1 distribute on ( int2 );
INSERT 0 0
-- review the distribution key of the new table
MYDATABASE(ADMIN)=> \d table2
Table "TABLE2"
Attribute | Type | Modifier | Default Value
-----------+---------+----------+---------------
INT1 | INTEGER | |
INT2 | INTEGER | |
Distributed on hash: "INT2"
MYDATABASE(ADMIN)=> alter table table2 set privileges to table1;
ALTER TABLE
-- generate statistics on the newly created table
MYDATABASE(ADMIN)=> generate statistics on table2;
GENERATE STATISTICS
-- rename the old table
MYDATABASE(ADMIN)=> alter table table1 rename to table1_original;
ALTER TABLE
-- rename the new table to replace the original
MYDATABASE(ADMIN)=> alter table table2 rename to table1;
ALTER TABLE
The above approach would require you to have free space on the SPUs equal to the size of the original table. If you did not have sufficient space on the SPUs, the approach would be similar but would required you to unload the data to a flat file on the NPS server first, drop the original table, and then reload the table.
Historical Number
NZ345569
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21573042