Re: Small issues with CREATE TABLE COMPRESSION

Hi,

My analysis of this open item is there are no code-level issues here,
but there is one line of documentation that clearly got forgotten,
some other documentation changes that might be nice, and maybe someone
wants to work more on testing and/or tab completion at some point.

On Tue, Apr 27, 2021 at 2:22 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> The behavior around CREATE TABLE AS and matviews is a bit confusing,
> and not documented.

It's no different from a bunch of other column properties that you
also can't set when creating a materialized view. I don't think this
patch created this problem, or that it is responsible for solving it.

> And so, ALTER MATERIALIZED VIEW supports SET COMPRESSION but this is
> not mentioned in its docs:

I agree that's an oversight and should be fixed.

> https://www.postgresql.org/docs/devel/sql-altermaterializedview.html
> psql could have tab completion support for that.

I don't believe it's our policy that incomplete tab completion support
rises to the level of an open item, especially given that, as Justin
points out, it's not supported for ALTER MATERIALIZED VIEW name ALTER
COLUMN name <tab> doesn't complete anything *at all*.

> There are no tests in pg_dump to make sure that some ALTER
> MATERIALIZED VIEW or ALTER TABLE commands are generated when the
> compression of a matview's or table's column is changed.

True, but it does seem to work. I am happy if you or anyone want to
write some tests.

> Another thing is the handling of the per-value compression that could
> be confusing to the user. As no materialization of the data is done
> for a CTAS or a matview, and the regression tests of compression.sql
> track that AFAIK, there can be a mix of toast values compressed with
> lz4 or pglz, with pg_attribute.attcompression being one or the other.

Yes. This is mentioned in the commit message, and was discussed
extensively on the original thread. We probably should have included
it in the documentation, as well. Justin's text seems fairly
reasonable to me.

> Similarly, we may want to document that COMPRESSION does not trigger a
> table rewrite, but that it is effective only for the new toast values
> inserted if a tuple is rebuilt and rewritten?

Sure. I think Justin's text covers this, too.

> Would it be better to document that pg_column_compression() returns
> NULL if the column is not a toastable type or if the column's value is
> not compressed?

We can.

Here's a proposed patch for the documentation issues not covered by
Justin's proposal.

--
Robert Haas
EDB: http://www.enterprisedb.com