Re: Per-table storage parameters for TableAM/IndexAM extensions

On Wed, Dec 29, 2021 at 12:08 PM Sadhuprasad Patro <b(dot)sadhu(at)gmail(dot)com> wrote:
> Open Question: When a user changes AM, then what should be the
> behavior for not supported storage options? Should we drop the options
> and go with only system storage options?
> Or throw an error, in which case the user has to clean the added parameters.

A few people have endorsed the error behavior here but I foresee problems.

Imagine that I am using the "foo" tableam with "compression=lots" and
I want to switch to the "bar" AM which does not support that option.
If I remove the "compression=lots" option using a separate command,
the "foo" table AM may rewrite my whole table and decompress
everything. Then when I convert to the "bar" AM it's going to have to
be rewritten again. That's painful. I clearly need some way to switch
AMs without having to rewrite the table twice.

It's also interesting to consider the other direction. If I am
switching from "bar" to "foo" I would really like to be able to add
the "compression=lots" option at the same time I make the switch.
There needs to be some syntax for that.

One way to solve the first of these problem is to silently drop
unsupported options. Maybe a better way is to have syntax that allows
you to specify options to be added and removed at the time you switch
AMs e.g.:

ALTER TABLE mytab SET ACCESS METHOD bar OPTIONS (DROP compression);
ALTER TABLE mytab SET ACCESS METHOD foo OPTIONS (ADD compression 'lots');

I don't like that particular syntax a ton personally but it does match
what we already use for ALTER SERVER. Unfortunately it's wildly
inconsistent with what we do for ALTER TABLE. Another idea might be
something like:

ALTER TABLE mytab SET ACCESS METHOD bar RESET compression;
ALTER TABLE mytab SET ACCESS METHOD foo SET compression = 'lots';

You'd need to be able to do multiple things with one command e.g.

ALTER TABLE mytab SET ACCESS METHOD baz RESET compression, SET
preferred_fruit = 'banana';

Regardless of the details, I don't think it's viable to just say,
well, rewrite the table multiple times if that's what it takes.

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