src/test/isolation2/input/ao_upgrade.source

-- set_ao_formatversion forces an AO[CO] format to a specific version (the last
-- argument is set to true for a column-oriented table, and false otherwise).
CREATE OR REPLACE FUNCTION set_ao_formatversion(aosegrel oid, version smallint, isaocs bool)
	RETURNS bool
	AS '@abs_builddir@/isolation2_regress@DLSUFFIX@', 'setAOFormatVersion'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

DROP TABLE IF EXISTS ao_upgrade_test;
DROP TABLE IF EXISTS aocs_upgrade_test;
DROP TABLE IF EXISTS aocs_rle_upgrade_test;

CREATE TABLE ao_upgrade_test (rowid int, n numeric) USING ao_row;
CREATE TABLE aocs_upgrade_test (rowid int, n numeric) USING ao_column;
CREATE TABLE aocs_rle_upgrade_test (rowid int, n numeric) USING ao_column WITH (compresstype=RLE_TYPE);

-- We want to load GPDB4 numerics into the table; to do that, add a direct cast
-- from bytea to numeric so we can hardcode what the GPDB4 data looked like.
CREATE CAST (bytea AS numeric) WITHOUT FUNCTION;

INSERT INTO ao_upgrade_test VALUES
	(1, '\x000003000c007a0d'::bytea::numeric),	-- 12.345
	(2, '\x00000000'::bytea::numeric),			-- 0
	(3, '\x000003400c007a0d'::bytea::numeric),	-- -12.345
	(4, '\x010000000100'::bytea::numeric),		-- 10000
	(5, '\xfeff0500e803'::bytea::numeric),		-- 0.00001
	(6, '\xfeff0900e803'::bytea::numeric),		-- 0.000010000
	(7, '\x190000000100'::bytea::numeric),		-- 1e100
	(8, '\x010000002400400b'::bytea::numeric),	-- 9! (362880)
	(9, '\x000000c0'::bytea::numeric);			-- NaN
INSERT INTO aocs_upgrade_test VALUES
	(1, '\x000003000c007a0d'::bytea::numeric),	-- 12.345
	(2, '\x00000000'::bytea::numeric),			-- 0
	(3, '\x000003400c007a0d'::bytea::numeric),	-- -12.345
	(4, '\x010000000100'::bytea::numeric),		-- 10000
	(5, '\xfeff0500e803'::bytea::numeric),		-- 0.00001
	(6, '\xfeff0900e803'::bytea::numeric),		-- 0.000010000
	(7, '\x190000000100'::bytea::numeric),		-- 1e100
	(8, '\x010000002400400b'::bytea::numeric),	-- 9! (362880)
	(9, '\x000000c0'::bytea::numeric);			-- NaN

-- For the RLE test case, insert a bunch of identical numerics so they will be
-- run-length compressed.
INSERT INTO aocs_rle_upgrade_test (SELECT a, '\x010000002400400b'::bytea::numeric FROM generate_series(1, 10) a);

-- Downgrade to GPDB4 (AO version 2).
--start_ignore
*U: SELECT set_ao_formatversion(
		(SELECT segrelid FROM pg_appendonly WHERE relid = 'ao_upgrade_test'::regclass),
		2::smallint, false);
*U: SELECT set_ao_formatversion(
		(SELECT segrelid FROM pg_appendonly WHERE relid = 'aocs_upgrade_test'::regclass),
		2::smallint, true);
*U: SELECT set_ao_formatversion(
		(SELECT segrelid FROM pg_appendonly WHERE relid = 'aocs_rle_upgrade_test'::regclass),
		2::smallint, true);
--end_ignore

-- Scan test. The numerics should be fixed again.
SELECT * FROM ao_upgrade_test;
SELECT * FROM aocs_upgrade_test;
SELECT * FROM aocs_rle_upgrade_test;

-- Fetch test. To force fetches, we'll add bitmap indexes and disable sequential
-- scan.
CREATE INDEX ao_bitmap_index ON ao_upgrade_test USING bitmap(n);
CREATE INDEX aocs_bitmap_index ON aocs_upgrade_test USING bitmap(n);
CREATE INDEX aocs_rle_bitmap_index ON aocs_rle_upgrade_test USING bitmap(n);

SET enable_seqscan TO off;

-- Ensure we're using a bitmap scan for our tests. Upgrade note to developers:
-- the only thing that this test needs to verify is that a fetch-based scan is
-- in use. Other diffs are fine.
EXPLAIN SELECT n FROM ao_upgrade_test WHERE n = factorial(9);
EXPLAIN SELECT n FROM aocs_upgrade_test WHERE n = factorial(9);
EXPLAIN SELECT n FROM aocs_rle_upgrade_test WHERE n = factorial(9);

SELECT n FROM ao_upgrade_test WHERE n = factorial(9);
SELECT n FROM aocs_upgrade_test WHERE n = factorial(9);
SELECT n FROM aocs_rle_upgrade_test WHERE n = factorial(9);

RESET enable_seqscan;

DROP CAST (bytea AS numeric);
DROP FUNCTION set_ao_formatversion(oid, smallint, bool);