-- -- MULTI_ALTER_TABLE_STATEMENTS -- CREATE SCHEMA multi_alter_table_statements; SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 220000; -- Check that we can run ALTER TABLE statements on distributed tables. -- We set the shardid sequence here so that the shardids in this test -- aren't affected by changes to the previous tests. CREATE TABLE lineitem_alter ( l_orderkey bigint not null, l_partkey integer not null, l_suppkey integer not null, l_linenumber integer not null, l_quantity decimal(15, 2) not null, l_extendedprice decimal(15, 2) not null, l_discount decimal(15, 2) not null, l_tax decimal(15, 2) not null, l_returnflag char(1) not null, l_linestatus char(1) not null, l_shipdate date not null, l_commitdate date not null, l_receiptdate date not null, l_shipinstruct char(25) not null, l_shipmode char(10) not null, l_comment varchar(44) not null ) WITH ( fillfactor = 80 ); SELECT create_distributed_table('lineitem_alter', 'l_orderkey', 'append'); create_distributed_table --------------------------------------------------------------------- (1 row) SELECT master_create_empty_shard('lineitem_alter') AS shardid \gset \set lineitem_1_data_file :abs_srcdir '/data/lineitem.1.data' copy lineitem_alter FROM :'lineitem_1_data_file' with (delimiter '|', append_to_shard :shardid); -- verify that the storage options made it to the table definitions SELECT relname, reloptions FROM pg_class WHERE relname = 'lineitem_alter'; relname | reloptions --------------------------------------------------------------------- lineitem_alter | {fillfactor=80} (1 row) \c - - - :worker_1_port SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'lineitem_alter%' ORDER BY relname; relname | reloptions --------------------------------------------------------------------- lineitem_alter_220000 | {fillfactor=80} (1 row) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 221000; -- Verify that we can add columns ALTER TABLE lineitem_alter ADD COLUMN float_column FLOAT; ALTER TABLE lineitem_alter ADD COLUMN date_column DATE; ALTER TABLE lineitem_alter ADD COLUMN int_column1 INTEGER DEFAULT 1; ALTER TABLE lineitem_alter ADD COLUMN int_column2 INTEGER DEFAULT 2; ALTER TABLE lineitem_alter ADD COLUMN null_column INTEGER; -- show changed schema on one worker \c - - - :worker_1_port SELECT attname, atttypid::regtype FROM (SELECT oid FROM pg_class WHERE relname LIKE 'lineitem_alter_%' ORDER BY relname LIMIT 1) pc JOIN pg_attribute ON (pc.oid = pg_attribute.attrelid) ORDER BY attnum; attname | atttypid --------------------------------------------------------------------- tableoid | oid cmax | cid xmax | xid cmin | cid xmin | xid ctid | tid l_orderkey | bigint l_partkey | integer l_suppkey | integer l_linenumber | integer l_quantity | numeric l_extendedprice | numeric l_discount | numeric l_tax | numeric l_returnflag | character l_linestatus | character l_shipdate | date l_commitdate | date l_receiptdate | date l_shipinstruct | character l_shipmode | character l_comment | character varying float_column | double precision date_column | date int_column1 | integer int_column2 | integer null_column | integer (27 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 222000; SELECT "Column", "Type", "Modifiers" FROM table_desc WHERE relid='lineitem_alter'::regclass; Column | Type | Modifiers --------------------------------------------------------------------- l_orderkey | bigint | not null l_partkey | integer | not null l_suppkey | integer | not null l_linenumber | integer | not null l_quantity | numeric(15,2) | not null l_extendedprice | numeric(15,2) | not null l_discount | numeric(15,2) | not null l_tax | numeric(15,2) | not null l_returnflag | character(1) | not null l_linestatus | character(1) | not null l_shipdate | date | not null l_commitdate | date | not null l_receiptdate | date | not null l_shipinstruct | character(25) | not null l_shipmode | character(10) | not null l_comment | character varying(44) | not null float_column | double precision | date_column | date | int_column1 | integer | default 1 int_column2 | integer | default 2 null_column | integer | (21 rows) SELECT float_column, count(*) FROM lineitem_alter GROUP BY float_column; float_column | count --------------------------------------------------------------------- | 6000 (1 row) SELECT int_column1, count(*) FROM lineitem_alter GROUP BY int_column1; int_column1 | count --------------------------------------------------------------------- 1 | 6000 (1 row) -- Verify that SET|DROP DEFAULT works ALTER TABLE lineitem_alter ALTER COLUMN float_column SET DEFAULT 1; ALTER TABLE lineitem_alter ALTER COLUMN int_column1 DROP DEFAULT; -- COPY to verify that default values take effect SELECT master_create_empty_shard('lineitem_alter') as shardid \gset copy lineitem_alter (l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment) FROM :'lineitem_1_data_file' with (delimiter '|', append_to_shard :shardid); SELECT float_column, count(*) FROM lineitem_alter GROUP BY float_column; float_column | count --------------------------------------------------------------------- | 6000 1 | 6000 (2 rows) SELECT int_column1, count(*) FROM lineitem_alter GROUP BY int_column1; int_column1 | count --------------------------------------------------------------------- | 6000 1 | 6000 (2 rows) -- Verify that SET NOT NULL works ALTER TABLE lineitem_alter ALTER COLUMN int_column2 SET NOT NULL; SELECT "Column", "Type", "Modifiers" FROM table_desc WHERE relid='lineitem_alter'::regclass; Column | Type | Modifiers --------------------------------------------------------------------- l_orderkey | bigint | not null l_partkey | integer | not null l_suppkey | integer | not null l_linenumber | integer | not null l_quantity | numeric(15,2) | not null l_extendedprice | numeric(15,2) | not null l_discount | numeric(15,2) | not null l_tax | numeric(15,2) | not null l_returnflag | character(1) | not null l_linestatus | character(1) | not null l_shipdate | date | not null l_commitdate | date | not null l_receiptdate | date | not null l_shipinstruct | character(25) | not null l_shipmode | character(10) | not null l_comment | character varying(44) | not null float_column | double precision | default 1 date_column | date | int_column1 | integer | int_column2 | integer | not null default 2 null_column | integer | (21 rows) -- Drop default so that NULLs will be inserted for this column ALTER TABLE lineitem_alter ALTER COLUMN int_column2 DROP DEFAULT; -- COPY should fail because it will try to insert NULLs for a NOT NULL column -- Note, this operation will create a table on the workers but it won't be in the metadata BEGIN; SELECT master_create_empty_shard('lineitem_alter') as shardid \gset copy lineitem_alter (l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment) FROM :'lineitem_1_data_file' with (delimiter '|', append_to_shard :shardid); ERROR: null value in column "int_column2" violates not-null constraint DETAIL: Failing row contains (1, 155190, 7706, 1, 17.00, 21168.23, 0.04, 0.02, N, O, 1996-03-13, 1996-02-12, 1996-03-22, DELIVER IN PERSON , TRUCK , egular courts above the, 1, null, null, null, null). END; -- Verify that DROP NOT NULL works ALTER TABLE lineitem_alter ALTER COLUMN int_column2 DROP NOT NULL; SELECT "Column", "Type", "Modifiers" FROM table_desc WHERE relid='lineitem_alter'::regclass; Column | Type | Modifiers --------------------------------------------------------------------- l_orderkey | bigint | not null l_partkey | integer | not null l_suppkey | integer | not null l_linenumber | integer | not null l_quantity | numeric(15,2) | not null l_extendedprice | numeric(15,2) | not null l_discount | numeric(15,2) | not null l_tax | numeric(15,2) | not null l_returnflag | character(1) | not null l_linestatus | character(1) | not null l_shipdate | date | not null l_commitdate | date | not null l_receiptdate | date | not null l_shipinstruct | character(25) | not null l_shipmode | character(10) | not null l_comment | character varying(44) | not null float_column | double precision | default 1 date_column | date | int_column1 | integer | int_column2 | integer | null_column | integer | (21 rows) -- COPY should succeed now SELECT master_create_empty_shard('lineitem_alter') as shardid \gset copy lineitem_alter (l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment) FROM :'lineitem_1_data_file' with (delimiter '|', append_to_shard :shardid); SELECT count(*) from lineitem_alter; count --------------------------------------------------------------------- 18000 (1 row) -- Verify that SET DATA TYPE works SELECT int_column2, pg_typeof(int_column2), count(*) from lineitem_alter GROUP BY int_column2; int_column2 | pg_typeof | count --------------------------------------------------------------------- | integer | 6000 2 | integer | 12000 (2 rows) ALTER TABLE lineitem_alter ALTER COLUMN int_column2 SET DATA TYPE FLOAT; SELECT "Column", "Type", "Modifiers" FROM table_desc WHERE relid='lineitem_alter'::regclass; Column | Type | Modifiers --------------------------------------------------------------------- l_orderkey | bigint | not null l_partkey | integer | not null l_suppkey | integer | not null l_linenumber | integer | not null l_quantity | numeric(15,2) | not null l_extendedprice | numeric(15,2) | not null l_discount | numeric(15,2) | not null l_tax | numeric(15,2) | not null l_returnflag | character(1) | not null l_linestatus | character(1) | not null l_shipdate | date | not null l_commitdate | date | not null l_receiptdate | date | not null l_shipinstruct | character(25) | not null l_shipmode | character(10) | not null l_comment | character varying(44) | not null float_column | double precision | default 1 date_column | date | int_column1 | integer | int_column2 | double precision | null_column | integer | (21 rows) SELECT int_column2, pg_typeof(int_column2), count(*) from lineitem_alter GROUP BY int_column2; int_column2 | pg_typeof | count --------------------------------------------------------------------- | double precision | 6000 2 | double precision | 12000 (2 rows) -- Verify that DROP COLUMN works ALTER TABLE lineitem_alter DROP COLUMN int_column1; ALTER TABLE lineitem_alter DROP COLUMN float_column; ALTER TABLE lineitem_alter DROP COLUMN date_column; -- Verify that RENAME COLUMN works ALTER TABLE lineitem_alter RENAME COLUMN l_orderkey TO l_orderkey_renamed; SELECT SUM(l_orderkey_renamed) FROM lineitem_alter; sum --------------------------------------------------------------------- 53620791 (1 row) -- Verify that IF EXISTS works as expected ALTER TABLE non_existent_table ADD COLUMN new_column INTEGER; ERROR: relation "non_existent_table" does not exist ALTER TABLE IF EXISTS non_existent_table ADD COLUMN new_column INTEGER; NOTICE: relation "non_existent_table" does not exist, skipping ALTER TABLE IF EXISTS lineitem_alter ALTER COLUMN int_column2 SET DATA TYPE INTEGER; ALTER TABLE lineitem_alter DROP COLUMN non_existent_column; ERROR: column "non_existent_column" of relation "lineitem_alter" does not exist ALTER TABLE lineitem_alter DROP COLUMN IF EXISTS non_existent_column; NOTICE: column "non_existent_column" of relation "lineitem_alter" does not exist, skipping ALTER TABLE lineitem_alter DROP COLUMN IF EXISTS int_column2; -- Verify with IF EXISTS for extant table ALTER TABLE IF EXISTS lineitem_alter RENAME COLUMN l_orderkey_renamed TO l_orderkey; SELECT SUM(l_orderkey) FROM lineitem_alter; sum --------------------------------------------------------------------- 53620791 (1 row) SELECT "Column", "Type", "Modifiers" FROM table_desc WHERE relid='lineitem_alter'::regclass; Column | Type | Modifiers --------------------------------------------------------------------- l_orderkey | bigint | not null l_partkey | integer | not null l_suppkey | integer | not null l_linenumber | integer | not null l_quantity | numeric(15,2) | not null l_extendedprice | numeric(15,2) | not null l_discount | numeric(15,2) | not null l_tax | numeric(15,2) | not null l_returnflag | character(1) | not null l_linestatus | character(1) | not null l_shipdate | date | not null l_commitdate | date | not null l_receiptdate | date | not null l_shipinstruct | character(25) | not null l_shipmode | character(10) | not null l_comment | character varying(44) | not null null_column | integer | (17 rows) -- Verify that we can execute commands with multiple subcommands ALTER TABLE lineitem_alter ADD COLUMN int_column1 INTEGER, ADD COLUMN int_column2 INTEGER; SELECT "Column", "Type", "Modifiers" FROM table_desc WHERE relid='lineitem_alter'::regclass; Column | Type | Modifiers --------------------------------------------------------------------- l_orderkey | bigint | not null l_partkey | integer | not null l_suppkey | integer | not null l_linenumber | integer | not null l_quantity | numeric(15,2) | not null l_extendedprice | numeric(15,2) | not null l_discount | numeric(15,2) | not null l_tax | numeric(15,2) | not null l_returnflag | character(1) | not null l_linestatus | character(1) | not null l_shipdate | date | not null l_commitdate | date | not null l_receiptdate | date | not null l_shipinstruct | character(25) | not null l_shipmode | character(10) | not null l_comment | character varying(44) | not null null_column | integer | int_column1 | integer | int_column2 | integer | (19 rows) ALTER TABLE lineitem_alter ADD COLUMN int_column3 INTEGER, ALTER COLUMN int_column1 SET STATISTICS 10; ERROR: alter table command is currently unsupported DETAIL: Only ADD|DROP COLUMN, SET|DROP NOT NULL, SET|DROP DEFAULT, ADD|DROP|VALIDATE CONSTRAINT, SET (), RESET (), ENABLE|DISABLE|NO FORCE|FORCE ROW LEVEL SECURITY, ATTACH|DETACH PARTITION and TYPE subcommands are supported. ALTER TABLE lineitem_alter DROP COLUMN int_column1, DROP COLUMN int_column2; SELECT "Column", "Type", "Modifiers" FROM table_desc WHERE relid='lineitem_alter'::regclass; Column | Type | Modifiers --------------------------------------------------------------------- l_orderkey | bigint | not null l_partkey | integer | not null l_suppkey | integer | not null l_linenumber | integer | not null l_quantity | numeric(15,2) | not null l_extendedprice | numeric(15,2) | not null l_discount | numeric(15,2) | not null l_tax | numeric(15,2) | not null l_returnflag | character(1) | not null l_linestatus | character(1) | not null l_shipdate | date | not null l_commitdate | date | not null l_receiptdate | date | not null l_shipinstruct | character(25) | not null l_shipmode | character(10) | not null l_comment | character varying(44) | not null null_column | integer | (17 rows) -- Verify that we cannot execute alter commands on the distribution column ALTER TABLE lineitem_alter ALTER COLUMN l_orderkey DROP NOT NULL; ERROR: cannot execute ALTER TABLE command involving partition column ALTER TABLE lineitem_alter DROP COLUMN l_orderkey; ERROR: cannot execute ALTER TABLE command involving partition column -- Verify that we error out on unsupported statement types ALTER TABLE lineitem_alter ALTER COLUMN l_orderkey SET STATISTICS 100; ERROR: alter table command is currently unsupported DETAIL: Only ADD|DROP COLUMN, SET|DROP NOT NULL, SET|DROP DEFAULT, ADD|DROP|VALIDATE CONSTRAINT, SET (), RESET (), ENABLE|DISABLE|NO FORCE|FORCE ROW LEVEL SECURITY, ATTACH|DETACH PARTITION and TYPE subcommands are supported. ALTER TABLE lineitem_alter DROP CONSTRAINT IF EXISTS non_existent_contraint; NOTICE: constraint "non_existent_contraint" of relation "lineitem_alter" does not exist, skipping ALTER TABLE lineitem_alter SET WITHOUT OIDS; ERROR: alter table command is currently unsupported DETAIL: Only ADD|DROP COLUMN, SET|DROP NOT NULL, SET|DROP DEFAULT, ADD|DROP|VALIDATE CONSTRAINT, SET (), RESET (), ENABLE|DISABLE|NO FORCE|FORCE ROW LEVEL SECURITY, ATTACH|DETACH PARTITION and TYPE subcommands are supported. -- Verify that we error out in case of postgres errors on supported statement -- types ALTER TABLE lineitem_alter ADD COLUMN new_column non_existent_type; ERROR: type "non_existent_type" does not exist ALTER TABLE lineitem_alter ALTER COLUMN null_column SET NOT NULL; ERROR: column "null_column" contains null values CONTEXT: while executing command on localhost:xxxxx ALTER TABLE lineitem_alter ALTER COLUMN l_partkey SET DEFAULT 'a'; ERROR: invalid input syntax for type integer: "a" -- Verify that we error out on RENAME CONSTRAINT statement ALTER TABLE lineitem_alter RENAME CONSTRAINT constraint_a TO constraint_b; ERROR: renaming constraints belonging to distributed tables is currently unsupported -- Verify that IF EXISTS works as expected with RENAME statements ALTER TABLE non_existent_table RENAME TO non_existent_table_renamed; ERROR: relation "non_existent_table" does not exist ALTER TABLE IF EXISTS non_existent_table RENAME TO non_existent_table_renamed; NOTICE: relation "non_existent_table" does not exist, skipping ALTER TABLE IF EXISTS non_existent_table RENAME COLUMN column1 TO column2; NOTICE: relation "non_existent_table" does not exist, skipping -- Verify that none of the failed alter table commands took effect on the master -- node SELECT "Column", "Type", "Modifiers" FROM table_desc WHERE relid='lineitem_alter'::regclass; Column | Type | Modifiers --------------------------------------------------------------------- l_orderkey | bigint | not null l_partkey | integer | not null l_suppkey | integer | not null l_linenumber | integer | not null l_quantity | numeric(15,2) | not null l_extendedprice | numeric(15,2) | not null l_discount | numeric(15,2) | not null l_tax | numeric(15,2) | not null l_returnflag | character(1) | not null l_linestatus | character(1) | not null l_shipdate | date | not null l_commitdate | date | not null l_receiptdate | date | not null l_shipinstruct | character(25) | not null l_shipmode | character(10) | not null l_comment | character varying(44) | not null null_column | integer | (17 rows) -- verify that non-propagated ddl commands are allowed inside a transaction block SET citus.enable_ddl_propagation to false; BEGIN; CREATE INDEX temp_index_1 ON lineitem_alter(l_linenumber); COMMIT; SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- temp_index_1 | lineitem_alter (1 row) DROP INDEX temp_index_1; -- verify that single distributed ddl commands are allowed inside a transaction block SET citus.enable_ddl_propagation to true; BEGIN; CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); COMMIT; SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- temp_index_2 | lineitem_alter (1 row) DROP INDEX temp_index_2; -- and so are multiple ddl statements BEGIN; CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); ALTER TABLE lineitem_alter ADD COLUMN first integer; COMMIT; SELECT "Column", "Type", "Modifiers" FROM table_desc WHERE relid='lineitem_alter'::regclass; Column | Type | Modifiers --------------------------------------------------------------------- l_orderkey | bigint | not null l_partkey | integer | not null l_suppkey | integer | not null l_linenumber | integer | not null l_quantity | numeric(15,2) | not null l_extendedprice | numeric(15,2) | not null l_discount | numeric(15,2) | not null l_tax | numeric(15,2) | not null l_returnflag | character(1) | not null l_linestatus | character(1) | not null l_shipdate | date | not null l_commitdate | date | not null l_receiptdate | date | not null l_shipinstruct | character(25) | not null l_shipmode | character(10) | not null l_comment | character varying(44) | not null null_column | integer | first | integer | (18 rows) SELECT "Column", "Type", "Definition" FROM index_attrs WHERE relid = 'temp_index_2'::regclass; Column | Type | Definition --------------------------------------------------------------------- l_orderkey | bigint | l_orderkey (1 row) ALTER TABLE lineitem_alter DROP COLUMN first; DROP INDEX temp_index_2; -- ensure that user-specified rollback causes full rollback BEGIN; CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); CREATE INDEX temp_index_3 ON lineitem_alter(l_partkey); ROLLBACK; SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- (0 rows) -- ensure that errors cause full rollback BEGIN; CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); ERROR: relation "temp_index_2" already exists ROLLBACK; SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- (0 rows) -- verify that SAVEPOINT is allowed... BEGIN; CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); SAVEPOINT my_savepoint; CREATE INDEX temp_index_3 ON lineitem_alter(l_partkey); ROLLBACK; -- and also rolling back to it is also allowed BEGIN; CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); SAVEPOINT my_savepoint; CREATE INDEX temp_index_3 ON lineitem_alter(l_partkey); ROLLBACK TO my_savepoint; COMMIT; SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- temp_index_2 | lineitem_alter (1 row) DROP INDEX temp_index_2; -- Add column on only one worker... \c - - - :worker_2_port ALTER TABLE multi_alter_table_statements.lineitem_alter_220000 ADD COLUMN first integer; \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 223000; -- and try to add it in a multi-statement block, which fails BEGIN; CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); ALTER TABLE lineitem_alter ADD COLUMN first integer; ERROR: column "first" of relation "lineitem_alter_220000" already exists CONTEXT: while executing command on localhost:xxxxx COMMIT; -- Nothing from the block should have committed SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- (0 rows) -- Create single-shard table (to avoid deadlocks in the upcoming test hackery) CREATE TABLE single_shard_items (id integer NOT NULL, name text); SET citus.shard_count TO 1; SET citus.shard_replication_factor TO 2; SELECT create_distributed_table('single_shard_items', 'id', 'hash'); create_distributed_table --------------------------------------------------------------------- (1 row) -- Verify that ALTER TABLE .. REPLICATION IDENTITY [USING INDEX]* .. works CREATE UNIQUE INDEX replica_idx on single_shard_items(id); SELECT relreplident FROM pg_class WHERE relname = 'single_shard_items'; relreplident --------------------------------------------------------------------- d (1 row) SELECT run_command_on_workers('SELECT relreplident FROM pg_class WHERE relname LIKE ''single_shard_items_%'' LIMIT 1;'); run_command_on_workers --------------------------------------------------------------------- (localhost,57637,t,d) (localhost,57638,t,d) (2 rows) ALTER TABLE single_shard_items REPLICA IDENTITY nothing; SELECT relreplident FROM pg_class WHERE relname = 'single_shard_items'; relreplident --------------------------------------------------------------------- n (1 row) SELECT run_command_on_workers('SELECT relreplident FROM pg_class WHERE relname LIKE ''single_shard_items_%'' LIMIT 1;'); run_command_on_workers --------------------------------------------------------------------- (localhost,57637,t,n) (localhost,57638,t,n) (2 rows) ALTER TABLE single_shard_items REPLICA IDENTITY full; SELECT relreplident FROM pg_class WHERE relname = 'single_shard_items'; relreplident --------------------------------------------------------------------- f (1 row) SELECT run_command_on_workers('SELECT relreplident FROM pg_class WHERE relname LIKE ''single_shard_items_%'' LIMIT 1;'); run_command_on_workers --------------------------------------------------------------------- (localhost,57637,t,f) (localhost,57638,t,f) (2 rows) ALTER TABLE single_shard_items REPLICA IDENTITY USING INDEX replica_idx; SELECT relreplident FROM pg_class WHERE relname = 'single_shard_items'; relreplident --------------------------------------------------------------------- i (1 row) SELECT run_command_on_workers('SELECT relreplident FROM pg_class WHERE relname LIKE ''single_shard_items_%'' LIMIT 1;'); run_command_on_workers --------------------------------------------------------------------- (localhost,57637,t,i) (localhost,57638,t,i) (2 rows) ALTER TABLE single_shard_items REPLICA IDENTITY default, REPLICA IDENTITY USING INDEX replica_idx, REPLICA IDENTITY nothing; SELECT relreplident FROM pg_class WHERE relname = 'single_shard_items'; relreplident --------------------------------------------------------------------- n (1 row) SELECT run_command_on_workers('SELECT relreplident FROM pg_class WHERE relname LIKE ''single_shard_items_%'' LIMIT 1;'); run_command_on_workers --------------------------------------------------------------------- (localhost,57637,t,n) (localhost,57638,t,n) (2 rows) ALTER TABLE single_shard_items ADD COLUMN test_col int, REPLICA IDENTITY full; DROP INDEX replica_idx; ALTER TABLE single_shard_items REPLICA IDENTITY default; -- Drop the column from the worker... \c - - - :worker_2_port ALTER TABLE multi_alter_table_statements.lineitem_alter_220000 DROP COLUMN first; -- Create table to trigger at-xact-end (deferred) failure CREATE TABLE ddl_commands (command text UNIQUE DEFERRABLE INITIALLY DEFERRED); -- Use an event trigger to log all DDL event tags in it SET citus.enable_metadata_sync TO OFF; CREATE FUNCTION log_ddl_tag() RETURNS event_trigger AS $ldt$ BEGIN INSERT INTO ddl_commands VALUES (tg_tag); END; $ldt$ LANGUAGE plpgsql; RESET citus.enable_metadata_sync; CREATE EVENT TRIGGER log_ddl_tag ON ddl_command_end EXECUTE PROCEDURE log_ddl_tag(); \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 224000; -- The above trigger will cause failure at transaction end on one placement. -- Citus always uses 2PC. 2PC should handle this "best" (no divergence) BEGIN; CREATE INDEX single_index_2 ON single_shard_items(id); CREATE INDEX single_index_3 ON single_shard_items(name); COMMIT; ERROR: duplicate key value violates unique constraint "ddl_commands_command_key" DETAIL: Key (command)=(CREATE INDEX) already exists. CONTEXT: while executing command on localhost:xxxxx -- Nothing from the block should have committed SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'single_shard_items' ORDER BY 1; indexname | tablename --------------------------------------------------------------------- (0 rows) \c - - - :worker_2_port DROP EVENT TRIGGER log_ddl_tag; DROP FUNCTION log_ddl_tag(); DROP TABLE ddl_commands; \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 225000; -- Distributed SELECTs may appear after ALTER BEGIN; CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); SELECT count(*) FROM lineitem_alter; count --------------------------------------------------------------------- 18000 (1 row) ROLLBACK; -- and before BEGIN; SELECT count(*) FROM lineitem_alter; count --------------------------------------------------------------------- 18000 (1 row) CREATE INDEX temp_index_2 ON lineitem_alter(l_orderkey); COMMIT; SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- temp_index_2 | lineitem_alter (1 row) DROP INDEX temp_index_2; -- verify that distributed ddl commands are allowed without transaction block as well -- Reminder: Now Citus always uses 2PC CREATE INDEX temp_index_3 ON lineitem_alter(l_orderkey); SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- temp_index_3 | lineitem_alter (1 row) DROP INDEX temp_index_3; SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- (0 rows) -- verify that not any of shard placements are marked as failed when a query failure occurs CREATE TABLE test_ab (a int, b int); SET citus.shard_count TO 8; SELECT create_distributed_table('test_ab', 'a', 'hash'); create_distributed_table --------------------------------------------------------------------- (1 row) INSERT INTO test_ab VALUES (2, 10); INSERT INTO test_ab VALUES (2, 11); CREATE UNIQUE INDEX temp_unique_index_1 ON test_ab(a); ERROR: could not create unique index "temp_unique_index_1_225006" DETAIL: Key (a)=(2) is duplicated. CONTEXT: while executing command on localhost:xxxxx SELECT shardid FROM pg_dist_shard_placement NATURAL JOIN pg_dist_shard WHERE logicalrelid='test_ab'::regclass AND shardstate=3; shardid --------------------------------------------------------------------- (0 rows) -- Check that the schema on the worker still looks reasonable \c - - - :worker_1_port SELECT attname, atttypid::regtype FROM (SELECT oid FROM pg_class WHERE relname LIKE 'lineitem_alter_%' ORDER BY relname LIMIT 1) pc JOIN pg_attribute ON (pc.oid = pg_attribute.attrelid) ORDER BY attnum; attname | atttypid --------------------------------------------------------------------- tableoid | oid cmax | cid xmax | xid cmin | cid xmin | xid ctid | tid l_orderkey | bigint l_partkey | integer l_suppkey | integer l_linenumber | integer l_quantity | numeric l_extendedprice | numeric l_discount | numeric l_tax | numeric l_returnflag | character l_linestatus | character l_shipdate | date l_commitdate | date l_receiptdate | date l_shipinstruct | character l_shipmode | character l_comment | character varying ........pg.dropped.17........ | - ........pg.dropped.18........ | - ........pg.dropped.19........ | - ........pg.dropped.20........ | - null_column | integer ........pg.dropped.22........ | - ........pg.dropped.23........ | - ........pg.dropped.24........ | - (30 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 226000; -- verify that we can rename distributed tables SHOW citus.enable_ddl_propagation; citus.enable_ddl_propagation --------------------------------------------------------------------- on (1 row) ALTER TABLE lineitem_alter RENAME TO lineitem_renamed; -- verify rename is performed SELECT relname FROM pg_class WHERE relname = 'lineitem_renamed'; relname --------------------------------------------------------------------- lineitem_renamed (1 row) -- show rename worked on one worker, too \c - - - :worker_1_port SELECT relname FROM pg_class WHERE relname LIKE 'lineitem_renamed%' ORDER BY relname; relname --------------------------------------------------------------------- lineitem_renamed_220000 lineitem_renamed_222000 lineitem_renamed_222002 (3 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 227000; -- revert it to original name ALTER TABLE lineitem_renamed RENAME TO lineitem_alter; -- show rename worked on one worker, too \c - - - :worker_1_port SELECT relname FROM pg_class WHERE relname LIKE 'lineitem_alter%' AND relname <> 'lineitem_alter_222001' /* failed copy trails */ ORDER BY relname; relname --------------------------------------------------------------------- lineitem_alter_220000 lineitem_alter_222000 lineitem_alter_222002 (3 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 228000; -- verify that we can set and reset storage parameters ALTER TABLE lineitem_alter SET(fillfactor=40); SELECT relname, reloptions FROM pg_class WHERE relname = 'lineitem_alter'; relname | reloptions --------------------------------------------------------------------- lineitem_alter | {fillfactor=40} (1 row) \c - - - :worker_1_port SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'lineitem_alter%' AND relname <> 'lineitem_alter_222001' /* failed copy trails */ ORDER BY relname; relname | reloptions --------------------------------------------------------------------- lineitem_alter_220000 | {fillfactor=40} lineitem_alter_222000 | {fillfactor=40} lineitem_alter_222002 | {fillfactor=40} (3 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 229000; ALTER TABLE lineitem_alter RESET(fillfactor); SELECT relname, reloptions FROM pg_class WHERE relname = 'lineitem_alter'; relname | reloptions --------------------------------------------------------------------- lineitem_alter | (1 row) \c - - - :worker_1_port SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'lineitem_alter%' AND relname <> 'lineitem_alter_222001' /* failed copy trails */ ORDER BY relname; relname | reloptions --------------------------------------------------------------------- lineitem_alter_220000 | lineitem_alter_222000 | lineitem_alter_222002 | (3 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 230000; -- verify that we can rename indexes on distributed tables CREATE INDEX temp_index_1 ON lineitem_alter(l_linenumber); ALTER INDEX temp_index_1 RENAME TO idx_lineitem_linenumber; -- verify rename is performed SELECT relname FROM pg_class WHERE relname = 'idx_lineitem_linenumber'; relname --------------------------------------------------------------------- idx_lineitem_linenumber (1 row) -- show rename worked on one worker, too \c - - - :worker_1_port SELECT relname FROM pg_class WHERE relname LIKE 'idx_lineitem_linenumber%' ORDER BY relname; relname --------------------------------------------------------------------- idx_lineitem_linenumber_220000 idx_lineitem_linenumber_222000 idx_lineitem_linenumber_222002 (3 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 231000; -- now get rid of the index DROP INDEX idx_lineitem_linenumber; -- verify that we don't intercept DDL commands if propagation is turned off SET citus.enable_ddl_propagation to false; -- table rename statement can be performed on the coordinator only now ALTER TABLE lineitem_alter RENAME TO lineitem_renamed; -- verify rename is performed SELECT relname FROM pg_class WHERE relname = 'lineitem_alter' or relname = 'lineitem_renamed'; relname --------------------------------------------------------------------- lineitem_renamed (1 row) -- revert it to original name ALTER TABLE lineitem_renamed RENAME TO lineitem_alter; -- this column is added to master table and not workers ALTER TABLE lineitem_alter ADD COLUMN column_only_added_to_master int; -- verify newly added column is not present in a worker shard \c - - - :worker_1_port SELECT column_only_added_to_master FROM multi_alter_table_statements.lineitem_alter_220000 LIMIT 0; ERROR: column "column_only_added_to_master" does not exist \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 232000; -- ddl propagation flag is reset to default, disable it again SET citus.enable_ddl_propagation to false; -- following query succeeds since it accesses an previously existing column SELECT l_orderkey FROM lineitem_alter LIMIT 0; l_orderkey --------------------------------------------------------------------- (0 rows) -- make master and workers have the same schema again ALTER TABLE lineitem_alter DROP COLUMN column_only_added_to_master; -- now this should succeed SELECT * FROM lineitem_alter LIMIT 0; l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment | null_column --------------------------------------------------------------------- (0 rows) -- previously unsupported statements are accepted by postgresql now ALTER TABLE lineitem_alter ALTER COLUMN l_orderkey SET STATISTICS 100; ALTER TABLE lineitem_alter DROP CONSTRAINT IF EXISTS non_existent_contraint; NOTICE: constraint "non_existent_contraint" of relation "lineitem_alter" does not exist, skipping ALTER TABLE lineitem_alter SET WITHOUT OIDS; -- distribution column still cannot be dropped. ALTER TABLE lineitem_alter DROP COLUMN l_orderkey; ERROR: cannot execute ALTER TABLE command dropping partition column -- Even unique indexes on l_partkey (non-partition column) are allowed. -- Citus would have prevented that. CREATE UNIQUE INDEX unique_lineitem_partkey on lineitem_alter(l_partkey); SELECT indexname, tablename FROM pg_indexes WHERE tablename = 'lineitem_alter'; indexname | tablename --------------------------------------------------------------------- unique_lineitem_partkey | lineitem_alter (1 row) -- verify index is not created on worker \c - - - :worker_1_port SELECT indexname, tablename FROM pg_indexes WHERE tablename like 'lineitem_alter_%'; indexname | tablename --------------------------------------------------------------------- (0 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 233000; -- verify alter table and drop sequence in the same transaction does not cause deadlock SET citus.shard_count TO 4; SET citus.shard_replication_factor TO 2; CREATE TABLE sequence_deadlock_test (a serial, b serial); SELECT create_distributed_table('sequence_deadlock_test', 'a'); create_distributed_table --------------------------------------------------------------------- (1 row) BEGIN; ALTER TABLE sequence_deadlock_test ADD COLUMN c int; -- suppress notice message caused by DROP ... CASCADE to prevent pg version difference SET client_min_messages TO 'WARNING'; DROP SEQUENCE sequence_deadlock_test_b_seq CASCADE; RESET client_min_messages; END; DROP TABLE sequence_deadlock_test; -- verify enable/disable trigger all works SET citus.shard_replication_factor TO 1; SET citus.shard_count TO 1; CREATE TABLE trigger_table ( id int, value text ); SELECT create_distributed_table('trigger_table', 'id'); create_distributed_table --------------------------------------------------------------------- (1 row) -- first set a trigger on a shard \c - - - :worker_1_port SET citus.enable_metadata_sync TO OFF; CREATE OR REPLACE FUNCTION update_value() RETURNS trigger AS $up$ BEGIN NEW.value := 'trigger enabled'; RETURN NEW; END; $up$ LANGUAGE plpgsql; RESET citus.enable_metadata_sync; CREATE TRIGGER update_value BEFORE INSERT ON multi_alter_table_statements.trigger_table_233004 FOR EACH ROW EXECUTE PROCEDURE update_value(); \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 234000; INSERT INTO trigger_table VALUES (1, 'trigger disabled'); SELECT value, count(*) FROM trigger_table GROUP BY value ORDER BY value; value | count --------------------------------------------------------------------- trigger enabled | 1 (1 row) ALTER TABLE trigger_table DISABLE TRIGGER ALL; ERROR: triggers are not supported on distributed tables INSERT INTO trigger_table VALUES (1, 'trigger disabled'); SELECT value, count(*) FROM trigger_table GROUP BY value ORDER BY value; value | count --------------------------------------------------------------------- trigger enabled | 2 (1 row) ALTER TABLE trigger_table ENABLE TRIGGER ALL; ERROR: triggers are not supported on distributed tables INSERT INTO trigger_table VALUES (1, 'trigger disabled'); SELECT value, count(*) FROM trigger_table GROUP BY value ORDER BY value; value | count --------------------------------------------------------------------- trigger enabled | 3 (1 row) DROP TABLE trigger_table; -- test ALTER TABLE ALL IN TABLESPACE -- we expect that it will warn out \set tablespace_location :abs_srcdir '/data' CREATE TABLESPACE super_fast_ssd LOCATION :'tablespace_location'; ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE super_fast_ssd; WARNING: not propagating ALTER TABLE ALL IN TABLESPACE commands to worker nodes HINT: Connect to worker nodes directly to manually move all tables. ALTER TABLE ALL IN TABLESPACE super_fast_ssd SET TABLESPACE pg_default; WARNING: not propagating ALTER TABLE ALL IN TABLESPACE commands to worker nodes HINT: Connect to worker nodes directly to manually move all tables. DROP TABLESPACE super_fast_ssd; -- Cleanup the table and its shards SET citus.enable_ddl_propagation to true; CREATE USER alter_table_owner WITH LOGIN; GRANT USAGE ON SCHEMA public TO alter_table_owner; GRANT USAGE ON SCHEMA multi_alter_table_statements TO alter_table_owner; \c - alter_table_owner - :master_port -- should not be able to access table without permission SELECT count(*) FROM multi_alter_table_statements.lineitem_alter; ERROR: permission denied for table lineitem_alter -- should not be able to drop the table as non table owner DROP TABLE multi_alter_table_statements.lineitem_alter; ERROR: must be owner of table lineitem_alter \c - postgres - :master_port ALTER TABLE multi_alter_table_statements.lineitem_alter OWNER TO alter_table_owner; \c - alter_table_owner - :master_port -- should be able to query the table as table owner SELECT count(*) FROM multi_alter_table_statements.lineitem_alter; count --------------------------------------------------------------------- 18000 (1 row) -- should be able to drop the table as table owner DROP TABLE multi_alter_table_statements.lineitem_alter; -- check that nothing's left over on workers, other than the leftover shard created -- during the unsuccessful COPY \c - postgres - :worker_1_port SELECT relname FROM pg_class WHERE relname LIKE 'lineitem_alter%'; relname --------------------------------------------------------------------- lineitem_alter_222001 (1 row) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 235000; -- drop the roles created REVOKE ALL ON SCHEMA PUBLIC FROM alter_table_owner; REVOKE ALL ON SCHEMA multi_alter_table_statements FROM alter_table_owner; DROP ROLE alter_table_owner; -- Test alter table with drop table in the same transaction BEGIN; CREATE TABLE test_table_1(id int); SELECT create_distributed_table('test_table_1','id'); create_distributed_table --------------------------------------------------------------------- (1 row) ALTER TABLE test_table_1 ADD CONSTRAINT u_key UNIQUE(id); DROP TABLE test_table_1; END; -- There should be no test_table_1 shard on workers \c - - - :worker_1_port SELECT relname FROM pg_class WHERE relname LIKE 'test_table_1%'; relname --------------------------------------------------------------------- (0 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 236000; -- verify logged info is propagated to workers when distributing the table CREATE TABLE logged_test(id int); ALTER TABLE logged_test SET UNLOGGED; SELECT create_distributed_table('logged_test', 'id'); create_distributed_table --------------------------------------------------------------------- (1 row) \c - - - :worker_1_port SELECT relname, CASE relpersistence WHEN 'u' THEN 'unlogged' WHEN 'p' then 'logged' ELSE 'unknown' END AS logged_info FROM pg_class WHERE relname ~ 'logged_test_' ORDER BY relname; relname | logged_info --------------------------------------------------------------------- logged_test_236000 | unlogged logged_test_236001 | unlogged logged_test_236002 | unlogged logged_test_236003 | unlogged (4 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 237000; -- verify SET LOGGED/UNLOGGED works after distributing the table ALTER TABLE logged_test SET LOGGED; SELECT relname, CASE relpersistence WHEN 'u' THEN 'unlogged' WHEN 'p' then 'logged' ELSE 'unknown' END AS logged_info FROM pg_class WHERE relname ~ 'logged_test*' ORDER BY relname; relname | logged_info --------------------------------------------------------------------- logged_test | logged (1 row) \c - - - :worker_1_port SELECT relname, CASE relpersistence WHEN 'u' THEN 'unlogged' WHEN 'p' then 'logged' ELSE 'unknown' END AS logged_info FROM pg_class WHERE relname ~ 'logged_test_' ORDER BY relname; relname | logged_info --------------------------------------------------------------------- logged_test_236000 | logged logged_test_236001 | logged logged_test_236002 | logged logged_test_236003 | logged (4 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 238000; ALTER TABLE logged_test SET UNLOGGED; SELECT relname, CASE relpersistence WHEN 'u' THEN 'unlogged' WHEN 'p' then 'logged' ELSE 'unknown' END AS logged_info FROM pg_class WHERE relname ~ 'logged_test*' ORDER BY relname; relname | logged_info --------------------------------------------------------------------- logged_test | unlogged (1 row) \c - - - :worker_1_port SELECT relname, CASE relpersistence WHEN 'u' THEN 'unlogged' WHEN 'p' then 'logged' ELSE 'unknown' END AS logged_info FROM pg_class WHERE relname ~ 'logged_test_' ORDER BY relname; relname | logged_info --------------------------------------------------------------------- logged_test_236000 | unlogged logged_test_236001 | unlogged logged_test_236002 | unlogged logged_test_236003 | unlogged (4 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 239000; DROP TABLE logged_test; -- Test WITH options on a normal simple hash-distributed table CREATE TABLE hash_dist(id bigint primary key, f1 text) WITH (fillfactor=40); SELECT create_distributed_table('hash_dist','id'); create_distributed_table --------------------------------------------------------------------- (1 row) -- verify that the storage options made it to the table definitions SELECT relname, reloptions FROM pg_class WHERE relname = 'hash_dist'; relname | reloptions --------------------------------------------------------------------- hash_dist | {fillfactor=40} (1 row) \c - - - :worker_1_port SELECT relname, reloptions FROM pg_class WHERE relkind = 'r' AND relname LIKE 'hash_dist_%' ORDER BY relname; relname | reloptions --------------------------------------------------------------------- hash_dist_239000 | {fillfactor=40} hash_dist_239001 | {fillfactor=40} hash_dist_239002 | {fillfactor=40} hash_dist_239003 | {fillfactor=40} (4 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 240000; -- verify that we can set and reset index storage parameters ALTER INDEX hash_dist_pkey SET(fillfactor=40); SELECT relname, reloptions FROM pg_class WHERE relname = 'hash_dist_pkey'; relname | reloptions --------------------------------------------------------------------- hash_dist_pkey | {fillfactor=40} (1 row) \c - - - :worker_1_port SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'hash_dist_pkey_%' ORDER BY relname; relname | reloptions --------------------------------------------------------------------- hash_dist_pkey_239000 | {fillfactor=40} hash_dist_pkey_239001 | {fillfactor=40} hash_dist_pkey_239002 | {fillfactor=40} hash_dist_pkey_239003 | {fillfactor=40} (4 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 241000; ALTER INDEX hash_dist_pkey RESET(fillfactor); SELECT relname, reloptions FROM pg_class WHERE relname = 'hash_dist_pkey'; relname | reloptions --------------------------------------------------------------------- hash_dist_pkey | (1 row) \c - - - :worker_1_port SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'hash_dist_pkey_%' ORDER BY relname; relname | reloptions --------------------------------------------------------------------- hash_dist_pkey_239000 | hash_dist_pkey_239001 | hash_dist_pkey_239002 | hash_dist_pkey_239003 | (4 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 242000; -- verify error message on ALTER INDEX, SET TABLESPACE is unsupported ALTER INDEX hash_dist_pkey SET TABLESPACE foo; ERROR: alter index ... set tablespace ... is currently unsupported DETAIL: Only RENAME TO, SET (), RESET (), ATTACH PARTITION and SET STATISTICS are supported. -- verify that we can add indexes with new storage options CREATE UNIQUE INDEX another_index ON hash_dist(id) WITH (fillfactor=50); -- show the index and its storage options on coordinator, then workers SELECT relname, reloptions FROM pg_class WHERE relname = 'another_index'; relname | reloptions --------------------------------------------------------------------- another_index | {fillfactor=50} (1 row) \c - - - :worker_1_port SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'another_index_%' ORDER BY relname; relname | reloptions --------------------------------------------------------------------- another_index_239000 | {fillfactor=50} another_index_239001 | {fillfactor=50} another_index_239002 | {fillfactor=50} another_index_239003 | {fillfactor=50} (4 rows) \c - - - :master_port SET search_path TO multi_alter_table_statements, public; SET citus.next_shard_id TO 243000; -- get rid of the index DROP INDEX another_index; -- check if we fail properly when a column with un-supported constraint is added -- UNIQUE, PRIMARY KEY on non-distribution column is not supported -- CHECK, FOREIGN KEY, UNIQE, PRIMARY KEY cannot be added together with ADD COLUMN SET citus.shard_replication_factor TO 1; CREATE TABLE test_table_1(id int); SELECT create_distributed_table('test_table_1', 'id'); create_distributed_table --------------------------------------------------------------------- (1 row) ALTER TABLE test_table_1 ADD COLUMN test_col int UNIQUE; ERROR: cannot create constraint on "test_table_1" DETAIL: Distributed relations cannot have UNIQUE, EXCLUDE, or PRIMARY KEY constraints that do not include the partition column (with an equality operator if EXCLUDE). ALTER TABLE test_table_1 ADD COLUMN test_col int PRIMARY KEY; ERROR: cannot create constraint on "test_table_1" DETAIL: Distributed relations cannot have UNIQUE, EXCLUDE, or PRIMARY KEY constraints that do not include the partition column (with an equality operator if EXCLUDE). ALTER TABLE test_table_1 ADD COLUMN test_col int CHECK (test_col > 3); ERROR: cannot execute ADD COLUMN command with PRIMARY KEY, UNIQUE, FOREIGN and CHECK constraints DETAIL: Adding a column with a constraint in one command is not supported because all constraints in Citus must have explicit names HINT: You can issue each command separately such as ALTER TABLE test_table_1 ADD COLUMN test_col data_type; ALTER TABLE test_table_1 ADD CONSTRAINT constraint_name CHECK (check_expression); CREATE TABLE reference_table(i int UNIQUE); SELECT create_reference_table('reference_table'); create_reference_table --------------------------------------------------------------------- (1 row) ALTER TABLE test_table_1 ADD COLUMN test_col_1 int REFERENCES reference_table(i) ON DELETE CASCADE; ALTER TABLE test_table_1 ADD COLUMN test_col_2 int REFERENCES reference_table(i) ON DELETE CASCADE ON UPDATE SET NULL; SELECT (groupid = 0) AS is_coordinator, result FROM run_command_on_all_nodes( $$SELECT get_grouped_fkey_constraints FROM get_grouped_fkey_constraints('multi_alter_table_statements.test_table_1')$$ ) JOIN pg_dist_node USING (nodeid) ORDER BY is_coordinator DESC, result; is_coordinator | result --------------------------------------------------------------------- t | [{"deferred": false, "deferable": false, "on_delete": "c", "on_update": "a", "match_type": "s", "constraint_names": ["test_table_1__fkey"], "referenced_tables": ["multi_alter_table_statements.reference_table"], "referenced_columns": ["i"], "referencing_tables": ["multi_alter_table_statements.test_table_1"], "referencing_columns": ["test_col_1"], "referencing_columns_set_null_or_default": null}, {"deferred": false, "deferable": false, "on_delete": "c", "on_update": "n", "match_type": "s", "constraint_names": ["test_table_1__fkey1"], "referenced_tables": ["multi_alter_table_statements.reference_table"], "referenced_columns": ["i"], "referencing_tables": ["multi_alter_table_statements.test_table_1"], "referencing_columns": ["test_col_2"], "referencing_columns_set_null_or_default": null}] f | [{"deferred": false, "deferable": false, "on_delete": "c", "on_update": "a", "match_type": "s", "constraint_names": ["test_table_1__fkey", "test_table_1__fkey_243000", "test_table_1__fkey_243002"], "referenced_tables": ["multi_alter_table_statements.reference_table", "multi_alter_table_statements.reference_table_243004", "multi_alter_table_statements.reference_table_243004"], "referenced_columns": ["i"], "referencing_tables": ["multi_alter_table_statements.test_table_1", "multi_alter_table_statements.test_table_1_243000", "multi_alter_table_statements.test_table_1_243002"], "referencing_columns": ["test_col_1"], "referencing_columns_set_null_or_default": null}, {"deferred": false, "deferable": false, "on_delete": "c", "on_update": "n", "match_type": "s", "constraint_names": ["test_table_1__fkey1", "test_table_1__fkey1_243000", "test_table_1__fkey1_243002"], "referenced_tables": ["multi_alter_table_statements.reference_table", "multi_alter_table_statements.reference_table_243004", "multi_alter_table_statements.reference_table_243004"], "referenced_columns": ["i"], "referencing_tables": ["multi_alter_table_statements.test_table_1", "multi_alter_table_statements.test_table_1_243000", "multi_alter_table_statements.test_table_1_243002"], "referencing_columns": ["test_col_2"], "referencing_columns_set_null_or_default": null}] f | [{"deferred": false, "deferable": false, "on_delete": "c", "on_update": "a", "match_type": "s", "constraint_names": ["test_table_1__fkey", "test_table_1__fkey_243001", "test_table_1__fkey_243003"], "referenced_tables": ["multi_alter_table_statements.reference_table", "multi_alter_table_statements.reference_table_243004", "multi_alter_table_statements.reference_table_243004"], "referenced_columns": ["i"], "referencing_tables": ["multi_alter_table_statements.test_table_1", "multi_alter_table_statements.test_table_1_243001", "multi_alter_table_statements.test_table_1_243003"], "referencing_columns": ["test_col_1"], "referencing_columns_set_null_or_default": null}, {"deferred": false, "deferable": false, "on_delete": "c", "on_update": "n", "match_type": "s", "constraint_names": ["test_table_1__fkey1", "test_table_1__fkey1_243001", "test_table_1__fkey1_243003"], "referenced_tables": ["multi_alter_table_statements.reference_table", "multi_alter_table_statements.reference_table_243004", "multi_alter_table_statements.reference_table_243004"], "referenced_columns": ["i"], "referencing_tables": ["multi_alter_table_statements.test_table_1", "multi_alter_table_statements.test_table_1_243001", "multi_alter_table_statements.test_table_1_243003"], "referencing_columns": ["test_col_2"], "referencing_columns_set_null_or_default": null}] (3 rows) BEGIN; SET LOCAL client_min_messages TO WARNING; DROP TABLE reference_table CASCADE; COMMIT; CREATE TABLE referenced_table(i int UNIQUE); SELECT create_distributed_table('referenced_table', 'i'); create_distributed_table --------------------------------------------------------------------- (1 row) ALTER TABLE test_table_1 ADD COLUMN test_col_3 int REFERENCES referenced_table(i); ERROR: cannot create foreign key constraint DETAIL: Foreign keys are supported in two cases, either in between two colocated tables including partition column in the same ordinal in the both tables or from distributed to reference tables DROP TABLE referenced_table, test_table_1; -- Check sequence propagate its own dependencies while adding a column CREATE TABLE table_without_sequence(a int); SELECT create_distributed_table('table_without_sequence', 'a'); create_distributed_table --------------------------------------------------------------------- (1 row) CREATE SCHEMA test_schema_for_sequence_propagation; CREATE SEQUENCE test_schema_for_sequence_propagation.seq_10; ALTER TABLE table_without_sequence ADD COLUMN x BIGINT DEFAULT nextval('test_schema_for_sequence_propagation.seq_10'); -- Should be distributed along with the sequence SELECT pg_identify_object_as_address(classid, objid, objsubid) from pg_catalog.pg_dist_object WHERE objid IN ('test_schema_for_sequence_propagation.seq_10'::regclass); pg_identify_object_as_address --------------------------------------------------------------------- (sequence,"{test_schema_for_sequence_propagation,seq_10}",{}) (1 row) SELECT pg_identify_object_as_address(classid, objid, objsubid) from pg_catalog.pg_dist_object WHERE objid IN ('test_schema_for_sequence_propagation'::regnamespace); pg_identify_object_as_address --------------------------------------------------------------------- (schema,{test_schema_for_sequence_propagation},{}) (1 row) SET client_min_messages TO WARNING; DROP SCHEMA test_schema_for_sequence_propagation CASCADE; DROP TABLE table_without_sequence; DROP SCHEMA multi_alter_table_statements CASCADE;