CREATE SCHEMA cstore_alter_table_6;
SET SEARCH_PATH TO cstore_alter_table_6;
SET INTERVALsTYLE = POSTGRES;
CREATE TABLE alter_addcols_101 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN);
CREATE INDEX idx101_1 ON alter_addcols_101 (info);
CREATE INDEX idx101_2 ON alter_addcols_101 (info1);
INSERT INTO alter_addcols_101 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM alter_addcols_101 WHERE info='aa' ORDER BY 1, 3;
id | info | info1
----+------+-------
1 | aa | 1
2 | aa | 2
3 | aa | 3
5 | aa | 5
(4 rows)
SELECT * FROM alter_addcols_101 WHERE info1>3 ORDER BY 1, 3;
id | info | info1
----+------+-------
4 | 4 | 4
5 | aa | 5
(2 rows)
ALTER TABLE alter_addcols_101 ALTER info TYPE varchar(1100);
SELECT * FROM alter_addcols_101 WHERE info='aa' ORDER BY 1, 3;
id | info | info1
----+------+-------
1 | aa | 1
2 | aa | 2
3 | aa | 3
5 | aa | 5
(4 rows)
SELECT * FROM alter_addcols_101 WHERE info1>3 ORDER BY 1, 3;
id | info | info1
----+------+-------
4 | 4 | 4
5 | aa | 5
(2 rows)
-- rebuild all the indexes when rewrite the heap data.
CREATE TABLE alter_addcols_98 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN);
CREATE INDEX idx98_1 ON alter_addcols_98 (info1);
INSERT INTO alter_addcols_98 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM alter_addcols_98 WHERE info='aa' ORDER BY 1, 3;
id | info | info1
----+------+-------
1 | aa | 1
2 | aa | 2
3 | aa | 3
5 | aa | 5
(4 rows)
SELECT * FROM alter_addcols_98 WHERE info1>3 ORDER BY 1, 3;
id | info | info1
----+------+-------
4 | 4 | 4
5 | aa | 5
(2 rows)
ALTER TABLE alter_addcols_98 ALTER info1 TYPE varchar(15);
SELECT * FROM alter_addcols_98 WHERE info='aa' ORDER BY 1, 3;
id | info | info1
----+------+-------
1 | aa | 1
2 | aa | 2
3 | aa | 3
5 | aa | 5
(4 rows)
SELECT * FROM alter_addcols_98 WHERE info1='3' ORDER BY 1, 3;
id | info | info1
----+------+-------
3 | aa | 3
(1 row)
ALTER TABLE alter_addcols_98 ALTER info1 TYPE int;
SELECT * FROM alter_addcols_98 WHERE info='aa' ORDER BY 1, 3;
id | info | info1
----+------+-------
1 | aa | 1
2 | aa | 2
3 | aa | 3
5 | aa | 5
(4 rows)
SELECT * FROM alter_addcols_98 WHERE info1='3' ORDER BY 1, 3;
id | info | info1
----+------+-------
3 | aa | 3
(1 row)
CREATE TABLE alter_addcols_102 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN)
partition by range(id)
(
partition p1 values less than (5),
partition p2 values less than (10)
);
CREATE INDEX idx102_1 ON alter_addcols_102 (info1) LOCAL;
INSERT INTO alter_addcols_102 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM alter_addcols_102 WHERE info='aa' ORDER BY 1, 3;
id | info | info1
----+------+-------
1 | aa | 1
2 | aa | 2
3 | aa | 3
5 | aa | 5
(4 rows)
SELECT * FROM alter_addcols_102 WHERE info1>3 ORDER BY 1, 3;
id | info | info1
----+------+-------
4 | 4 | 4
5 | aa | 5
(2 rows)
ALTER TABLE alter_addcols_102 ALTER info1 TYPE varchar(15);
SELECT * FROM alter_addcols_102 WHERE info='aa' ORDER BY 1, 3;
id | info | info1
----+------+-------
1 | aa | 1
2 | aa | 2
3 | aa | 3
5 | aa | 5
(4 rows)
SELECT * FROM alter_addcols_102 WHERE info1='3' ORDER BY 1, 3;
id | info | info1
----+------+-------
3 | aa | 3
(1 row)
ALTER TABLE alter_addcols_102 ALTER info1 TYPE int;
SELECT * FROM alter_addcols_102 WHERE info='aa' ORDER BY 1, 3;
id | info | info1
----+------+-------
1 | aa | 1
2 | aa | 2
3 | aa | 3
5 | aa | 5
(4 rows)
SELECT * FROM alter_addcols_102 WHERE info1='3' ORDER BY 1, 3;
id | info | info1
----+------+-------
3 | aa | 3
(1 row)
DROP TABLE IF EXISTS base_copy_table ;
NOTICE: table "base_copy_table" does not exist, skipping
create table base_copy_table (c_hashid int, c_tinyint tinyint,c_smallint smallint,c_int integer,c_bigint bigint,c_money money,c_numeric numeric,c_real real,c_double double precision,c_decimal decimal,c_varchar varchar,c_char char(30),c_nvarchar2 nvarchar2,c_text text,c_timestamp timestamp with time zone,c_timestamptz timestamp without time zone,c_date date,c_time time without time zone,c_timetz time with time zone,c_interval interval,c_tinterval tinterval,c_smalldatetime smalldatetime,c_bytea bytea,c_boolean boolean,c_inet inet,c_cidr cidr,c_bit bit(10),c_varbit varbit(10),c_oid oid);
copy base_copy_table from '@abs_srcdir@/data/cstore_alter_change_plus_update.dat';
DROP TABLE IF EXISTS alter_addcols_103 ;
NOTICE: table "alter_addcols_103" does not exist, skipping
DROP TABLE IF EXISTS alter_addcols_104 ;
NOTICE: table "alter_addcols_104" does not exist, skipping
create table alter_addcols_103 (c_hashid int, c_tinyint tinyint,c_smallint smallint,c_int integer,c_bigint bigint,c_money money,c_numeric numeric,c_real real,c_double double precision,c_decimal decimal,c_varchar varchar,c_char char(30),c_nvarchar2 nvarchar2,c_text text,c_timestamp timestamp with time zone,c_timestamptz timestamp without time zone,c_date date,c_time time without time zone,c_timetz time with time zone,c_interval interval,c_tinterval tinterval,c_smalldatetime smalldatetime,c_bytea bytea,c_boolean boolean,c_inet inet,c_cidr cidr,c_bit bit(10),c_varbit varbit(10),c_oid oid) with (orientation=column)
partition by range(c_numeric)(
partition create_columnar_transaction_table_010_partition_s1 values less than(1),
partition create_columnar_transaction_table_010_partition_s2 values less than(3),
partition create_columnar_transaction_table_010_partition_s3 values less than(27),
partition create_columnar_transaction_table_010_partition_s4 values less than(1221),
partition create_columnar_transaction_table_010_partition_s5 values less than(861121),
partition create_columnar_transaction_table_010_partition_s6 values less than(maxvalue))
;
create table alter_addcols_104 (c_hashid int, c_tinyint tinyint,c_smallint smallint,c_int integer,c_bigint bigint,c_money money,c_numeric numeric,c_real real,c_double double precision,c_decimal decimal,c_varchar varchar,c_char char(30),c_nvarchar2 nvarchar2,c_text text,c_timestamp timestamp with time zone,c_timestamptz timestamp without time zone,c_date date,c_time time without time zone,c_timetz time with time zone,c_interval interval,c_tinterval tinterval,c_smalldatetime smalldatetime,c_bytea bytea,c_boolean boolean,c_inet inet,c_cidr cidr,c_bit bit(10),c_varbit varbit(10),c_oid oid) with (orientation=column)
partition by range(c_numeric)(
partition create_columnar_transaction_table_010_partition_s1 values less than(1),
partition create_columnar_transaction_table_010_partition_s2 values less than(3),
partition create_columnar_transaction_table_010_partition_s3 values less than(27),
partition create_columnar_transaction_table_010_partition_s4 values less than(1221),
partition create_columnar_transaction_table_010_partition_s5 values less than(861121),
partition create_columnar_transaction_table_010_partition_s6 values less than(maxvalue))
;
insert into alter_addcols_103 select * from base_copy_table;
insert into alter_addcols_104 select * from base_copy_table;
start transaction isolation level read committed;
alter table alter_addcols_103 alter column c_tinyint set data type int;
update alter_addcols_103 set c_tinyint=c_tinyint+10 where c_tinyint<10;
rollback;
(select * from alter_addcols_103 ) minus (select * from alter_addcols_104);
c_hashid | c_tinyint | c_smallint | c_int | c_bigint | c_money | c_numeric | c_real | c_double | c_decimal | c_varchar | c_char | c_nvarchar2 | c_text | c_timestamp | c_timestamptz | c_date | c_time | c_timetz | c_interval | c_tinterval | c_smalldatetime | c_bytea | c_boolean | c_inet | c_cidr | c_bit | c_varbit | c_oid
----------+-----------+------------+-------+----------+---------+-----------+--------+----------+-----------+-----------+--------+-------------+--------+-------------+---------------+--------+--------+----------+------------+-------------+-----------------+---------+-----------+--------+--------+-------+----------+-------
(0 rows)
(select * from alter_addcols_104 ) minus (select * from alter_addcols_103);
c_hashid | c_tinyint | c_smallint | c_int | c_bigint | c_money | c_numeric | c_real | c_double | c_decimal | c_varchar | c_char | c_nvarchar2 | c_text | c_timestamp | c_timestamptz | c_date | c_time | c_timetz | c_interval | c_tinterval | c_smalldatetime | c_bytea | c_boolean | c_inet | c_cidr | c_bit | c_varbit | c_oid
----------+-----------+------------+-------+----------+---------+-----------+--------+----------+-----------+-----------+--------+-------------+--------+-------------+---------------+--------+--------+----------+------------+-------------+-----------------+---------+-----------+--------+--------+-------+----------+-------
(0 rows)
start transaction isolation level read committed;
alter table alter_addcols_103 alter column c_tinyint set data type int;
select count(*) from alter_addcols_103 ;
count
-------
60001
(1 row)
rollback;
start transaction isolation level read committed;
alter table alter_addcols_103 alter column c_tinyint set data type int;
update alter_addcols_103 set c_tinyint=c_tinyint+10 where c_tinyint<10;
alter table alter_addcols_104 alter column c_tinyint set data type int;
alter table alter_addcols_104 alter column c_smallint set data type int;
rollback;
DROP TABLE IF EXISTS alter_addcols_105 ;
NOTICE: table "alter_addcols_105" does not exist, skipping
DROP TABLE IF EXISTS alter_addcols_106 ;
NOTICE: table "alter_addcols_106" does not exist, skipping
create table alter_addcols_105 (c_hashid int, c_tinyint tinyint,c_smallint smallint,c_int integer,c_bigint bigint,c_money money,c_numeric numeric,c_real real,c_double double precision,c_decimal decimal,c_varchar varchar,c_char char(30),c_nvarchar2 nvarchar2,c_text text,c_timestamp timestamp with time zone,c_timestamptz timestamp without time zone,c_date date,c_time time without time zone,c_timetz time with time zone,c_interval interval,c_tinterval tinterval,c_smalldatetime smalldatetime,c_bytea bytea,c_boolean boolean,c_inet inet,c_cidr cidr,c_bit bit(10),c_varbit varbit(10),c_oid oid) with (orientation=column) ;
create table alter_addcols_106 (c_hashid int, c_tinyint tinyint,c_smallint smallint,c_int integer,c_bigint bigint,c_money money,c_numeric numeric,c_real real,c_double double precision,c_decimal decimal,c_varchar varchar,c_char char(30),c_nvarchar2 nvarchar2,c_text text,c_timestamp timestamp with time zone,c_timestamptz timestamp without time zone,c_date date,c_time time without time zone,c_timetz time with time zone,c_interval interval,c_tinterval tinterval,c_smalldatetime smalldatetime,c_bytea bytea,c_boolean boolean,c_inet inet,c_cidr cidr,c_bit bit(10),c_varbit varbit(10),c_oid oid) with (orientation=column) ;
insert into alter_addcols_105 select * from base_copy_table;
insert into alter_addcols_106 select * from base_copy_table;
start transaction isolation level read committed;
alter table alter_addcols_106 alter column c_tinyint set data type int;
update alter_addcols_106 set c_tinyint=c_tinyint+10 where c_tinyint<10;
rollback;
(select * from alter_addcols_106 ) minus (select * from alter_addcols_105);
c_hashid | c_tinyint | c_smallint | c_int | c_bigint | c_money | c_numeric | c_real | c_double | c_decimal | c_varchar | c_char | c_nvarchar2 | c_text | c_timestamp | c_timestamptz | c_date | c_time | c_timetz | c_interval | c_tinterval | c_smalldatetime | c_bytea | c_boolean | c_inet | c_cidr | c_bit | c_varbit | c_oid
----------+-----------+------------+-------+----------+---------+-----------+--------+----------+-----------+-----------+--------+-------------+--------+-------------+---------------+--------+--------+----------+------------+-------------+-----------------+---------+-----------+--------+--------+-------+----------+-------
(0 rows)
(select * from alter_addcols_105 ) minus (select * from alter_addcols_106);
c_hashid | c_tinyint | c_smallint | c_int | c_bigint | c_money | c_numeric | c_real | c_double | c_decimal | c_varchar | c_char | c_nvarchar2 | c_text | c_timestamp | c_timestamptz | c_date | c_time | c_timetz | c_interval | c_tinterval | c_smalldatetime | c_bytea | c_boolean | c_inet | c_cidr | c_bit | c_varbit | c_oid
----------+-----------+------------+-------+----------+---------+-----------+--------+----------+-----------+-----------+--------+-------------+--------+-------------+---------------+--------+--------+----------+------------+-------------+-----------------+---------+-----------+--------+--------+-------+----------+-------
(0 rows)
start transaction isolation level read committed;
alter table alter_addcols_106 alter column c_tinyint set data type int;
select count(*) from alter_addcols_106 ;
count
-------
60001
(1 row)
rollback;
start transaction isolation level read committed;
alter table alter_addcols_106 alter column c_tinyint set data type int;
update alter_addcols_106 set c_tinyint=c_tinyint+10 where c_tinyint<10;
alter table alter_addcols_105 alter column c_tinyint set data type int;
alter table alter_addcols_105 alter column c_smallint set data type int;
rollback;
-- support SET STATISTICS feature
CREATE TABLE alter_set_stat_00 (id int, info int) with (orientation = column);
ALTER TABLE alter_set_stat_00 ALTER COLUMN info SET STATISTICS 1000;
select attstattarget from pg_attribute where attrelid = ( select oid from pg_class where relname = 'alter_set_stat_00' ) and attnum = 2;
attstattarget
---------------
1000
(1 row)
ALTER TABLE alter_set_stat_00 ALTER COLUMN info SET STATISTICS PERCENT 80;
select attstattarget from pg_attribute where attrelid = ( select oid from pg_class where relname = 'alter_set_stat_00' ) and attnum = 2;
attstattarget
---------------
-81
(1 row)
DROP TABLE alter_set_stat_00;
drop schema cstore_alter_table_6 cascade;
NOTICE: drop cascades to 8 other objects
DETAIL: drop cascades to table alter_addcols_101
drop cascades to table alter_addcols_98
drop cascades to table alter_addcols_102
drop cascades to table base_copy_table
drop cascades to table alter_addcols_103
drop cascades to table alter_addcols_104
drop cascades to table alter_addcols_105
drop cascades to table alter_addcols_106