From: Ildus Kurbangaliev Date: Mon, 18 Jun 2018 15:59:52 +0300 Subject: [PATCH 7/8] Add tests for compression methods Signed-off-by: Ildus Kurbangaliev --- contrib/test_decoding/expected/ddl.out | 50 +-- src/backend/access/compression/cm_zlib.c | 2 +- src/test/regress/expected/copy2.out | 8 +- src/test/regress/expected/create_cm.out | 405 +++++++++++++++++ src/test/regress/expected/create_cm_1.out | 409 ++++++++++++++++++ src/test/regress/expected/create_table.out | 132 +++--- .../regress/expected/create_table_like.out | 68 +-- src/test/regress/expected/domain.out | 16 +- src/test/regress/expected/foreign_data.out | 258 +++++------ src/test/regress/expected/inherit.out | 138 +++--- src/test/regress/expected/insert.out | 118 ++--- src/test/regress/expected/opr_sanity.out | 4 +- src/test/regress/expected/publication.out | 40 +- .../regress/expected/replica_identity.out | 14 +- src/test/regress/expected/rowsecurity.out | 16 +- src/test/regress/expected/rules.out | 20 +- src/test/regress/expected/sanity_check.out | 3 + src/test/regress/expected/update.out | 16 +- src/test/regress/parallel_schedule | 1 + src/test/regress/serial_schedule | 1 + src/test/regress/sql/create_cm.sql | 203 +++++++++ src/test/regress/sql/opr_sanity.sql | 4 +- 22 files changed, 1476 insertions(+), 450 deletions(-) create mode 100644 src/test/regress/expected/create_cm.out create mode 100644 src/test/regress/expected/create_cm_1.out create mode 100644 src/test/regress/sql/create_cm.sql diff --git a/contrib/test_decoding/expected/ddl.out b/contrib/test_decoding/expected/ddl.out index 2bd28e6d15..01947e0696 100644 --- a/contrib/test_decoding/expected/ddl.out +++ b/contrib/test_decoding/expected/ddl.out @@ -438,12 +438,12 @@ CREATE TABLE replication_metadata ( WITH (user_catalog_table = true) ; \d+ replication_metadata - Table "public.replication_metadata" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+--------------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | | - relation | name | | not null | | plain | | - options | text[] | | | | extended | | + Table "public.replication_metadata" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +----------+---------+-----------+----------+--------------------------------------------------+----------+-------------+--------------+------------- + id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | | | + relation | name | | not null | | plain | | | + options | text[] | | | | extended | pglz | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) Options: user_catalog_table=true @@ -452,12 +452,12 @@ INSERT INTO replication_metadata(relation, options) VALUES ('foo', ARRAY['a', 'b']); ALTER TABLE replication_metadata RESET (user_catalog_table); \d+ replication_metadata - Table "public.replication_metadata" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+--------------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | | - relation | name | | not null | | plain | | - options | text[] | | | | extended | | + Table "public.replication_metadata" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +----------+---------+-----------+----------+--------------------------------------------------+----------+-------------+--------------+------------- + id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | | | + relation | name | | not null | | plain | | | + options | text[] | | | | extended | pglz | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) @@ -465,12 +465,12 @@ INSERT INTO replication_metadata(relation, options) VALUES ('bar', ARRAY['a', 'b']); ALTER TABLE replication_metadata SET (user_catalog_table = true); \d+ replication_metadata - Table "public.replication_metadata" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+--------------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | | - relation | name | | not null | | plain | | - options | text[] | | | | extended | | + Table "public.replication_metadata" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +----------+---------+-----------+----------+--------------------------------------------------+----------+-------------+--------------+------------- + id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | | | + relation | name | | not null | | plain | | | + options | text[] | | | | extended | pglz | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) Options: user_catalog_table=true @@ -483,13 +483,13 @@ ALTER TABLE replication_metadata ALTER COLUMN rewritemeornot TYPE text; ERROR: cannot rewrite table "replication_metadata" used as a catalog table ALTER TABLE replication_metadata SET (user_catalog_table = false); \d+ replication_metadata - Table "public.replication_metadata" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------------+---------+-----------+----------+--------------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | | - relation | name | | not null | | plain | | - options | text[] | | | | extended | | - rewritemeornot | integer | | | | plain | | + Table "public.replication_metadata" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +----------------+---------+-----------+----------+--------------------------------------------------+----------+-------------+--------------+------------- + id | integer | | not null | nextval('replication_metadata_id_seq'::regclass) | plain | | | + relation | name | | not null | | plain | | | + options | text[] | | | | extended | pglz | | + rewritemeornot | integer | | | | plain | | | Indexes: "replication_metadata_pkey" PRIMARY KEY, btree (id) Options: user_catalog_table=false diff --git a/src/backend/access/compression/cm_zlib.c b/src/backend/access/compression/cm_zlib.c index 0dcb56ddf3..dc8666f309 100644 --- a/src/backend/access/compression/cm_zlib.c +++ b/src/backend/access/compression/cm_zlib.c @@ -182,7 +182,6 @@ zlib_cmcompress(CompressionAmOptions *cmoptions, const struct varlena *value) } pfree(tmp); -#endif return NULL; } @@ -231,6 +230,7 @@ zlib_cmdecompress(CompressionAmOptions *cmoptions, const struct varlena *value) pfree(zp); return result; } +#endif Datum zlibhandler(PG_FUNCTION_ARGS) diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 75d4119eaa..54fe30b3c0 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -464,10 +464,10 @@ begin end $$ language plpgsql immutable; alter table check_con_tbl add check (check_con_function(check_con_tbl.*)); \d+ check_con_tbl - Table "public.check_con_tbl" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | | | plain | | + Table "public.check_con_tbl" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + f1 | integer | | | | plain | | | Check constraints: "check_con_tbl_check" CHECK (check_con_function(check_con_tbl.*)) diff --git a/src/test/regress/expected/create_cm.out b/src/test/regress/expected/create_cm.out new file mode 100644 index 0000000000..de14d87885 --- /dev/null +++ b/src/test/regress/expected/create_cm.out @@ -0,0 +1,405 @@ +-- test drop +DROP ACCESS METHOD pglz; --fail +ERROR: cannot drop access method pglz because it is required by the database system +CREATE ACCESS METHOD pglz1 TYPE COMPRESSION HANDLER pglzhandler; +CREATE TABLE droptest(d1 TEXT COMPRESSION pglz1); +DROP ACCESS METHOD pglz1; +ERROR: cannot drop access method pglz1 because other objects depend on it +DETAIL: column d1 of table droptest depends on access method pglz1 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP ACCESS METHOD pglz1 CASCADE; +NOTICE: drop cascades to column d1 of table droptest +\d+ droptest + Table "public.droptest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+---------+-------------+--------------+------------- + +DROP TABLE droptest; +CREATE ACCESS METHOD pglz1 TYPE COMPRESSION HANDLER pglzhandler; +-- test auto drop of related attribute compression +CREATE ACCESS METHOD pglz2 TYPE COMPRESSION HANDLER pglzhandler; +CREATE TABLE cmaltertest (f1 TEXT COMPRESSION pglz2); +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass; + acname | acattnum | acoptions +--------+----------+----------- + pglz2 | 1 | +(1 row) + +ALTER TABLE cmaltertest DROP COLUMN f1; +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass; + acname | acattnum | acoptions +--------+----------+----------- +(0 rows) + +DROP TABLE cmaltertest; +-- test drop +DROP ACCESS METHOD pglz2; +-- test alter data type +CREATE TABLE cmaltertest(at1 TEXT); +ALTER TABLE cmaltertest ALTER COLUMN at1 SET COMPRESSION pglz1 PRESERVE (pglz); +SELECT pg_column_compression('cmaltertest', 'at1'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +ALTER TABLE cmaltertest ALTER COLUMN at1 SET DATA TYPE INT USING at1::INTEGER; +\d+ cmaltertest + Table "public.cmaltertest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + at1 | integer | | | | plain | | | + +SELECT pg_column_compression('cmaltertest', 'at1'); + pg_column_compression +----------------------- + +(1 row) + +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass; + acname | acattnum | acoptions +--------+----------+----------- +(0 rows) + +ALTER TABLE cmaltertest ALTER COLUMN at1 SET DATA TYPE TEXT; +SELECT pg_column_compression('cmaltertest', 'at1'); + pg_column_compression +----------------------- + pglz +(1 row) + +DROP TABLE cmaltertest; +-- test storages +CREATE TABLE cmstoragetest(st1 TEXT, st2 INT); +ALTER TABLE cmstoragetest ALTER COLUMN st2 + SET COMPRESSION pglz WITH (min_input_size '100'); -- fail +ERROR: column data type integer does not support compression +ALTER TABLE cmstoragetest ALTER COLUMN st1 + SET COMPRESSION pglz WITH (min_input_size '100', min_comp_rate '50'); +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE EXTERNAL; +\d+ cmstoragetest + Table "public.cmstoragetest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+------------------------------------------------+--------------+------------- + st1 | text | | | | external | pglz(min_comp_rate '50', min_input_size '100') | | + st2 | integer | | | | plain | | | + +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE MAIN; +\d+ cmstoragetest + Table "public.cmstoragetest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+------------------------------------------------+--------------+------------- + st1 | text | | | | main | pglz(min_comp_rate '50', min_input_size '100') | | + st2 | integer | | | | plain | | | + +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE PLAIN; +\d+ cmstoragetest + Table "public.cmstoragetest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+------------------------------------------------+--------------+------------- + st1 | text | | | | plain | pglz(min_comp_rate '50', min_input_size '100') | | + st2 | integer | | | | plain | | | + +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE EXTENDED; +\d+ cmstoragetest + Table "public.cmstoragetest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+------------------------------------------------+--------------+------------- + st1 | text | | | | extended | pglz(min_comp_rate '50', min_input_size '100') | | + st2 | integer | | | | plain | | | + +DROP TABLE cmstoragetest; +CREATE ACCESS METHOD pglz2 TYPE COMPRESSION HANDLER pglzhandler; +-- test PRESERVE +CREATE TABLE cmtest(f1 TEXT); +\d+ cmtest + Table "public.cmtest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz | | + +-- view to check dependencies +CREATE VIEW cmtest_deps AS + SELECT classid, objsubid, refclassid, refobjsubid, deptype + FROM pg_depend + WHERE (refclassid = 4001 OR classid = 4001) AND + (objid = 'cmtest'::regclass OR refobjid = 'cmtest'::regclass) + ORDER by objid, refobjid; +INSERT INTO cmtest VALUES(repeat('1234567890',1001)); +-- one normal dependency +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 1259 | 1 | 4001 | 0 | n +(1 row) + +-- check decompression +SELECT length(f1) FROM cmtest; + length +-------- + 10010 +(1 row) + +SELECT length(f1) FROM cmtest; + length +-------- + 10010 +(1 row) + +CREATE FUNCTION on_cmtest_rewrite() +RETURNS event_trigger AS $$ +BEGIN + RAISE NOTICE 'cmtest rewrite'; +END; +$$ LANGUAGE plpgsql; +CREATE EVENT TRIGGER notice_on_cmtest_rewrite ON table_rewrite + EXECUTE PROCEDURE on_cmtest_rewrite(); +-- no rewrite +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz1 PRESERVE (pglz); +INSERT INTO cmtest VALUES(repeat('1234567890',1002)); +SELECT length(f1) FROM cmtest; + length +-------- + 10010 + 10020 +(2 rows) + +SELECT pg_column_compression('cmtest', 'f1'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +-- one normal and one internal dependency +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 1259 | 1 | 4001 | 0 | n + 4001 | 0 | 1259 | 1 | i +(2 rows) + +-- rewrite +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz2 PRESERVE (pglz1); +NOTICE: cmtest rewrite +INSERT INTO cmtest VALUES(repeat('1234567890',1003)); +SELECT length(f1) FROM cmtest; + length +-------- + 10010 + 10020 + 10030 +(3 rows) + +SELECT pg_column_compression('cmtest', 'f1'); + pg_column_compression +----------------------- + pglz2, pglz1 +(1 row) + +-- two internal dependencies +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 4001 | 0 | 1259 | 1 | i + 4001 | 0 | 1259 | 1 | i +(2 rows) + +-- rewrite +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz; +NOTICE: cmtest rewrite +INSERT INTO cmtest VALUES(repeat('1234567890',1004)); +SELECT length(f1) FROM cmtest; + length +-------- + 10010 + 10020 + 10030 + 10040 +(4 rows) + +SELECT pg_column_compression('cmtest', 'f1'); + pg_column_compression +----------------------- + pglz +(1 row) + +-- one nornal dependency +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 1259 | 1 | 4001 | 0 | n +(1 row) + +-- no rewrites +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz1 PRESERVE (pglz); +INSERT INTO cmtest VALUES(repeat('1234567890',1005)); +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz + WITH (min_input_size '1000') PRESERVE (pglz, pglz1); +INSERT INTO cmtest VALUES(repeat('1234567890',1006)); +-- one nornal dependency and two internal dependencies +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 1259 | 1 | 4001 | 0 | n + 4001 | 0 | 1259 | 1 | i + 4001 | 0 | 1259 | 1 | i +(3 rows) + +-- remove function and related event trigger +DROP FUNCTION on_cmtest_rewrite CASCADE; +NOTICE: drop cascades to event trigger notice_on_cmtest_rewrite +-- test moving +CREATE TABLE cmdata(f1 text COMPRESSION pglz WITH (first_success_by '10000')); +INSERT INTO cmdata VALUES(repeat('1234567890',1000)); +INSERT INTO cmdata VALUES(repeat('1234567890',1001)); +-- copy with table creation +SELECT * INTO cmmove1 FROM cmdata; +-- we update using datum from different table +CREATE TABLE cmmove2(f1 text COMPRESSION pglz WITH (min_input_size '100')); +INSERT INTO cmmove2 VALUES (repeat('1234567890',1004)); +UPDATE cmmove2 SET f1 = cmdata.f1 FROM cmdata; +-- copy to existing table +CREATE TABLE cmmove3(f1 text COMPRESSION pglz WITH (min_input_size '100')); +INSERT INTO cmmove3 SELECT * FROM cmdata; +-- drop original compression information +DROP TABLE cmdata; +-- check data is ok +SELECT length(f1) FROM cmmove1; + length +-------- + 10000 + 10010 +(2 rows) + +SELECT length(f1) FROM cmmove2; + length +-------- + 10000 +(1 row) + +SELECT length(f1) FROM cmmove3; + length +-------- + 10000 + 10010 +(2 rows) + +-- create different types of tables +CREATE TABLE cmexample (f1 TEXT COMPRESSION pglz1); +CREATE TABLE cmtestlike1 (LIKE cmexample INCLUDING COMPRESSION); +CREATE TABLE cmtestlike2 (f2 INT) INHERITS (cmexample); +\d+ cmtestlike1 + Table "public.cmtestlike1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz1 | | + +\d+ cmtestlike2 + Table "public.cmtestlike2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz1 | | + f2 | integer | | | | plain | | | +Inherits: cmexample + +-- test two columns +CREATE TABLE cmaltertest(f1 TEXT, f2 TEXT COMPRESSION pglz1); +\d+ cmaltertest; + Table "public.cmaltertest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz | | + f2 | text | | | | extended | pglz1 | | + +-- fail, changing one column twice +ALTER TABLE cmaltertest ALTER COLUMN f1 SET COMPRESSION pglz, + ALTER COLUMN f1 SET COMPRESSION pglz; +ERROR: cannot alter compression of column "f1" twice +HINT: Remove one of statements from the command. +-- with rewrite +ALTER TABLE cmaltertest ALTER COLUMN f1 SET COMPRESSION pglz1, + ALTER COLUMN f2 SET COMPRESSION pglz PRESERVE (pglz1); +SELECT pg_column_compression('cmaltertest', 'f1'); + pg_column_compression +----------------------- + pglz1 +(1 row) + +SELECT pg_column_compression('cmaltertest', 'f2'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +-- no rewrite +ALTER TABLE cmaltertest ALTER COLUMN f1 SET COMPRESSION pglz PRESERVE (pglz1), + ALTER COLUMN f2 SET COMPRESSION pglz2 PRESERVE (pglz1, pglz); +SELECT pg_column_compression('cmaltertest', 'f1'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +SELECT pg_column_compression('cmaltertest', 'f2'); + pg_column_compression +----------------------- + pglz, pglz2, pglz1 +(1 row) + +-- make pglz2 droppable +ALTER TABLE cmaltertest ALTER COLUMN f2 SET COMPRESSION pglz1; +SELECT pg_column_compression('cmaltertest', 'f1'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +SELECT pg_column_compression('cmaltertest', 'f2'); + pg_column_compression +----------------------- + pglz1 +(1 row) + +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass OR acrelid = 'cmtest'::regclass; + acname | acattnum | acoptions +--------+----------+----------------------- + pglz1 | 1 | + pglz | 1 | {min_input_size=1000} + pglz1 | 2 | + pglz1 | 1 | +(4 rows) + +-- zlib compression +CREATE TABLE zlibtest(f1 TEXT COMPRESSION zlib WITH (invalid 'param')); +ERROR: unexpected parameter for zlib: "invalid" +CREATE TABLE zlibtest(f1 TEXT COMPRESSION zlib WITH (level 'best')); +ERROR: unexpected value for zlib compression level: "best" +CREATE TABLE zlibtest(f1 TEXT COMPRESSION zlib); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (level 'best_compression'); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (level 'best_speed'); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (level 'default'); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (dict 'one'); +ERROR: zlib dictionary is too small +INSERT INTO zlibtest VALUES(repeat('1234567890',1004)); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (dict 'one two') PRESERVE (zlib); +INSERT INTO zlibtest VALUES(repeat('1234567890 one two three',1004)); +SELECT length(f1) FROM zlibtest; + length +-------- + 10040 + 24096 +(2 rows) + +DROP ACCESS METHOD pglz2; +DROP VIEW cmtest_deps; +DROP TABLE cmmove1, cmmove2, cmmove3; +DROP TABLE cmexample, cmtestlike1, cmtestlike2, zlibtest; diff --git a/src/test/regress/expected/create_cm_1.out b/src/test/regress/expected/create_cm_1.out new file mode 100644 index 0000000000..755d40caef --- /dev/null +++ b/src/test/regress/expected/create_cm_1.out @@ -0,0 +1,409 @@ +-- test drop +DROP ACCESS METHOD pglz; --fail +ERROR: cannot drop access method pglz because it is required by the database system +CREATE ACCESS METHOD pglz1 TYPE COMPRESSION HANDLER pglzhandler; +CREATE TABLE droptest(d1 TEXT COMPRESSION pglz1); +DROP ACCESS METHOD pglz1; +ERROR: cannot drop access method pglz1 because other objects depend on it +DETAIL: column d1 of table droptest depends on access method pglz1 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP ACCESS METHOD pglz1 CASCADE; +NOTICE: drop cascades to column d1 of table droptest +\d+ droptest + Table "public.droptest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+---------+-------------+--------------+------------- + +DROP TABLE droptest; +CREATE ACCESS METHOD pglz1 TYPE COMPRESSION HANDLER pglzhandler; +-- test auto drop of related attribute compression +CREATE ACCESS METHOD pglz2 TYPE COMPRESSION HANDLER pglzhandler; +CREATE TABLE cmaltertest (f1 TEXT COMPRESSION pglz2); +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass; + acname | acattnum | acoptions +--------+----------+----------- + pglz2 | 1 | +(1 row) + +ALTER TABLE cmaltertest DROP COLUMN f1; +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass; + acname | acattnum | acoptions +--------+----------+----------- +(0 rows) + +DROP TABLE cmaltertest; +-- test drop +DROP ACCESS METHOD pglz2; +-- test alter data type +CREATE TABLE cmaltertest(at1 TEXT); +ALTER TABLE cmaltertest ALTER COLUMN at1 SET COMPRESSION pglz1 PRESERVE (pglz); +SELECT pg_column_compression('cmaltertest', 'at1'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +ALTER TABLE cmaltertest ALTER COLUMN at1 SET DATA TYPE INT USING at1::INTEGER; +\d+ cmaltertest + Table "public.cmaltertest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + at1 | integer | | | | plain | | | + +SELECT pg_column_compression('cmaltertest', 'at1'); + pg_column_compression +----------------------- + +(1 row) + +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass; + acname | acattnum | acoptions +--------+----------+----------- +(0 rows) + +ALTER TABLE cmaltertest ALTER COLUMN at1 SET DATA TYPE TEXT; +SELECT pg_column_compression('cmaltertest', 'at1'); + pg_column_compression +----------------------- + pglz +(1 row) + +DROP TABLE cmaltertest; +-- test storages +CREATE TABLE cmstoragetest(st1 TEXT, st2 INT); +ALTER TABLE cmstoragetest ALTER COLUMN st2 + SET COMPRESSION pglz WITH (min_input_size '100'); -- fail +ERROR: column data type integer does not support compression +ALTER TABLE cmstoragetest ALTER COLUMN st1 + SET COMPRESSION pglz WITH (min_input_size '100', min_comp_rate '50'); +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE EXTERNAL; +\d+ cmstoragetest + Table "public.cmstoragetest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+------------------------------------------------+--------------+------------- + st1 | text | | | | external | pglz(min_comp_rate '50', min_input_size '100') | | + st2 | integer | | | | plain | | | + +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE MAIN; +\d+ cmstoragetest + Table "public.cmstoragetest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+------------------------------------------------+--------------+------------- + st1 | text | | | | main | pglz(min_comp_rate '50', min_input_size '100') | | + st2 | integer | | | | plain | | | + +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE PLAIN; +\d+ cmstoragetest + Table "public.cmstoragetest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+------------------------------------------------+--------------+------------- + st1 | text | | | | plain | pglz(min_comp_rate '50', min_input_size '100') | | + st2 | integer | | | | plain | | | + +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE EXTENDED; +\d+ cmstoragetest + Table "public.cmstoragetest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+------------------------------------------------+--------------+------------- + st1 | text | | | | extended | pglz(min_comp_rate '50', min_input_size '100') | | + st2 | integer | | | | plain | | | + +DROP TABLE cmstoragetest; +CREATE ACCESS METHOD pglz2 TYPE COMPRESSION HANDLER pglzhandler; +-- test PRESERVE +CREATE TABLE cmtest(f1 TEXT); +\d+ cmtest + Table "public.cmtest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz | | + +-- view to check dependencies +CREATE VIEW cmtest_deps AS + SELECT classid, objsubid, refclassid, refobjsubid, deptype + FROM pg_depend + WHERE (refclassid = 4001 OR classid = 4001) AND + (objid = 'cmtest'::regclass OR refobjid = 'cmtest'::regclass) + ORDER by objid, refobjid; +INSERT INTO cmtest VALUES(repeat('1234567890',1001)); +-- one normal dependency +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 1259 | 1 | 4001 | 0 | n +(1 row) + +-- check decompression +SELECT length(f1) FROM cmtest; + length +-------- + 10010 +(1 row) + +SELECT length(f1) FROM cmtest; + length +-------- + 10010 +(1 row) + +CREATE FUNCTION on_cmtest_rewrite() +RETURNS event_trigger AS $$ +BEGIN + RAISE NOTICE 'cmtest rewrite'; +END; +$$ LANGUAGE plpgsql; +CREATE EVENT TRIGGER notice_on_cmtest_rewrite ON table_rewrite + EXECUTE PROCEDURE on_cmtest_rewrite(); +-- no rewrite +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz1 PRESERVE (pglz); +INSERT INTO cmtest VALUES(repeat('1234567890',1002)); +SELECT length(f1) FROM cmtest; + length +-------- + 10010 + 10020 +(2 rows) + +SELECT pg_column_compression('cmtest', 'f1'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +-- one normal and one internal dependency +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 1259 | 1 | 4001 | 0 | n + 4001 | 0 | 1259 | 1 | i +(2 rows) + +-- rewrite +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz2 PRESERVE (pglz1); +NOTICE: cmtest rewrite +INSERT INTO cmtest VALUES(repeat('1234567890',1003)); +SELECT length(f1) FROM cmtest; + length +-------- + 10010 + 10020 + 10030 +(3 rows) + +SELECT pg_column_compression('cmtest', 'f1'); + pg_column_compression +----------------------- + pglz2, pglz1 +(1 row) + +-- two internal dependencies +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 4001 | 0 | 1259 | 1 | i + 4001 | 0 | 1259 | 1 | i +(2 rows) + +-- rewrite +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz; +NOTICE: cmtest rewrite +INSERT INTO cmtest VALUES(repeat('1234567890',1004)); +SELECT length(f1) FROM cmtest; + length +-------- + 10010 + 10020 + 10030 + 10040 +(4 rows) + +SELECT pg_column_compression('cmtest', 'f1'); + pg_column_compression +----------------------- + pglz +(1 row) + +-- one nornal dependency +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 1259 | 1 | 4001 | 0 | n +(1 row) + +-- no rewrites +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz1 PRESERVE (pglz); +INSERT INTO cmtest VALUES(repeat('1234567890',1005)); +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz + WITH (min_input_size '1000') PRESERVE (pglz, pglz1); +INSERT INTO cmtest VALUES(repeat('1234567890',1006)); +-- one nornal dependency and two internal dependencies +SELECT * FROM cmtest_deps; + classid | objsubid | refclassid | refobjsubid | deptype +---------+----------+------------+-------------+--------- + 1259 | 1 | 4001 | 0 | n + 4001 | 0 | 1259 | 1 | i + 4001 | 0 | 1259 | 1 | i +(3 rows) + +-- remove function and related event trigger +DROP FUNCTION on_cmtest_rewrite CASCADE; +NOTICE: drop cascades to event trigger notice_on_cmtest_rewrite +-- test moving +CREATE TABLE cmdata(f1 text COMPRESSION pglz WITH (first_success_by '10000')); +INSERT INTO cmdata VALUES(repeat('1234567890',1000)); +INSERT INTO cmdata VALUES(repeat('1234567890',1001)); +-- copy with table creation +SELECT * INTO cmmove1 FROM cmdata; +-- we update using datum from different table +CREATE TABLE cmmove2(f1 text COMPRESSION pglz WITH (min_input_size '100')); +INSERT INTO cmmove2 VALUES (repeat('1234567890',1004)); +UPDATE cmmove2 SET f1 = cmdata.f1 FROM cmdata; +-- copy to existing table +CREATE TABLE cmmove3(f1 text COMPRESSION pglz WITH (min_input_size '100')); +INSERT INTO cmmove3 SELECT * FROM cmdata; +-- drop original compression information +DROP TABLE cmdata; +-- check data is ok +SELECT length(f1) FROM cmmove1; + length +-------- + 10000 + 10010 +(2 rows) + +SELECT length(f1) FROM cmmove2; + length +-------- + 10000 +(1 row) + +SELECT length(f1) FROM cmmove3; + length +-------- + 10000 + 10010 +(2 rows) + +-- create different types of tables +CREATE TABLE cmexample (f1 TEXT COMPRESSION pglz1); +CREATE TABLE cmtestlike1 (LIKE cmexample INCLUDING COMPRESSION); +CREATE TABLE cmtestlike2 (f2 INT) INHERITS (cmexample); +\d+ cmtestlike1 + Table "public.cmtestlike1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz1 | | + +\d+ cmtestlike2 + Table "public.cmtestlike2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz1 | | + f2 | integer | | | | plain | | | +Inherits: cmexample + +-- test two columns +CREATE TABLE cmaltertest(f1 TEXT, f2 TEXT COMPRESSION pglz1); +\d+ cmaltertest; + Table "public.cmaltertest" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz | | + f2 | text | | | | extended | pglz1 | | + +-- fail, changing one column twice +ALTER TABLE cmaltertest ALTER COLUMN f1 SET COMPRESSION pglz, + ALTER COLUMN f1 SET COMPRESSION pglz; +ERROR: cannot alter compression of column "f1" twice +HINT: Remove one of statements from the command. +-- with rewrite +ALTER TABLE cmaltertest ALTER COLUMN f1 SET COMPRESSION pglz1, + ALTER COLUMN f2 SET COMPRESSION pglz PRESERVE (pglz1); +SELECT pg_column_compression('cmaltertest', 'f1'); + pg_column_compression +----------------------- + pglz1 +(1 row) + +SELECT pg_column_compression('cmaltertest', 'f2'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +-- no rewrite +ALTER TABLE cmaltertest ALTER COLUMN f1 SET COMPRESSION pglz PRESERVE (pglz1), + ALTER COLUMN f2 SET COMPRESSION pglz2 PRESERVE (pglz1, pglz); +SELECT pg_column_compression('cmaltertest', 'f1'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +SELECT pg_column_compression('cmaltertest', 'f2'); + pg_column_compression +----------------------- + pglz, pglz2, pglz1 +(1 row) + +-- make pglz2 droppable +ALTER TABLE cmaltertest ALTER COLUMN f2 SET COMPRESSION pglz1; +SELECT pg_column_compression('cmaltertest', 'f1'); + pg_column_compression +----------------------- + pglz, pglz1 +(1 row) + +SELECT pg_column_compression('cmaltertest', 'f2'); + pg_column_compression +----------------------- + pglz1 +(1 row) + +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass OR acrelid = 'cmtest'::regclass; + acname | acattnum | acoptions +--------+----------+----------------------- + pglz1 | 1 | + pglz | 1 | {min_input_size=1000} + pglz1 | 2 | + pglz1 | 1 | +(4 rows) + +-- zlib compression +CREATE TABLE zlibtest(f1 TEXT COMPRESSION zlib WITH (invalid 'param')); +ERROR: not built with zlib support +CREATE TABLE zlibtest(f1 TEXT COMPRESSION zlib WITH (level 'best')); +ERROR: not built with zlib support +CREATE TABLE zlibtest(f1 TEXT COMPRESSION zlib); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (level 'best_compression'); +ERROR: not built with zlib support +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (level 'best_speed'); +ERROR: not built with zlib support +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (level 'default'); +ERROR: not built with zlib support +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (dict 'one'); +ERROR: not built with zlib support +INSERT INTO zlibtest VALUES(repeat('1234567890',1004)); +ERROR: not built with zlib support +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (dict 'one two') PRESERVE (zlib); +ERROR: not built with zlib support +INSERT INTO zlibtest VALUES(repeat('1234567890 one two three',1004)); +ERROR: not built with zlib support +SELECT length(f1) FROM zlibtest; + length +-------- +(0 rows) + +DROP ACCESS METHOD pglz2; +DROP VIEW cmtest_deps; +DROP TABLE cmmove1, cmmove2, cmmove3; +DROP TABLE cmexample, cmtestlike1, cmtestlike2, zlibtest; diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index d51e547278..bd84883175 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -445,11 +445,11 @@ Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "C") Number of partitions: 0 \d+ partitioned2 - Partitioned table "public.partitioned2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | integer | | | | plain | | - b | text | | | | extended | | + Partitioned table "public.partitioned2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | integer | | | | plain | | | + b | text | | | | extended | pglz | | Partition key: RANGE (((a + 1)), substr(b, 1, 5)) Number of partitions: 0 @@ -458,11 +458,11 @@ ERROR: no partition of relation "partitioned2" found for row DETAIL: Partition key of the failing row contains ((a + 1), substr(b, 1, 5)) = (2, hello). CREATE TABLE part2_1 PARTITION OF partitioned2 FOR VALUES FROM (-1, 'aaaaa') TO (100, 'ccccc'); \d+ part2_1 - Table "public.part2_1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | integer | | | | plain | | - b | text | | | | extended | | + Table "public.part2_1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | integer | | | | plain | | | + b | text | | | | extended | pglz | | Partition of: partitioned2 FOR VALUES FROM ('-1', 'aaaaa') TO (100, 'ccccc') Partition constraint: (((a + 1) IS NOT NULL) AND (substr(b, 1, 5) IS NOT NULL) AND (((a + 1) > '-1'::integer) OR (((a + 1) = '-1'::integer) AND (substr(b, 1, 5) >= 'aaaaa'::text))) AND (((a + 1) < 100) OR (((a + 1) = 100) AND (substr(b, 1, 5) < 'ccccc'::text)))) @@ -479,10 +479,10 @@ CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2); CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1)); CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); \d+ list_parted - Partitioned table "public.list_parted" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | + Partitioned table "public.list_parted" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + a | integer | | | | plain | | | Partition key: LIST (a) Partitions: part_null FOR VALUES IN (NULL), part_p1 FOR VALUES IN (1), @@ -832,21 +832,21 @@ create table test_part_coll_cast2 partition of test_part_coll_posix for values f drop table test_part_coll_posix; -- Partition bound in describe output \d+ part_b - Table "public.part_b" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | not null | 1 | plain | | + Table "public.part_b" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | not null | 1 | plain | | | Partition of: parted FOR VALUES IN ('b') Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text)) -- Both partition bound and partition key in describe output \d+ part_c - Partitioned table "public.part_c" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | not null | 0 | plain | | + Partitioned table "public.part_c" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | not null | 0 | plain | | | Partition of: parted FOR VALUES IN ('c') Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text)) Partition key: RANGE (b) @@ -854,11 +854,11 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) -- a level-2 partition's constraint will include the parent's expressions \d+ part_c_1_10 - Table "public.part_c_1_10" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | not null | 0 | plain | | + Table "public.part_c_1_10" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | not null | 0 | plain | | | Partition of: part_c FOR VALUES FROM (1) TO (10) Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10)) @@ -887,46 +887,46 @@ Number of partitions: 3 (Use \d+ to list them.) CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c); CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE); \d+ unbounded_range_part - Table "public.unbounded_range_part" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | + Table "public.unbounded_range_part" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + a | integer | | | | plain | | | + b | integer | | | | plain | | | + c | integer | | | | plain | | | Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (MAXVALUE, MAXVALUE, MAXVALUE) Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL)) DROP TABLE unbounded_range_part; CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE); \d+ range_parted4_1 - Table "public.range_parted4_1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | + Table "public.range_parted4_1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + a | integer | | | | plain | | | + b | integer | | | | plain | | | + c | integer | | | | plain | | | Partition of: range_parted4 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1, MAXVALUE, MAXVALUE) Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND (abs(a) <= 1)) CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE); \d+ range_parted4_2 - Table "public.range_parted4_2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | + Table "public.range_parted4_2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + a | integer | | | | plain | | | + b | integer | | | | plain | | | + c | integer | | | | plain | | | Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, MAXVALUE) Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7)))) CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE); \d+ range_parted4_3 - Table "public.range_parted4_3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | + Table "public.range_parted4_3" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + a | integer | | | | plain | | | + b | integer | | | | plain | | | + c | integer | | | | plain | | | Partition of: range_parted4 FOR VALUES FROM (6, 8, MINVALUE) TO (9, MAXVALUE, MAXVALUE) Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (c IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9)) @@ -958,11 +958,11 @@ SELECT obj_description('parted_col_comment'::regclass); (1 row) \d+ parted_col_comment - Partitioned table "public.parted_col_comment" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+--------------- - a | integer | | | | plain | | Partition key - b | text | | | | extended | | + Partitioned table "public.parted_col_comment" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+--------------- + a | integer | | | | plain | | | Partition key + b | text | | | | extended | pglz | | Partition key: LIST (a) Number of partitions: 0 @@ -971,10 +971,10 @@ DROP TABLE parted_col_comment; CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a); CREATE TABLE arrlp12 PARTITION OF arrlp FOR VALUES IN ('{1}', '{2}'); \d+ arrlp12 - Table "public.arrlp12" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-----------+-----------+----------+---------+----------+--------------+------------- - a | integer[] | | | | extended | | + Table "public.arrlp12" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-----------+-----------+----------+---------+----------+-------------+--------------+------------- + a | integer[] | | | | extended | pglz | | Partition of: arrlp FOR VALUES IN ('{1}', '{2}') Partition constraint: ((a IS NOT NULL) AND ((a = '{1}'::integer[]) OR (a = '{2}'::integer[]))) @@ -984,10 +984,10 @@ create table boolspart (a bool) partition by list (a); create table boolspart_t partition of boolspart for values in (true); create table boolspart_f partition of boolspart for values in (false); \d+ boolspart - Partitioned table "public.boolspart" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | boolean | | | | plain | | + Partitioned table "public.boolspart" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + a | boolean | | | | plain | | | Partition key: LIST (a) Partitions: boolspart_f FOR VALUES IN (false), boolspart_t FOR VALUES IN (true) diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index b582211270..d830ce830c 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -158,32 +158,32 @@ CREATE TABLE ctlt4 (a text, c text); ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL; CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE); \d+ ctlt12_storage - Table "public.ctlt12_storage" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | - b | text | | | | extended | | - c | text | | | | external | | + Table "public.ctlt12_storage" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | not null | | main | pglz | | + b | text | | | | extended | pglz | | + c | text | | | | external | pglz | | CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); \d+ ctlt12_comments - Table "public.ctlt12_comments" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | extended | | A - b | text | | | | extended | | B - c | text | | | | extended | | C + Table "public.ctlt12_comments" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | not null | | extended | pglz | | A + b | text | | | | extended | pglz | | B + c | text | | | | extended | pglz | | C CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); NOTICE: merging column "a" with inherited definition NOTICE: merging column "b" with inherited definition NOTICE: merging constraint "ctlt1_a_check" with inherited definition \d+ ctlt1_inh - Table "public.ctlt1_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | A - b | text | | | | extended | | B + Table "public.ctlt1_inh" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | not null | | main | pglz | | A + b | text | | | | extended | pglz | | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) Inherits: ctlt1 @@ -197,12 +197,12 @@ SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_con CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); NOTICE: merging multiple inherited definitions of column "a" \d+ ctlt13_inh - Table "public.ctlt13_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | - b | text | | | | extended | | - c | text | | | | external | | + Table "public.ctlt13_inh" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | not null | | main | pglz | | + b | text | | | | extended | pglz | | + c | text | | | | external | pglz | | Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) @@ -212,12 +212,12 @@ Inherits: ctlt1, CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); NOTICE: merging column "a" with inherited definition \d+ ctlt13_like - Table "public.ctlt13_like" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | A3 - b | text | | | | extended | | - c | text | | | | external | | C + Table "public.ctlt13_like" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | not null | | main | pglz | | A3 + b | text | | | | extended | pglz | | + c | text | | | | external | pglz | | C Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) @@ -231,11 +231,11 @@ SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_con CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); \d+ ctlt_all - Table "public.ctlt_all" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------+-----------+----------+---------+----------+--------------+------------- - a | text | | not null | | main | | A - b | text | | | | extended | | B + Table "public.ctlt_all" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | not null | | main | pglz | | A + b | text | | | | extended | pglz | | B Indexes: "ctlt_all_pkey" PRIMARY KEY, btree (a) "ctlt_all_b_idx" btree (b) diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 4ff1b4af41..5255951060 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -272,10 +272,10 @@ explain (verbose, costs off) create rule silly as on delete to dcomptable do instead update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0; \d+ dcomptable - Table "public.dcomptable" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-----------+-----------+----------+---------+----------+--------------+------------- - d1 | dcomptype | | | | extended | | + Table "public.dcomptable" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-----------+-----------+----------+---------+----------+-------------+--------------+------------- + d1 | dcomptype | | | | extended | pglz | | Indexes: "dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1) Rules: @@ -409,10 +409,10 @@ create rule silly as on delete to dcomptable do instead update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 where d1[1].i > 0; \d+ dcomptable - Table "public.dcomptable" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+------------+-----------+----------+---------+----------+--------------+------------- - d1 | dcomptypea | | | | extended | | + Table "public.dcomptable" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------------+-----------+----------+---------+----------+-------------+--------------+------------- + d1 | dcomptypea | | | | extended | pglz | | Indexes: "dcomptable_d1_key" UNIQUE CONSTRAINT, btree (d1) Rules: diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 4d82d3a7e8..8c8a03cfc2 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -1332,12 +1332,12 @@ CREATE TABLE fd_pt1 ( CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Child tables: ft2 \d+ ft2 @@ -1353,12 +1353,12 @@ Inherits: fd_pt1 DROP FOREIGN TABLE ft2; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | CREATE FOREIGN TABLE ft2 ( c1 integer NOT NULL, @@ -1377,12 +1377,12 @@ FDW options: (delimiter ',', quote '"', "be quoted" 'value') ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Child tables: ft2 \d+ ft2 @@ -1420,12 +1420,12 @@ Child tables: ct3, ft3 \d+ ct3 - Table "public.ct3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.ct3" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Inherits: ft2 \d+ ft3 @@ -1445,17 +1445,17 @@ ALTER TABLE fd_pt1 ADD COLUMN c6 integer; ALTER TABLE fd_pt1 ADD COLUMN c7 integer NOT NULL; ALTER TABLE fd_pt1 ADD COLUMN c8 integer; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | - c4 | integer | | | | plain | | - c5 | integer | | | 0 | plain | | - c6 | integer | | | | plain | | - c7 | integer | | not null | | plain | | - c8 | integer | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | + c4 | integer | | | | plain | | | + c5 | integer | | | 0 | plain | | | + c6 | integer | | | | plain | | | + c7 | integer | | not null | | plain | | | + c8 | integer | | | | plain | | | Child tables: ft2 \d+ ft2 @@ -1477,17 +1477,17 @@ Child tables: ct3, ft3 \d+ ct3 - Table "public.ct3" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | - c4 | integer | | | | plain | | - c5 | integer | | | 0 | plain | | - c6 | integer | | | | plain | | - c7 | integer | | not null | | plain | | - c8 | integer | | | | plain | | + Table "public.ct3" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | + c4 | integer | | | | plain | | | + c5 | integer | | | 0 | plain | | | + c6 | integer | | | | plain | | | + c7 | integer | | not null | | plain | | | + c8 | integer | | | | plain | | | Inherits: ft2 \d+ ft3 @@ -1519,17 +1519,17 @@ ALTER TABLE fd_pt1 ALTER COLUMN c1 SET (n_distinct = 100); ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STATISTICS -1; ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | - c4 | integer | | | 0 | plain | | - c5 | integer | | | | plain | | - c6 | integer | | not null | | plain | | - c7 | integer | | | | plain | | - c8 | text | | | | external | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | 10000 | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | + c4 | integer | | | 0 | plain | | | + c5 | integer | | | | plain | | | + c6 | integer | | not null | | plain | | | + c7 | integer | | | | plain | | | + c8 | text | | | | external | pglz | | Child tables: ft2 \d+ ft2 @@ -1557,12 +1557,12 @@ ALTER TABLE fd_pt1 DROP COLUMN c6; ALTER TABLE fd_pt1 DROP COLUMN c7; ALTER TABLE fd_pt1 DROP COLUMN c8; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | 10000 | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Child tables: ft2 \d+ ft2 @@ -1594,12 +1594,12 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit -- child does not inherit NO INHERIT constraints \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | 10000 | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) @@ -1638,12 +1638,12 @@ ALTER FOREIGN TABLE ft2 ADD CONSTRAINT fd_pt1chk2 CHECK (c2 <> ''); ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; -- child does not inherit NO INHERIT constraints \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | 10000 | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Check constraints: "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) @@ -1669,12 +1669,12 @@ ALTER TABLE fd_pt1 DROP CONSTRAINT fd_pt1chk2 CASCADE; INSERT INTO fd_pt1 VALUES (1, 'fd_pt1'::text, '1994-01-01'::date); ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | 10000 | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID Child tables: ft2 @@ -1696,12 +1696,12 @@ Inherits: fd_pt1 -- VALIDATE CONSTRAINT need do nothing on foreign tables ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | 10000 | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | 10000 | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Check constraints: "fd_pt1chk3" CHECK (c2 <> ''::text) Child tables: ft2 @@ -1727,12 +1727,12 @@ ALTER TABLE fd_pt1 RENAME COLUMN c3 TO f3; -- changes name of a constraint recursively ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check; \d+ fd_pt1 - Table "public.fd_pt1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - f1 | integer | | not null | | plain | 10000 | - f2 | text | | | | extended | | - f3 | date | | | | plain | | + Table "public.fd_pt1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | integer | | not null | | plain | | 10000 | + f2 | text | | | | extended | pglz | | + f3 | date | | | | plain | | | Check constraints: "f2_check" CHECK (f2 <> ''::text) Child tables: ft2 @@ -1791,12 +1791,12 @@ CREATE TABLE fd_pt2 ( CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Partition key: LIST (c1) Partitions: fd_pt2_1 FOR VALUES IN (1) @@ -1836,12 +1836,12 @@ ERROR: table "fd_pt2_1" contains column "c4" not found in parent "fd_pt2" DETAIL: The new partition may contain only the columns present in parent. DROP FOREIGN TABLE fd_pt2_1; \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Partition key: LIST (c1) Number of partitions: 0 @@ -1863,12 +1863,12 @@ FDW options: (delimiter ',', quote '"', "be quoted" 'value') -- no attach partition validation occurs for foreign tables ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Partition key: LIST (c1) Partitions: fd_pt2_1 FOR VALUES IN (1) @@ -1891,12 +1891,12 @@ ERROR: cannot add column to a partition ALTER TABLE fd_pt2_1 ALTER c3 SET NOT NULL; ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> ''); \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | | | extended | pglz | | + c3 | date | | | | plain | | | Partition key: LIST (c1) Partitions: fd_pt2_1 FOR VALUES IN (1) @@ -1921,12 +1921,12 @@ ERROR: column "c1" is marked NOT NULL in parent table ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | not null | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | not null | | extended | pglz | | + c3 | date | | | | plain | | | Partition key: LIST (c1) Number of partitions: 0 @@ -1949,12 +1949,12 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); \d+ fd_pt2 - Partitioned table "public.fd_pt2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - c1 | integer | | not null | | plain | | - c2 | text | | not null | | extended | | - c3 | date | | | | plain | | + Partitioned table "public.fd_pt2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + c1 | integer | | not null | | plain | | | + c2 | text | | not null | | extended | pglz | | + c3 | date | | | | plain | | | Partition key: LIST (c1) Check constraints: "fd_pt2chk1" CHECK (c1 > 0) diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 565d947b6d..285e36fb21 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1033,13 +1033,13 @@ ALTER TABLE inhts RENAME aa TO aaa; -- to be failed ERROR: cannot rename inherited column "aa" ALTER TABLE inhts RENAME d TO dd; \d+ inhts - Table "public.inhts" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - aa | integer | | | | plain | | - b | integer | | | | plain | | - c | integer | | | | plain | | - dd | integer | | | | plain | | + Table "public.inhts" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + aa | integer | | | | plain | | | + b | integer | | | | plain | | | + c | integer | | | | plain | | | + dd | integer | | | | plain | | | Inherits: inht1, inhs1 @@ -1052,14 +1052,14 @@ NOTICE: merging multiple inherited definitions of column "aa" NOTICE: merging multiple inherited definitions of column "b" ALTER TABLE inht1 RENAME aa TO aaa; \d+ inht4 - Table "public.inht4" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - aaa | integer | | | | plain | | - b | integer | | | | plain | | - x | integer | | | | plain | | - y | integer | | | | plain | | - z | integer | | | | plain | | + Table "public.inht4" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + aaa | integer | | | | plain | | | + b | integer | | | | plain | | | + x | integer | | | | plain | | | + y | integer | | | | plain | | | + z | integer | | | | plain | | | Inherits: inht2, inht3 @@ -1069,14 +1069,14 @@ ALTER TABLE inht1 RENAME aaa TO aaaa; ALTER TABLE inht1 RENAME b TO bb; -- to be failed ERROR: cannot rename inherited column "b" \d+ inhts - Table "public.inhts" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - aaaa | integer | | | | plain | | - b | integer | | | | plain | | - x | integer | | | | plain | | - c | integer | | | | plain | | - d | integer | | | | plain | | + Table "public.inhts" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + aaaa | integer | | | | plain | | | + b | integer | | | | plain | | | + x | integer | | | | plain | | | + c | integer | | | | plain | | | + d | integer | | | | plain | | | Inherits: inht2, inhs1 @@ -1116,33 +1116,33 @@ drop cascades to table inht4 CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2)); CREATE TABLE test_constraints_inh () INHERITS (test_constraints); \d+ test_constraints - Table "public.test_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+--------------+------------- - id | integer | | | | plain | | - val1 | character varying | | | | extended | | - val2 | integer | | | | plain | | + Table "public.test_constraints" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- + id | integer | | | | plain | | | + val1 | character varying | | | | extended | pglz | | + val2 | integer | | | | plain | | | Indexes: "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2) Child tables: test_constraints_inh ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; \d+ test_constraints - Table "public.test_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+--------------+------------- - id | integer | | | | plain | | - val1 | character varying | | | | extended | | - val2 | integer | | | | plain | | + Table "public.test_constraints" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- + id | integer | | | | plain | | | + val1 | character varying | | | | extended | pglz | | + val2 | integer | | | | plain | | | Child tables: test_constraints_inh \d+ test_constraints_inh - Table "public.test_constraints_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+--------------+------------- - id | integer | | | | plain | | - val1 | character varying | | | | extended | | - val2 | integer | | | | plain | | + Table "public.test_constraints_inh" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- + id | integer | | | | plain | | | + val1 | character varying | | | | extended | pglz | | + val2 | integer | | | | plain | | | Inherits: test_constraints DROP TABLE test_constraints_inh; @@ -1153,27 +1153,27 @@ CREATE TABLE test_ex_constraints ( ); CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); \d+ test_ex_constraints - Table "public.test_ex_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------+-----------+----------+---------+---------+--------------+------------- - c | circle | | | | plain | | + Table "public.test_ex_constraints" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+--------+-----------+----------+---------+---------+-------------+--------------+------------- + c | circle | | | | plain | | | Indexes: "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&) Child tables: test_ex_constraints_inh ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; \d+ test_ex_constraints - Table "public.test_ex_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------+-----------+----------+---------+---------+--------------+------------- - c | circle | | | | plain | | + Table "public.test_ex_constraints" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+--------+-----------+----------+---------+---------+-------------+--------------+------------- + c | circle | | | | plain | | | Child tables: test_ex_constraints_inh \d+ test_ex_constraints_inh - Table "public.test_ex_constraints_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------+-----------+----------+---------+---------+--------------+------------- - c | circle | | | | plain | | + Table "public.test_ex_constraints_inh" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+--------+-----------+----------+---------+---------+-------------+--------------+------------- + c | circle | | | | plain | | | Inherits: test_ex_constraints DROP TABLE test_ex_constraints_inh; @@ -1183,37 +1183,37 @@ CREATE TABLE test_primary_constraints(id int PRIMARY KEY); CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); \d+ test_primary_constraints - Table "public.test_primary_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id | integer | | not null | | plain | | + Table "public.test_primary_constraints" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + id | integer | | not null | | plain | | | Indexes: "test_primary_constraints_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) \d+ test_foreign_constraints - Table "public.test_foreign_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id1 | integer | | | | plain | | + Table "public.test_foreign_constraints" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + id1 | integer | | | | plain | | | Foreign-key constraints: "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) Child tables: test_foreign_constraints_inh ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; \d+ test_foreign_constraints - Table "public.test_foreign_constraints" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id1 | integer | | | | plain | | + Table "public.test_foreign_constraints" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + id1 | integer | | | | plain | | | Child tables: test_foreign_constraints_inh \d+ test_foreign_constraints_inh - Table "public.test_foreign_constraints_inh" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - id1 | integer | | | | plain | | + Table "public.test_foreign_constraints_inh" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + id1 | integer | | | | plain | | | Inherits: test_foreign_constraints DROP TABLE test_foreign_constraints_inh; diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index 1cf6531c01..37f96ea7a2 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -142,11 +142,11 @@ create rule irule3 as on insert to inserttest2 do also insert into inserttest (f4[1].if1, f4[1].if2[2]) select new.f1, new.f2; \d+ inserttest2 - Table "public.inserttest2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------+-----------+----------+---------+----------+--------------+------------- - f1 | bigint | | | | plain | | - f2 | text | | | | extended | | + Table "public.inserttest2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+--------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | bigint | | | | plain | | | + f2 | text | | | | extended | pglz | | Rules: irule1 AS ON INSERT TO inserttest2 DO INSERT INTO inserttest (f3.if2[1], f3.if2[2]) @@ -448,11 +448,11 @@ from hash_parted order by part; -- test \d+ output on a table which has both partitioned and unpartitioned -- partitions \d+ list_parted - Partitioned table "public.list_parted" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Partitioned table "public.list_parted" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition key: LIST (lower(a)) Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'), part_cc_dd FOR VALUES IN ('cc', 'dd'), @@ -470,10 +470,10 @@ drop table hash_parted; create table list_parted (a int) partition by list (a); create table part_default partition of list_parted default; \d+ part_default - Table "public.part_default" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - a | integer | | | | plain | | + Table "public.part_default" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + a | integer | | | | plain | | | Partition of: list_parted DEFAULT No partition constraint @@ -799,11 +799,11 @@ create table mcrparted6_common_ge_10 partition of mcrparted for values from ('co create table mcrparted7_gt_common_lt_d partition of mcrparted for values from ('common', maxvalue) to ('d', minvalue); create table mcrparted8_ge_d partition of mcrparted for values from ('d', minvalue) to (maxvalue, maxvalue); \d+ mcrparted - Partitioned table "public.mcrparted" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Partitioned table "public.mcrparted" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition key: RANGE (a, b) Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE), mcrparted2_b FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE), @@ -815,74 +815,74 @@ Partitions: mcrparted1_lt_b FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVAL mcrparted8_ge_d FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) \d+ mcrparted1_lt_b - Table "public.mcrparted1_lt_b" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted1_lt_b" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition of: mcrparted FOR VALUES FROM (MINVALUE, MINVALUE) TO ('b', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a < 'b'::text)) \d+ mcrparted2_b - Table "public.mcrparted2_b" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted2_b" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition of: mcrparted FOR VALUES FROM ('b', MINVALUE) TO ('c', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'b'::text) AND (a < 'c'::text)) \d+ mcrparted3_c_to_common - Table "public.mcrparted3_c_to_common" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted3_c_to_common" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition of: mcrparted FOR VALUES FROM ('c', MINVALUE) TO ('common', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'c'::text) AND (a < 'common'::text)) \d+ mcrparted4_common_lt_0 - Table "public.mcrparted4_common_lt_0" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted4_common_lt_0" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition of: mcrparted FOR VALUES FROM ('common', MINVALUE) TO ('common', 0) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b < 0)) \d+ mcrparted5_common_0_to_10 - Table "public.mcrparted5_common_0_to_10" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted5_common_0_to_10" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition of: mcrparted FOR VALUES FROM ('common', 0) TO ('common', 10) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 0) AND (b < 10)) \d+ mcrparted6_common_ge_10 - Table "public.mcrparted6_common_ge_10" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted6_common_ge_10" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition of: mcrparted FOR VALUES FROM ('common', 10) TO ('common', MAXVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 'common'::text) AND (b >= 10)) \d+ mcrparted7_gt_common_lt_d - Table "public.mcrparted7_gt_common_lt_d" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted7_gt_common_lt_d" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition of: mcrparted FOR VALUES FROM ('common', MAXVALUE) TO ('d', MINVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a > 'common'::text) AND (a < 'd'::text)) \d+ mcrparted8_ge_d - Table "public.mcrparted8_ge_d" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | integer | | | | plain | | + Table "public.mcrparted8_ge_d" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | integer | | | | plain | | | Partition of: mcrparted FOR VALUES FROM ('d', MINVALUE) TO (MAXVALUE, MAXVALUE) Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a >= 'd'::text)) diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index ce25ee044a..d8cd5d8fde 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1806,7 +1806,9 @@ WHERE p1.amhandler = 0; SELECT p1.oid, p1.amname, p2.oid, p2.proname FROM pg_am AS p1, pg_proc AS p2 WHERE p2.oid = p1.amhandler AND - (p2.prorettype != 'index_am_handler'::regtype OR p2.proretset + ((p2.prorettype != 'index_am_handler'::regtype + AND p2.prorettype != 'compression_am_handler'::regtype) + OR p2.proretset OR p2.pronargs != 1 OR p2.proargtypes[0] != 'internal'::regtype); oid | amname | oid | proname diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index afbbdd543d..4f386a84e7 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -65,11 +65,11 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall (1 row) \d+ testpub_tbl2 - Table "public.testpub_tbl2" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | plain | | - data | text | | | | extended | | + Table "public.testpub_tbl2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+------------------------------------------+----------+-------------+--------------+------------- + id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | plain | | | + data | text | | | | extended | pglz | | Indexes: "testpub_tbl2_pkey" PRIMARY KEY, btree (id) Publications: @@ -141,22 +141,22 @@ ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1; ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk; ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1; \d+ pub_test.testpub_nopk - Table "pub_test.testpub_nopk" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - foo | integer | | | | plain | | - bar | integer | | | | plain | | + Table "pub_test.testpub_nopk" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + foo | integer | | | | plain | | | + bar | integer | | | | plain | | | Publications: "testpib_ins_trunct" "testpub_default" "testpub_fortbl" \d+ testpub_tbl1 - Table "public.testpub_tbl1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | - data | text | | | | extended | | + Table "public.testpub_tbl1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+------------------------------------------+----------+-------------+--------------+------------- + id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | | + data | text | | | | extended | pglz | | Indexes: "testpub_tbl1_pkey" PRIMARY KEY, btree (id) Publications: @@ -178,11 +178,11 @@ ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk; ERROR: relation "testpub_nopk" is not part of the publication \d+ testpub_tbl1 - Table "public.testpub_tbl1" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | - data | text | | | | extended | | + Table "public.testpub_tbl1" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+------------------------------------------+----------+-------------+--------------+------------- + id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | | + data | text | | | | extended | pglz | | Indexes: "testpub_tbl1_pkey" PRIMARY KEY, btree (id) Publications: diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out index 175ecd2879..7cf1927971 100644 --- a/src/test/regress/expected/replica_identity.out +++ b/src/test/regress/expected/replica_identity.out @@ -153,13 +153,13 @@ SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; (1 row) \d+ test_replica_identity - Table "public.test_replica_identity" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------------------------------------------------+----------+--------------+------------- - id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | plain | | - keya | text | | not null | | extended | | - keyb | text | | not null | | extended | | - nonkey | text | | | | extended | | + Table "public.test_replica_identity" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------------------------------------------------+----------+-------------+--------------+------------- + id | integer | | not null | nextval('test_replica_identity_id_seq'::regclass) | plain | | | + keya | text | | not null | | extended | pglz | | + keyb | text | | not null | | extended | pglz | | + nonkey | text | | | | extended | pglz | | Indexes: "test_replica_identity_pkey" PRIMARY KEY, btree (id) "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 2e170497c9..df49e98251 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -938,14 +938,14 @@ CREATE POLICY pp1 ON part_document AS PERMISSIVE CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave USING (cid < 55); \d+ part_document - Partitioned table "regress_rls_schema.part_document" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------+---------+-----------+----------+---------+----------+--------------+------------- - did | integer | | | | plain | | - cid | integer | | | | plain | | - dlevel | integer | | not null | | plain | | - dauthor | name | | | | plain | | - dtitle | text | | | | extended | | + Partitioned table "regress_rls_schema.part_document" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +---------+---------+-----------+----------+---------+----------+-------------+--------------+------------- + did | integer | | | | plain | | | + cid | integer | | | | plain | | | + dlevel | integer | | not null | | plain | | | + dauthor | name | | | | plain | | | + dtitle | text | | | | extended | pglz | | Partition key: RANGE (cid) Policies: POLICY "pp1" diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 98f417cb57..a0b03839d5 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2814,11 +2814,11 @@ select * from rules_log; create rule r3 as on delete to rules_src do notify rules_src_deletion; \d+ rules_src - Table "public.rules_src" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | | | plain | | - f2 | integer | | | | plain | | + Table "public.rules_src" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + f1 | integer | | | | plain | | | + f2 | integer | | | | plain | | | Rules: r1 AS ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) @@ -2834,11 +2834,11 @@ Rules: create rule r4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2; create rule r5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1; \d+ rules_src - Table "public.rules_src" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+---------+-----------+----------+---------+---------+--------------+------------- - f1 | integer | | | | plain | | - f2 | integer | | | | plain | | + Table "public.rules_src" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+---------+-----------+----------+---------+---------+-------------+--------------+------------- + f1 | integer | | | | plain | | | + f2 | integer | | | | plain | | | Rules: r1 AS ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 6cd937eb52..de8be73a54 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -32,6 +32,8 @@ check2_tbl|f check_tbl|f circle_tbl|t city|f +cmaltertest|f +cmtest|f copy_tbl|f d|f d_star|f @@ -106,6 +108,7 @@ pg_aggregate|t pg_am|t pg_amop|t pg_amproc|t +pg_attr_compression|t pg_attrdef|t pg_attribute|t pg_auth_members|t diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index 2083345c8e..b245059e37 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -690,14 +690,14 @@ DROP TRIGGER d15_insert_trig ON part_d_15_20; :init_range_parted; create table part_def partition of range_parted default; \d+ part_def - Table "public.part_def" - Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+--------------+------------- - a | text | | | | extended | | - b | bigint | | | | plain | | - c | numeric | | | | main | | - d | integer | | | | plain | | - e | character varying | | | | extended | | + Table "public.part_def" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+-------------------+-----------+----------+---------+----------+-------------+--------------+------------- + a | text | | | | extended | pglz | | + b | bigint | | | | plain | | | + c | numeric | | | | main | pglz | | + d | integer | | | | plain | | | + e | character varying | | | | extended | pglz | | Partition of: range_parted DEFAULT Partition constraint: (NOT ((a IS NOT NULL) AND (b IS NOT NULL) AND (((a = 'a'::text) AND (b >= '1'::bigint) AND (b < '10'::bigint)) OR ((a = 'a'::text) AND (b >= '10'::bigint) AND (b < '20'::bigint)) OR ((a = 'b'::text) AND (b >= '1'::bigint) AND (b < '10'::bigint)) OR ((a = 'b'::text) AND (b >= '10'::bigint) AND (b < '20'::bigint)) OR ((a = 'b'::text) AND (b >= '20'::bigint) AND (b < '30'::bigint))))) diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 4051a4ad4e..f591a981ce 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -41,6 +41,7 @@ test: create_function_1 test: create_type test: create_table test: create_function_2 +test: create_cm # ---------- # Load huge amounts of data diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index ac1ea622d6..ae4724e9e7 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -40,6 +40,7 @@ test: inet test: macaddr test: macaddr8 test: tstypes +test: create_cm test: geometry test: horology test: regex diff --git a/src/test/regress/sql/create_cm.sql b/src/test/regress/sql/create_cm.sql new file mode 100644 index 0000000000..d7e6c5eba2 --- /dev/null +++ b/src/test/regress/sql/create_cm.sql @@ -0,0 +1,203 @@ +-- test drop +DROP ACCESS METHOD pglz; --fail + +CREATE ACCESS METHOD pglz1 TYPE COMPRESSION HANDLER pglzhandler; +CREATE TABLE droptest(d1 TEXT COMPRESSION pglz1); +DROP ACCESS METHOD pglz1; +DROP ACCESS METHOD pglz1 CASCADE; +\d+ droptest +DROP TABLE droptest; + +CREATE ACCESS METHOD pglz1 TYPE COMPRESSION HANDLER pglzhandler; + +-- test auto drop of related attribute compression +CREATE ACCESS METHOD pglz2 TYPE COMPRESSION HANDLER pglzhandler; +CREATE TABLE cmaltertest (f1 TEXT COMPRESSION pglz2); +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass; +ALTER TABLE cmaltertest DROP COLUMN f1; +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass; +DROP TABLE cmaltertest; + +-- test drop +DROP ACCESS METHOD pglz2; + +-- test alter data type +CREATE TABLE cmaltertest(at1 TEXT); +ALTER TABLE cmaltertest ALTER COLUMN at1 SET COMPRESSION pglz1 PRESERVE (pglz); +SELECT pg_column_compression('cmaltertest', 'at1'); +ALTER TABLE cmaltertest ALTER COLUMN at1 SET DATA TYPE INT USING at1::INTEGER; +\d+ cmaltertest +SELECT pg_column_compression('cmaltertest', 'at1'); +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass; +ALTER TABLE cmaltertest ALTER COLUMN at1 SET DATA TYPE TEXT; +SELECT pg_column_compression('cmaltertest', 'at1'); +DROP TABLE cmaltertest; + +-- test storages +CREATE TABLE cmstoragetest(st1 TEXT, st2 INT); +ALTER TABLE cmstoragetest ALTER COLUMN st2 + SET COMPRESSION pglz WITH (min_input_size '100'); -- fail +ALTER TABLE cmstoragetest ALTER COLUMN st1 + SET COMPRESSION pglz WITH (min_input_size '100', min_comp_rate '50'); +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE EXTERNAL; +\d+ cmstoragetest +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE MAIN; +\d+ cmstoragetest +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE PLAIN; +\d+ cmstoragetest +ALTER TABLE cmstoragetest ALTER COLUMN st1 SET STORAGE EXTENDED; +\d+ cmstoragetest +DROP TABLE cmstoragetest; + +CREATE ACCESS METHOD pglz2 TYPE COMPRESSION HANDLER pglzhandler; + +-- test PRESERVE +CREATE TABLE cmtest(f1 TEXT); +\d+ cmtest +-- view to check dependencies +CREATE VIEW cmtest_deps AS + SELECT classid, objsubid, refclassid, refobjsubid, deptype + FROM pg_depend + WHERE (refclassid = 4001 OR classid = 4001) AND + (objid = 'cmtest'::regclass OR refobjid = 'cmtest'::regclass) + ORDER by objid, refobjid; +INSERT INTO cmtest VALUES(repeat('1234567890',1001)); + +-- one normal dependency +SELECT * FROM cmtest_deps; + +-- check decompression +SELECT length(f1) FROM cmtest; +SELECT length(f1) FROM cmtest; + +CREATE FUNCTION on_cmtest_rewrite() +RETURNS event_trigger AS $$ +BEGIN + RAISE NOTICE 'cmtest rewrite'; +END; +$$ LANGUAGE plpgsql; + +CREATE EVENT TRIGGER notice_on_cmtest_rewrite ON table_rewrite + EXECUTE PROCEDURE on_cmtest_rewrite(); + +-- no rewrite +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz1 PRESERVE (pglz); +INSERT INTO cmtest VALUES(repeat('1234567890',1002)); +SELECT length(f1) FROM cmtest; +SELECT pg_column_compression('cmtest', 'f1'); +-- one normal and one internal dependency +SELECT * FROM cmtest_deps; + +-- rewrite +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz2 PRESERVE (pglz1); +INSERT INTO cmtest VALUES(repeat('1234567890',1003)); +SELECT length(f1) FROM cmtest; +SELECT pg_column_compression('cmtest', 'f1'); +-- two internal dependencies +SELECT * FROM cmtest_deps; + +-- rewrite +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz; +INSERT INTO cmtest VALUES(repeat('1234567890',1004)); +SELECT length(f1) FROM cmtest; +SELECT pg_column_compression('cmtest', 'f1'); +-- one nornal dependency +SELECT * FROM cmtest_deps; + +-- no rewrites +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz1 PRESERVE (pglz); +INSERT INTO cmtest VALUES(repeat('1234567890',1005)); +ALTER TABLE cmtest ALTER COLUMN f1 SET COMPRESSION pglz + WITH (min_input_size '1000') PRESERVE (pglz, pglz1); +INSERT INTO cmtest VALUES(repeat('1234567890',1006)); +-- one nornal dependency and two internal dependencies +SELECT * FROM cmtest_deps; + +-- remove function and related event trigger +DROP FUNCTION on_cmtest_rewrite CASCADE; + +-- test moving +CREATE TABLE cmdata(f1 text COMPRESSION pglz WITH (first_success_by '10000')); +INSERT INTO cmdata VALUES(repeat('1234567890',1000)); +INSERT INTO cmdata VALUES(repeat('1234567890',1001)); + +-- copy with table creation +SELECT * INTO cmmove1 FROM cmdata; + +-- we update using datum from different table +CREATE TABLE cmmove2(f1 text COMPRESSION pglz WITH (min_input_size '100')); +INSERT INTO cmmove2 VALUES (repeat('1234567890',1004)); +UPDATE cmmove2 SET f1 = cmdata.f1 FROM cmdata; + +-- copy to existing table +CREATE TABLE cmmove3(f1 text COMPRESSION pglz WITH (min_input_size '100')); +INSERT INTO cmmove3 SELECT * FROM cmdata; + +-- drop original compression information +DROP TABLE cmdata; + +-- check data is ok +SELECT length(f1) FROM cmmove1; +SELECT length(f1) FROM cmmove2; +SELECT length(f1) FROM cmmove3; + +-- create different types of tables +CREATE TABLE cmexample (f1 TEXT COMPRESSION pglz1); +CREATE TABLE cmtestlike1 (LIKE cmexample INCLUDING COMPRESSION); +CREATE TABLE cmtestlike2 (f2 INT) INHERITS (cmexample); + +\d+ cmtestlike1 +\d+ cmtestlike2 + +-- test two columns +CREATE TABLE cmaltertest(f1 TEXT, f2 TEXT COMPRESSION pglz1); +\d+ cmaltertest; +-- fail, changing one column twice +ALTER TABLE cmaltertest ALTER COLUMN f1 SET COMPRESSION pglz, + ALTER COLUMN f1 SET COMPRESSION pglz; + +-- with rewrite +ALTER TABLE cmaltertest ALTER COLUMN f1 SET COMPRESSION pglz1, + ALTER COLUMN f2 SET COMPRESSION pglz PRESERVE (pglz1); +SELECT pg_column_compression('cmaltertest', 'f1'); +SELECT pg_column_compression('cmaltertest', 'f2'); + +-- no rewrite +ALTER TABLE cmaltertest ALTER COLUMN f1 SET COMPRESSION pglz PRESERVE (pglz1), + ALTER COLUMN f2 SET COMPRESSION pglz2 PRESERVE (pglz1, pglz); +SELECT pg_column_compression('cmaltertest', 'f1'); +SELECT pg_column_compression('cmaltertest', 'f2'); + +-- make pglz2 droppable +ALTER TABLE cmaltertest ALTER COLUMN f2 SET COMPRESSION pglz1; +SELECT pg_column_compression('cmaltertest', 'f1'); +SELECT pg_column_compression('cmaltertest', 'f2'); + +SELECT acname, acattnum, acoptions FROM pg_attr_compression + WHERE acrelid = 'cmaltertest'::regclass OR acrelid = 'cmtest'::regclass; + +-- zlib compression +CREATE TABLE zlibtest(f1 TEXT COMPRESSION zlib WITH (invalid 'param')); +CREATE TABLE zlibtest(f1 TEXT COMPRESSION zlib WITH (level 'best')); +CREATE TABLE zlibtest(f1 TEXT COMPRESSION zlib); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (level 'best_compression'); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (level 'best_speed'); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (level 'default'); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (dict 'one'); +INSERT INTO zlibtest VALUES(repeat('1234567890',1004)); +ALTER TABLE zlibtest + ALTER COLUMN f1 SET COMPRESSION zlib WITH (dict 'one two') PRESERVE (zlib); +INSERT INTO zlibtest VALUES(repeat('1234567890 one two three',1004)); +SELECT length(f1) FROM zlibtest; + +DROP ACCESS METHOD pglz2; +DROP VIEW cmtest_deps; +DROP TABLE cmmove1, cmmove2, cmmove3; +DROP TABLE cmexample, cmtestlike1, cmtestlike2, zlibtest; diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index e2014fc2b5..64bbe004b0 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -1206,7 +1206,9 @@ WHERE p1.amhandler = 0; SELECT p1.oid, p1.amname, p2.oid, p2.proname FROM pg_am AS p1, pg_proc AS p2 WHERE p2.oid = p1.amhandler AND - (p2.prorettype != 'index_am_handler'::regtype OR p2.proretset + ((p2.prorettype != 'index_am_handler'::regtype + AND p2.prorettype != 'compression_am_handler'::regtype) + OR p2.proretset OR p2.pronargs != 1 OR p2.proargtypes[0] != 'internal'::regtype); -- 2.20.1 --MP_/IoARF=uydinlHTRfc3S.uiA Content-Type: text/x-patch Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename=0008-Add-documentation-for-custom-compression-methods-v21.patch