From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter@eisentraut.org>
cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
        pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: table inheritance versus column compression and storage settings
In-reply-to: <3426460.1708095481@sss.pgh.pa.us>
References: <24656cec-d6ef-4d15-8b5b-e8dfc9c833a7@eisentraut.org> <CAExHW5uH0q810ZZ7ZP2Bvz_aF33yPTUsZ5NNC=yvA1MsVVtDTw@mail.gmail.com> <38546838-8a60-4d50-ba8e-b2c7cf2cb64b@eisentraut.org> <CAExHW5uAqaO7RcDgBiwm2pVO58evFY8AKzvVks-2dn3FKyWUkQ@mail.gmail.com> <CAExHW5sVAMNBNeD8-nih8HY8n4ySJWe9Uf+QNRTMOD7u-De-CQ@mail.gmail.com> <CAExHW5v+Au_kadPJd7ifmKLzFu+fbtkDp5duuzuS6bZdLJsD1w@mail.gmail.com> <eb076b15-64b5-4992-b232-9e00603e0849@eisentraut.org> <CAExHW5tgaiJynSWA25ufWwnXex8s9475G23+7DNnhifP9NPNvw@mail.gmail.com> <c3090836-9da9-48ed-8332-031bd0fee457@eisentraut.org> <3426460.1708095481@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 16 Feb 2024 09:58:01 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3462357.1708107856.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 16 Feb 2024 13:24:16 -0500
Message-ID: <3462358.1708107856@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3462358.1708107856%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> I find it surprising that the committed patch does not touch
> pg_dump.  Is it really true that pg_dump dumps situations with
> differing compression/storage settings accurately already?

It's worse than I thought.  Run "make installcheck" with
today's HEAD, then:

$ pg_dump -Fc regression >r.dump
$ createdb r2
$ pg_restore -d r2 r.dump =

pg_restore: error: could not execute query: ERROR:  column "a" inherits co=
nflicting storage methods
HINT:  To resolve the conflict, specify a storage method explicitly.
Command was: CREATE TABLE public.stchild4 (
    a text
)
INHERITS (public.stparent1, public.stparent2);
ALTER TABLE ONLY public.stchild4 ALTER COLUMN a SET STORAGE MAIN;


pg_restore: error: could not execute query: ERROR:  relation "public.stchi=
ld4" does not exist
Command was: ALTER TABLE public.stchild4 OWNER TO postgres;

pg_restore: error: could not execute query: ERROR:  relation "public.stchi=
ld4" does not exist
Command was: COPY public.stchild4 (a) FROM stdin;
pg_restore: warning: errors ignored on restore: 3


What I'd intended to compare was the results of the query added to the
regression tests:

regression=3D# SELECT attrelid::regclass, attname, attstorage FROM pg_attr=
ibute
WHERE (attrelid::regclass::name like 'stparent%'
OR attrelid::regclass::name like 'stchild%')
and attname =3D 'a'
ORDER BY 1, 2;
 attrelid  | attname | attstorage =

-----------+---------+------------
 stparent1 | a       | p
 stparent2 | a       | x
 stchild1  | a       | p
 stchild3  | a       | m
 stchild4  | a       | m
 stchild5  | a       | x
 stchild6  | a       | m
(7 rows)

r2=3D# SELECT attrelid::regclass, attname, attstorage FROM pg_attribute
WHERE (attrelid::regclass::name like 'stparent%'
OR attrelid::regclass::name like 'stchild%')
and attname =3D 'a'
ORDER BY 1, 2;
 attrelid  | attname | attstorage =

-----------+---------+------------
 stparent1 | a       | p
 stchild1  | a       | p
 stchild3  | a       | m
 stparent2 | a       | x
 stchild5  | a       | p
 stchild6  | a       | m
(6 rows)

So not only does stchild4 fail to restore altogether, but stchild5
ends with the wrong attstorage.

This patch definitely needs more work.

			regards, tom lane