Re: table inheritance versus column compression and storage settings - Mailing list pgsql-hackers

On Mon, Feb 12, 2024 at 8:48 PM Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 08.02.24 08:20, Ashutosh Bapat wrote:
> > On Wed, Feb 7, 2024 at 12:47 PM Ashutosh Bapat
> > <ashutosh.bapat.oss@gmail.com> wrote:
> >
> >> 0001 fixes compression inheritance
> >> 0002 fixes storage inheritance
> >>
> >
> > The first patch does not update compression_1.out which makes CI
> > unhappy. Here's patchset fixing that.
>
> The changed behavior looks good to me.  The tests are good, the code
> changes are pretty straightforward.
>
> Did you by any change check that pg_dump dumps the resulting structures
> correctly?  I notice in tablecmds.c that ALTER COLUMN SET STORAGE
> recurses but ALTER COLUMN SET COMPRESSION does not.  I don't understand
> why that is, and I wonder whether it affects pg_dump.
>

I used src/bin/pg_upgrade/t/002_pg_upgrade.pl to test dump and restore
by leaving back the new objects created in compression.sql and
inherit.sql.

COMPRESSION is set using ALTER TABLE ONLY so it affects only the
parent and should not propagate to children. A child inherits the
parent first and then changes compression property. For example
```
CREATE TABLE public.cmparent1 (
    f1 text
);
ALTER TABLE ONLY public.cmparent1 ALTER COLUMN f1 SET COMPRESSION pglz;

CREATE TABLE public.cminh1 (
    f1 text
)
INHERITS (public.cmparent1);
ALTER TABLE ONLY public.cminh1 ALTER COLUMN f1 SET COMPRESSION lz4;
```

Same is true with the STORAGE parameter. Example
```
CREATE TABLE public.stparent1 (
    a text
);
ALTER TABLE ONLY public.stparent1 ALTER COLUMN a SET STORAGE PLAIN;

CREATE TABLE public.stchild1 (
    a text
)
INHERITS (public.stparent1);
ALTER TABLE ONLY public.stchild1 ALTER COLUMN a SET STORAGE PLAIN;
```

I don't think pg_dump would be affected by the difference you noted.

--
Best Wishes,
Ashutosh Bapat