-- 6. test table has tablespace
\! rm -fr '/data2/jiangyan/openGauss-server/src/test/regress/testtablespace/hw_partition_sql_adapt_ts1'
\! mkdir '/data2/jiangyan/openGauss-server/src/test/regress/testtablespace/hw_partition_sql_adapt_ts1'
create tablespace hw_partition_sql_adapt_ts1 location '/data2/jiangyan/openGauss-server/src/test/regress/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;
relname | spcname
---------------------------------------+----------------------------
test_modify_column_type_tablespace | hw_partition_sql_adapt_ts1
test_modify_column_type_tablespace_p1 | hw_partition_sql_adapt_ts1
test_modify_column_type_tablespace_p2 | hw_partition_sql_adapt_ts1
test_modify_column_type_tablespace_p3 | hw_partition_sql_adapt_ts1
(4 rows)
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;
relname | spcname
---------------------------------------+----------------------------
test_modify_column_type_tablespace | hw_partition_sql_adapt_ts1
test_modify_column_type_tablespace_p1 | hw_partition_sql_adapt_ts1
test_modify_column_type_tablespace_p2 | hw_partition_sql_adapt_ts1
test_modify_column_type_tablespace_p3 | hw_partition_sql_adapt_ts1
(4 rows)
insert into test_modify_column_type_tablespace values (1, 1, 'c1', 1);
select * from test_modify_column_type_tablespace order by 1, 2;
a | b | c | d
---+---+----+---
0 | 0 | 0 | 0
1 | 1 | c1 | 1
(2 rows)
-- 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;
relname | spcname
--------------------------------------------+----------------------------
test_modify_column_type_tablespace_null |
test_modify_column_type_tablespace_null_p1 |
test_modify_column_type_tablespace_null_p2 | hw_partition_sql_adapt_ts1
test_modify_column_type_tablespace_null_p3 | hw_partition_sql_adapt_ts1
(4 rows)
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;
relname | spcname
--------------------------------------------+----------------------------
test_modify_column_type_tablespace_null |
test_modify_column_type_tablespace_null_p1 |
test_modify_column_type_tablespace_null_p2 | hw_partition_sql_adapt_ts1
test_modify_column_type_tablespace_null_p3 | hw_partition_sql_adapt_ts1
(4 rows)
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;
a | b | c | d
---+---+----+---
0 | 0 | 0 | 0
1 | 1 | c1 | 1
(2 rows)
-- 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;
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view temp_view depends on column "c"
-- 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;
a | b | c | d
---+---+----+---
0 | 0 | 0 | 0
1 | 1 | c1 | 1
(2 rows)
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;
a | b | c | d
---+---+----+---
0 | 0 | 0 | 0
1 | 1 | c1 | 1
2 | 2 | c2 | 2
(3 rows)
-- 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;
a | b | c | d
---+---+----+----
0 | 0 | 0 | 0
1 | 1 | c1 | 1
2 | 2 | c2 | 2
3 | 3 | c3 | d3
(4 rows)
rollback;
-- failed
insert into test_modify_column_type_rollback values (4, 4, 'c4', 'd4');
ERROR: invalid input syntax for integer: "d4"
LINE 1: ... test_modify_column_type_rollback values (4, 4, 'c4', 'd4');
^
CONTEXT: referenced column: d
select * from test_modify_column_type_rollback order by 1,2,3,4;
a | b | c | d
---+---+----+---
0 | 0 | 0 | 0
1 | 1 | c1 | 1
2 | 2 | c2 | 2
(3 rows)
-- 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;
a
---
1
(1 row)
-- 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;
a
---
2
(1 row)
-- 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);
ERROR: inserted partition key does not map to any table partition
select a from test_drop_range_key_column_default where a=2;
a
---
(0 rows)
-- 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;
a
---
2
(1 row)
-- 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;
b
---
1
(1 row)
-- 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;
b
---
2
(1 row)
-- 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;
b
---
(0 rows)
-- 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;
b
---
(0 rows)
-- 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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | c | 200
(1 row)
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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | c | -1
(1 row)
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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | c | 0
(1 row)
-- 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;
ERROR: statistics target -2 is too low
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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | c | 0
(1 row)
-- 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;
WARNING: lowering statistics target to 10000
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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | c | 10000
(1 row)
---- 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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | a | 200
(1 row)
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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | a | -1
(1 row)
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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | a | 0
(1 row)
-- 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;
ERROR: statistics target -2 is too low
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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | a | 0
(1 row)
-- 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;
WARNING: lowering statistics target to 10000
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';
relname | attname | attstattarget
---------------------+---------+---------------
test_set_statistics | a | 10000
(1 row)
-- 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';
relname | attname | attoptions
---------------------------+---------+----------------
test_set_attribute_option | c | {n_distinct=1}
(1 row)
-- 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';
relname | attname | attoptions
---------------------------+---------+----------------
test_set_attribute_option | c | {n_distinct=0}
(1 row)
-- 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';
relname | attname | attoptions
---------------------------+---------+-----------------
test_set_attribute_option | c | {n_distinct=-1}
(1 row)
-- 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';
relname | attname | attoptions
---------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_set_attribute_option | c | {n_distinct=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000}
(1 row)
-- failed: too low
alter table test_set_attribute_option alter column c set (n_distinct=-2);
ERROR: value -2 out of bounds for option "n_distinct"
DETAIL: Valid values are between "-1.000000" and "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';
relname | attname | attoptions
---------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_set_attribute_option | c | {n_distinct=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000}
(1 row)
-- failed: too big
alter table test_set_attribute_option alter column c set (n_distinct=279769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
ERROR: invalid value for floating point option "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';
relname | attname | attoptions
---------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_set_attribute_option | c | {n_distinct=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000}
(1 row)
-- 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';
relname | attname | attoptions
---------------------------+---------+----------------
test_set_attribute_option | a | {n_distinct=1}
(1 row)
-- 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';
relname | attname | attoptions
---------------------------+---------+----------------
test_set_attribute_option | a | {n_distinct=0}
(1 row)
-- 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';
relname | attname | attoptions
---------------------------+---------+-----------------
test_set_attribute_option | a | {n_distinct=-1}
(1 row)
-- 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';
relname | attname | attoptions
---------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_set_attribute_option | a | {n_distinct=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000}
(1 row)
-- failed: too low
alter table test_set_attribute_option alter column a set (n_distinct=-2);
ERROR: value -2 out of bounds for option "n_distinct"
DETAIL: Valid values are between "-1.000000" and "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';
relname | attname | attoptions
---------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_set_attribute_option | a | {n_distinct=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000}
(1 row)
-- failed: too big
alter table test_set_attribute_option alter column a set (n_distinct=279769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
ERROR: invalid value for floating point option "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';
relname | attname | attoptions
---------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_set_attribute_option | a | {n_distinct=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000}
(1 row)
-- 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);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | c |
(1 row)
-- success
alter table test_set_attribute_option alter column c set (n_distinct_inherited=0);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | c |
(1 row)
-- success
alter table test_set_attribute_option alter column c set (n_distinct_inherited=-1);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | c |
(1 row)
-- success
alter table test_set_attribute_option alter column c set (n_distinct_inherited=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | c |
(1 row)
-- failed: too low
alter table test_set_attribute_option alter column c set (n_distinct_inherited=-2);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | c |
(1 row)
-- failed: too big
alter table test_set_attribute_option alter column c set (n_distinct_inherited=279769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | c |
(1 row)
-- recovery
alter table test_set_attribute_option alter column c reset (n_distinct_inherited);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
---- b. column is partition key
-- success
alter table test_set_attribute_option alter column a set (n_distinct_inherited=1);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | a |
(1 row)
-- success
alter table test_set_attribute_option alter column a set (n_distinct_inherited=0);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | a |
(1 row)
-- success
alter table test_set_attribute_option alter column a set (n_distinct_inherited=-1);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | a |
(1 row)
-- success
alter table test_set_attribute_option alter column a set (n_distinct_inherited=179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | a |
(1 row)
-- failed: too low
alter table test_set_attribute_option alter column a set (n_distinct_inherited=-2);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | a |
(1 row)
-- failed: too big
alter table test_set_attribute_option alter column a set (n_distinct_inherited=279769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation
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';
relname | attname | attoptions
---------------------------+---------+------------
test_set_attribute_option | a |
(1 row)
-- recovery
alter table test_set_attribute_option alter column a reset (n_distinct_inherited);
ERROR: Un-support feature
DETAIL: Forbid to set option "n_distinct_inherited" for both row and column relation