Re: autovacuum and TOAST tables

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Note that this patch allows a toast table to be vacuumed by the user:
> > I don't have a problem with that, but if anyone thinks this is not a
> > good idea, please speak up.
>
> The permissions on pg_toast will prevent anyone but a superuser from
> doing that anyway, so it's no big deal.
>
> Possibly more interesting is what happens if someone drops the parent
> table while VACUUM is working independently on the toast table. Does
> DROP take exclusive lock on a toast table? Probably, but it needs
> to be checked.

Yes, it does. So the autovacuum process working on the TOAST table
would get cancelled by the DROP TABLE, TRUNCATE, CLUSTER. The one ALTER
TABLE variant that I think needs to handle the TOAST table is ALTER
TYPE, but I think it should work that it is being vacuumed concurrently.
REINDEX TABLE should perhaps also be concerned because it does reindex
the toast table, but it grabs the lock before actually doing the
reindexing so I don't think there's a problem here.

BTW only now I notice that CLUSTER leaves the toast table name in bad
shape: if you create a table with OID X its TOAST table is named
pg_toast_X. If you then cluster this table, a new transient table gets
created with OID Y; the TOAST table for Y is named pg_toast_Y, and then
this new TOAST table is used as the new TOAST table for the original
table X. So you end up with table OID X having TOAST table pg_toast_Y.

This is not a concern from the system standpoint because it doesn't use
this name for anything, but people looking at the catalogs manually may
be taken by surprise.

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