Thread: reloptions and toast tables

Right now we don't allow setting reloptions to toast tables:

=# alter table pg_toast.pg_toast_16395 set (fillfactor = 40);
ERROR:  "pg_toast_16395" is not a table or index

However this is needed for autovacuum, per previous discussion.

I'm wondering if I should just allow all reloptions (including
fillfactor) or just the autovacuum ones.

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

Alvaro Herrera escreveu:
> I'm wondering if I should just allow all reloptions (including
> fillfactor) or just the autovacuum ones.
> 
Yes, please. But i'm afraid it is too 'complicated' to expose
'pg_toast.pg_toast_xxxxx' to user (but we can solve it with good
documentation). What about xxx_toast reloptions? The con is that we need to
add 2 reloptions if the new reloption is table-related.


--  Euler Taveira de Oliveira http://www.timbira.com/

Euler Taveira de Oliveira wrote:
> Alvaro Herrera escreveu:
> > I'm wondering if I should just allow all reloptions (including
> > fillfactor) or just the autovacuum ones.
> > 
> Yes, please. But i'm afraid it is too 'complicated' to expose
> 'pg_toast.pg_toast_xxxxx' to user (but we can solve it with good
> documentation). What about xxx_toast reloptions? The con is that we need to
> add 2 reloptions if the new reloption is table-related.

Hmm, now that I look at that again, it seems a very bad idea. 

