-- -- MULTI_ALTER_TABLE_ADD_CONSTRAINTS_WITHOUT_NAME -- -- Test checks whether constraints of distributed tables can be adjusted using -- the ALTER TABLE ... ADD without specifying a name. ALTER SEQUENCE pg_catalog.pg_dist_shardid_seq RESTART 5410000; ALTER SEQUENCE pg_catalog.pg_dist_placement_placementid_seq RESTART 5410000; CREATE SCHEMA AT_AddConstNoName; -- Check "ADD PRIMARY KEY" CREATE TABLE AT_AddConstNoName.products ( product_no integer, name text, price numeric ); SELECT create_distributed_table('AT_AddConstNoName.products', 'product_no'); create_distributed_table --------------------------------------------------------------------- (1 row) ALTER TABLE AT_AddConstNoName.products ADD PRIMARY KEY(product_no); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products'; conname --------------------------------------------------------------------- products_pkey (1 row) -- Check that the primary key name created on the coordinator is sent to workers and -- the constraints created for the shard tables conform to the _shardid naming scheme. \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_5410000'; conname --------------------------------------------------------------------- products_pkey_5410000 (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_pkey; -- Check "ADD PRIMARY KEY USING INDEX ..." CREATE TABLE AT_AddConstNoName.tbl(col1 int, col2 int); SELECT create_distributed_table('AT_AddConstNoName.tbl', 'col1'); create_distributed_table --------------------------------------------------------------------- (1 row) CREATE UNIQUE INDEX my_index ON AT_AddConstNoName.tbl(col1); ALTER TABLE AT_AddConstNoName.tbl ADD PRIMARY KEY USING INDEX my_index; SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'tbl'; conname --------------------------------------------------------------------- my_index (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'tbl%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- my_index my_index_5410004 my_index_5410005 my_index_5410006 my_index_5410007 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.tbl DROP CONSTRAINT my_index; -- Check "ADD UNIQUE USING INDEX ..." CREATE UNIQUE INDEX my_index ON AT_AddConstNoName.tbl(col1); ALTER TABLE AT_AddConstNoName.tbl ADD UNIQUE USING INDEX my_index; SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'tbl'; conname --------------------------------------------------------------------- my_index (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'tbl%'ORDER BY con.conname ASC; conname --------------------------------------------------------------------- my_index my_index_5410004 my_index_5410005 my_index_5410006 my_index_5410007 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.tbl DROP CONSTRAINT my_index; -- Check "ADD PRIMARY KEY DEFERRABLE" ALTER TABLE AT_AddConstNoName.products ADD PRIMARY KEY(product_no) DEFERRABLE; \c - - :public_worker_1_host :worker_1_port SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint WHERE tgrelid = 'AT_AddConstNoName.products_5410000'::regclass; conname | tgfoid | tgtype | tgdeferrable | tginitdeferred --------------------------------------------------------------------- products_pkey_5410000 | unique_key_recheck | 21 | t | f (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_pkey; ALTER TABLE AT_AddConstNoName.products ADD PRIMARY KEY(product_no) DEFERRABLE INITIALLY DEFERRED; \c - - :public_worker_1_host :worker_1_port SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint WHERE tgrelid = 'AT_AddConstNoName.products_5410000'::regclass; conname | tgfoid | tgtype | tgdeferrable | tginitdeferred --------------------------------------------------------------------- products_pkey_5410000 | unique_key_recheck | 21 | t | t (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_pkey; -- Check "ADD UNIQUE" ALTER TABLE AT_AddConstNoName.products ADD UNIQUE(product_no); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products'; conname --------------------------------------------------------------------- products_product_no_key (1 row) -- Check that UNIQUE constraint name created on the coordinator is sent to workers and -- the constraints created for the shard tables conform to the _shardid scheme. \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_5410000'; conname --------------------------------------------------------------------- products_product_no_key_5410000 (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_product_no_key; -- Check "ADD UNIQUE" with column name list ALTER TABLE AT_AddConstNoName.products ADD UNIQUE(product_no,name); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products'; conname --------------------------------------------------------------------- products_product_no_name_key (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_5410000'; conname --------------------------------------------------------------------- products_product_no_name_key_5410000 (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_product_no_name_key; -- Check "ADD UNIQUE ... INCLUDE" ALTER TABLE AT_AddConstNoName.products ADD UNIQUE(product_no) INCLUDE(price); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products'; conname --------------------------------------------------------------------- products_product_no_key (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_5410000'; conname --------------------------------------------------------------------- products_product_no_key_5410000 (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_product_no_key; -- Check "ADD UNIQUE NULLS NOT DISTICT" SHOW server_version \gset SELECT substring(:'server_version', '\d+')::int >= 15 AS server_version_ge_15 \gset \if :server_version_ge_15 ALTER TABLE AT_AddConstNoName.products ADD UNIQUE NULLS NOT DISTINCT (product_no, price); ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_product_no_price_key; \endif -- Check "ADD UNIQUE ... DEFERRABLE" ALTER TABLE AT_AddConstNoName.products ADD UNIQUE(product_no) INCLUDE(price) DEFERRABLE; \c - - :public_worker_1_host :worker_1_port SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint WHERE tgrelid = 'AT_AddConstNoName.products_5410000'::regclass; conname | tgfoid | tgtype | tgdeferrable | tginitdeferred --------------------------------------------------------------------- products_product_no_key_5410000 | unique_key_recheck | 21 | t | f (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_product_no_key; ALTER TABLE AT_AddConstNoName.products ADD UNIQUE(product_no) INCLUDE(price) DEFERRABLE INITIALLY DEFERRED; \c - - :public_worker_1_host :worker_1_port SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint WHERE tgrelid = 'AT_AddConstNoName.products_5410000'::regclass; conname | tgfoid | tgtype | tgdeferrable | tginitdeferred --------------------------------------------------------------------- products_product_no_key_5410000 | unique_key_recheck | 21 | t | t (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_product_no_key; -- Check "ADD EXCLUDE" CREATE EXTENSION btree_gist; ALTER TABLE AT_AddConstNoName.products ADD EXCLUDE USING gist (name WITH <> , product_no WITH =); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products'; conname --------------------------------------------------------------------- products_name_product_no_excl (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_5410000'; conname --------------------------------------------------------------------- products_name_product_no_excl_5410000 (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_name_product_no_excl; -- Check "ADD EXCLUDE ... DEFERRABLE" ALTER TABLE AT_AddConstNoName.products ADD EXCLUDE USING gist (name WITH <> , product_no WITH =) DEFERRABLE; \c - - :public_worker_1_host :worker_1_port SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint WHERE tgrelid = 'AT_AddConstNoName.products_5410000'::regclass; conname | tgfoid | tgtype | tgdeferrable | tginitdeferred --------------------------------------------------------------------- products_name_product_no_excl_5410000 | unique_key_recheck | 21 | t | f (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_name_product_no_excl; ALTER TABLE AT_AddConstNoName.products ADD EXCLUDE USING gist (name WITH <> , product_no WITH =) DEFERRABLE INITIALLY DEFERRED; \c - - :public_worker_1_host :worker_1_port SELECT conname, tgfoid::regproc, tgtype, tgdeferrable, tginitdeferred FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint WHERE tgrelid = 'AT_AddConstNoName.products_5410000'::regclass; conname | tgfoid | tgtype | tgdeferrable | tginitdeferred --------------------------------------------------------------------- products_name_product_no_excl_5410000 | unique_key_recheck | 21 | t | t (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_name_product_no_excl; -- Check "ADD CHECK" ALTER TABLE AT_AddConstNoName.products ADD CHECK (product_no > 0 AND price > 0); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products'; conname --------------------------------------------------------------------- products_check (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_5410000'; conname --------------------------------------------------------------------- products_check_5410000 (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_check; -- Check "ADD CHECK ... NOINHERIT" ALTER TABLE AT_AddConstNoName.products ADD CHECK (product_no > 0 AND price > 0) NO INHERIT; SELECT con.conname, con.connoinherit FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products'; conname | connoinherit --------------------------------------------------------------------- products_check | t (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname, con.connoinherit FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_5410000'; conname | connoinherit --------------------------------------------------------------------- products_check_5410000 | t (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_check; -- Check "ADD CHECK ... NOT VALID" ALTER TABLE AT_AddConstNoName.products ADD CHECK (product_no > 0 AND price > 0) NOT VALID; SELECT con.conname, con.convalidated FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products'; conname | convalidated --------------------------------------------------------------------- products_check | f (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname, con.convalidated FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_5410000'; conname | convalidated --------------------------------------------------------------------- products_check_5410000 | f (1 row) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.products DROP CONSTRAINT products_check; DROP TABLE AT_AddConstNoName.products; -- Check "ADD PRIMARY KEY" with reference table CREATE TABLE AT_AddConstNoName.products_ref ( product_no integer, name text, price numeric ); CREATE TABLE AT_AddConstNoName.products_ref_2 ( product_no integer, name text, price numeric ); CREATE TABLE AT_AddConstNoName.products_ref_3 ( product_no integer, name text, price numeric ); SELECT create_reference_table('AT_AddConstNoName.products_ref'); create_reference_table --------------------------------------------------------------------- (1 row) SELECT create_reference_table('AT_AddConstNoName.products_ref_3'); create_reference_table --------------------------------------------------------------------- (1 row) -- Check that name collisions are handled for PRIMARY KEY. ALTER TABLE AT_AddConstNoName.products_ref_3 ADD CONSTRAINT products_ref_pkey PRIMARY KEY(name); ALTER TABLE AT_AddConstNoName.products_ref_2 ADD CONSTRAINT products_ref_pkey1 PRIMARY KEY(name); ALTER TABLE AT_AddConstNoName.products_ref ADD PRIMARY KEY(name); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_ref'; conname --------------------------------------------------------------------- products_ref_pkey2 (1 row) ALTER TABLE AT_AddConstNoName.products_ref DROP CONSTRAINT products_ref_pkey2; -- Check that name collisions are handled for UNIQUE. ALTER TABLE AT_AddConstNoName.products_ref_3 ADD CONSTRAINT products_ref_name_key UNIQUE(name); ALTER TABLE AT_AddConstNoName.products_ref_2 ADD CONSTRAINT products_ref_name_key1 UNIQUE(name); ALTER TABLE AT_AddConstNoName.products_ref ADD UNIQUE(name); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_ref'; conname --------------------------------------------------------------------- products_ref_name_key2 (1 row) ALTER TABLE AT_AddConstNoName.products_ref DROP CONSTRAINT products_ref_name_key2; -- Check that name collisions are handled for EXCLUDE ALTER TABLE AT_AddConstNoName.products_ref_3 ADD CONSTRAINT products_ref_product_no_excl EXCLUDE (product_no WITH =); ALTER TABLE AT_AddConstNoName.products_ref_2 ADD CONSTRAINT products_ref_product_no_excl1 EXCLUDE (product_no WITH =); ALTER TABLE AT_AddConstNoName.products_ref ADD EXCLUDE(product_no WITH =); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_ref'; conname --------------------------------------------------------------------- products_ref_product_no_excl2 (1 row) ALTER TABLE AT_AddConstNoName.products_ref DROP CONSTRAINT products_ref_product_no_excl2; -- Check that name collisions are handled for CHECK ALTER TABLE AT_AddConstNoName.products_ref_3 ADD CONSTRAINT products_ref_check CHECK (product_no > 0); ALTER TABLE AT_AddConstNoName.products_ref_2 ADD CONSTRAINT products_ref_check1 CHECK (product_no > 0); ALTER TABLE AT_AddConstNoName.products_ref ADD CHECK (product_no > 0); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'products_ref'; conname --------------------------------------------------------------------- products_ref_check2 (1 row) ALTER TABLE AT_AddConstNoName.products_ref DROP CONSTRAINT products_ref_check2; DROP TABLE AT_AddConstNoName.products_ref; -- Check "ADD PRIMARY KEY" with max table name (63 chars) CREATE TABLE AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglonger ( product_no integer, name text, price numeric ); NOTICE: identifier "verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglonger" will be truncated to "verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon" SELECT create_distributed_table('AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon', 'product_no'); create_distributed_table --------------------------------------------------------------------- (1 row) ALTER TABLE AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon ADD PRIMARY KEY(product_no); -- Constraint should be created on the coordinator with a shortened name SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%'; conname --------------------------------------------------------------------- verylonglonglonglonglonglonglonglonglonglonglonglonglonglo_pkey (1 row) -- Constraints for the main table and the shards should be created on the worker with a shortened name \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- verylonglonglonglonglonglonglonglonglonglonglo_559ab79d_5410010 verylonglonglonglonglonglonglonglonglonglonglo_559ab79d_5410011 verylonglonglonglonglonglonglonglonglonglonglo_559ab79d_5410012 verylonglonglonglonglonglonglonglonglonglonglo_559ab79d_5410013 verylonglonglonglonglonglonglonglonglonglonglonglonglonglo_pkey (5 rows) -- Constraint can be deleted via the coordinator \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon DROP CONSTRAINT verylonglonglonglonglonglonglonglonglonglonglonglonglonglo_pkey; \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%'; conname --------------------------------------------------------------------- (0 rows) -- Check "ADD UNIQUE" with max table name (63 chars) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon ADD UNIQUE(product_no); -- Constraint should be created on the coordinator with a shortened name SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%'; conname --------------------------------------------------------------------- verylonglonglonglonglonglonglonglonglonglonglong_product_no_key (1 row) -- Constraints for the main table and the shards should be created on the worker with a shortened name \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- verylonglonglonglonglonglonglonglonglonglonglo_cd61b0cf_5410010 verylonglonglonglonglonglonglonglonglonglonglo_cd61b0cf_5410011 verylonglonglonglonglonglonglonglonglonglonglo_cd61b0cf_5410012 verylonglonglonglonglonglonglonglonglonglonglo_cd61b0cf_5410013 verylonglonglonglonglonglonglonglonglonglonglong_product_no_key (5 rows) -- UNIQUE constraint can be deleted via the coordinator \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon DROP CONSTRAINT verylonglonglonglonglonglonglonglonglonglonglong_product_no_key; \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%'; conname --------------------------------------------------------------------- (0 rows) -- Check "ADD EXCLUDE" with max table name (63 chars) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon ADD EXCLUDE (product_no WITH =); -- Constraint should be created on the coordinator with a shortened name SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%'; conname --------------------------------------------------------------------- verylonglonglonglonglonglonglonglonglonglonglon_product_no_excl (1 row) -- Constraints for the main table and the shards should be created on the worker with a shortened name \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- verylonglonglonglonglonglonglonglonglonglonglo_057ed027_5410010 verylonglonglonglonglonglonglonglonglonglonglo_057ed027_5410011 verylonglonglonglonglonglonglonglonglonglonglo_057ed027_5410012 verylonglonglonglonglonglonglonglonglonglonglo_057ed027_5410013 verylonglonglonglonglonglonglonglonglonglonglon_product_no_excl (5 rows) -- EXCLUDE constraint can be deleted via the coordinator \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon DROP CONSTRAINT verylonglonglonglonglonglonglonglonglonglonglon_product_no_excl; \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%'; conname --------------------------------------------------------------------- (0 rows) -- Check "ADD CHECK" with max table name (63 chars) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon ADD CHECK (product_no > 0); -- Constraint should be created on the coordinator with a shortened name SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%'; conname --------------------------------------------------------------------- verylonglonglonglonglonglonglonglonglonglonglonglonglongl_check (1 row) -- Constraints for the main table and the shards should be created on the worker with a shortened name \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- verylonglonglonglonglonglonglonglonglonglonglo_d943e063_5410010 verylonglonglonglonglonglonglonglonglonglonglo_d943e063_5410011 verylonglonglonglonglonglonglonglonglonglonglo_d943e063_5410012 verylonglonglonglonglonglonglonglonglonglonglo_d943e063_5410013 verylonglonglonglonglonglonglonglonglonglonglonglonglongl_check (5 rows) -- CHECK constraint can be deleted via the coordinator \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon DROP CONSTRAINT verylonglonglonglonglonglonglonglonglonglonglonglonglongl_check; \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'very%'; conname --------------------------------------------------------------------- (0 rows) -- Test the scenario where a partitioned distributed table has a child with max allowed name -- Verify that we switch to sequential execution mode to avoid deadlock in this scenario \c - - :master_host :master_port CREATE TABLE AT_AddConstNoName.dist_partitioned_table (dist_col int, another_col int, partition_col timestamp) PARTITION BY RANGE (partition_col); CREATE TABLE AT_AddConstNoName.p1 PARTITION OF AT_AddConstNoName.dist_partitioned_table FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'); CREATE TABLE AT_AddConstNoName.longlonglonglonglonglonglonglonglonglonglonglonglonglonglongabc PARTITION OF AT_AddConstNoName.dist_partitioned_table FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'); SELECT create_distributed_table('AT_AddConstNoName.dist_partitioned_table', 'partition_col'); create_distributed_table --------------------------------------------------------------------- (1 row) -- Check "ADD PRIMARY KEY" SET client_min_messages TO DEBUG1; ALTER TABLE AT_AddConstNoName.dist_partitioned_table ADD PRIMARY KEY(partition_col); DEBUG: the constraint name on the shards of the partition is too long, switching to sequential and local execution mode to prevent self deadlocks: longlonglonglonglonglonglonglonglonglonglonglo_537570f5_54_pkey DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "dist_partitioned_table_pkey" for table "dist_partitioned_table" DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "longlonglonglonglonglonglonglonglonglonglonglonglonglonglo_pkey" for table "longlonglonglonglonglonglonglonglonglonglonglonglonglonglongabc" DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "p1_pkey" for table "p1" DEBUG: verifying table "p1" DEBUG: verifying table "longlonglonglonglonglonglonglonglonglonglonglonglonglonglongabc" RESET client_min_messages; SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'dist_partitioned_table'; conname --------------------------------------------------------------------- dist_partitioned_table_pkey (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'longlonglonglonglonglonglonglonglong%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- longlonglonglonglonglonglonglonglonglonglonglo_9e4e3069_5410018 longlonglonglonglonglonglonglonglonglonglonglo_9e4e3069_5410019 longlonglonglonglonglonglonglonglonglonglonglo_9e4e3069_5410020 longlonglonglonglonglonglonglonglonglonglonglo_9e4e3069_5410021 longlonglonglonglonglonglonglonglonglonglonglonglonglonglo_pkey (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.dist_partitioned_table DROP CONSTRAINT dist_partitioned_table_pkey; \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'longlonglonglonglonglonglonglonglong%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- (0 rows) -- Check "ADD UNIQUE" \c - - :master_host :master_port SET client_min_messages TO DEBUG1; ALTER TABLE AT_AddConstNoName.dist_partitioned_table ADD UNIQUE(partition_col); DEBUG: the constraint name on the shards of the partition is too long, switching to sequential and local execution mode to prevent self deadlocks: longlonglonglonglonglonglonglonglonglonglongl_partition_col_key DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "dist_partitioned_table_partition_col_key" for table "dist_partitioned_table" DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "longlonglonglonglonglonglonglonglonglonglongl_partition_col_key" for table "longlonglonglonglonglonglonglonglonglonglonglonglonglonglongabc" DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "p1_partition_col_key" for table "p1" RESET client_min_messages; SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'dist_partitioned_table'; conname --------------------------------------------------------------------- dist_partitioned_table_partition_col_key (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'longlonglonglonglonglonglonglonglong%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- longlonglonglonglonglonglonglonglonglonglongl__d794d9f1_5410018 longlonglonglonglonglonglonglonglonglonglongl__d794d9f1_5410019 longlonglonglonglonglonglonglonglonglonglongl__d794d9f1_5410020 longlonglonglonglonglonglonglonglonglonglongl__d794d9f1_5410021 longlonglonglonglonglonglonglonglonglonglongl_partition_col_key (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.dist_partitioned_table DROP CONSTRAINT dist_partitioned_table_partition_col_key; -- Check "ADD CHECK" SET client_min_messages TO DEBUG1; ALTER TABLE AT_AddConstNoName.dist_partitioned_table ADD CHECK(dist_col >= another_col); DEBUG: the constraint name on the shards of the partition is too long, switching to sequential and local execution mode to prevent self deadlocks: longlonglonglonglonglonglonglonglonglonglonglo_537570f5_5_check DEBUG: verifying table "p1" DEBUG: verifying table "longlonglonglonglonglonglonglonglonglonglonglonglonglonglongabc" RESET client_min_messages; SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'dist_partitioned_table'; conname --------------------------------------------------------------------- dist_partitioned_table_check (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'longlonglonglonglonglonglonglonglong%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- dist_partitioned_table_check dist_partitioned_table_check dist_partitioned_table_check dist_partitioned_table_check dist_partitioned_table_check (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.dist_partitioned_table DROP CONSTRAINT dist_partitioned_table_check; \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'longlonglonglonglonglonglonglonglong%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- (0 rows) -- Test we error out when creating a constraint on a partition table with a long name if we cannot -- switch to sequential execution -- Check "ADD PRIMARY KEY" \c - - :master_host :master_port BEGIN; SELECT count(*) FROM AT_AddConstNoName.dist_partitioned_table; count --------------------------------------------------------------------- 0 (1 row) ALTER TABLE AT_AddConstNoName.dist_partitioned_table ADD PRIMARY KEY(partition_col); ERROR: The constraint name (longlonglonglonglonglonglonglonglonglonglonglo_537570f5_54_pkey) on a shard is too long and could lead to deadlocks when executed in a transaction block after a parallel query HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" ROLLBACK; -- try inside a sequential block BEGIN; SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; SELECT count(*) FROM AT_AddConstNoName.dist_partitioned_table; count --------------------------------------------------------------------- 0 (1 row) ALTER TABLE AT_AddConstNoName.dist_partitioned_table ADD PRIMARY KEY(partition_col); ROLLBACK; -- Check "ADD UNIQUE" \c - - :master_host :master_port BEGIN; SELECT count(*) FROM AT_AddConstNoName.dist_partitioned_table; count --------------------------------------------------------------------- 0 (1 row) ALTER TABLE AT_AddConstNoName.dist_partitioned_table ADD UNIQUE(partition_col); ERROR: The constraint name (longlonglonglonglonglonglonglonglonglonglongl_partition_col_key) on a shard is too long and could lead to deadlocks when executed in a transaction block after a parallel query HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" ROLLBACK; -- try inside a sequential block BEGIN; SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; SELECT count(*) FROM AT_AddConstNoName.dist_partitioned_table; count --------------------------------------------------------------------- 0 (1 row) ALTER TABLE AT_AddConstNoName.dist_partitioned_table ADD UNIQUE(partition_col); ROLLBACK; -- Check "ADD CHECK" \c - - :master_host :master_port BEGIN; SELECT count(*) FROM AT_AddConstNoName.dist_partitioned_table; count --------------------------------------------------------------------- 0 (1 row) ALTER TABLE AT_AddConstNoName.dist_partitioned_table ADD CHECK(dist_col > another_col); ERROR: The constraint name (longlonglonglonglonglonglonglonglonglonglonglo_537570f5_5_check) on a shard is too long and could lead to deadlocks when executed in a transaction block after a parallel query HINT: Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';" ROLLBACK; -- try inside a sequential block BEGIN; SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; SELECT count(*) FROM AT_AddConstNoName.dist_partitioned_table; count --------------------------------------------------------------------- 0 (1 row) ALTER TABLE AT_AddConstNoName.dist_partitioned_table ADD CHECK(dist_col > another_col); ROLLBACK; DROP TABLE AT_AddConstNoName.dist_partitioned_table; -- Test with Citus Local Tables -- Test "ADD PRIMARY KEY" \c - - :master_host :master_port CREATE TABLE AT_AddConstNoName.citus_local_table(id int, other_column int); SELECT citus_add_local_table_to_metadata('AT_AddConstNoName.citus_local_table'); citus_add_local_table_to_metadata --------------------------------------------------------------------- (1 row) ALTER TABLE AT_AddConstNoName.citus_local_table ADD PRIMARY KEY(id); -- Check the primary key is created for the local table and its shard SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_pkey citus_local_table_pkey_5410026 (2 rows) SELECT create_distributed_table('AT_AddConstNoName.citus_local_table','id'); create_distributed_table --------------------------------------------------------------------- (1 row) SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_pkey (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_pkey citus_local_table_pkey_5410027 citus_local_table_pkey_5410028 citus_local_table_pkey_5410029 citus_local_table_pkey_5410030 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_table DROP CONSTRAINT citus_local_table_pkey; -- Check "ADD UNIQUE" \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_table ADD UNIQUE(id); -- Check the UNIQUE constraint is created for the local table and its shard SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_id_key (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_id_key citus_local_table_id_key_5410027 citus_local_table_id_key_5410028 citus_local_table_id_key_5410029 citus_local_table_id_key_5410030 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_table DROP CONSTRAINT citus_local_table_id_key; -- Check "ADD EXCLUDE" \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_table ADD EXCLUDE(id WITH =); -- Check the EXCLUDE constraint is created for the local table and its shard SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_id_excl (1 row) SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_id_excl (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_id_excl citus_local_table_id_excl_5410027 citus_local_table_id_excl_5410028 citus_local_table_id_excl_5410029 citus_local_table_id_excl_5410030 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_table DROP CONSTRAINT citus_local_table_id_excl; -- Check "ADD CHECK" \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_table ADD CHECK(id > 100); -- Check the CHECK constraint is created for the local table and its shard SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_check (1 row) SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_check (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'citus_local_table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_table_check citus_local_table_check_5410027 citus_local_table_check_5410028 citus_local_table_check_5410029 citus_local_table_check_5410030 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_table DROP CONSTRAINT citus_local_table_check; DROP TABLE AT_AddConstNoName.citus_local_table; -- Test with partitioned citus local table CREATE TABLE AT_AddConstNoName.citus_local_partitioned_table (dist_col int, another_col int, partition_col timestamp) PARTITION BY RANGE (partition_col); CREATE TABLE AT_AddConstNoName.p1 PARTITION OF AT_AddConstNoName.citus_local_partitioned_table FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'); CREATE TABLE AT_AddConstNoName.longlonglonglonglonglonglonglonglonglonglonglonglonglonglongabc PARTITION OF AT_AddConstNoName.citus_local_partitioned_table FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'); SELECT citus_add_local_table_to_metadata('AT_AddConstNoName.citus_local_partitioned_table'); citus_add_local_table_to_metadata --------------------------------------------------------------------- (1 row) -- Check "ADD PRIMARY KEY" ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table ADD PRIMARY KEY(partition_col); SELECT create_distributed_table('AT_AddConstNoName.citus_local_partitioned_table', 'partition_col'); create_distributed_table --------------------------------------------------------------------- (1 row) ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table DROP CONSTRAINT citus_local_partitioned_table_pkey; SET client_min_messages TO DEBUG1; ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table ADD PRIMARY KEY(partition_col); DEBUG: the constraint name on the shards of the partition is too long, switching to sequential and local execution mode to prevent self deadlocks: longlonglonglonglonglonglonglonglonglonglonglo_537570f5_54_pkey DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "citus_local_partitioned_table_pkey" for table "citus_local_partitioned_table" DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "longlonglonglonglonglonglonglonglonglonglonglonglonglonglo_pkey" for table "longlonglonglonglonglonglonglonglonglonglonglonglonglonglongabc" DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "p1_pkey" for table "p1" RESET client_min_messages; SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'citus_local_partitioned_table'; conname --------------------------------------------------------------------- citus_local_partitioned_table_pkey (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'longlonglonglonglonglonglonglonglong%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- longlonglonglonglonglonglonglonglonglonglonglo_9e4e3069_5410038 longlonglonglonglonglonglonglonglonglonglonglo_9e4e3069_5410039 longlonglonglonglonglonglonglonglonglonglonglo_9e4e3069_5410040 longlonglonglonglonglonglonglonglonglonglonglo_9e4e3069_5410041 longlonglonglonglonglonglonglonglonglonglonglonglonglonglo_pkey (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table DROP CONSTRAINT citus_local_partitioned_table_pkey; -- Check "ADD UNIQUE" ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table ADD UNIQUE(partition_col); ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table DROP CONSTRAINT citus_local_partitioned_table_partition_col_key; SET client_min_messages TO DEBUG1; ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table ADD UNIQUE(partition_col); DEBUG: the constraint name on the shards of the partition is too long, switching to sequential and local execution mode to prevent self deadlocks: longlonglonglonglonglonglonglonglonglonglongl_partition_col_key DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "citus_local_partitioned_table_partition_col_key" for table "citus_local_partitioned_table" DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "longlonglonglonglonglonglonglonglonglonglongl_partition_col_key" for table "longlonglonglonglonglonglonglonglonglonglonglonglonglonglongabc" DEBUG: ALTER TABLE / ADD UNIQUE will create implicit index "p1_partition_col_key" for table "p1" RESET client_min_messages; SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'citus_local_partitioned_table'; conname --------------------------------------------------------------------- citus_local_partitioned_table_partition_col_key (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'longlonglonglonglonglonglonglonglong%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- longlonglonglonglonglonglonglonglonglonglongl__d794d9f1_5410038 longlonglonglonglonglonglonglonglonglonglongl__d794d9f1_5410039 longlonglonglonglonglonglonglonglonglonglongl__d794d9f1_5410040 longlonglonglonglonglonglonglonglonglonglongl__d794d9f1_5410041 longlonglonglonglonglonglonglonglonglonglongl_partition_col_key (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table DROP CONSTRAINT citus_local_partitioned_table_partition_col_key; -- Check "ADD EXCLUDE" errors out for partitioned table since the postgres does not allow it ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table ADD EXCLUDE(partition_col WITH =); ERROR: exclusion constraints are not supported on partitioned tables -- Check "ADD CHECK" SET client_min_messages TO DEBUG1; ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table ADD CHECK (dist_col > 0); DEBUG: the constraint name on the shards of the partition is too long, switching to sequential and local execution mode to prevent self deadlocks: longlonglonglonglonglonglonglonglonglonglonglo_537570f5_5_check DEBUG: verifying table "longlonglonglonglonglonglonglonglonglonglonglonglonglonglongabc" DEBUG: verifying table "p1" RESET client_min_messages; SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = 'citus_local_partitioned_table'; conname --------------------------------------------------------------------- citus_local_partitioned_table_check (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'longlonglonglonglonglonglonglonglong%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- citus_local_partitioned_table_check citus_local_partitioned_table_check citus_local_partitioned_table_check citus_local_partitioned_table_check citus_local_partitioned_table_check (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName.citus_local_partitioned_table DROP CONSTRAINT citus_local_partitioned_table_check; \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE 'longlonglonglonglonglonglonglonglong%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- (0 rows) \c - - :master_host :master_port -- Test with unusual table and column names CREATE TABLE AT_AddConstNoName."2nd table" ( "2nd id" INTEGER, "3rd id" INTEGER); SELECT create_distributed_table('AT_AddConstNoName."2nd table"','2nd id'); create_distributed_table --------------------------------------------------------------------- (1 row) -- Check "ADD PRIMARY KEY" ALTER TABLE AT_AddConstNoName."2nd table" ADD PRIMARY KEY ("2nd id", "3rd id"); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = '2nd table'; conname --------------------------------------------------------------------- 2nd table_pkey (1 row) -- Check if a primary key constraint is created for the shard tables on the workers \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE '2nd table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- 2nd table_pkey 2nd table_pkey_5410046 2nd table_pkey_5410047 2nd table_pkey_5410048 2nd table_pkey_5410049 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName."2nd table" DROP CONSTRAINT "2nd table_pkey"; -- Check "ADD UNIQUE" \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName."2nd table" ADD UNIQUE ("2nd id", "3rd id"); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = '2nd table'; conname --------------------------------------------------------------------- 2nd table_2nd id_3rd id_key (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE '2nd table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- 2nd table_2nd id_3rd id_key 2nd table_2nd id_3rd id_key_5410046 2nd table_2nd id_3rd id_key_5410047 2nd table_2nd id_3rd id_key_5410048 2nd table_2nd id_3rd id_key_5410049 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName."2nd table" DROP CONSTRAINT "2nd table_2nd id_3rd id_key"; -- Check "ADD EXCLUDE" \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName."2nd table" ADD EXCLUDE ("2nd id" WITH =); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = '2nd table'; conname --------------------------------------------------------------------- 2nd table_2nd id_excl (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE '2nd table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- 2nd table_2nd id_excl 2nd table_2nd id_excl_5410046 2nd table_2nd id_excl_5410047 2nd table_2nd id_excl_5410048 2nd table_2nd id_excl_5410049 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName."2nd table" DROP CONSTRAINT "2nd table_2nd id_excl"; -- Check "ADD CHECK" \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName."2nd table" ADD CHECK ("2nd id" > 0 ); SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = '2nd table'; conname --------------------------------------------------------------------- 2nd table_check (1 row) \c - - :public_worker_1_host :worker_1_port SELECT con.conname FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname LIKE '2nd table%' ORDER BY con.conname ASC; conname --------------------------------------------------------------------- 2nd table_check 2nd table_check_5410046 2nd table_check_5410047 2nd table_check_5410048 2nd table_check_5410049 (5 rows) \c - - :master_host :master_port ALTER TABLE AT_AddConstNoName."2nd table" DROP CONSTRAINT "2nd table_check"; DROP EXTENSION btree_gist; DROP SCHEMA AT_AddConstNoName CASCADE; NOTICE: drop cascades to 7 other objects DETAIL: drop cascades to table at_addconstnoname.tbl drop cascades to table at_addconstnoname.products_ref_2 drop cascades to table at_addconstnoname.products_ref_3 drop cascades to table at_addconstnoname.products_ref_3_5410009 drop cascades to table at_addconstnoname.verylonglonglonglonglonglonglonglonglonglonglonglonglonglonglon drop cascades to table at_addconstnoname.citus_local_partitioned_table drop cascades to table at_addconstnoname."2nd table"