1-- create a tablespace using WITH clause 2CREATE TABLESPACE regress_tblspacewith LOCATION '@testtablespace@' WITH (some_nonexistent_parameter = true); -- fail 3CREATE TABLESPACE regress_tblspacewith LOCATION '@testtablespace@' WITH (random_page_cost = 3.0); -- ok 4 5-- check to see the parameter was used 6SELECT spcoptions FROM pg_tablespace WHERE spcname = 'regress_tblspacewith'; 7 8-- drop the tablespace so we can re-use the location 9DROP TABLESPACE regress_tblspacewith; 10 11-- create a tablespace we can use 12CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@'; 13 14-- try setting and resetting some properties for the new tablespace 15ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1); 16ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail 17ALTER TABLESPACE regress_tblspace RESET (random_page_cost = 2.0); -- fail 18ALTER TABLESPACE regress_tblspace RESET (random_page_cost, effective_io_concurrency); -- ok 19 20-- create a schema we can use 21CREATE SCHEMA testschema; 22 23-- try a table 24CREATE TABLE testschema.foo (i int) TABLESPACE regress_tblspace; 25SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c 26 where c.reltablespace = t.oid AND c.relname = 'foo'; 27 28INSERT INTO testschema.foo VALUES(1); 29INSERT INTO testschema.foo VALUES(2); 30 31-- tables from dynamic sources 32CREATE TABLE testschema.asselect TABLESPACE regress_tblspace AS SELECT 1; 33SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c 34 where c.reltablespace = t.oid AND c.relname = 'asselect'; 35 36PREPARE selectsource(int) AS SELECT $1; 37CREATE TABLE testschema.asexecute TABLESPACE regress_tblspace 38 AS EXECUTE selectsource(2); 39SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c 40 where c.reltablespace = t.oid AND c.relname = 'asexecute'; 41 42-- index 43CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE regress_tblspace; 44SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c 45 where c.reltablespace = t.oid AND c.relname = 'foo_idx'; 46 47-- partitioned index 48CREATE TABLE testschema.part (a int) PARTITION BY LIST (a); 49CREATE TABLE testschema.part1 PARTITION OF testschema.part FOR VALUES IN (1); 50CREATE INDEX part_a_idx ON testschema.part (a) TABLESPACE regress_tblspace; 51CREATE TABLE testschema.part2 PARTITION OF testschema.part FOR VALUES IN (2); 52SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c 53 where c.reltablespace = t.oid AND c.relname LIKE 'part%_idx'; 54 55-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds 56CREATE TABLE testschema.test_default_tab(id bigint) TABLESPACE regress_tblspace; 57INSERT INTO testschema.test_default_tab VALUES (1); 58CREATE INDEX test_index1 on testschema.test_default_tab (id); 59CREATE INDEX test_index2 on testschema.test_default_tab (id) TABLESPACE regress_tblspace; 60\d testschema.test_index1 61\d testschema.test_index2 62-- use a custom tablespace for default_tablespace 63SET default_tablespace TO regress_tblspace; 64-- tablespace should not change if no rewrite 65ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint; 66\d testschema.test_index1 67\d testschema.test_index2 68SELECT * FROM testschema.test_default_tab; 69-- tablespace should not change even if there is an index rewrite 70ALTER TABLE testschema.test_default_tab ALTER id TYPE int; 71\d testschema.test_index1 72\d testschema.test_index2 73SELECT * FROM testschema.test_default_tab; 74-- now use the default tablespace for default_tablespace 75SET default_tablespace TO ''; 76-- tablespace should not change if no rewrite 77ALTER TABLE testschema.test_default_tab ALTER id TYPE int; 78\d testschema.test_index1 79\d testschema.test_index2 80-- tablespace should not change even if there is an index rewrite 81ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint; 82\d testschema.test_index1 83\d testschema.test_index2 84DROP TABLE testschema.test_default_tab; 85 86-- check that default_tablespace affects index additions in ALTER TABLE 87CREATE TABLE testschema.test_tab(id int) TABLESPACE regress_tblspace; 88INSERT INTO testschema.test_tab VALUES (1); 89SET default_tablespace TO regress_tblspace; 90ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_unique UNIQUE (id); 91SET default_tablespace TO ''; 92ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_pkey PRIMARY KEY (id); 93\d testschema.test_tab_unique 94\d testschema.test_tab_pkey 95SELECT * FROM testschema.test_tab; 96DROP TABLE testschema.test_tab; 97 98-- let's try moving a table from one place to another 99CREATE TABLE testschema.atable AS VALUES (1), (2); 100CREATE UNIQUE INDEX anindex ON testschema.atable(column1); 101 102ALTER TABLE testschema.atable SET TABLESPACE regress_tblspace; 103ALTER INDEX testschema.anindex SET TABLESPACE regress_tblspace; 104ALTER INDEX testschema.part_a_idx SET TABLESPACE pg_global; 105ALTER INDEX testschema.part_a_idx SET TABLESPACE pg_default; 106ALTER INDEX testschema.part_a_idx SET TABLESPACE regress_tblspace; 107 108INSERT INTO testschema.atable VALUES(3); -- ok 109INSERT INTO testschema.atable VALUES(1); -- fail (checks index) 110SELECT COUNT(*) FROM testschema.atable; -- checks heap 111 112-- Will fail with bad path 113CREATE TABLESPACE regress_badspace LOCATION '/no/such/location'; 114 115-- No such tablespace 116CREATE TABLE bar (i int) TABLESPACE regress_nosuchspace; 117 118-- Fail, in use for some partitioned object 119DROP TABLESPACE regress_tblspace; 120ALTER INDEX testschema.part_a_idx SET TABLESPACE pg_default; 121-- Fail, not empty 122DROP TABLESPACE regress_tblspace; 123 124CREATE ROLE regress_tablespace_user1 login; 125CREATE ROLE regress_tablespace_user2 login; 126GRANT USAGE ON SCHEMA testschema TO regress_tablespace_user2; 127 128ALTER TABLESPACE regress_tblspace OWNER TO regress_tablespace_user1; 129 130CREATE TABLE testschema.tablespace_acl (c int); 131-- new owner lacks permission to create this index from scratch 132CREATE INDEX k ON testschema.tablespace_acl (c) TABLESPACE regress_tblspace; 133ALTER TABLE testschema.tablespace_acl OWNER TO regress_tablespace_user2; 134 135SET SESSION ROLE regress_tablespace_user2; 136CREATE TABLE tablespace_table (i int) TABLESPACE regress_tblspace; -- fail 137ALTER TABLE testschema.tablespace_acl ALTER c TYPE bigint; 138RESET ROLE; 139 140ALTER TABLESPACE regress_tblspace RENAME TO regress_tblspace_renamed; 141 142ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default; 143ALTER INDEX ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default; 144 145-- Should show notice that nothing was done 146ALTER TABLE ALL IN TABLESPACE regress_tblspace_renamed SET TABLESPACE pg_default; 147 148-- Should succeed 149DROP TABLESPACE regress_tblspace_renamed; 150 151DROP SCHEMA testschema CASCADE; 152 153DROP ROLE regress_tablespace_user1; 154DROP ROLE regress_tablespace_user2; 155