-- 6. test table has tablespace
\! rm -fr '@testtablespace@/hw_partition_sql_adapt_ts1'
\! mkdir '@testtablespace@/hw_partition_sql_adapt_ts1'
create tablespace hw_partition_sql_adapt_ts1 location '@testtablespace@/hw_partition_sql_adapt_ts1';
-- a. partitioned table has tablespace
create table test_modify_column_type_tablespace (a int, b int, c int, d int)
tablespace hw_partition_sql_adapt_ts1
partition by range (a, b)
(
partition test_modify_column_type_tablespace_p1 values less than(1, 1),
partition test_modify_column_type_tablespace_p2 values less than(4, 4)tablespace hw_partition_sql_adapt_ts1,
partition test_modify_column_type_tablespace_p3 values less than(7, 7)tablespace hw_partition_sql_adapt_ts1
);
insert into test_modify_column_type_tablespace values (0, 0, 0, 0);
select p.relname, t.spcname from pg_partition p left join pg_tablespace t on (p.reltablespace = t.oid) where p.parentid in (select oid from pg_class where relname = 'test_modify_column_type_tablespace') order by 1,2;
alter table test_modify_column_type_tablespace alter column c type text;
select p.relname, t.spcname from pg_partition p left join pg_tablespace t on (p.reltablespace = t.oid) where p.parentid in (select oid from pg_class where relname = 'test_modify_column_type_tablespace') order by 1,2;
insert into test_modify_column_type_tablespace values (1, 1, 'c1', 1);
select * from test_modify_column_type_tablespace order by 1, 2;
-- clean
drop table test_modify_column_type_tablespace;
-- b. partitioned table has not tablespace
create table test_modify_column_type_tablespace_null (a int, b int, c int, d int)
partition by range (a, b)
(
partition test_modify_column_type_tablespace_null_p1 values less than(1, 1),
partition test_modify_column_type_tablespace_null_p2 values less than(4, 4)tablespace hw_partition_sql_adapt_ts1,
partition test_modify_column_type_tablespace_null_p3 values less than(7, 7)tablespace hw_partition_sql_adapt_ts1
);
insert into test_modify_column_type_tablespace_null values (0, 0, 0, 0);
select p.relname, t.spcname from pg_partition p left join pg_tablespace t on (p.reltablespace = t.oid) where p.parentid in (select oid from pg_class where relname = 'test_modify_column_type_tablespace_null') order by 1,2;
alter table test_modify_column_type_tablespace_null alter column c type text;
select p.relname, t.spcname from pg_partition p left join pg_tablespace t on (p.reltablespace = t.oid) where p.parentid in (select oid from pg_class where relname = 'test_modify_column_type_tablespace_null') order by 1,2;
insert into test_modify_column_type_tablespace_null values (1, 1, 'c1', 1);
select * from test_modify_column_type_tablespace_null order by 1,2,3,4;
-- clean
drop table test_modify_column_type_tablespace_null;
-- clean tablespace
drop tablespace hw_partition_sql_adapt_ts1;
-- 7. test make view on partitioned table
create table test_modify_column_type_view (a int, b int, c int, d int)
partition by range (a, b)
(
partition test_modify_column_type_view_p1 values less than(1, 1),
partition test_modify_column_type_view_p2 values less than(4, 4),
partition test_modify_column_type_view_p3 values less than(7, 7)
);
insert into test_modify_column_type_view values (0, 0, 0, 0);
create view temp_view as select * from test_modify_column_type_view;
-- failed
alter table test_modify_column_type_view alter column c type text;
-- clean
drop view temp_view;
drop table test_modify_column_type_view;
-- 8. test transaction commit and rollack
create table test_modify_column_type_rollback (a int, b int, c int, d int)
partition by range (a, b)
(
partition test_modify_column_type_rollback_p1 values less than(1, 1),
partition test_modify_column_type_rollback_p2 values less than(4, 4),
partition test_modify_column_type_rollback_p3 values less than(7, 7)
);
insert into test_modify_column_type_rollback values (0, 0, 0, 0);
-- a. commit
start transaction;
alter table test_modify_column_type_rollback alter column c type text;
insert into test_modify_column_type_rollback values (1, 1, 'c1', 1);
select * from test_modify_column_type_rollback order by 1,2,3,4;
commit;
--success
insert into test_modify_column_type_rollback values (2, 2, 'c2', 2);
select * from test_modify_column_type_rollback order by 1,2,3,4;
-- b. rollback
start transaction;
alter table test_modify_column_type_rollback alter column d type text;
insert into test_modify_column_type_rollback values (3, 3, 'c3', 'd3');
select * from test_modify_column_type_rollback order by 1,2,3,4;
rollback;
-- failed
insert into test_modify_column_type_rollback values (4, 4, 'c4', 'd4');
select * from test_modify_column_type_rollback order by 1,2,3,4;
-- clean
drop table test_modify_column_type_rollback;
CREATE TABLE TABLE_MULTI_BUFFER_MANAGEMENT_UPDATE_AND_EXAMINE_114(C_INTEGER INTEGER NOT NULL, C_SMALLINT SMALLINT NOT NULL, C_BIGINT BIGINT NOT NULL, C_DECIMAL DECIMAL , C_NUMERIC NUMERIC CHECK(C_NUMERIC <> 3.24), C_REAL REAL , C_DOUBLE DOUBLE PRECISION , C_BIGSERIAL BIGINT , C_MONEY MONEY , C_CHARACTER CHARACTER VARYING(4096) , C_VARCHAR VARCHAR(4096) , C_CHAR CHAR(4096) , C_TEXT TEXT , C_BYTEA BYTEA , C_TIMESTAMP_1 TIMESTAMP WITHOUT TIME ZONE ,C_TIMESTAMP_2 TIMESTAMP WITH TIME ZONE , C_BOOLEAN BOOLEAN , C_POINT POINT , C_LSEG LSEG , C_BOX BOX , C_PATH PATH , C_POLYGON POLYGON , C_CIRCLE CIRCLE , C_CIDR CIDR , C_INET INET , C_MACADDR MACADDR , C_BIT_1 BIT(6) , C_BIT_2 BIT VARYING(6) , C_OID OID , C_REGPROC REGPROC , C_REGPROCEDURE REGPROCEDURE , C_REGOPERATOR REGOPERATOR , C_REGCLASS REGCLASS , C_REGTYPE REGTYPE , C_CHARACTER_1 CHARACTER(4096) , C_INTERVAL INTERVAL , C_DATE DATE , C_TIMESTAMP_3 TIME WITHOUT TIME ZONE , C_TIMESTAMP_4 TIME WITH TIME ZONE,C_NUMBER INT,C_NVARCHAR2 CHARACTER VARYING(4096),C_SMALLDATETIME TIMESTAMP,C_TINYINT INT, C_SERIAL INT)WITH (FILLFACTOR =70)
partition by range (C_INTEGER,C_CHAR)
(
partition TABLE_PT_114_1 values less than (500,'f'),
partition TABLE_PT_114_2 values less than (1000,'g'),
partition TABLE_PT_114_3 values less than (3000,'g')
)enable row movement;
insert into TABLE_MULTI_BUFFER_MANAGEMENT_UPDATE_AND_EXAMINE_114(C_INTEGER , C_SMALLINT , C_BIGINT , C_DECIMAL , C_NUMERIC, C_REAL , C_DOUBLE , C_BIGSERIAL , C_MONEY , C_CHARACTER , C_VARCHAR , C_CHAR , C_TEXT, C_BYTEA , C_TIMESTAMP_1 ,C_TIMESTAMP_2 , C_BOOLEAN , C_POINT, C_LSEG, C_BOX , C_PATH , C_POLYGON, C_CIRCLE, C_CIDR , C_INET, C_MACADDR, C_BIT_1, C_BIT_2, C_OID, C_REGPROC , C_REGPROCEDURE , C_REGOPERATOR , C_REGCLASS , C_REGTYPE , C_CHARACTER_1 , C_INTERVAL , C_DATE , C_TIMESTAMP_3 , C_TIMESTAMP_4 ,C_NUMBER ,C_NVARCHAR2 ,C_SMALLDATETIME ,C_TINYINT) SELECT trunc(random() * 2999 + 1), 1, 3, 0.124, 3.234, 1.2345, 0.1712, 21474836478, 1000.00, 'aaaaa', 'BBBBBBBB', 'ccccccccc', 'hello', '1101', '2004-10-19 10:23:01', '2004-10-19 10:23:01 +2', true, '(1,3)', '((-1,-7), (-4,2))', '((10, 3), (3,19))', '((4,2),(5,1))', '((1,2),(3,4),(5,6))', '<(1,2),3>', '10.1.2.3', '192.168.61.68', '08-00-2b-01-02-03', '101000', '101111', 123, 'pi', 'sqrt(numeric)', '-(bigint, bigint)', 'pg_am', 'float', 'aaaaa', '1 12:59:10', '2013-04-16', '13:50:00', '13:50:00 +8',1.2,'FG','2013-04-16',99 FROM generate_series(1,1000);
create index INDEX_BUFFER_PT_114 on TABLE_MULTI_BUFFER_MANAGEMENT_UPDATE_AND_EXAMINE_114 using BTREE(C_INTEGER,C_CHAR) local( partition TABLE_PT_114_1,partition TABLE_PT_114_2,partition TABLE_PT_114_3);
--
---- pgxc is unsupported autonomous transaction
--
--start transaction;
--pragma autonomous_transaction;
--alter table TABLE_MULTI_BUFFER_MANAGEMENT_UPDATE_AND_EXAMINE_114 alter column C_SMALLINT type float;
--commit;
--commit;
drop table TABLE_MULTI_BUFFER_MANAGEMENT_UPDATE_AND_EXAMINE_114;
create table hw_partition_sql_adapt_tt (c_id int,c_data varchar(30))
partition by range(c_id)
(
partition hw_partition_sql_adapt_tt_part1 values less than (10),
partition hw_partition_sql_adapt_tt_part2 values less than (20)
)ENABLE ROW MOVEMENT;
insert into hw_partition_sql_adapt_tt values(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
insert into hw_partition_sql_adapt_tt values(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
insert into hw_partition_sql_adapt_tt values(11,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
insert into hw_partition_sql_adapt_tt values(11,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
create index t_index on hw_partition_sql_adapt_tt(c_data) local;
alter table hw_partition_sql_adapt_tt alter column c_data type text;
update hw_partition_sql_adapt_tt set c_data = 'aaaaaaaaaaaaaaa';
drop table hw_partition_sql_adapt_tt;
--
---- ALTER TABLE ALTER [ COLUMN ] column DROP DEFAULT
--
---- 1). check drop default value of column which is partition key of range partitioned table
create table test_drop_range_key_column_default (a int default 2)
partition by range(a)
(
partition test_drop_range_key_column_default_p1 values less than (1),
partition test_drop_range_key_column_default_p2 values less than (4),
partition test_drop_range_key_column_default_p3 values less than (7)
);
-- success: insert into ordinary value
insert into test_drop_range_key_column_default values (1);
select a from test_drop_range_key_column_default where a=1;
-- success: insert into default value
insert into test_drop_range_key_column_default values (default);
select a from test_drop_range_key_column_default where a=2;
-- delete table
delete from test_drop_range_key_column_default;
-- success: drop column default value
alter table test_drop_range_key_column_default alter a drop default;
-- success: insert into defaule value, fail insert
insert into test_drop_range_key_column_default values (default);
select a from test_drop_range_key_column_default where a=2;
-- success: insert into value behind droping column default value
insert into test_drop_range_key_column_default values (2);
select a from test_drop_range_key_column_default where a=2;
-- clean
drop table test_drop_range_key_column_default;
---- 2). check drop default value of column which is ordinary column of range partitioned table
create table test_drop_range_ordinary_column_default (a int, b int default 2)
partition by range(a)
(
partition test_drop_range_ordinary_column_default_p1 values less than (1),
partition test_drop_range_ordinary_column_default_p2 values less than (4),
partition test_drop_range_ordinary_column_default_p3 values less than (7)
);
-- success: insert into ordinary value
insert into test_drop_range_ordinary_column_default values (1, 1);
select b from test_drop_range_ordinary_column_default where a=1;
-- success: insert into default value
insert into test_drop_range_ordinary_column_default values (2, default);
select b from test_drop_range_ordinary_column_default where a=2;
-- delete table
delete from test_drop_range_ordinary_column_default;
-- success: drop column default value
alter table test_drop_range_ordinary_column_default alter b drop default;
-- success: insert into defaule value, fail insert
insert into test_drop_range_ordinary_column_default values (3, default);
select b from test_drop_range_ordinary_column_default where a=2;
-- success: insert into value behind droping column default value
insert into test_drop_range_ordinary_column_default values (4, 2);
select b from test_drop_range_ordinary_column_default where a=2;
-- clean
drop table test_drop_range_ordinary_column_default;
---- 3). check drop default value of column which is partition key of interval partitioned table
--create table test_drop_interval_key_column_default (a int default 2)
--partition by range(a)
--interval (2)
--(
-- partition p1 values less than (1),
-- partition p2 values less than (4),
-- partition p3 values less than (7)
--);
--
---- success: insert into ordinary value
--insert into test_drop_interval_key_column_default values (1);
--select a from test_drop_interval_key_column_default where a=1;
--
---- success: insert into default value
--insert into test_drop_interval_key_column_default values (default);
--select a from test_drop_interval_key_column_default where a=2;
--
---- delete table
--delete from test_drop_interval_key_column_default;
--
---- success: drop column default value
--alter table test_drop_interval_key_column_default alter a drop default;
--
---- success: insert into defaule value, fail insert
--insert into test_drop_interval_key_column_default values (default);
--select a from test_drop_interval_key_column_default where a=2;
--
---- success: insert into value behind droping column default value
--insert into test_drop_interval_key_column_default values (2);
--select a from test_drop_interval_key_column_default where a=2;
--
---- clean
--drop table test_drop_interval_key_column_default;
---- 4). check drop default value of column which is ordinary column of interval partitioned table
--create table test_drop_interval_ordinary_column_default (a int, b int default 2)
--partition by range(a)
--interval (2)
--(
-- partition p1 values less than (1),
-- partition p2 values less than (4),
-- partition p3 values less than (7)
--);
--
---- success: insert into ordinary value
--insert into test_drop_interval_ordinary_column_default values (1, 1);
--select b from test_drop_interval_ordinary_column_default where a=1;
--
---- success: insert into default value on range partition
--insert into test_drop_interval_ordinary_column_default values (2, default);
--select b from test_drop_interval_ordinary_column_default where a=2;
--
---- faild
--insert into test_drop_interval_ordinary_column_default values (10, default);
--select b from test_drop_interval_ordinary_column_default where a=10;
--
---- delete table
--delete from test_drop_interval_ordinary_column_default;
--
---- success: drop column default value
--alter table test_drop_interval_ordinary_column_default alter b drop default;
--
---- success: insert into defaule value, fail insert
--insert into test_drop_interval_ordinary_column_default values (3, default);
--select b from test_drop_interval_ordinary_column_default where a=2;
--
---- success: insert into value behind droping column default value
--insert into test_drop_interval_ordinary_column_default values (4, 2);
--select b from test_drop_interval_ordinary_column_default where a=2;
--
---- clean
--drop table test_drop_interval_ordinary_column_default;
--
---- ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] ALTER [ COLUMN ] column SET STATISTICS integer
--
---- This form sets the per-column statistics-gathering target for subsequent ANALYZE operations.
---- The target can be set in the range 0 to 10000; alternatively, set it to -1 to revert to using
---- the system default statistics target (default_statistics_target=100).
----
---- The target value sets the maximum number of entries in the most-common-value list and the
---- maximum number of bins in the histogram. The default target value is 100, but this can be
---- adjusted up or down to trade off accuracy of planner estimates against the time taken for
---- ANALYZE and the amount of space occupied in pg_statistic. In particular, setting the
---- statistics target to zero disables collection of statistics for that column. It might be
---- useful to do that for columns that are never used as part of the WHERE, GROUP BY, or
---- ORDER BY clauses of queries, since the planner will have no use for statistics on such columns.
create table test_set_statistics (a int, b int, c int, d int)
partition by range (a, b)
(
partition test_set_statistics_p1 values less than(1, 1),
partition test_set_statistics_p2 values less than(4, 4),
partition test_set_statistics_p3 values less than(7, 7)
);
---- a. column is not partition key
-- success
alter table test_set_statistics alter c set statistics 200;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='c';
alter table test_set_statistics alter c set statistics -1;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='c';
alter table test_set_statistics alter c set statistics 0;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='c';
-- failed: value of statistics is too low, and value of statistics will be not changed
alter table test_set_statistics alter c set statistics -2;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='c';
-- failed: value of statistics is too big, and value of statistics will be changed to 10000
alter table test_set_statistics alter c set statistics 10001;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='c';
---- b. column is partition key
-- success
alter table test_set_statistics alter a set statistics 200;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='a';
alter table test_set_statistics alter a set statistics -1;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='a';
alter table test_set_statistics alter a set statistics 0;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='a';
-- failed: value of statistics is too low, and value of statistics will be not changed
alter table test_set_statistics alter a set statistics -2;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='a';
-- failed: value of statistics is too big, and value of statistics will be changed to 10000
alter table test_set_statistics alter a set statistics 10001;
select c.relname, a.attname, a.attstattarget from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_statistics' and a.attname='a';
-- clean
drop table test_set_statistics;
--
---- ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )
--
create table test_set_attribute_option (a int, b int, c int, d int)
partition by range (a, b)
(
partition test_set_attribute_option_p1 values less than(1, 1),
partition test_set_attribute_option_p2 values less than(4, 4),
partition test_set_attribute_option_p3 values less than(7, 7)
);
insert into test_set_attribute_option values (0, 0, 0, 0);
------ 1. set (n_distinct)
---- a. column is not partition key
-- success
alter table test_set_attribute_option alter column c set (n_distinct=1);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- success
alter table test_set_attribute_option alter column c set (n_distinct=0);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- success
alter table test_set_attribute_option alter column c set (n_distinct=-1);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- success
alter table test_set_attribute_option alter column c set (n_distinct=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- failed: too low
alter table test_set_attribute_option alter column c set (n_distinct=-2);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- failed: too big
alter table test_set_attribute_option alter column c set (n_distinct=279769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- recovery
alter table test_set_attribute_option alter column c reset (n_distinct);
---- b. column is partition key
-- success
alter table test_set_attribute_option alter column a set (n_distinct=1);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- success
alter table test_set_attribute_option alter column a set (n_distinct=0);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- success
alter table test_set_attribute_option alter column a set (n_distinct=-1);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- success
alter table test_set_attribute_option alter column a set (n_distinct=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- failed: too low
alter table test_set_attribute_option alter column a set (n_distinct=-2);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- failed: too big
alter table test_set_attribute_option alter column a set (n_distinct=279769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- recovery
alter table test_set_attribute_option alter column a reset (n_distinct);
------ 2. set (n_distinct_inherited)
---- a. column is not partition key
-- success
alter table test_set_attribute_option alter column c set (n_distinct_inherited=1);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- success
alter table test_set_attribute_option alter column c set (n_distinct_inherited=0);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- success
alter table test_set_attribute_option alter column c set (n_distinct_inherited=-1);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- success
alter table test_set_attribute_option alter column c set (n_distinct_inherited=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- failed: too low
alter table test_set_attribute_option alter column c set (n_distinct_inherited=-2);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- failed: too big
alter table test_set_attribute_option alter column c set (n_distinct_inherited=279769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='c';
-- recovery
alter table test_set_attribute_option alter column c reset (n_distinct_inherited);
---- b. column is partition key
-- success
alter table test_set_attribute_option alter column a set (n_distinct_inherited=1);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- success
alter table test_set_attribute_option alter column a set (n_distinct_inherited=0);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- success
alter table test_set_attribute_option alter column a set (n_distinct_inherited=-1);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- success
alter table test_set_attribute_option alter column a set (n_distinct_inherited=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- failed: too low
alter table test_set_attribute_option alter column a set (n_distinct_inherited=-2);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- failed: too big
alter table test_set_attribute_option alter column a set (n_distinct_inherited=279769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
select c.relname, a.attname, a.attoptions from pg_class c, pg_attribute a where c.oid=a.attrelid and c.relname='test_set_attribute_option' and a.attname='a';
-- recovery
alter table test_set_attribute_option alter column a reset (n_distinct_inherited);