May. 30th, 2016

nataraj: (Бритый небритый)
When you are creating table in postgres, you are creating up to two relations in a row.
In case when you create table with fixed-length attributes only, only one relation is created. A heap relation.
If you have at least one variable-length attribute in your table, then both heap and toast relations will be created.

Relations also have options: reloptions. You can set them while creating and altering table. To set options for toast relations you should use toast. prefix before reloption name:
CREATE TABLE reloptions_test (s varchar) WITH (toast.autovacuum_vacuum_cost_delay = 23 );

The only problem is that if you have table with no varlen values, postgres will accept toast reloption, but will not write it anywhere.
#CREATE TABLE reloptions_test (i int) WITH (toast.autovacuum_vacuum_cost_delay = 23 );
CREATE TABLE
# select reltoastrelid from pg_class where oid = 'reloptions_test'::regclass;
 reltoastrelid 
---------------
             0
(1 row)

there is no toast relation and reloption is not saved at all, postgres reports, everything is ok

Same for alter table:
# ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24 );
ALTER TABLE
# select reltoastrelid from pg_class where oid = 'reloptions_test'::regclass;
 reltoastrelid 
---------------
             0
(1 row)

This is not nice behavior, isn't it?

PS please when writing a comment, login with any account you have, or just leave a name and/or e-mail so I will be able to answer that comment ;-)

Profile

nataraj: (Default)
Swami Dhyan Nataraj

July 2024

S M T W T F S
 123456
789 10111213
14151617181920
21222324252627
28293031   

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Jun. 9th, 2025 09:53 am
Powered by Dreamwidth Studios
OSZAR »