Re: Allowing ALTER TYPE to change storage strategy

On Wed, Mar 4, 2020 at 4:15 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > I think this is committable --- how about you?
>
> ... or not. I just noticed that the typcache tracks each type's
> typstorage setting, and there's no provision for flushing/reloading
> that.
>
> As far as I can find, there is only one place where the cached
> value is used, and that's in rangetypes.c which needs to know
> whether the range element type is toastable. (It doesn't actually
> need to know the exact value of typstorage, only whether it is or
> isn't PLAIN.)
>
> [...]

>
> 3. Drop the ability for ALTER TYPE to promote from PLAIN to not-PLAIN
> typstorage, and adjust the typcache so that it only remembers boolean
> toastability not the specific toasting strategy. Then the cache is
> still immutable so no need for update logic.
>
> I'm kind of liking #3, ugly as it sounds, because I'm not sure how
> much of a use-case there is for the upgrade-from-PLAIN case.
> Particularly now that TOAST is so ingrained in the system, it seems
> rather unlikely that a production-grade data type wouldn't have
> been designed to be toastable from the beginning, if there could be
> any advantage to that. Either #1 or #2 seem like mighty high prices
> to pay for offering an option that might have no real-world uses.
>

Tomas' opening paragraph for this thread indicated this was motivated by
the plain-to-toast change but I'm not in a position to provide independent
insight.

Without that piece this is mainly about being able to specify a type's
preference for when and how it can be toasted. That seems like sufficient
motivation, though that functionality seems basic enough that I'm wondering
why it hasn't come up before now (this seems like a different topic of
wonder than what Tomas mentioned in the OP).

Is there also an issue with whether the type has implemented compression or
not - i.e., should the x->e and m->e paths be forbidden too? Or is it
always the case a non-plain type is compressible and the other non-plain
options just switch between preferences (so External just says "while I can
be compressed, please don't")?
Separately...

Can you please include an edit to [1] indicating that "e" is the
abbreviation for External and "x" is Extended (spelling out the other two
as well). Might be worth a comment at [2] as well.

[1] https://www.postgresql.org/docs/12/catalog-pg-type.html
[2] https://www.postgresql.org/docs/12/storage-toast.html

Thanks!

David J.