Re: [HACKERS] How to turn off TOAST on a table/column

On Tue, Nov 27, 2001 at 03:52:27PM -0500, Tom Lane wrote:
> Barry Lind <barry(at)xythos(dot)com> writes:
> > So how do I create a table without toast enabled?
>
> Unless you want to muck with the backend code, the only way to create
> a table that has no toast table attached is to declare columns that
> the backend can prove to itself will never add up to more than BLCKSZ
> space per tuple. For example, use varchar(n) not text. (If you've got
> MULTIBYTE enabled then that doesn't work either, since the n is
> measure in characters not bytes.)
>
> However, the mere existence of a toast table doesn't cost anything
> (except for some increase of the time for CREATE TABLE). What you
> probably really want to do is turn on and off the *use* of the toast
> table. Which you can do by mucking with the attstorage attributes of
> the table columns. I don't think anyone's gotten round to providing
> a nice clean ALTER TABLE interface, but a quick
>
> UPDATE pg_attribute SET attstorage = 'p'
> WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable')
> AND attnum > 0
>
> would suffice to disable toasting of all columns in 'mytable'.

This would reimpose the max-tuple limit on that table, would it not?
So trying to store 'too large' a text would error? Definitely one for
the regression tests, once we've got that ALTER TABLE interface.

>
> See src/include/pg_attribute.h for documentation of the allowed values
> for attstorage.

This needs to get into the admin docs. I suppose it's also waiting on the
ALTER TABLE interface.

Ross