On Wed, Mar 24, 2021 at 3:40 PM Dilip Kumar <dilipbal...@gmail.com> wrote:
>
> 0001 ->shows compression method for the index attribute in index describe
> 0002 -> fix the reported bug (test case included)
>
> Apart from this, I was thinking that currently, we are allowing to
> ALTER SET COMPRESSION only for the table and matview, IMHO it makes
> sense to allow to alter the compression method for the index column as
> well? I mean it is just a one-line change, but just wanted to know
> the opinion from others. It is not required for the storage because
> indexes can not have a toast table but index attributes can be
> compressed so it makes sense to allow to alter the compression method.
> Thought?
I have anyway created a patch for this as well. Including all three
patches so we don't lose track.
0001 ->shows compression method for the index attribute in index describe
0002 -> fix the reported bug (test case included)
(optional) 0003-> Alter set compression for index column
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
From 6f83c026ddfe9cda14a9c5e965841b6cd2df0a2a Mon Sep 17 00:00:00 2001
From: Dilip Kumar <dilipkumar@localhost.localdomain>
Date: Wed, 24 Mar 2021 17:08:31 +0530
Subject: [PATCH v3 3/3] ALTER SET COMPRESSION for index columns
---
src/backend/commands/tablecmds.c | 2 +-
src/bin/psql/tab-complete.c | 4 ++--
src/test/regress/expected/compression.out | 16 ++++++++++++++++
src/test/regress/expected/compression_1.out | 6 ++++++
src/test/regress/sql/compression.sql | 4 ++++
5 files changed, 29 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3349bcf..6d5b79d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4335,7 +4335,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_MISC;
break;
case AT_SetCompression: /* ALTER COLUMN SET COMPRESSION */
- ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX);
/* This command never recurses */
/* No command-specific prep needed */
pass = AT_PASS_MISC;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b67f4ea..305665e 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1772,10 +1772,10 @@ psql_completion(const char *text, int start, int end)
}
/* ALTER INDEX <name> ALTER COLUMN <colnum> */
else if (Matches("ALTER", "INDEX", MatchAny, "ALTER", "COLUMN", MatchAny))
- COMPLETE_WITH("SET STATISTICS");
+ COMPLETE_WITH("SET");
/* ALTER INDEX <name> ALTER COLUMN <colnum> SET */
else if (Matches("ALTER", "INDEX", MatchAny, "ALTER", "COLUMN", MatchAny, "SET"))
- COMPLETE_WITH("STATISTICS");
+ COMPLETE_WITH("COMPRESSION", "STATISTICS");
/* ALTER INDEX <name> ALTER COLUMN <colnum> SET STATISTICS */
else if (Matches("ALTER", "INDEX", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "STATISTICS"))
{
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index 19707fb..000983f 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -326,6 +326,22 @@ generate_series(1, 50) g), VERSION());
expr | text | yes | (f1 || f2) | extended | pglz |
unique, btree, for table "public.cmdata2"
+CREATE INDEX idx2 ON cmdata2(f2);
+\d+ idx2
+ Index "public.idx2"
+ Column | Type | Key? | Definition | Storage | Compression | Stats target
+--------+------+------+------------+----------+-------------+--------------
+ f2 | text | yes | f2 | extended | lz4 |
+btree, for table "public.cmdata2"
+
+ALTER INDEX idx2 ALTER COLUMN f2 SET COMPRESSION pglz;
+\d+ idx2
+ Index "public.idx2"
+ Column | Type | Key? | Definition | Storage | Compression | Stats target
+--------+------+------+------------+----------+-------------+--------------
+ f2 | text | yes | f2 | extended | pglz |
+btree, for table "public.cmdata2"
+
-- check data is ok
SELECT length(f1) FROM cmdata;
length
diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out
index 84b933d..761fee4 100644
--- a/src/test/regress/expected/compression_1.out
+++ b/src/test/regress/expected/compression_1.out
@@ -324,6 +324,12 @@ ERROR: relation "cmdata2" does not exist
LINE 1: INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::...
^
\d+ idx1
+CREATE INDEX idx2 ON cmdata2(f2);
+ERROR: relation "cmdata2" does not exist
+\d+ idx2
+ALTER INDEX idx2 ALTER COLUMN f2 SET COMPRESSION pglz;
+ERROR: relation "idx2" does not exist
+\d+ idx2
-- check data is ok
SELECT length(f1) FROM cmdata;
length
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql
index 4afd5a2..6777f11 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression.sql
@@ -137,6 +137,10 @@ CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2));
INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM
generate_series(1, 50) g), VERSION());
\d+ idx1
+CREATE INDEX idx2 ON cmdata2(f2);
+\d+ idx2
+ALTER INDEX idx2 ALTER COLUMN f2 SET COMPRESSION pglz;
+\d+ idx2
-- check data is ok
SELECT length(f1) FROM cmdata;
--
1.8.3.1
From fa3ee05e21af64af86bedcc919488300c4f9a92d Mon Sep 17 00:00:00 2001
From: Dilip Kumar <dilipkumar@localhost.localdomain>
Date: Wed, 24 Mar 2021 15:08:14 +0530
Subject: [PATCH v3 1/3] Show compression method in index describe
---
src/bin/psql/describe.c | 1 +
1 file changed, 1 insertion(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index eeac0ef..9d15324 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1897,6 +1897,7 @@ describeOneTableDetails(const char *schemaname,
if (pset.sversion >= 140000 &&
!pset.hide_compression &&
(tableinfo.relkind == RELKIND_RELATION ||
+ tableinfo.relkind == RELKIND_INDEX ||
tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
tableinfo.relkind == RELKIND_MATVIEW))
{
--
1.8.3.1
From 54edcec2d44cfac01e1c2d20cc413dac57496ca9 Mon Sep 17 00:00:00 2001
From: Dilip Kumar <dilipkumar@localhost.localdomain>
Date: Wed, 24 Mar 2021 14:02:06 +0530
Subject: [PATCH v3 2/3] Fix attcompression for index expression columns
For expression columns the attcompression was not set. So this patch
set it to the default compression method for compressible types otherwise
to the invalid compression method.
---
src/backend/catalog/index.c | 11 +++++++++++
src/test/regress/expected/compression.out | 13 +++++++++++++
src/test/regress/expected/compression_1.out | 14 ++++++++++++++
src/test/regress/sql/compression.sql | 8 ++++++++
4 files changed, 46 insertions(+)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 397d70d..b5a79ce 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -30,6 +30,7 @@
#include "access/relscan.h"
#include "access/sysattr.h"
#include "access/tableam.h"
+#include "access/toast_compression.h"
#include "access/transam.h"
#include "access/visibilitymap.h"
#include "access/xact.h"
@@ -379,6 +380,16 @@ ConstructTupleDescriptor(Relation heapRelation,
to->attalign = typeTup->typalign;
to->atttypmod = exprTypmod(indexkey);
+ /*
+ * For expression column, if attribute type storage is compressible
+ * then set the default compression method, otherwise invalid
+ * compression method.
+ */
+ if (IsStorageCompressible(typeTup->typstorage))
+ to->attcompression = GetDefaultToastCompression();
+ else
+ to->attcompression = InvalidCompressionMethod;
+
ReleaseSysCache(tuple);
/*
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index c2f2e0e..19707fb 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -313,6 +313,19 @@ SELECT pg_column_compression(f1) FROM cmdata;
lz4
(2 rows)
+-- test expression index
+DROP TABLE cmdata2;
+CREATE TABLE cmdata2 (f1 TEXT COMPRESSION pglz, f2 TEXT COMPRESSION lz4);
+CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2));
+INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM
+generate_series(1, 50) g), VERSION());
+\d+ idx1
+ Index "public.idx1"
+ Column | Type | Key? | Definition | Storage | Compression | Stats target
+--------+------+------+------------+----------+-------------+--------------
+ expr | text | yes | (f1 || f2) | extended | pglz |
+unique, btree, for table "public.cmdata2"
+
-- check data is ok
SELECT length(f1) FROM cmdata;
length
diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out
index 6626f8e..84b933d 100644
--- a/src/test/regress/expected/compression_1.out
+++ b/src/test/regress/expected/compression_1.out
@@ -310,6 +310,20 @@ SELECT pg_column_compression(f1) FROM cmdata;
pglz
(2 rows)
+-- test expression index
+DROP TABLE cmdata2;
+CREATE TABLE cmdata2 (f1 TEXT COMPRESSION pglz, f2 TEXT COMPRESSION lz4);
+ERROR: unsupported LZ4 compression method
+DETAIL: This functionality requires the server to be built with lz4 support.
+HINT: You need to rebuild PostgreSQL using --with-lz4.
+CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2));
+ERROR: relation "cmdata2" does not exist
+INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM
+generate_series(1, 50) g), VERSION());
+ERROR: relation "cmdata2" does not exist
+LINE 1: INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::...
+ ^
+\d+ idx1
-- check data is ok
SELECT length(f1) FROM cmdata;
length
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql
index 5e178be..4afd5a2 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression.sql
@@ -130,6 +130,14 @@ SELECT pg_column_compression(f1) FROM cmdata;
VACUUM FULL cmdata;
SELECT pg_column_compression(f1) FROM cmdata;
+-- test expression index
+DROP TABLE cmdata2;
+CREATE TABLE cmdata2 (f1 TEXT COMPRESSION pglz, f2 TEXT COMPRESSION lz4);
+CREATE UNIQUE INDEX idx1 ON cmdata2 ((f1 || f2));
+INSERT INTO cmdata2 VALUES((SELECT array_agg(md5(g::TEXT))::TEXT FROM
+generate_series(1, 50) g), VERSION());
+\d+ idx1
+
-- check data is ok
SELECT length(f1) FROM cmdata;
SELECT length(f1) FROM cmdata1;
--
1.8.3.1