Your idea of having separate options for the toast table, I take you
mean having toast_autovacuum_enabled and such, and they would be
attached to the main table?  If that's what you mean, I admit I don't
like it either -- we would duplicate the size of the reloptions table
for no good reason :-(

It would be better to have a separate command, that doesn't force the
user to look up the toast table name.  I'm not sure what such a syntax
would actually look like though.  I'm open to ideas.

ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
ALTER TABLE foo SET (toast.autovacuum_enabled = false);
ALTER TABLE foo TOAST SET (autovacuum_enabled = false);
ALTER TABLE foo SET TOAST (autovacuum_enabled = false);

...?

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Euler Taveira de Oliveira wrote:
>> Yes, please. But i'm afraid it is too 'complicated' to expose
>> 'pg_toast.pg_toast_xxxxx' to user (but we can solve it with good
>> documentation).

> Hmm, now that I look at that again, it seems a very bad idea. 

Yeah --- whatever solution you pick should be amenable to letting
pg_dump preserve the settings.  Directly referencing the toast
table seems right out on that basis.
        regards, tom lane

Alvaro Herrera napsal(a):

> ALTER TABLE foo SET (toast.autovacuum_enabled = false);

+1

Do not forget on toast index as well.

ALTER TABLE foo SET (toast_idx.fillfactor = 50);

Another potential problem with toast setting is that reloption is toastable and 
it could generates loops in detoasting pg_class tuples. For example toast chunk 
size cannot be implement like reloption (or pg_class should use every time 
default values).
    Zdenek

Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> Another potential problem with toast setting is that reloption is toastable and 
> it could generates loops in detoasting pg_class tuples. For example toast chunk 
> size cannot be implement like reloption (or pg_class should use every time 
> default values).

Nonsense.  Toast chunk size isn't going to become variable *at all*,
unless we go over to the proposed toast indexing method that allows
the chunks to be self-identifying; in which case there's no problem
in pg_class or anyplace else.
        regards, tom lane

Tom Lane napsal(a):
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> Another potential problem with toast setting is that reloption is toastable and 
>> it could generates loops in detoasting pg_class tuples. For example toast chunk 
>> size cannot be implement like reloption (or pg_class should use every time 
>> default values).
> 
> Nonsense.  Toast chunk size isn't going to become variable *at all*,
> unless we go over to the proposed toast indexing method that allows
> the chunks to be self-identifying; in which case there's no problem
> in pg_class or anyplace else.

I know. It was only example that some reloption cannot be applied on pg_class 
relation and pg_class toast table, because it could introduce chicken/egg problem.
    Zdenek

On 12/20/08, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>
> ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
...
> ALTER TABLE foo SET TOAST (autovacuum_enabled = false);
>

i will be happy with any of this options (actually i prefer the second
one but don't have a strong argument against the first)

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Sunday 21 December 2008 01:48:42 Alvaro Herrera wrote:
> ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
> ALTER TABLE foo SET (toast.autovacuum_enabled = false);
> ALTER TABLE foo TOAST SET (autovacuum_enabled = false);
> ALTER TABLE foo SET TOAST (autovacuum_enabled = false);

The last two don't appear to allow setting TOAST and non-TOAST options in one 
go.  I think it would be handy to allow that, though.

Peter Eisentraut wrote:
> On Sunday 21 December 2008 01:48:42 Alvaro Herrera wrote:
> > ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
> > ALTER TABLE foo SET (toast.autovacuum_enabled = false);
> > ALTER TABLE foo TOAST SET (autovacuum_enabled = false);
> > ALTER TABLE foo SET TOAST (autovacuum_enabled = false);
>
> The last two don't appear to allow setting TOAST and non-TOAST options in one
> go.  I think it would be handy to allow that, though.

Agreed -- so I'm now playing with this version:

> > ALTER TABLE foo SET (TOAST autovacuum_enabled = false);

So the grammar modifications needed to accept that are attached.  The
support code is a lot messier than I'd like :-(

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Peter Eisentraut wrote:
>> On Sunday 21 December 2008 01:48:42 Alvaro Herrera wrote:
>>> ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
>>> ALTER TABLE foo SET (toast.autovacuum_enabled = false);
>>> ALTER TABLE foo TOAST SET (autovacuum_enabled = false);
>>> ALTER TABLE foo SET TOAST (autovacuum_enabled = false);
>> 
>> The last two don't appear to allow setting TOAST and non-TOAST options in one 
>> go.  I think it would be handy to allow that, though.

> Agreed -- so I'm now playing with this version:

> ALTER TABLE foo SET (TOAST autovacuum_enabled = false);

> So the grammar modifications needed to accept that are attached.  The
> support code is a lot messier than I'd like :-(

This is not only really ugly, but 100% toast-specific.  The
qualified-name approach ("toast.autovacuum_enabled") has at least
a chance of being good for something else.  Or just make it
toast_autovacuum_enabled and do the translation magic at some low
level in the statement execution code.
        regards, tom lane

On Wed, Dec 31, 2008 at 9:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Peter Eisentraut wrote:
>>> On Sunday 21 December 2008 01:48:42 Alvaro Herrera wrote:
>>>> ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
>>>> ALTER TABLE foo SET (toast.autovacuum_enabled = false);
>>>> ALTER TABLE foo TOAST SET (autovacuum_enabled = false);
>>>> ALTER TABLE foo SET TOAST (autovacuum_enabled = false);
>>>
>>> The last two don't appear to allow setting TOAST and non-TOAST options in one
>>> go.  I think it would be handy to allow that, though.
>
>> Agreed -- so I'm now playing with this version:
>
>> ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
>
>> So the grammar modifications needed to accept that are attached.  The
>> support code is a lot messier than I'd like :-(
>
> This is not only really ugly, but 100% toast-specific.  The
> qualified-name approach ("toast.autovacuum_enabled") has at least
> a chance of being good for something else.  Or just make it
> toast_autovacuum_enabled and do the translation magic at some low
> level in the statement execution code.

Are we expecting this patch (or whatever it turns into) to go into
8.4? It was marked as WIP when feature freeze started and clearly
still is quite undefined at this stage.

The reason I raise this is that this is precisely the sort of patch
that has a major knock-on effect to the tools the many people expect
to be able to use with a new version of the server as soon as it's
released. Obviously we need our own freeze and beta periods prior to
that time which is already extremely tight as we wait for last minute
changes in the server that need support.  The last thing we need is
for something like the per-table vacuum settings interface to
redefined right before beta as that is likely to require a fair amount
of re-working.

This is something I think we need to be more mindful of as our
project, it's surrounding eco-system of tools and users expectations
grow.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Dave Page wrote:

> Are we expecting this patch (or whatever it turns into) to go into
> 8.4? It was marked as WIP when feature freeze started and clearly
> still is quite undefined at this stage.

Right.  This is a fair objection.  I started just by reviewing the
autovacuum-in-reloptions patch, but it turned out to be unworkable in
quite some ways, so I'm reworking it.

I need some more opinions on whether I should continue working here, or
stop and leave it for 8.5.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> I need some more opinions on whether I should continue working here, or
> stop and leave it for 8.5.

Bruce and I were just talking yesterday about the need to start closing
down this commitfest.  I'm not sure what the schedule is going to end
up being; but if you can't see a pretty short path to finishing whatever
development still needs doing, my advice is to set it aside for 8.5.
        regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> I need some more opinions on whether I should continue working here, or
>> stop and leave it for 8.5.
> 
> Bruce and I were just talking yesterday about the need to start closing
> down this commitfest.  I'm not sure what the schedule is going to end
> up being; but if you can't see a pretty short path to finishing whatever
> development still needs doing, my advice is to set it aside for 8.5.

I agree in principle. OTOH, the current behavior can almost be defined
as a bug, since we can't restore any changes you make to the autovacuum
configuration. I know it's by design, but it makes it pretty fragile to
use the ability to change the configuration at all...

//Magnus

Tom Lane wrote:

> This is not only really ugly, but 100% toast-specific.  The
> qualified-name approach ("toast.autovacuum_enabled") has at least
> a chance of being good for something else.  Or just make it
> toast_autovacuum_enabled and do the translation magic at some low
> level in the statement execution code.

Does this look better?

This is still WIP, as some cases are not handled correctly; but with
this patch it's possible to set a toast table fillfactor (not that
that's useful for anything AFAICS).

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