Postgres Pro
Downloads
Home   >   mailing lists

Thread: Toast rel options

Toast rel options

From
Simon Riggs
Date:
It's possible to set toast reloptions for tables that don't have toast
tables at all. e.g. 

create table test (c1 integer);
ALTER TABLE test SET (toast.autovacuum_enabled = off);

Why?

-- Simon Riggs           www.2ndQuadrant.com

Re: Toast rel options

From
Tom Lane
Date:
Simon Riggs <simon@2ndQuadrant.com> writes:
> It's possible to set toast reloptions for tables that don't have toast
> tables at all. e.g. 

> create table test (c1 integer);
> ALTER TABLE test SET (toast.autovacuum_enabled = off);

> Why?

1.  Why not?

2. They might have toast tables later, after an ALTER ADD COLUMN
for instance.

3. They might have had/needed a toast table in the past.  Do
we need to make ALTER DROP COLUMN capable of flushing those
reloptions, so that they won't cause a failure at dump/reload?

I don't see any advantage whatsoever to forbidding this, and
a lot of corner cases to take care of if we did try to forbid it.
        regards, tom lane

Re: Toast rel options

From
Simon Riggs
Date:
On Wed, 2010-04-28 at 21:11 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > It's possible to set toast reloptions for tables that don't have toast
> > tables at all. e.g. 
> 
> > create table test (c1 integer);
> > ALTER TABLE test SET (toast.autovacuum_enabled = off);
> 
> > Why?
> 
> 1.  Why not?
> 
> 2. They might have toast tables later, after an ALTER ADD COLUMN
> for instance.

Main reason is that this doesn't do anything. The toast reloptions are
stored on the toast table, so if it doesn't exist then there are no
reloptions. They aren't saved for later and won't be set of later add a
column which causes a toast table to be added.

> 3. They might have had/needed a toast table in the past.  Do
> we need to make ALTER DROP COLUMN capable of flushing those
> reloptions, so that they won't cause a failure at dump/reload?
> 
> I don't see any advantage whatsoever to forbidding this, and
> a lot of corner cases to take care of if we did try to forbid it.

Not required.

Why do we protect against this?

postgres=# alter table test set (nonexistent = on);
ERROR:  unrecognized parameter "notexistent"

Also, this seems not to work?

postgres=# alter table test set (my.expectation = on);
ERROR:  unrecognized parameter namespace "my"

I thought we had enabled custom table options in this release?
Or was that deferred, or even rejected completely?

-- Simon Riggs           www.2ndQuadrant.com

Re: Toast rel options

From
Alvaro Herrera
Date:
Simon Riggs wrote:

> Also, this seems not to work?
> 
> postgres=# alter table test set (my.expectation = on);
> ERROR:  unrecognized parameter namespace "my"
> 
> I thought we had enabled custom table options in this release?
> Or was that deferred, or even rejected completely?

IIRC you can define your own parameter namespaces for access methods you
define, but you can't add namespaces to hardcoded AMs.  Possibly not a
very useful definition (because you don't really define new AMs all that
frequently), but it covers what was needed at the time.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Toast rel options

From
Alvaro Herrera
Date:
Simon Riggs wrote:

> Why do we protect against this?
> 
> postgres=# alter table test set (nonexistent = on);
> ERROR:  unrecognized parameter "notexistent"

Eh?  The parameter doesn't exist.  It's not exactly the same as defining
a value for a parameter that exists but is unused.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support