src/test/regress/input/gp_tablespace.source

-- start_matchignore
-- m/WARNING:  tablespace symlink path is too long for TAR/
-- m/DETAIL:  The symlinked path \".*\" will be truncated to 100 characters when sending a TAR to the utilities \(e.g. pg_basebackup\)/
-- end_matchignore
create or replace function has_init_file_for_oid(object oid) returns boolean as $$
	select count(pg_stat_file(p.pg_relation_filepath || '_init')) = 1 as success
	 from (select * from pg_relation_filepath(object)) p;
$$ language sql;

create or replace function has_init_file(aotablename regclass, tabletype text) returns boolean as $$
DECLARE
	relation_id regclass;
BEGIN
	case tabletype
	when 'table' then
		relation_id := aotablename;
	when 'index' then
		relation_id := aotablename;
	when 'aoseg' then
		select segrelid into relation_id from pg_appendonly where relid = aotablename;
	when 'blockdir' then
		select blkdirrelid into relation_id from pg_appendonly where relid = aotablename;
	when 'blockdirindex' then
		select blkdiridxid into relation_id from pg_appendonly where relid = aotablename;
	when 'visimap' then
		select visimaprelid into relation_id from pg_appendonly where relid = aotablename;
	when 'visimapindex' then
		select visimapidxid into relation_id from pg_appendonly where relid = aotablename;
	else
		raise notice 'Invalid tabletype for has_init_file %', tabletype;
		return false;
	end case;

	return has_init_file_for_oid(relation_id);
END;
$$ language plpgsql;

-- create tablespaces we can use
CREATE TABLESPACE testspace LOCATION '@testtablespace@';
CREATE TABLESPACE ul_testspace LOCATION '@testtablespace@_unlogged';
SELECT gp_segment_id,
       CASE tblspc_loc
            WHEN '@testtablespace@' THEN 'testtablespace'
            ELSE 'testtablespace_unknown'
       END AS tblspc_loc
FROM gp_tablespace_location((SELECT oid FROM pg_tablespace WHERE spcname='testspace'));
SELECT gp_segment_id,
       CASE tblspc_loc
            WHEN '@testtablespace@_unlogged' THEN 'testtablespace_unlogged'
            ELSE 'testtablespace_unknown'
       END AS tblspc_loc
FROM gp_tablespace_location((SELECT oid FROM pg_tablespace WHERE spcname='ul_testspace'));

-- Test that test tablespaces have the catalog directory directly under their
-- symlink
SELECT pg_ls_dir('./pg_tblspc/' || oid) = get_tablespace_version_directory_name()
         AS has_version_dir
FROM pg_tablespace WHERE spcname = 'testspace';

-- Ensure mirrors have applied filesystem changes
SELECT force_mirrors_to_catch_up();
\! ls @testtablespace@;

-- Test moving AO/AOCO tables from one tablespace to another.
CREATE TABLE ao_ts_table (id int4, t text) with (appendonly=true, orientation=row) distributed by (id);
CREATE TABLE aoco_ts_table (id int4, t text) with (appendonly=true, orientation=column) distributed by (id);
CREATE UNLOGGED TABLE ao_ul_ts_table (id int4, t text)
with (appendonly=true, orientation=row) distributed by (id);
CREATE UNLOGGED TABLE aoco_ul_ts_table (id int4, t text)
with (appendonly=true, orientation=column) distributed by (id);

insert into ao_ts_table select g, 'foo' || g from generate_series(1, 10000) g;
insert into aoco_ts_table select g, 'bar' || g from generate_series(1, 10000) g;
insert into ao_ul_ts_table select * from ao_ts_table;
insert into aoco_ul_ts_table select * from ao_ul_ts_table;

-- alter table to create scenario where .0 file also has data
ALTER TABLE ao_ts_table ALTER COLUMN id TYPE bigint;
-- to satisfy orca
ANALYZE ao_ts_table;

ALTER TABLE aoco_ts_table ALTER COLUMN id TYPE bigint;
-- to satisfy orca
ANALYZE aoco_ts_table;

insert into ao_ts_table select g, 'foofoo' || g from generate_series(10000, 10100) g;
insert into aoco_ts_table select g, 'barbar' || g from generate_series(10000, 10100) g;

