Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1riRuP-00HDoF-Jd for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Mar 2024 04:32:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1riRuO-00ACAX-6k for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Mar 2024 04:32:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1riRuN-00ACAO-2P for pgsql-hackers@lists.postgresql.org; Fri, 08 Mar 2024 04:32:36 +0000 Received: from fout2-smtp.messagingengine.com ([103.168.172.145]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1riRuD-003Q7t-Mm for pgsql-hackers@postgresql.org; Fri, 08 Mar 2024 04:32:34 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.48]) by mailfout.nyi.internal (Postfix) with ESMTP id E377913800A8; Thu, 7 Mar 2024 23:32:25 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute7.internal (MEProxy); Thu, 07 Mar 2024 23:32:25 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm1; t=1709872345; x=1709958745; bh=DKkk98C8zO CSuFt0XfPfD+/TfJ/+9nedV4FKOCncuX4=; b=gSfQZ2bCGgjkergZk+LQd0ajXH sfJhlgDfaadzMrauIe3fzklCkGIV1dPOmvgIaxcPIJAcVBGhg8MhCSeoNHOUwtpk QtCwVbMEVSGvRmBrhECyw0YT9sseMvxjYZwH2pWKrnX+sfplIh+bASoKevGRhwHM 5kNBZmuJ69l7l0APtlDeQ9JJCS9j0Bj4eYhtN8t7oYq87IudqP0skB/Y+HRmjBRA Tsgia1+3X54m4UNpx4KNFJPQKFyNw8pboEBICfjv26SwNZZf45saZqJQ9sF2WpEU P7OCWVot6M2JW2DDFrSxO76iCSGQe62vlevEeO2kQqUDIyhaQRnJGtXLqFIw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm1; t=1709872345; x=1709958745; bh=DKkk98C8zOCSuFt0XfPfD+/TfJ/+ 9nedV4FKOCncuX4=; b=AL5whfPBchFOkgGXLRezus9uykEDSeBzehpaMJdrG5yp 8hcwz4vurlA+3WtuJm5Q+7da5kaOqWYWNhlfE4PNjszPEjQch3e5XKci+x2SkDPq Bv4K1ID69KnQOAjjKHDnFfM0+79j+3l9X5LDNb+F4e2iSMuN2YPIbTKnmqvA/+2E P49sLqNhbFBNDjb0y1/k08YJLoFg5ydAqZ6ce3N9AwpuWFgLVFbA3ShKOEGXFPZU lonNpnY/EUeBgFxJPN7fZHj9DU5h97+BdlNzavp8z0c0QTNiXHP4VXZHp2HTtspW OOWeNMjaSa9f8+6Yf1+w1e8lHo3gWnczWzn9UaVqug== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrieeggdejudcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenfg hrlhcuvffnffculdejtddmnecujfgurhepfffhvfevuffkfhggtggujgesghdtreertddt vdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoehmihgthhgrvghlsehprg hquhhivghrrdighiiiqeenucggtffrrghtthgvrhhnpeetleeifedufffhhfdtteelgeeg geffhfekueevteeigfduudevudetgfegiedvjeenucevlhhushhtvghrufhiiigvpedtne curfgrrhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiii X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 7 Mar 2024 23:32:21 -0500 (EST) Date: Fri, 8 Mar 2024 13:32:10 +0900 From: Michael Paquier To: Justin Pryzby Cc: Peter Eisentraut , Soumyadeep Chakraborty , Zhihong Yu , pgsql-hackers@postgresql.org, Ashwin Agrawal , vanjared@vmware.com Subject: Re: ALTER TABLE SET ACCESS METHOD on partitioned tables Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="O/RRAajCb1/SU9l8" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --O/RRAajCb1/SU9l8 Content-Type: multipart/mixed; boundary="GwIx8fQGLTIhv7Sg" Content-Disposition: inline --GwIx8fQGLTIhv7Sg Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Thu, Mar 07, 2024 at 08:02:00PM -0600, Justin Pryzby wrote: > As you wrote it, you pass the "defaultAccessMethod" bool to > ATExecSetAccessMethodNoStorage(), which seems odd. Why not just pass > the target amoid as newAccessMethod ? + /* + * Check that the table access method exists. + * Use the access method, if specified, otherwise (when not specified) 0 + * for partitioned tables or the configured default AM. + */ + if (amname !=3D NULL) + amoid =3D get_table_am_oid(amname, false); + else if (rel->rd_rel->relkind =3D=3D RELKIND_PARTITIONED_TABLE) + amoid =3D 0; + else + amoid =3D get_table_am_oid(default_table_access_method, false); =2E. While using this flow to choose the AM oid, that's neater than the versions I could come up with, pretty cool. > When I fooled with this on my side, I called it "chgAccessMethod" to > follow "chgPersistence". I think "is default" isn't the right data > structure. >=20 > Attached a relative patch with my version. Thanks. I've applied the patch to add the DEFAULT clause for now, to ease the work on this patch. > Also: I just realized that instead of adding a bool, we could test > (tab->rewrite & AT_REWRITE_ACCESS_METHOD) !=3D 0 Hmm. I've considered that, but the boolean style is able to do the work, while being more consistent, so I'm OK with what you are doing in your 0002. > +-- Default and AM set in in clause are the same, relam should be set. >=20 > in in? Oops, fixed. I have spent more time reviewing the whole and the tests (I didn't see much value in testing the DEFAULT clause twice for the partitioned table case and there is a test in d61a6cad6418), tweaked a few comments and the documentation, did an indentation and a commit message draft. How does that look to you? The test coverage and the semantics do what we want them to do, so that looks rather reasonable here. A second or even third pair of eyes would not hurt. -- Michael --GwIx8fQGLTIhv7Sg Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0001-Allow-specifying-access-method-for-partitioned-ta.patch" Content-Transfer-Encoding: quoted-printable =46rom 4114c7944c78dbb6eb85320434d942daa77fa4d8 Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Fri, 8 Mar 2024 13:24:14 +0900 Subject: [PATCH v4] Allow specifying access method for partitioned tables Partitioned tables gain support for CREATE TABLE .. USING, where specifying an access method can serve as a hint when creating partitions on it. This includes support for ALTER TABLE .. SET ACCESS METHOD, where specifying DEFAULT is equivalent to resetting the partitioned table's relam to 0. Specifying a non-DEFAULT access method will set its relam to not be 0. The historical default of using pg_class.relam as InvalidOid is preserved, corresponding to the case where USING clause is not used. In this case, like previous releases, a partition is created with the access method set by default_table_access_method if its CREATE TABLE query does not specify an access method with a USING clause. The relcache of partitioned tables is not changed: rd_tableam is not set, even if a partitioned table has a relam set. Authors: Justin Pryzby, Soumyadeep Chakraborty Reviewed-by: Michael Paquier, and a few others.. Discussion: https://postgr.es/m/ --- src/include/catalog/pg_class.h | 4 +- src/include/utils/lsyscache.h | 1 + src/backend/commands/tablecmds.c | 172 ++++++++++++++++++++---- src/backend/utils/cache/lsyscache.c | 22 +++ src/backend/utils/cache/relcache.c | 7 + src/bin/pg_dump/pg_dump.c | 3 +- src/bin/pg_dump/t/002_pg_dump.pl | 35 +++++ src/test/regress/expected/create_am.out | 158 +++++++++++++++++++++- src/test/regress/sql/create_am.sql | 91 ++++++++++++- doc/src/sgml/catalogs.sgml | 5 +- doc/src/sgml/ref/alter_table.sgml | 8 ++ doc/src/sgml/ref/create_table.sgml | 4 + 12 files changed, 471 insertions(+), 39 deletions(-) diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index 3b7533e7bb..0fc2c093b0 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -219,7 +219,9 @@ MAKE_SYSCACHE(RELNAMENSP, pg_class_relname_nsp_index, 1= 28); /* * Relation kinds with a table access method (rd_tableam). Although seque= nces * use the heap table AM, they are enough of a special case in most uses t= hat - * they are not included here. + * they are not included here. Likewise, partitioned tables can have an a= ccess + * method defined so that their partitions can inherit it, but they do not= set + * rd_tableam; hence, this is handled specially outside of this macro. */ #define RELKIND_HAS_TABLE_AM(relkind) \ ((relkind) =3D=3D RELKIND_RELATION || \ diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index e4a200b00e..35a8dec2b9 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -139,6 +139,7 @@ extern char get_rel_relkind(Oid relid); extern bool get_rel_relispartition(Oid relid); extern Oid get_rel_tablespace(Oid relid); extern char get_rel_persistence(Oid relid); +extern Oid get_rel_relam(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); extern Oid get_transform_tosql(Oid typid, Oid langid, List *trftypes); extern bool get_typisdefined(Oid typid); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablec= mds.c index 7014be8039..4dd96b93cb 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -183,7 +183,9 @@ typedef struct AlteredTableInfo List *afterStmts; /* List of utility command parsetrees */ bool verify_new_notnull; /* T if we should recheck NOT NULL */ int rewrite; /* Reason for forced rewrite, if any */ - Oid newAccessMethod; /* new access method; 0 means no change */ + bool chgAccessMethod; /* T if SET ACCESS METHOD is used */ + Oid newAccessMethod; /* new access method; 0 means no change, + * if above is true */ Oid newTableSpace; /* new tablespace; 0 means no change */ bool chgPersistence; /* T if SET LOGGED/UNLOGGED is used */ char newrelpersistence; /* if above is true */ @@ -589,6 +591,7 @@ static ObjectAddress ATExecClusterOn(Relation rel, cons= t char *indexName, LOCKMODE lockmode); static void ATExecDropCluster(Relation rel, LOCKMODE lockmode); static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, con= st char *amname); +static void ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMeth= od); static bool ATPrepChangePersistence(Relation rel, bool toLogged); static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, const char *tablespacename, LOCKMODE lockmode); @@ -703,7 +706,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid owne= rId, Oid ofTypeId; ObjectAddress address; LOCKMODE parentLockmode; - const char *accessMethod =3D NULL; Oid accessMethodId =3D InvalidOid; =20 /* @@ -956,20 +958,25 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ow= nerId, * a type of relation that needs one, use the default. */ if (stmt->accessMethod !=3D NULL) + accessMethodId =3D get_table_am_oid(stmt->accessMethod, false); + else if (stmt->partbound) { - accessMethod =3D stmt->accessMethod; - - if (partitioned) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("specifying a table access method is not supported on a parti= tioned table"))); + /* + * For partitions, if no access method is specified, use the AM of the + * parent table. + */ + Assert(list_length(inheritOids) =3D=3D 1); + accessMethodId =3D get_rel_relam(linitial_oid(inheritOids)); } - else if (RELKIND_HAS_TABLE_AM(relkind)) - accessMethod =3D default_table_access_method; + else + accessMethodId =3D InvalidOid; =20 - /* look up the access method, verify it is for a table */ - if (accessMethod !=3D NULL) - accessMethodId =3D get_table_am_oid(accessMethod, false); + /* + * Still nothing? Use the default. Partitioned tables default to + * InvalidOid without an access method specified. + */ + if (RELKIND_HAS_TABLE_AM(relkind) && !OidIsValid(accessMethodId)) + accessMethodId =3D get_table_am_oid(default_table_access_method, false); =20 /* * Create the relation. Inherited defaults and constraints are passed in @@ -5045,14 +5052,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd= *cmd, case AT_SetAccessMethod: /* SET ACCESS METHOD */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW); =20 - /* partitioned tables don't have an access method */ - if (rel->rd_rel->relkind =3D=3D RELKIND_PARTITIONED_TABLE) - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("cannot change access method of a partitioned table"))); - /* check if another access method change was already requested */ - if (OidIsValid(tab->newAccessMethod)) + if (tab->chgAccessMethod) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot have multiple SET ACCESS METHOD subcommands"))); @@ -5407,6 +5408,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, break; case AT_SetAccessMethod: /* SET ACCESS METHOD */ /* handled specially in Phase 3 */ + + /* + * Only do this for partitioned tables, for which this is just a + * catalog change. Tables with storage are handled by Phase 3. + */ + if (rel->rd_rel->relkind =3D=3D RELKIND_PARTITIONED_TABLE && + tab->chgAccessMethod) + ATExecSetAccessMethodNoStorage(rel, tab->newAccessMethod); break; case AT_SetTableSpace: /* SET TABLESPACE */ =20 @@ -5812,7 +5821,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqu= eue, LOCKMODE lockmode, * Select destination access method (same as original unless user * requested a change) */ - if (OidIsValid(tab->newAccessMethod)) + if (tab->chgAccessMethod) NewAccessMethod =3D tab->newAccessMethod; else NewAccessMethod =3D OldHeap->rd_rel->relam; @@ -6399,6 +6408,7 @@ ATGetQueueEntry(List **wqueue, Relation rel) tab->relkind =3D rel->rd_rel->relkind; tab->oldDesc =3D CreateTupleDescCopyConstr(RelationGetDescr(rel)); tab->newAccessMethod =3D InvalidOid; + tab->chgAccessMethod =3D false; tab->newTableSpace =3D InvalidOid; tab->newrelpersistence =3D RELPERSISTENCE_PERMANENT; tab->chgPersistence =3D false; @@ -15201,17 +15211,32 @@ ATExecDropCluster(Relation rel, LOCKMODE lockmode) * Preparation phase for SET ACCESS METHOD * * Check that access method exists. If it is the same as the table's curr= ent - * access method, it is a no-op. Otherwise, a table rewrite is necessary. + * access method, it is a no-op. Otherwise, a table rewrite is necessary = for + * relations with storage. * If amname is NULL, select default_table_access_method as access method. + * + * Partitioned tables may use InvalidOid to assign the default AM to parti= tions + * when created. This operation is a no-op if a DEFAULT was given and the + * partitioned table uses InvalidOid as relam, or if the relam and the acc= ess + * method specified match. */ static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amn= ame) { Oid amoid; =20 - /* Check that the table access method exists */ - amoid =3D get_table_am_oid(amname ? amname : default_table_access_method, - false); + /* + * Check that the table access method exists. + * + * Use the access method specified, otherwise, when not specified, use + * InvalidOid for partitioned tables or the configured default AM. + */ + if (amname !=3D NULL) + amoid =3D get_table_am_oid(amname, false); + else if (rel->rd_rel->relkind =3D=3D RELKIND_PARTITIONED_TABLE) + amoid =3D InvalidOid; + else + amoid =3D get_table_am_oid(default_table_access_method, false); =20 if (rel->rd_rel->relam =3D=3D amoid) return; @@ -15219,6 +15244,7 @@ ATPrepSetAccessMethod(AlteredTableInfo *tab, Relati= on rel, const char *amname) /* Save info for Phase 3 to do the real work */ tab->rewrite |=3D AT_REWRITE_ACCESS_METHOD; tab->newAccessMethod =3D amoid; + tab->chgAccessMethod =3D true; } =20 /* @@ -15543,6 +15569,102 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpa= ce, LOCKMODE lockmode) list_free(reltoastidxids); } =20 +/* + * Special handling of ALTER TABLE SET ACCESS METHOD for relations with no + * storage that have an interest in preserving AM. + * + * Since these have no storage, setting the access method is a catalog only + * operation. + */ +static void +ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId) +{ + Relation pg_class; + Oid oldAccessMethodId; + HeapTuple tuple; + Form_pg_class rd_rel; + Oid reloid =3D RelationGetRelid(rel); + + /* + * Shouldn't be called on relations having storage; these are processed in + * phase 3. + */ + Assert(!RELKIND_HAS_STORAGE(rel->rd_rel->relkind)); + + /* Get a modifiable copy of the relation's pg_class row. */ + pg_class =3D table_open(RelationRelationId, RowExclusiveLock); + + tuple =3D SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", reloid); + rd_rel =3D (Form_pg_class) GETSTRUCT(tuple); + + /* Update the pg_class row. */ + oldAccessMethodId =3D rd_rel->relam; + rd_rel->relam =3D newAccessMethodId; + + /* Leave if no update required */ + if (rd_rel->relam =3D=3D oldAccessMethodId) + { + heap_freetuple(tuple); + table_close(pg_class, RowExclusiveLock); + return; + } + + CatalogTupleUpdate(pg_class, &tuple->t_self, tuple); + + /* + * Update the dependency on the new access method. No dependency is added + * if the new access method is InvalidOid (default case). Be very careful + * that this has to compare the previous value stored in pg_class with the + * new one. + */ + if (!OidIsValid(oldAccessMethodId) && OidIsValid(rd_rel->relam)) + { + ObjectAddress relobj, + referenced; + + /* + * New access method is defined and there was no dependency + * previously, so record a new one. + */ + ObjectAddressSet(relobj, RelationRelationId, reloid); + ObjectAddressSet(referenced, AccessMethodRelationId, rd_rel->relam); + recordDependencyOn(&relobj, &referenced, DEPENDENCY_NORMAL); + } + else if (OidIsValid(oldAccessMethodId) && + !OidIsValid(rd_rel->relam)) + { + /* + * There was an access method defined, and no new one, so just remove + * the existing dependency. + */ + deleteDependencyRecordsForClass(RelationRelationId, reloid, + AccessMethodRelationId, + DEPENDENCY_NORMAL); + } + else + { + Assert(OidIsValid(oldAccessMethodId) && + OidIsValid(rd_rel->relam)); + + /* Both are valid, so update the dependency */ + changeDependencyFor(RelationRelationId, reloid, + AccessMethodRelationId, + oldAccessMethodId, rd_rel->relam); + } + + CommandCounterIncrement(); + + InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), 0); + + heap_freetuple(tuple); + table_close(pg_class, RowExclusiveLock); + + /* Make sure the relam change is visible */ + CommandCounterIncrement(); +} + /* * Special handling of ALTER TABLE SET TABLESPACE for relations with no * storage that have an interest in preserving tablespace. diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/= lsyscache.c index 6418d1c6eb..26368ffcc9 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -2069,6 +2069,28 @@ get_rel_persistence(Oid relid) return result; } =20 +/* + * get_rel_relam + * + * Returns the relam associated with a given relation. + */ +Oid +get_rel_relam(Oid relid) +{ + HeapTuple tp; + Form_pg_class reltup; + Oid result; + + tp =3D SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for relation %u", relid); + reltup =3D (Form_pg_class) GETSTRUCT(tp); + result =3D reltup->relam; + ReleaseSysCache(tp); + + return result; +} + =20 /* ---------- TRANSFORM CACHE ---------- */ =20 diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/r= elcache.c index 4d339ee795..18c6044238 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -1208,6 +1208,13 @@ retry: else if (RELKIND_HAS_TABLE_AM(relation->rd_rel->relkind) || relation->rd_rel->relkind =3D=3D RELKIND_SEQUENCE) RelationInitTableAccessMethod(relation); + else if (relation->rd_rel->relkind =3D=3D RELKIND_PARTITIONED_TABLE) + { + /* + * Do nothing: access methods are a setting that partitions can + * inherit. + */ + } else Assert(relation->rd_rel->relam =3D=3D InvalidOid); =20 diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 10cbf02beb..ec406bff29 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -16610,7 +16610,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbi= nfo) if (RELKIND_HAS_TABLESPACE(tbinfo->relkind)) tablespace =3D tbinfo->reltablespace; =20 - if (RELKIND_HAS_TABLE_AM(tbinfo->relkind)) + if (RELKIND_HAS_TABLE_AM(tbinfo->relkind) || + tbinfo->relkind =3D=3D RELKIND_PARTITIONED_TABLE) tableam =3D tbinfo->amname; =20 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId, diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_du= mp.pl index 00b5092713..dfee1982f4 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -4587,6 +4587,41 @@ my %tests =3D ( no_table_access_method =3D> 1, only_dump_measurement =3D> 1, }, + }, + + # CREATE TABLE with partitioned table and various AMs. One + # partition uses the same default as the parent, and a second + # uses its own AM. + 'CREATE TABLE regress_pg_dump_table_part' =3D> { + create_order =3D> 19, + create_sql =3D> ' + CREATE TABLE dump_test.regress_pg_dump_table_am_parent (id int) PARTITI= ON BY LIST (id); + ALTER TABLE dump_test.regress_pg_dump_table_am_parent SET ACCESS METHOD= regress_table_am; + CREATE TABLE dump_test.regress_pg_dump_table_am_child_1 + PARTITION OF dump_test.regress_pg_dump_table_am_parent FOR VALUES IN = (1) USING heap; + CREATE TABLE dump_test.regress_pg_dump_table_am_child_2 + PARTITION OF dump_test.regress_pg_dump_table_am_parent FOR VALUES IN = (2);', + regexp =3D> qr/^ + \QSET default_table_access_method =3D regress_table_am;\E + (\n(?!SET[^;]+;)[^\n]*)* + \n\QCREATE TABLE dump_test.regress_pg_dump_table_am_parent (\E + (.*\n)* + \QSET default_table_access_method =3D heap;\E + (\n(?!SET[^;]+;)[^\n]*)* + \n\QCREATE TABLE dump_test.regress_pg_dump_table_am_child_1 (\E + (.*\n)* + \QSET default_table_access_method =3D regress_table_am;\E + (\n(?!SET[^;]+;)[^\n]*)* + \n\QCREATE TABLE dump_test.regress_pg_dump_table_am_child_2 (\E + (.*\n)*/xm, + like =3D> { + %full_runs, %dump_test_schema_runs, section_pre_data =3D> 1, + }, + unlike =3D> { + exclude_dump_test_schema =3D> 1, + no_table_access_method =3D> 1, + only_dump_measurement =3D> 1, + }, }); =20 ######################################### diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/exp= ected/create_am.out index 8d73e21356..a27805a8f5 100644 --- a/src/test/regress/expected/create_am.out +++ b/src/test/regress/expected/create_am.out @@ -176,9 +176,16 @@ SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1; 1 (1 row) =20 --- CREATE TABLE .. PARTITION BY doesn't not support USING +-- CREATE TABLE .. PARTITION BY supports USING. CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) US= ING heap2; -ERROR: specifying a table access method is not supported on a partitioned= table +SELECT a.amname FROM pg_class c, pg_am a + WHERE c.relname =3D 'tableam_parted_heap2' AND a.oid =3D c.relam; + amname=20 +-------- + heap2 +(1 row) + +DROP TABLE tableam_parted_heap2; CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a); -- new partitions will inherit from the current default, rather the partit= ion root SET default_table_access_method =3D 'heap'; @@ -336,12 +343,151 @@ ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD hea= p, SET ACCESS METHOD heap2; ERROR: cannot have multiple SET ACCESS METHOD subcommands DROP MATERIALIZED VIEW heapmv; DROP TABLE heaptable; --- No support for partitioned tables. -CREATE TABLE am_partitioned(x INT, y INT) - PARTITION BY hash (x); +-- Partition hierarchies with access methods +BEGIN; +SET LOCAL default_table_access_method =3D 'heap'; +CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x); +-- pg_class.relam is 0, no dependency recorded between the AM and the +-- partitioned table. +SELECT relam FROM pg_class WHERE relname =3D 'am_partitioned'; + relam=20 +------- + 0 +(1 row) + +SELECT pg_describe_object(classid, objid, objsubid) AS obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as refobj + FROM pg_depend, pg_am + WHERE pg_depend.refclassid =3D 'pg_am'::regclass + AND pg_am.oid =3D pg_depend.refobjid + AND pg_depend.objid =3D 'am_partitioned'::regclass; + obj | refobj=20 +-----+-------- +(0 rows) + +-- New default is set, with dependency added. ALTER TABLE am_partitioned SET ACCESS METHOD heap2; -ERROR: cannot change access method of a partitioned table +SELECT a.amname FROM pg_class c, pg_am a + WHERE c.relname =3D 'am_partitioned' AND a.oid =3D c.relam; + amname=20 +-------- + heap2 +(1 row) + +SELECT pg_describe_object(classid, objid, objsubid) AS obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as refobj + FROM pg_depend, pg_am + WHERE pg_depend.refclassid =3D 'pg_am'::regclass + AND pg_am.oid =3D pg_depend.refobjid + AND pg_depend.objid =3D 'am_partitioned'::regclass; + obj | refobj =20 +----------------------+--------------------- + table am_partitioned | access method heap2 +(1 row) + +-- Default is set, with dependency updated. +SET LOCAL default_table_access_method =3D 'heap2'; +ALTER TABLE am_partitioned SET ACCESS METHOD heap; +SELECT a.amname FROM pg_class c, pg_am a + WHERE c.relname =3D 'am_partitioned' AND a.oid =3D c.relam; + amname=20 +-------- + heap +(1 row) + +-- Dependency pinned, hence removed. +SELECT pg_describe_object(classid, objid, objsubid) AS obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as refobj + FROM pg_depend, pg_am + WHERE pg_depend.refclassid =3D 'pg_am'::regclass + AND pg_am.oid =3D pg_depend.refobjid + AND pg_depend.objid =3D 'am_partitioned'::regclass; + obj | refobj=20 +-----+-------- +(0 rows) + +-- Default and AM set in the clause are the same, relam should be set. +SET LOCAL default_table_access_method =3D 'heap2'; +ALTER TABLE am_partitioned SET ACCESS METHOD heap2; +SELECT a.amname FROM pg_class c, pg_am a + WHERE c.relname =3D 'am_partitioned' AND a.oid =3D c.relam; + amname=20 +-------- + heap2 +(1 row) + +-- Reset to default +ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT; +SELECT relam FROM pg_class WHERE relname =3D 'am_partitioned'; + relam=20 +------- + 0 +(1 row) + +-- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitio= ns +-- will inherit the AM set. Existing partitioned are unchanged. +SELECT relam FROM pg_class WHERE relname =3D 'am_partitioned'; + relam=20 +------- + 0 +(1 row) + +SET LOCAL default_table_access_method =3D 'heap'; +CREATE TABLE am_partitioned_0 PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 0); +SET LOCAL default_table_access_method =3D 'heap2'; +CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 1); +SET LOCAL default_table_access_method =3D 'heap'; +ALTER TABLE am_partitioned SET ACCESS METHOD heap2; +CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 2); +ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT; +SELECT relam FROM pg_class WHERE relname =3D 'am_partitioned'; + relam=20 +------- + 0 +(1 row) + +CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 3); +-- Partitioned table with relam at 0 +ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT; +CREATE TABLE am_partitioned_5p PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 5) PARTITION BY hash(y); +-- Partitions of this partitioned table inherit default AM at creation +-- time. +CREATE TABLE am_partitioned_5p1 PARTITION OF am_partitioned_5p + FOR VALUES WITH (MODULUS 10, REMAINDER 1); +-- Partitioned table with relam set. +ALTER TABLE am_partitioned SET ACCESS METHOD heap2; +CREATE TABLE am_partitioned_6p PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 6) PARTITION BY hash(y); +-- Partitions of this partitioned table inherit its AM. +CREATE TABLE am_partitioned_6p1 PARTITION OF am_partitioned_6p + FOR VALUES WITH (MODULUS 10, REMAINDER 1); +SELECT c.relname, a.amname FROM pg_class c, pg_am a + WHERE c.relam =3D a.oid AND + c.relname LIKE 'am_partitioned%' +UNION ALL +SELECT c.relname, 'default' FROM pg_class c + WHERE c.relam =3D 0 + AND c.relname LIKE 'am_partitioned%' ORDER BY 1; + relname | amname =20 +--------------------+--------- + am_partitioned | heap2 + am_partitioned_0 | heap + am_partitioned_1 | heap2 + am_partitioned_2 | heap2 + am_partitioned_3 | heap + am_partitioned_5p | default + am_partitioned_5p1 | heap + am_partitioned_6p | heap2 + am_partitioned_6p1 | heap2 +(9 rows) + DROP TABLE am_partitioned; +COMMIT; -- Second, create objects in the new AM by changing the default AM BEGIN; SET LOCAL default_table_access_method =3D 'heap2'; diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/crea= te_am.sql index 606ee4cb24..adff0079f9 100644 --- a/src/test/regress/sql/create_am.sql +++ b/src/test/regress/sql/create_am.sql @@ -124,8 +124,11 @@ CREATE SEQUENCE tableam_seq_heap2 USING heap2; CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM = tableam_tbl_heap2; SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1; =20 --- CREATE TABLE .. PARTITION BY doesn't not support USING +-- CREATE TABLE .. PARTITION BY supports USING. CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) US= ING heap2; +SELECT a.amname FROM pg_class c, pg_am a + WHERE c.relname =3D 'tableam_parted_heap2' AND a.oid =3D c.relam; +DROP TABLE tableam_parted_heap2; =20 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a); -- new partitions will inherit from the current default, rather the partit= ion root @@ -213,11 +216,91 @@ ALTER TABLE heaptable SET ACCESS METHOD DEFAULT, SET = ACCESS METHOD heap2; ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD h= eap2; DROP MATERIALIZED VIEW heapmv; DROP TABLE heaptable; --- No support for partitioned tables. -CREATE TABLE am_partitioned(x INT, y INT) - PARTITION BY hash (x); + +-- Partition hierarchies with access methods +BEGIN; +SET LOCAL default_table_access_method =3D 'heap'; +CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x); +-- pg_class.relam is 0, no dependency recorded between the AM and the +-- partitioned table. +SELECT relam FROM pg_class WHERE relname =3D 'am_partitioned'; +SELECT pg_describe_object(classid, objid, objsubid) AS obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as refobj + FROM pg_depend, pg_am + WHERE pg_depend.refclassid =3D 'pg_am'::regclass + AND pg_am.oid =3D pg_depend.refobjid + AND pg_depend.objid =3D 'am_partitioned'::regclass; +-- New default is set, with dependency added. ALTER TABLE am_partitioned SET ACCESS METHOD heap2; +SELECT a.amname FROM pg_class c, pg_am a + WHERE c.relname =3D 'am_partitioned' AND a.oid =3D c.relam; +SELECT pg_describe_object(classid, objid, objsubid) AS obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as refobj + FROM pg_depend, pg_am + WHERE pg_depend.refclassid =3D 'pg_am'::regclass + AND pg_am.oid =3D pg_depend.refobjid + AND pg_depend.objid =3D 'am_partitioned'::regclass; +-- Default is set, with dependency updated. +SET LOCAL default_table_access_method =3D 'heap2'; +ALTER TABLE am_partitioned SET ACCESS METHOD heap; +SELECT a.amname FROM pg_class c, pg_am a + WHERE c.relname =3D 'am_partitioned' AND a.oid =3D c.relam; +-- Dependency pinned, hence removed. +SELECT pg_describe_object(classid, objid, objsubid) AS obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as refobj + FROM pg_depend, pg_am + WHERE pg_depend.refclassid =3D 'pg_am'::regclass + AND pg_am.oid =3D pg_depend.refobjid + AND pg_depend.objid =3D 'am_partitioned'::regclass; +-- Default and AM set in the clause are the same, relam should be set. +SET LOCAL default_table_access_method =3D 'heap2'; +ALTER TABLE am_partitioned SET ACCESS METHOD heap2; +SELECT a.amname FROM pg_class c, pg_am a + WHERE c.relname =3D 'am_partitioned' AND a.oid =3D c.relam; +-- Reset to default +ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT; +SELECT relam FROM pg_class WHERE relname =3D 'am_partitioned'; +-- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitio= ns +-- will inherit the AM set. Existing partitioned are unchanged. +SELECT relam FROM pg_class WHERE relname =3D 'am_partitioned'; +SET LOCAL default_table_access_method =3D 'heap'; +CREATE TABLE am_partitioned_0 PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 0); +SET LOCAL default_table_access_method =3D 'heap2'; +CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 1); +SET LOCAL default_table_access_method =3D 'heap'; +ALTER TABLE am_partitioned SET ACCESS METHOD heap2; +CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 2); +ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT; +SELECT relam FROM pg_class WHERE relname =3D 'am_partitioned'; +CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 3); +-- Partitioned table with relam at 0 +ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT; +CREATE TABLE am_partitioned_5p PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 5) PARTITION BY hash(y); +-- Partitions of this partitioned table inherit default AM at creation +-- time. +CREATE TABLE am_partitioned_5p1 PARTITION OF am_partitioned_5p + FOR VALUES WITH (MODULUS 10, REMAINDER 1); +-- Partitioned table with relam set. +ALTER TABLE am_partitioned SET ACCESS METHOD heap2; +CREATE TABLE am_partitioned_6p PARTITION OF am_partitioned + FOR VALUES WITH (MODULUS 10, REMAINDER 6) PARTITION BY hash(y); +-- Partitions of this partitioned table inherit its AM. +CREATE TABLE am_partitioned_6p1 PARTITION OF am_partitioned_6p + FOR VALUES WITH (MODULUS 10, REMAINDER 1); +SELECT c.relname, a.amname FROM pg_class c, pg_am a + WHERE c.relam =3D a.oid AND + c.relname LIKE 'am_partitioned%' +UNION ALL +SELECT c.relname, 'default' FROM pg_class c + WHERE c.relam =3D 0 + AND c.relname LIKE 'am_partitioned%' ORDER BY 1; DROP TABLE am_partitioned; +COMMIT; =20 -- Second, create objects in the new AM by changing the default AM BEGIN; diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 0ae97d1ada..e4fcacb610 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1989,8 +1989,9 @@ SCRAM-SHA-256$<iteration count>:&l If this is a table or an index, the access method used (heap, - B-tree, hash, etc.); otherwise zero (zero occurs for sequences, - as well as relations without storage, such as views) + B-tree, hash, etc.); otherwise zero. Zero occurs for sequences, + as well as relations without storage, such as views. Partitioned + tables may use a non-zero value. =20 diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_tab= le.sgml index 96e3d77605..b6e794e6ce 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -737,6 +737,14 @@ WITH ( MODULUS numeri= c_literal, REM DEFAULT changes the access method of the table to . + + When applied to a partitioned table, there is no data to rewrite, bu= t any + partitions created afterwards will use that access method unless + overridden by a USING clause. Using + DEFAULT switches the partitioned table to use + default_table_access_method when new partitions a= re + created. + =20 diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_t= able.sgml index 4cbaaccaf7..b79081a5ec 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1330,6 +1330,10 @@ WITH ( MODULUS nume= ric_literal, REM method is chosen for the new table. See for more information. + + When creating a partition, the table access method is the access met= hod + of its partitioned table, if set. + =20 --=20 2.43.0 --GwIx8fQGLTIhv7Sg-- --O/RRAajCb1/SU9l8 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmXqlMoACgkQnvQgOdby QH28Eg/+KVchOyh4HoLXorC816raI5sH0Xp1jIRQ4cXSP3UbNZHkqOmAPTTe395r 0PqPl6US3ZXdD/yJuUT+dw6LAGXhBLI+2ruLkpI2nFd7+RjCOA5dTtlfKMCSPLHl p6RhyyV32/DVPaSGRBWuCKPfz/PEb2zRR/DLexKsjPEt1yqDYbQvJaN31jNy21Np JKvphOTGFRykTzDPPdk+UO0av4enp8a/+3ceOWi5ZquY6pUOKRm1u++dwDdZYv89 lhYSE8H9U2I/talHounlweOfl8JFcR9Q2fAZACdsHcvIlCNC5XwXONMMNNgy+5O7 sAV/rgxnoWvcLjMflVGpuX5EWxI/bYcxDMPPE+LlHJQa9qk+m/ILRLVfL3VOG+Zj 5NnZzC0Q9XqEd6MwBgE1muocJABLFZ+YR3WSI5j7wjj6wm/+SbZdr8foSdTOGlIG Bnm3leF7lT9BQZa3r2AE6iwX6GBRF4hB+4LM5Bb17ABfLEYXEf12nVysLn+7Secc /ReD0Yh85zEZWqlHZ67/9aeiAzXQlJeD1G6wMqJ1yQBgKgyNGynivFTPJPz52J7Q mMDooIsHUubj0bMca0wyT9umq3QoVtT0y3IDmOoRfbBbVQueA+EQzvxmn5BfWmuB Ac11MrNXHQyF5Cyqjdc/LanrEd3U8ABRIlAkxajNz1TsgicpvhI= =42Tj -----END PGP SIGNATURE----- --O/RRAajCb1/SU9l8--