SET client_min_messages = warning; -- -- Tablespace features tests -- DROP TABLESPACE IF EXISTS testts; \! mkdir -p /tmp/pg-migrate-tablespace CREATE TABLESPACE testts LOCATION '/tmp/pg-migrate-tablespace'; SELECT spcname FROM pg_tablespace WHERE spcname = 'testts'; spcname --------- testts (1 row) -- If the query above failed you must create the 'testts' tablespace; CREATE TABLE testts1 (id serial primary key, data text); CREATE INDEX testts1_partial_idx on testts1 (id) where (id > 0); CREATE INDEX testts1_with_idx on testts1 (id) with (fillfactor=80); INSERT INTO testts1 (data) values ('a'); INSERT INTO testts1 (data) values ('b'); INSERT INTO testts1 (data) values ('c'); -- check the indexes definitions SELECT regexp_replace( migrate.migrate_indexdef(indexrelid, 'testts1'::regclass, NULL, false, 'hash'), '_[0-9]+', '_OID', 'g') FROM pg_index i join pg_class c ON c.oid = indexrelid WHERE indrelid = 'testts1'::regclass ORDER BY relname; regexp_replace ------------------------------------------------------------------------------------------------- CREATE INDEX testts1_partial_idx_hash ON migrate.table_OID USING btree (id) WHERE (id > 0) CREATE UNIQUE INDEX testts1_pkey_hash ON migrate.table_OID USING btree (id) CREATE INDEX testts1_with_idx_hash ON migrate.table_OID USING btree (id) WITH (fillfactor='80') (3 rows) SELECT regexp_replace( migrate.migrate_indexdef(indexrelid, 'testts1'::regclass, 'foo', false, 'hash'), '_[0-9]+', '_OID', 'g') FROM pg_index i join pg_class c ON c.oid = indexrelid WHERE indrelid = 'testts1'::regclass ORDER BY relname; regexp_replace ---------------------------------------------------------------------------------------------------------------- CREATE INDEX testts1_partial_idx_hash ON migrate.table_OID USING btree (id) TABLESPACE foo WHERE (id > 0) CREATE UNIQUE INDEX testts1_pkey_hash ON migrate.table_OID USING btree (id) TABLESPACE foo CREATE INDEX testts1_with_idx_hash ON migrate.table_OID USING btree (id) WITH (fillfactor='80') TABLESPACE foo (3 rows) SELECT regexp_replace( migrate.migrate_indexdef(indexrelid, 'testts1'::regclass, NULL, true, 'hash'), '_[0-9]+', '_OID', 'g') FROM pg_index i join pg_class c ON c.oid = indexrelid WHERE indrelid = 'testts1'::regclass ORDER BY relname; regexp_replace ----------------------------------------------------------------------------------------------------------- CREATE INDEX CONCURRENTLY testts1_partial_idx_hash ON public.testts1 USING btree (id) WHERE (id > 0) CREATE UNIQUE INDEX CONCURRENTLY testts1_pkey_hash ON public.testts1 USING btree (id) CREATE INDEX CONCURRENTLY testts1_with_idx_hash ON public.testts1 USING btree (id) WITH (fillfactor='80') (3 rows) SELECT regexp_replace( migrate.migrate_indexdef(indexrelid, 'testts1'::regclass, 'foo', true, 'hash'), '_[0-9]+', '_OID', 'g') FROM pg_index i join pg_class c ON c.oid = indexrelid WHERE indrelid = 'testts1'::regclass ORDER BY relname; regexp_replace -------------------------------------------------------------------------------------------------------------------------- CREATE INDEX CONCURRENTLY testts1_partial_idx_hash ON public.testts1 USING btree (id) TABLESPACE foo WHERE (id > 0) CREATE UNIQUE INDEX CONCURRENTLY testts1_pkey_hash ON public.testts1 USING btree (id) TABLESPACE foo CREATE INDEX CONCURRENTLY testts1_with_idx_hash ON public.testts1 USING btree (id) WITH (fillfactor='80') TABLESPACE foo (3 rows) -- can specify the tablespace, other than default \! pg_migrate --dbname=contrib_regression --table=testts1 --tablespace testts --alter='ADD COLUMN a1 INT' --execute INFO: migrating table "public.testts1" INFO: altering table with: ADD COLUMN a1 INT SELECT relname, spcname FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace WHERE relname ~ '^testts1' AND NOT relname ~ '^testts1_pre_migrate' ORDER BY relname; relname | spcname ---------------------------+--------- testts1 | testts testts1_partial_idx_adf63 | testts testts1_pkey_adf63 | testts testts1_with_idx_adf63 | testts (4 rows) SELECT * from testts1 order by id; id | data | a1 ----+------+---- 1 | a | 2 | b | 3 | c | (3 rows) -- tablespace stays where it is \! pg_migrate --dbname=contrib_regression --table=testts1 --alter='ADD COLUMN a2 INT' --execute INFO: migrating table "public.testts1" INFO: altering table with: ADD COLUMN a2 INT SELECT relname, spcname FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace WHERE relname ~ '^testts1' AND NOT relname ~ '^testts1_pre_migrate' ORDER BY relname; relname | spcname ---------------------------------+--------- testts1 | testts testts1_partial_idx_adf63 | testts testts1_partial_idx_adf63_adf63 | testts testts1_pkey_adf63 | testts testts1_pkey_adf63_adf63 | testts testts1_with_idx_adf63 | testts testts1_with_idx_adf63_adf63 | testts (7 rows) -- can move the tablespace back to default \! pg_migrate --dbname=contrib_regression --table=testts1 -s pg_default --alter='ADD COLUMN a3 INT' --execute INFO: migrating table "public.testts1" INFO: altering table with: ADD COLUMN a3 INT SELECT relname, spcname FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace WHERE relname ~ '^testts1' AND NOT relname ~ '^testts1_pre_migrate' ORDER BY relname; relname | spcname ---------------------------------+--------- testts1_partial_idx_adf63 | testts testts1_partial_idx_adf63_adf63 | testts testts1_pkey_adf63 | testts testts1_pkey_adf63_adf63 | testts testts1_with_idx_adf63 | testts testts1_with_idx_adf63_adf63 | testts (6 rows) -- can move the table together with the indexes \! pg_migrate --dbname=contrib_regression --table=testts1 --tablespace testts --alter='ADD COLUMN a4 INT' --execute INFO: migrating table "public.testts1" INFO: altering table with: ADD COLUMN a4 INT SELECT relname, spcname FROM pg_class JOIN pg_tablespace ts ON ts.oid = reltablespace WHERE relname ~ '^testts1' AND NOT relname ~ '^testts1_pre_migrate' ORDER BY relname; relname | spcname ---------------------------------------------+--------- testts1 | testts testts1_partial_idx_adf63 | testts testts1_partial_idx_adf63_adf63 | testts testts1_partial_idx_adf63_adf63_adf63_adf63 | testts testts1_pkey_adf63 | testts testts1_pkey_adf63_adf63 | testts testts1_pkey_adf63_adf63_adf63_adf63 | testts testts1_with_idx_adf63 | testts testts1_with_idx_adf63_adf63 | testts testts1_with_idx_adf63_adf63_adf63_adf63 | testts (10 rows)