CREATE INDEX ao_ts_index ON ao_ts_table(id);
CREATE INDEX ao_ul_ts_index ON ao_ul_ts_table(id);

SELECT COUNT(*) FROM ao_ts_table;
SELECT COUNT(*) FROM aoco_ts_table;
SELECT COUNT(*) FROM ao_ul_ts_table;
SELECT COUNT(*) FROM aoco_ul_ts_table;

ALTER TABLE ao_ts_table SET TABLESPACE testspace;
ALTER TABLE aoco_ts_table SET TABLESPACE testspace;
ALTER INDEX ao_ts_index SET TABLESPACE testspace;

ALTER TABLE ao_ul_ts_table SET TABLESPACE ul_testspace;
ALTER INDEX ao_ul_ts_index SET TABLESPACE ul_testspace;
ALTER TABLE aoco_ul_ts_table SET TABLESPACE ul_testspace;

-- Unlogged tables with .0 segfiles
CREATE UNLOGGED TABLE ao_ul_ctas USING ao_row AS select * from ao_ul_ts_table distributed by (id);
ALTER TABLE ao_ul_ctas SET TABLESPACE ul_testspace;

CREATE UNLOGGED TABLE aoco_ul_ctas USING ao_column AS select * from aoco_ul_ts_table distributed by (id);
ALTER TABLE aoco_ul_ctas SET TABLESPACE ul_testspace;

-- The .0 segfile should have been moved correctly, leading to
-- non-zero tuple count
select count(*) from ao_ul_ctas;
select * from gp_toolkit.__gp_aoseg('ao_ul_ctas');
select count(*) from aoco_ul_ctas;
select * from gp_toolkit.__gp_aocsseg('aoco_ul_ctas');

-- Check that init fork exists on master
select has_init_file('ao_ul_ts_table', 'table');
select has_init_file('ao_ul_ts_index', 'index');
select has_init_file('ao_ul_ts_table', 'aoseg');
select has_init_file('ao_ul_ts_table', 'blockdir');
select has_init_file('ao_ul_ts_table', 'blockdirindex');
select has_init_file('ao_ul_ts_table', 'visimap');
select has_init_file('ao_ul_ts_table', 'visimapindex');

INSERT INTO ao_ts_table VALUES(-1);
INSERT INTO aoco_ts_table VALUES(-1);
SELECT COUNT(*) FROM ao_ts_table;
SELECT COUNT(*) FROM aoco_ts_table;

INSERT INTO ao_ul_ts_table VALUES(-1);
INSERT INTO aoco_ul_ts_table VALUES(-1);
SELECT COUNT(*) FROM ao_ul_ts_table;
SELECT COUNT(*) FROM aoco_ul_ts_table;
-- Since count(*) for CO doesn't actually read all the columns, this query will
-- read all the columns and all the newly written files.
SELECT * FROM aoco_ts_table where id > 9995 and id < 10005;
SELECT * FROM aoco_ul_ts_table where id > 9995 and id < 10005;

-- Test moving multi-level partitioned table from one tablespace to another
CREATE TABLE part_ts_table(a int, b int, c int)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
SUBPARTITION BY RANGE (c)
(
	PARTITION p1 START (1) END (2) (
		SUBPARTITION pp1 START (1) END (2),
		SUBPARTITION pp2 START (2) END (3)
	),
	PARTITION p2 START (2) END (3) (
		SUBPARTITION pp1 START (1) END (2),
		SUBPARTITION pp2 START (2) END (3)
	)
);

INSERT INTO part_ts_table SELECT i, i, i FROM generate_series(1, 2)i;

-- Move a middle-level partitioned table to another tablespace
ALTER TABLE part_ts_table ALTER PARTITION p1 SET TABLESPACE testspace;
-- The partition being altered and all of its inheritors should be moved
SELECT relname, spcname FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid
    where c.relname LIKE 'part_ts_table%' order by relname;
SELECT * FROM part_ts_table;

-- Move the root partitioned table to another tablespace
ALTER TABLE part_ts_table SET TABLESPACE testspace;
-- The root table and all of its inheritors should be moved
SELECT relname, spcname FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid
    where c.relname LIKE 'part_ts_table%' order by relname;
