Re: Which SET TYPE don't actually require a rewrite

On Wed, Jul 15, 2020 at 6:25 PM Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>
> Our Fine Manual (TM) specifies:
> "As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt."
>
> First of all, how is a non-internals-expert even supposed to know what a binary coercible type is? That's not a very user-friendly way to say it.
>
> Second, how is even an expert supposed to find the list? :)
>
> For example, we can query pg_cast for casts that are binary coercible, that's a start, but it doesn't really tell us the answer.
>
> We can also for example increase the precision of numeric without a rewrite (but not scale). Or we can change between text and varchar. And we can increase the length of a varchar but not decrease it.
>
> Surely we can do better than this when it comes to documenting it? Even if it's a pluggable thing so it may or may not be true of external datatypes installed later, we should be able to at least be more clear about the builtin types, I think?
>

+1 for providing more information in the documentation. One way could
be that we give some examples of how a user can check whether types
are binary coercible or not and then also specify clearly in which
other cases the rewrite can happen. Similarly, it seems the
information when the rewrite can happen for "SET (storage_parameter
...)" (doc says: "depending on the parameter you might need to rewrite
the table to get the desired effects") is thin.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com