Allowing ALTER TYPE to change storage strategy
От | Tomas Vondra |
---|---|
Тема | Allowing ALTER TYPE to change storage strategy |
Дата | |
Msg-id | 20200228004440.b23ein4qvmxnlpht@development обсуждение исходный текст |
Ответы |
Re: Allowing ALTER TYPE to change storage strategy
|
Список | pgsql-hackers |
Hi, From time to time I run into the limitation that ALTER TYPE does not allow changing storage strategy. I've written a bunch of data types over the years - in some cases I simply forgot to specify storage in CREATE TYPE (so it defaulted to PLAIN) or I expected PLAIN to be sufficient and then later wished I could enable TOAST. Obviously, without ALTER TYPE supporting that it's rather tricky. You either need to do dump/restore, or tweak the pg_type catalog directly. So here is an initial patch extending ALTER TYPE to support this. The question is why this was not implemented before - my assumption is this is not simply because no one wanted that. We track the storage in pg_attribute too, and ALTER TABLE allows changing that ... My understanding is that pg_type.typstorage essentially specifies two things: (a) default storage strategy for the attributes with this type, and (b) whether the type implementation is prepared to handle TOAST-ed values or not. And pg_attribute.attstorage has to respect this - when the type says PLAIN then the attribute can't simply use strategy that would enable TOAST. Luckily, this is only a problem when switching typstorage to PLAIN (i.e. when disabling TOAST for the type). The attached v1 patch checks if there are attributes with non-PLAIN storage for this type, and errors out if it finds one. But unfortunately that's not entirely correct, because ALTER TABLE only sets storage for new data. A table may be created with e.g. EXTENDED storage for an attribute, a bunch of rows may be loaded and then the storage for the attribute may be changed to PLAIN. That would pass the check as it's currently in the patch, yet there may be TOAST-ed values for the type with PLAIN storage :-( I'm not entirely sure what to do about this, but I think it's OK to just reject changes in this direction (from non-PLAIN to PLAIN storage). I've never needed it, and it seems pretty useless - it seems fine to just instruct the user to do a dump/restore. In the opposite direction - when switching from PLAIN to a TOAST-enabled storage, or enabling/disabling compression, this is not an issue at all. It's legal for type to specify e.g. EXTENDED but attribute to use PLAIN, for example. So the attached v1 patch simply allows this direction. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Вложения
В списке pgsql-hackers по дате отправления: