On Wed, Dec 29, 2021 at 12:08 PM Sadhuprasad Patro <b.sa...@gmail.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
