Feedback-ID: i0fe9450f:Fastmail
Date: Tue, 28 Mar 2023 09:13:10 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Soumyadeep Chakraborty <soumyadeep2007@gmail.com>
Cc: Zhihong Yu <zyu@yugabyte.com>, Justin Pryzby <pryzby@telsasoft.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>,
	Ashwin Agrawal <ashwinstar@gmail.com>, vanjared@vmware.com
Subject: Re: ALTER TABLE SET ACCESS METHOD on partitioned tables
Message-ID: <ZCIxFkgOGv64nYE7@paquier.xyz>
References: <CAE-ML+9zM4wJCGCBGv01k96qQ3gFv4WFcFy=zqPHKeaEFwwv6A@mail.gmail.com>
 <20220518231414.GN19626@telsasoft.com>
 <CAE-ML+_2dfVcxqxNOWooCLLLFRwaSda3_7uXXwsiXZNsh5TZyA@mail.gmail.com>
 <CALNJ-vRWNh87w0jHEep6zzB3rxJWe_TEm-YrJsixTOZ0PyTOdQ@mail.gmail.com>
 <CAE-ML+9NycfS3Q9PhMVpgB2NN62HMa9OBCwNW60AiO8NgCCNXw@mail.gmail.com>
 <ZBepKJRdD2OUTz+O@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="gtBRNuwwskge2Ikj"
Content-Disposition: inline
In-Reply-To: <ZBepKJRdD2OUTz+O@paquier.xyz>
Archived-At: <https://www.postgresql.org/message-id/ZCIxFkgOGv64nYE7%40paquier.xyz>
Precedence: bulk


--gtBRNuwwskge2Ikj
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Mon, Mar 20, 2023 at 09:30:50AM +0900, Michael Paquier wrote:
> Did you check dump and restore flows with partition
> trees and --no-table-access-method?  Perhaps there should be
> some regression tests with partitioned tables?

I was looking at the patch, and as I suspected the dumps generated
are forgetting to apply the AM to the partitioned tables.  For
example, assuming that the default AM is heap, the patch creates
child_0_10 with heap2 as AM, which is what we want:
CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler;
CREATE TABLE parent_tab (id int) PARTITION BY RANGE (id) USING heap2;
CREATE TABLE child_0_10 PARTITION OF parent_tab
  FOR VALUES FROM (0) TO (10);

However a dump produces that (content cut except for its most relevant
parts):
CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler;
SET default_tablespace = '';
CREATE TABLE public.parent_tab (
    id integer
)
PARTITION BY RANGE (id);
SET default_table_access_method = heap2;
CREATE TABLE public.child_0_10 (
    id integer
);

This would restore the previous contents incorrectly, where parent_tab
would use heap and child_0_10 would use heap2, causing any partitions
created after the restore to use silently heap.  This is going to
require a logic similar to tablespaces, where generate SET commands
on default_table_access_method so as --no-table-access-method in
pg_dump and pg_restore are able to work correctly.  Having tests to
cover all that is a must-have.
--
Michael

--gtBRNuwwskge2Ikj
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmQiMRYACgkQnvQgOdby
QH0Bow/+MtPaRInMi10mEa8AU4h0cljuvpQmBeSxP+0eIpnxqElOMEH9WD7eYOkn
TOR7Jnt4ZGZN1NTCREENhHjgK3qNb1FgU/B9KqFBupK10ElWSn8fH9efDddIdgR7
hhWAl6VrgG7WL3fCYKZolQt84bmjhKswPVbKGlo10d1yo5PlObVf6kVxEW60Dk3d
+yl+yCkbF/S+3ONkdRRwMlLYIsHroQ0diu9PBvL2VgJNRiLB8kCXtyKMYM8S3jMx
lw51MnvaGTtJHWUOK/KdKwr807S4dd0B7bK1qWPOzwygakOEUJSyUbrbuttMG3M4
zqPUOH44x5UwmbIHZUUJ5o+TkjtdL1z1z11b01gI2GFCtKjOO+RZEWhbecRr7Pco
70pOC6P3Ir21fXILKlwG9uS+e7Uuff6Nd/5NLAXmimKxaLaVIs0nRZ09EPzMgjFA
lWyPPvYn21V7AQiqkMCh/P8xEeeNWnHt35qzXuZxGXL5NA9yS9MruemnzIEjBDRy
3L3GDJMX/IEagtKGqZTWe735hBO3CLU1XySEZ6OUigMNdsSn3vb/ZM/oeN6Sz9yl
9sosUjnfR8bHreTQ7688f8fCAnyruR1Wx2E1KUNx8TEKq0GQYEkT0AIyfGPstamG
vY89fauQ2Rd5MDoNG/K4bq5KSWG3/DE2mx+9rTWJphHbDpNZWf4=
=AxXW
-----END PGP SIGNATURE-----

--gtBRNuwwskge2Ikj--