Skip site navigation (1) Skip section navigation (2)

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

From: "Ross J(dot) Reedstrom" <reedstrm(at)rice(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Barry Lind <barry(at)xythos(dot)com>, pgsql-general(at)postgresql(dot)org, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] How to turn off TOAST on a table/column
Date: 2001-11-27 22:02:00
Message-ID: 20011127160200.B10361@rice.edu (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2001-11-27 22:08:32
Subject: Re: [HACKERS] How to turn off TOAST on a table/column
Previous:From: Tom LaneDate: 2001-11-27 21:27:57
Subject: Re: Announcement: I've joined Red Hat

pgsql-general by date

Next:From: Tom LaneDate: 2001-11-27 22:08:32
Subject: Re: [HACKERS] How to turn off TOAST on a table/column
Previous:From: John GrayDate: 2001-11-27 21:14:41
Subject: Re: How to turn off TOAST on a table/column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group