[postgis-users] where did pg_toast,pg_toast_temp1 come from?

[Greg Williamson]

Robert --

>
>Ok thanks,
>
>
>Is it dangerous to delete toast tables?
>


Why would you want to ? They exist for a reason and are integral to postgres' design. 

That said, you can turn off TOAST, restart postgres and then a rewrite of the relevant rows will force the data to be "in-line" I think. Best to raise this issue on the posgres General or admin mail lists though.

... but again, there is a good rationale for TOAST tables (research the postgres documentation for your version and check the general mail list archives) and long varlena values, which a postGIS database can certainly have a lot of. The extra time in grabbing the compressed value from the TOAST table is far outweighed by the improvement in utilization of pages by rows of data.

Greg Williamson

>
>________________________________
>Von: Greg Williamson <gwilliamson39 at yahoo.com>
>An: Robert Buckley <robertdbuckley at yahoo.com>; "postgis-users at postgis.refractions.net" <postgis-users at postgis.refractions.net>; PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>Gesendet: 22:43 Donnerstag, 22.September 2011 
>Betreff: Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?
>
>Rob --
>
><...>
>>
>>Thanks,
>>
>>my version is 8.4. The table contains strings but are not too long...under 100char.
>>
>>Thanks,
>>
>
>
>According to <http://trac.osgeo.org/postgis/wiki/DevWikiPostGISCoding>:
>
>"All PostGIS objects are "varlena", they don't have a fixed size. ..."
>
>So maybe you got some large geometries that triggered TOAST processing ?
>
>Greg W.
>
>>
>>________________________________
>>Von: Greg Williamson <gwilliamson39 at yahoo.com>
>>An: Robert Buckley <robertdbuckley at yahoo.com>; "postgis-users at postgis.refractions.net" <postgis-users at postgis.refractions.net>; PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>>Gesendet: 21:19 Donnerstag, 22.September 2011 
>>Betreff: Re: [postgis-users] where did pg_toast,pg_toast_temp1 come from?
>>
>>Rob -- 
>>
>>>Hi,
>>>
>>>
>>>when I left work today these pg_toast tables were not in my database. when I looked later ther were.
>>>
>>>
>>>Can anyone tell me where they came
from and why they are automatically created in every database?
>>>
>>
>>
>>You don't state what version of postgres this happens on, but in general TOAST tables are created by the system to hold long compressed values (typically text aka varlena tables). I think you can turn this facility off, but in general postgres will try to take very long strings, for example, and compress them, putting them into a toast table to that the row size of the original table doesn't grow excessively. See, for example, 
>> <http://www.postgresql.org/docs/9.0/interactive/storage-toast.html>
>>
>>So I suspect what happened is that someone entered some long text values and postgres created the toast tables to handle these long strings.
>>
>>HTH,
>>
>>Greg
Williamson
>>
>>
>>
>>_______________________________________________
>>postgis-users mailing list
>>postgis-users at postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>>
>
>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>