-- problem:
-- SQL clause about CREATE TABLE for compression
-- case 1: default compress level
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column ) ;
SELECT pg_relation_with_compression('cstore_create_clause_00');
pg_relation_with_compression
------------------------------
t
(1 row)
\dS+ cstore_create_clause_00
Table "public.cstore_create_clause_00"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
Has OIDs: no
Options: orientation=column, compression=low
DROP TABLE cstore_create_clause_00;
-- case 2: no compress
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , compression = no ) ;
SELECT pg_relation_with_compression('cstore_create_clause_00');
pg_relation_with_compression
------------------------------
f
(1 row)
\dS+ cstore_create_clause_00
Table "public.cstore_create_clause_00"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
Has OIDs: no
Options: orientation=column, compression=no
DROP TABLE cstore_create_clause_00;
-- case 3: compress yes
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , compression = yes ) ;
SELECT pg_relation_with_compression('cstore_create_clause_00');
pg_relation_with_compression
------------------------------
t
(1 row)
\dS+ cstore_create_clause_00
Table "public.cstore_create_clause_00"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
Has OIDs: no
Options: orientation=column, compression=yes
DROP TABLE cstore_create_clause_00;
-- case 4: compress low
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , compression = low ) ;
SELECT pg_relation_with_compression('cstore_create_clause_00');
pg_relation_with_compression
------------------------------
t
(1 row)
\dS+ cstore_create_clause_00
Table "public.cstore_create_clause_00"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
Has OIDs: no
Options: orientation=column, compression=low
DROP TABLE cstore_create_clause_00;
-- case 5: compress middle
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , compression = middle ) ;
SELECT pg_relation_with_compression('cstore_create_clause_00');
pg_relation_with_compression
------------------------------
t
(1 row)
\dS+ cstore_create_clause_00
Table "public.cstore_create_clause_00"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
Has OIDs: no
Options: orientation=column, compression=middle
DROP TABLE cstore_create_clause_00;
-- case 4: compress high
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , compression = high ) ;
SELECT pg_relation_with_compression('cstore_create_clause_00');
pg_relation_with_compression
------------------------------
t
(1 row)
\dS+ cstore_create_clause_00
Table "public.cstore_create_clause_00"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
Has OIDs: no
Options: orientation=column, compression=high
DROP TABLE cstore_create_clause_00;
-- case 5: invalid compress type
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , compression = zlib ) ;
ERROR: Invalid string for "COMPRESSION" option
DETAIL: Valid string are "no", "yes", "low", "middle", "high" for non-dfs table.
-- case 6: max_batchrow option test
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , max_batchrow = 10000) ;
SELECT pg_relation_with_compression('cstore_create_clause_00');
pg_relation_with_compression
------------------------------
t
(1 row)
\dS+ cstore_create_clause_00
Table "public.cstore_create_clause_00"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
Has OIDs: no
Options: orientation=column, max_batchrow=10000, compression=low
DROP TABLE cstore_create_clause_00;
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , max_batchrow = 60000) ;
SELECT pg_relation_with_compression('cstore_create_clause_00');
pg_relation_with_compression
------------------------------
t
(1 row)
\dS+ cstore_create_clause_00
Table "public.cstore_create_clause_00"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
Has OIDs: no
Options: orientation=column, max_batchrow=60000, compression=low
DROP TABLE cstore_create_clause_00;
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , max_batchrow = 65536) ;
ERROR: value 65536 out of bounds for option "max_batchrow"
DETAIL: Valid values are between "10000" and "60000".
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , max_batchrow = 9999) ;
ERROR: value 9999 out of bounds for option "max_batchrow"
DETAIL: Valid values are between "10000" and "60000".
-- case 7: deltarow_threshold option test
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , deltarow_threshold = 10000) ;
ERROR: value 10000 out of bounds for option "deltarow_threshold"
DETAIL: Valid values are between "0" and "9999".
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , deltarow_threshold = -1) ;
ERROR: value -1 out of bounds for option "deltarow_threshold"
DETAIL: Valid values are between "0" and "9999".
CREATE TABLE cstore_create_clause_00
(
a int,
b int
) with ( orientation = column , deltarow_threshold = 0) ;
SELECT pg_relation_with_compression('cstore_create_clause_00');
pg_relation_with_compression
------------------------------
t
(1 row)
\dS+ cstore_create_clause_00;
Table "public.cstore_create_clause_00"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | | plain | |
b | integer | | plain | |
Has OIDs: no
Options: orientation=column, deltarow_threshold=0, compression=low
DROP TABLE cstore_create_clause_00;