ao_upgrade_optimizer.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;
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) WITH (appendonly=true);
CREATE
CREATE TABLE aocs_upgrade_test (rowid int, n numeric) WITH (appendonly=true, orientation=column);
CREATE
CREATE TABLE aocs_rle_upgrade_test (rowid int, n numeric) WITH (appendonly=true, orientation=column, 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 = (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 = (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 = (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 = (9 !);
 n      
--------
 362880 
(1 row)
SELECT n FROM aocs_upgrade_test WHERE n = (9 !);
 n      
--------
 362880 
(1 row)
SELECT n FROM aocs_rle_upgrade_test WHERE n = (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