| -- 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; |
| CREATE |
| |
| DROP TABLE IF EXISTS ao_upgrade_test; |
| DROP |
| DROP TABLE IF EXISTS aocs_upgrade_test; |
| DROP |
| DROP TABLE IF EXISTS aocs_rle_upgrade_test; |
| DROP |
| |
| CREATE TABLE ao_upgrade_test (rowid int, n numeric) USING ao_row; |
| CREATE |
| CREATE TABLE aocs_upgrade_test (rowid int, n numeric) USING ao_column; |
| CREATE |
| CREATE TABLE aocs_rle_upgrade_test (rowid int, n numeric) USING ao_column WITH (compresstype=RLE_TYPE); |
| CREATE |
| |
| -- 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; |
| CREATE |
| |
| 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); |
| INSERT 10 |
| |
| -- 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); |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| *U: SELECT set_ao_formatversion( (SELECT segrelid FROM pg_appendonly WHERE relid = 'aocs_upgrade_test'::regclass), 2::smallint, true); |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| *U: SELECT set_ao_formatversion( (SELECT segrelid FROM pg_appendonly WHERE relid = 'aocs_rle_upgrade_test'::regclass), 2::smallint, true); |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| |
| set_ao_formatversion |
| ---------------------- |
| t |
| (1 row) |
| --end_ignore |
| |
| -- Scan test. The numerics should be fixed again. |
| SELECT * FROM ao_upgrade_test; |
| rowid | n |
| -------+------------------------------------------------------------------------------------------------------- |
| 2 | 0 |
| 5 | 0.00001 |
| 6 | 0.000010000 |
| 9 | NaN |
| 1 | 12.345 |
| 3 | -12.345 |
| 4 | 10000 |
| 7 | 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
| 8 | 362880 |
| (9 rows) |
| SELECT * FROM aocs_upgrade_test; |
| rowid | n |
| -------+------------------------------------------------------------------------------------------------------- |
| 2 | 0 |
| 5 | 0.00001 |
| 6 | 0.000010000 |
| 9 | NaN |
| 4 | 10000 |
| 7 | 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
| 8 | 362880 |
| 1 | 12.345 |
| 3 | -12.345 |
| (9 rows) |
| SELECT * FROM aocs_rle_upgrade_test; |
| rowid | n |
| -------+-------- |
| 1 | 362880 |
| 2 | 362880 |
| 3 | 362880 |
| 4 | 362880 |
| 5 | 362880 |
| 6 | 362880 |
| 7 | 362880 |
| 8 | 362880 |
| 9 | 362880 |
| 10 | 362880 |
| (10 rows) |
| |
| -- 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 |
| CREATE INDEX aocs_bitmap_index ON aocs_upgrade_test USING bitmap(n); |
| CREATE |
| CREATE INDEX aocs_rle_bitmap_index ON aocs_rle_upgrade_test USING bitmap(n); |
| CREATE |
| |
| SET enable_seqscan TO off; |
| SET |
| |
| -- 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); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=1000.36..1100.37 rows=1 width=9) |
| -> Bitmap Heap Scan on ao_upgrade_test (cost=1000.36..1100.37 rows=1 width=9) |
| Recheck Cond: n = 362880::numeric |
| -> Bitmap Index Scan on ao_bitmap_index (cost=0.00..1000.36 rows=1 width=0) |
| Index Cond: n = 362880::numeric |
| Settings: enable_seqscan=off |
| Optimizer status: Postgres query optimizer |
| (7 rows) |
| EXPLAIN SELECT n FROM aocs_upgrade_test WHERE n = factorial(9); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (cost=1000.36..1100.37 rows=1 width=9) |
| -> Bitmap Heap Scan on aocs_upgrade_test (cost=1000.36..1100.37 rows=1 width=9) |
| Recheck Cond: n = 362880::numeric |
| -> Bitmap Index Scan on aocs_bitmap_index (cost=0.00..1000.36 rows=1 width=0) |
| Index Cond: n = 362880::numeric |
| Settings: enable_seqscan=off |
| Optimizer status: Postgres query optimizer |
| (7 rows) |
| EXPLAIN SELECT n FROM aocs_rle_upgrade_test WHERE n = factorial(9); |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=1000.36..1100.37 rows=1 width=9) |
| -> Bitmap Heap Scan on aocs_rle_upgrade_test (cost=1000.36..1100.37 rows=1 width=9) |
| Recheck Cond: n = 362880::numeric |
| -> Bitmap Index Scan on aocs_rle_bitmap_index (cost=0.00..1000.36 rows=1 width=0) |
| Index Cond: n = 362880::numeric |
| Settings: enable_seqscan=off |
| Optimizer status: Postgres query optimizer |
| (7 rows) |
| |
| SELECT n FROM ao_upgrade_test WHERE n = factorial(9); |
| n |
| -------- |
| 362880 |
| (1 row) |
| SELECT n FROM aocs_upgrade_test WHERE n = factorial(9); |
| n |
| -------- |
| 362880 |
| (1 row) |
| SELECT n FROM aocs_rle_upgrade_test WHERE n = factorial(9); |
| n |
| -------- |
| 362880 |
| 362880 |
| 362880 |
| 362880 |
| 362880 |
| 362880 |
| 362880 |
| 362880 |
| 362880 |
| 362880 |
| (10 rows) |
| |
| RESET enable_seqscan; |
| RESET |
| |
| DROP CAST (bytea AS numeric); |
| DROP |
| DROP FUNCTION set_ao_formatversion(oid, smallint, bool); |
| DROP |
