Re: ALTER TABLE on system catalogs

From: John Naylor <john(dot)naylor(at)2ndquadrant(dot)com>
To: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
Cc: peter(dot)eisentraut(at)2ndquadrant(dot)com, andres(at)anarazel(dot)de, tgl(at)sss(dot)pgh(dot)pa(dot)us, michael(at)paquier(dot)xyz, pgsql-hackers(at)postgresql(dot)org
Subject: Re: ALTER TABLE on system catalogs
Date: 2019-02-16 15:04:59
Message-ID: CACPNZCtMkk7-tc0Uh1iw78bsKOgradv2CQiwVgJWpV2BHR1wYg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 2/8/19, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> [v2 patch]

I poked this around a bit and found that this mechanism only works for
bootstrapped tables, as those are the only ones where we can scribble
on pg_attribute entries directly during bootstrap. As such, with this
patch we cannot perform ALTER TABLE for pg_index or pg_largeobject*
[1]. IMHO, it's not worth it to introduce new notation unless it
offers complete coverage. If we're willing to only solve the problem
for pg_class and pg_attribute, I'd rather mark the table rather than
the columns, because we already have visibility into CATALOG_VARLEN.
(rough example attached)

On 2/14/19, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> That already exists: 'm': Value can be stored compressed inline
>
> I agree that it seems we should be using that for those tables that
> don't have a toast table. Maybe the genbki stuff could do it
> automatically for the appropriate catalogs.

The docs say:
(Actually, out-of-line storage will still be performed for such
columns, but only as a last resort when there is no other way to make
the row small enough to fit on a page.)

If we allow 'm' as an exception, would that interfere with this? My
demo patch has this just in case:

- if (att->attstorage != 'p')
+ if (att->attstorage != 'p' &&
+ !(att->attstorage == 'm' && IsCatalogRelation(rel)))
has_toastable_attrs = true;

Here's another idea: During initdb, do "ALTER TABLE ALTER COLUMN xyz
SET STORAGE MAIN;"
In initdb, we already pass "-O" to allow system table mods, so I think
we would have to just make sure this one statement doesn't try to add
a toast table. We could have genbki.pl emit a file with SQL statements
to cover all necessary tables/columns.

[1] https://www.postgresql.org/message-id/20180928190630.crt43sk5zd5p555h%40alvherre.pgsql

--
John Naylor https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
allow-alter-table-on-pg_class-pg_attribute.patch text/x-patch 4.4 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2019-02-16 15:10:25 Re: allow online change primary_conninfo
Previous Message Michael Paquier 2019-02-16 15:01:50 Re: 2019-03 CF Summary / Review - Tranche #2