SELECT * FROM part_ts_table;

-- Clean up. (It would be good to leave some extra tablespaces behind, so that
-- they would go through the gpcheckcat, pg_upgrade, etc. passes that run
-- after the main regression test suite. But all those tools are not up to
-- snuff yet to deal with tablespaces.)
drop table ao_ts_table;
drop table aoco_ts_table;
drop table part_ts_table;
drop tablespace testspace;
drop table ao_ul_ts_table;
drop table aoco_ul_ts_table;
drop table ao_ul_ctas;
drop table aoco_ul_ctas;
drop tablespace ul_testspace;

-- Cloudberry tablespaces have the option to define tablespace location for specific segments
CREATE TABLESPACE testspace_otherloc LOCATION '@testtablespace@' WITH (content9999='@testtablespace@_otherloc'); -- should fail
CREATE TABLESPACE testspace_otherloc LOCATION '@testtablespace@' WITH (content1='@testtablespace@_otherloc');
SELECT gp_segment_id,
       CASE tblspc_loc
            WHEN '@testtablespace@' THEN 'testtablespace'
            WHEN '@testtablespace@_otherloc' THEN 'testtablespace_otherloc'
            ELSE 'testtablespace_unknown'
       END AS tblspc_loc
FROM gp_tablespace_location((SELECT oid FROM pg_tablespace WHERE spcname='testspace_otherloc'));

-- Create a tablespace with an existing GP_TABLESPACE_VERSION_DIRECTORY for
-- another version of GPDB.
CREATE TABLESPACE testspace_existing_version_dir LOCATION '@testtablespace@_existing_version_dir';

SELECT * FROM
  (SELECT pg_ls_dir('pg_tblspc/' || oid) AS versiondirs
    FROM pg_tablespace
    WHERE spcname = 'testspace_existing_version_dir'
  ) a
WHERE a.versiondirs != get_tablespace_version_directory_name();

SELECT count(*) FROM
  (SELECT pg_ls_dir('pg_tblspc/' || oid) AS versiondirs
    FROM pg_tablespace
    WHERE spcname = 'testspace_existing_version_dir'
  ) a
WHERE a.versiondirs = get_tablespace_version_directory_name();

-- Do not drop the dbid directory, nor the existing version directory if you
-- drop this tablespace
DROP TABLESPACE testspace_existing_version_dir;

-- Ensure mirrors have applied filesystem changes
SELECT force_mirrors_to_catch_up();
\! ls @testtablespace@_existing_version_dir/*;

-- Test alter tablespace: PG does not seem to test these.

-- test SET & OWNER
ALTER TABLESPACE testspace_otherloc SET (random_page_cost=20.0);
SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspace_otherloc';
CREATE ROLE t_owner;
ALTER TABLESPACE testspace_otherloc OWNER TO t_owner;
SELECT rolname from pg_roles WHERE oid in (SELECT spcowner FROM pg_tablespace WHERE spcname = 'testspace_otherloc');
ALTER TABLESPACE testspace_otherloc OWNER TO @curusername@;
DROP ROLE t_owner;

-- test RENAME
ALTER TABLESPACE testspace_otherloc RENAME TO testspace_otherloc2;
SELECT spcname, spcoptions FROM pg_tablespace WHERE spcname = 'testspace_otherloc2';
ALTER TABLESPACE testspace_otherloc2 RENAME TO testspace_otherloc;

-- test RESET
ALTER TABLESPACE testspace_otherloc RESET (random_page_cost);
SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspace_otherloc';

CREATE TABLE tblspc_otherloc_heap(a int, b text) distributed by (a);
INSERT INTO tblspc_otherloc_heap select i, 'foo' || i from generate_series(1,10000)i;
SELECT COUNT(*) FROM tblspc_otherloc_heap;

DROP TABLE tblspc_otherloc_heap;
DROP TABLESPACE testspace_otherloc;

CREATE TABLESPACE testspace_dir_empty LOCATION '@testtablespace@';
CREATE TABLE t_dir_empty(a int);
\! rm -rf @testtablespace@/*;
DROP TABLE IF EXISTS t_dir_empty;
DROP TABLESPACE testspace_dir_empty;