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 1rzWtL-004fdq-HV for pgsql-hackers@arkaria.postgresql.org; Wed, 24 Apr 2024 07:18:08 +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 1rzWtK-00CSCv-7j for pgsql-hackers@arkaria.postgresql.org; Wed, 24 Apr 2024 07:18:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzWtJ-00CSCn-6p for pgsql-hackers@lists.postgresql.org; Wed, 24 Apr 2024 07:18:05 +0000 Received: from wfout6-smtp.messagingengine.com ([64.147.123.149]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzWtF-002fDU-Pu for pgsql-hackers@lists.postgresql.org; Wed, 24 Apr 2024 07:18:04 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailfout.west.internal (Postfix) with ESMTP id 54D931C000E3 for ; Wed, 24 Apr 2024 03:17:58 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute5.internal (MEProxy); Wed, 24 Apr 2024 03:17:58 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:content-type:content-type:date:date:from:from:in-reply-to :message-id:mime-version:reply-to:subject:subject:to:to; s=fm3; t=1713943077; x=1714029477; bh=1WznBqqGmyPWn3X9cCOJlFnYOoqD6j1/ yaBU3vUBaSY=; b=CSSTM6ndKduplOrqTKnUE/q8aj73fwP1l5SbGKxnk5eXyrXi cQPK6xKIDl6mfrQvcs7h7yYkjpFczzRoeB643PdpGOkBdxAZYd2TG3l21AYjzNjo iuFlWiyjTsNEywqX55CR++aTRDXTRI1fr088ioTYNSdUDhjJ5YSX3pVA+MX9Ahgx wyavpjMwQu9NWUHG+ko3E5P/0lR0GWVP782M7xzJxnWfFlfswo+lg1FoP3mMUB+S RAABjEGFGHFoYqh6bOmpuO+LGdCSJe68kEAfZFUXwFGY5oAxD5DbIbntp86PENDE 1QvrHZd1yvCPVui2EWNGLqzEB+3N7Re1Pa7imw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:message-id :mime-version:reply-to:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t= 1713943077; x=1714029477; bh=1WznBqqGmyPWn3X9cCOJlFnYOoqD6j1/yaB U3vUBaSY=; b=T8vrEdW4v+SDPXuGUQX00J5vvFjx99+MFGVgukNEaGbsasVZKbZ khxXusOBXvF2ExTqZaKpSC8N4dt2c1vP0+u53aTUn/CexgI+0wkCEGzQbZPXxqB5 BrcOqEnzH+MOgQrGM7PI8R04+Alrsh583r0nruRuJks6oP1NZk3mpiQSV6gDdA1Y PmzDSRk4xt6O3S6f/MLl9gKXrCJLJb4DG6+nc0JjcWeBR37WV7JfjrVZzpPEcpgt vAboWc4NtogxwN7D8B1H5tyAxmj6hlqFGg+I6Kgc/OwwiK7HwvOIoXENyKqnsBXc c0BpJPs2aU+kZ4KUxZDk8jjFKPcvjSdjdFA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrudelvddguddufecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enfghrlhcuvffnffculdefhedmnecujfgurhepfffhvffukfggtggusehgtderredttddv necuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesphgrqh huihgvrhdrgiihiieqnecuggftrfgrthhtvghrnhepieehvdevkeejjeduudehgeejiedv hffhfeegfedtjeeileegtdegfffhtedvvedtnecuffhomhgrihhnpehpohhsthhgrhgvsh hqlhdrohhrghenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhr ohhmpehmihgthhgrvghlsehprghquhhivghrrdighiii X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA for ; Wed, 24 Apr 2024 03:17:55 -0400 (EDT) Date: Wed, 24 Apr 2024 16:17:44 +0900 From: Michael Paquier To: Postgres hackers Subject: Partitioned tables and [un]loggedness Message-ID: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="+BZEbbd7OfPWKCwg" Content-Disposition: inline List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --+BZEbbd7OfPWKCwg Content-Type: multipart/mixed; boundary="7actJiB2aMlTdNbF" Content-Disposition: inline --7actJiB2aMlTdNbF Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Hi all, As a recent poke on a thread of 2019 has reminded me, the current situation of partitioned tables with unlogged is kind of weird: https://www.postgresql.org/message-id/15954-b61523bed4b110c4%40postgresql.org To sum up the situation: - ALTER TABLE .. SET LOGGED/UNLOGGED does not affect partitioned tables. - New partitions don't inherit the loggedness of the partitioned table. One of the things that could be done is to forbid the use of UNLOGGED in partitioned tables, but I'm wondering if we could be smarter than that to provide a more natural user experience. I've been experiencing with that and finished with the POC attached, that uses the following properties: - Support ALTER TABLE .. SET LOGGED/UNLOGGED for partitioned tables, where the command only works on partitioned tables so that's only a catalog switch. - CREATE TABLE PARTITION OF would make a new partition inherit the logged ness of the parent if UNLOGGED is not directly specified. There are some open questions that need attention: - How about ONLY? Would it make sense to support it so as ALTER TABLE ONLY on a partitioned table does not touch any of its partitions? Would not specifying ONLY mean that the loggedness of the partitioned table and all its partitions is changed? That would mean a burst in WAL when switching to LOGGED, which was a concern when this feature was first implemented even for a single table, so for potentially hundreds of them, that would really hurt if a DBA is not careful. - CREATE TABLE does not have a LOGGED keyword, hence it is not possible through the parser to force a partition to have a permanent persistence even if its partitioned table uses UNLOGGED. Like tablespaces or even recently access methods, the heuristics can be fun to define depending on the impact of the table rewrites. The attached has the code and regression tests to make the rewrites cheaper, which is to make new partitions inherit the loggedness of the parent while altering a parent's property leaves the partitions untouched. With the lack of a LOGGED keyword to force a partition to be permanent when its partitioned table is unlogged, that's a bit funny but feel free to comment. Thanks, -- Michael --7actJiB2aMlTdNbF Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0001-Support-LOGGED-UNLOGGED-for-partitioned-tables.patch" Content-Transfer-Encoding: quoted-printable =46rom f6462a5f4b9b35d5ace52d55759fefc350e371d3 Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Wed, 24 Apr 2024 16:15:29 +0900 Subject: [PATCH] Support LOGGED/UNLOGGED for partitioned tables When using ALTET TABLE SET LOGGED/UNLOGGED, indexes and sequences that are owned by the partitioned table changed need to have their relpersistence also changed. There are a few things that need to be considered here about ONLY: - If ONLY is used only on a partitioned table, switch the persistence be switched only for the partitioned table? - If ONLY is not used, should this operation recurse across all the partitions? CREATE TABLE comes with its own limitation because there is no way to detect in the parser if a new partition should be forced as logged. For now, this patch does: - If UNLOGGED is specified, the partition is unlogged. - If PERMANENT is found, the partition inherits the loggedness from the parent. - There is no way to force a partition to be permanent at query level. --- src/backend/commands/tablecmds.c | 145 ++++++++++++++++++---- src/test/regress/expected/alter_table.out | 84 +++++++++++++ src/test/regress/sql/alter_table.sql | 35 ++++++ doc/src/sgml/ref/alter_table.sgml | 6 + doc/src/sgml/ref/create_table.sgml | 5 + 5 files changed, 252 insertions(+), 23 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablec= mds.c index 3556240c8e..90b3993fc4 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -601,7 +601,9 @@ static ObjectAddress ATExecClusterOn(Relation rel, cons= t char *indexName, 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 ATExecSetPersistenceNoStorage(Relation rel, char newrelpersist= ence); +static void ATPrepSetPersistence(AlteredTableInfo *tab, Relation rel, + bool toLogged); static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, const char *tablespacename, LOCKMODE lockmode); static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE = lockmode); @@ -985,6 +987,30 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid own= erId, accessMethodId =3D get_table_am_oid(default_table_access_method, false); } =20 + /* determine the persistence to use for a partition */ + if (stmt->partbound) + { + /* If UNLOGGED has been specified by the query, just use it */ + if (stmt->relation->relpersistence =3D=3D RELPERSISTENCE_TEMP) + { + /* Nothing to do, should be temp */ + } + else if (stmt->relation->relpersistence =3D=3D RELPERSISTENCE_UNLOGGED) + { + /* UNLOGGED has been specified by the query, just use it */ + } + else + { + /* + * Case of RELPERSISTENCE_PERMANENT, where the query specified + * nothing so inherit the persistency from the parent. + */ + Assert(list_length(inheritOids) =3D=3D 1); + stmt->relation->relpersistence =3D + get_rel_persistence(linitial_oid(inheritOids)); + } + } + /* * Create the relation. Inherited defaults and constraints are passed in * for immediate handling --- since they don't need parsing, they can be @@ -5036,13 +5062,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd= *cmd, ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot change persistence setting twice"))); - tab->chgPersistence =3D ATPrepChangePersistence(rel, true); - /* force rewrite if necessary; see comment in ATRewriteTables */ - if (tab->chgPersistence) - { - tab->rewrite |=3D AT_REWRITE_ALTER_PERSISTENCE; - tab->newrelpersistence =3D RELPERSISTENCE_PERMANENT; - } + ATPrepSetPersistence(tab, rel, true); pass =3D AT_PASS_MISC; break; case AT_SetUnLogged: /* SET UNLOGGED */ @@ -5051,13 +5071,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd= *cmd, ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot change persistence setting twice"))); - tab->chgPersistence =3D ATPrepChangePersistence(rel, false); - /* force rewrite if necessary; see comment in ATRewriteTables */ - if (tab->chgPersistence) - { - tab->rewrite |=3D AT_REWRITE_ALTER_PERSISTENCE; - tab->newrelpersistence =3D RELPERSISTENCE_UNLOGGED; - } + ATPrepSetPersistence(tab, rel, false); pass =3D AT_PASS_MISC; break; case AT_DropOids: /* SET WITHOUT OIDS */ @@ -5427,6 +5441,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, break; case AT_SetLogged: /* SET LOGGED */ case AT_SetUnLogged: /* SET UNLOGGED */ + + /* + * 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->chgPersistence) + ATExecSetPersistenceNoStorage(rel, tab->newrelpersistence); break; case AT_DropOids: /* SET WITHOUT OIDS */ /* nothing to do here, oid columns don't exist anymore */ @@ -15518,6 +15540,79 @@ ATExecSetAccessMethodNoStorage(Relation rel, Oid n= ewAccessMethodId) table_close(pg_class, RowExclusiveLock); } =20 +/* + * Special handling of ALTER TABLE SET LOGGED/UNLOGGED for relations with = no + * storage that have an interest in changing their persistence. + * + * Since these have no storage, setting the persistence to permanent or + * unlogged is a catalog-only operation. This needs to switch the + * persistence of all sequences and indexes related to this relation. + */ +static void +ATExecSetPersistenceNoStorage(Relation rel, char newrelpersistence) +{ + Relation pg_class; + HeapTuple tuple; + List *reloids; /* for indexes and sequences */ + ListCell *elt; + 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); + + /* Leave if no update required */ + if (rd_rel->relpersistence =3D=3D newrelpersistence) + { + heap_freetuple(tuple); + table_close(pg_class, RowExclusiveLock); + return; + } + + /* Update the pg_class row. */ + rd_rel->relpersistence =3D newrelpersistence; + CatalogTupleUpdate(pg_class, &tuple->t_self, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), 0); + + heap_freetuple(tuple); + + /* Update the per-sequence and per-index relpersistence */ + reloids =3D getOwnedSequences(reloid); + reloids =3D list_union_oid(reloids, RelationGetIndexList(rel)); + foreach(elt, reloids) + { + Oid classoid =3D lfirst_oid(elt); + + tuple =3D SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(classoid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", classoid); + rd_rel =3D (Form_pg_class) GETSTRUCT(tuple); + + rd_rel->relpersistence =3D newrelpersistence; + CatalogTupleUpdate(pg_class, &tuple->t_self, tuple); + InvokeObjectPostAlterHook(RelationRelationId, classoid, 0); + + heap_freetuple(tuple); + } + + /* Make sure the persistence changes are visible */ + CommandCounterIncrement(); + + table_close(pg_class, RowExclusiveLock); +} + /* * ALTER TABLE SET TABLESPACE */ @@ -17749,12 +17844,10 @@ ATExecSetCompression(Relation rel, * This verifies that we're not trying to change a temp table. Also, * existing foreign key constraints are checked to avoid ending up with * permanent tables referencing unlogged tables. - * - * Return value is false if the operation is a no-op (in which case the - * checks are skipped), otherwise true. */ -static bool -ATPrepChangePersistence(Relation rel, bool toLogged) +static void +ATPrepSetPersistence(AlteredTableInfo *tab, Relation rel, + bool toLogged) { Relation pg_constraint; HeapTuple tuple; @@ -17778,12 +17871,12 @@ ATPrepChangePersistence(Relation rel, bool toLogg= ed) case RELPERSISTENCE_PERMANENT: if (toLogged) /* nothing to do */ - return false; + return; break; case RELPERSISTENCE_UNLOGGED: if (!toLogged) /* nothing to do */ - return false; + return; break; } =20 @@ -17866,7 +17959,13 @@ ATPrepChangePersistence(Relation rel, bool toLogge= d) =20 table_close(pg_constraint, AccessShareLock); =20 - return true; + /* force rewrite if necessary; see comment in ATRewriteTables */ + tab->rewrite |=3D AT_REWRITE_ALTER_PERSISTENCE; + if (toLogged) + tab->newrelpersistence =3D RELPERSISTENCE_PERMANENT; + else + tab->newrelpersistence =3D RELPERSISTENCE_UNLOGGED; + tab->chgPersistence =3D true; } =20 /* diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/e= xpected/alter_table.out index 7666c76238..5f3caf6baf 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3597,6 +3597,90 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do not= hing DROP TABLE logged3; DROP TABLE logged2; DROP TABLE logged1; +-- SET LOGGED/UNLOGGED with partitioned tables +CREATE TABLE logged_part_1(f1 SERIAL PRIMARY KEY) + PARTITION BY LIST (f1); -- has sequence, index +CREATE TABLE logged_part_2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES lo= gged_part_1) + PARTITION BY LIST (f1); -- foreign key +CREATE TABLE logged_part_3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES lo= gged_part_3) + PARTITION BY LIST (f1); -- self-referencing foreign key +-- Check relpersistence of all the objects created. +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part' + ORDER BY relname; + relname | relpersistence=20 +----------------------+---------------- + logged_part_1 | p + logged_part_1_f1_seq | p + logged_part_1_pkey | p + logged_part_2 | p + logged_part_2_f1_seq | p + logged_part_2_pkey | p + logged_part_3 | p + logged_part_3_f1_seq | p + logged_part_3_pkey | p +(9 rows) + +ALTER TABLE logged_part_1 SET UNLOGGED; -- fails as a foreign-key exists +ERROR: could not change table "logged_part_1" to unlogged because it refe= rences logged table "logged_part_2" +ALTER TABLE logged_part_2 SET UNLOGGED; +ALTER TABLE logged_part_3 SET UNLOGGED; -- skip self-referencing foreign k= ey +-- Re-check relpersistence of all the objects created. +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part' + ORDER BY relname; + relname | relpersistence=20 +----------------------+---------------- + logged_part_1 | p + logged_part_1_f1_seq | p + logged_part_1_pkey | p + logged_part_2 | u + logged_part_2_f1_seq | u + logged_part_2_pkey | u + logged_part_3 | u + logged_part_3_f1_seq | u + logged_part_3_pkey | u +(9 rows) + +ALTER TABLE logged_part_1 SET LOGGED; -- no-op +ALTER TABLE logged_part_2 SET LOGGED; +ALTER TABLE logged_part_3 SET LOGGED; -- skip self-referencing foreign key +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part= _[2|3]' + ORDER BY relname; + relname | relpersistence=20 +----------------------+---------------- + logged_part_2 | p + logged_part_2_f1_seq | p + logged_part_2_pkey | p + logged_part_3 | p + logged_part_3_f1_seq | p + logged_part_3_pkey | p +(6 rows) + +-- Partitions +CREATE TABLE logged_part_2_1 PARTITION OF logged_part_2 + FOR VALUES IN (1); -- permanent, inherited +CREATE UNLOGGED TABLE logged_part_2_2 PARTITION OF logged_part_2 + FOR VALUES IN (2); -- unlogged, not inherited +ALTER TABLE logged_part_2 SET UNLOGGED; +CREATE TABLE logged_part_2_3 PARTITION OF logged_part_2 + FOR VALUES IN (3); -- unlogged, inherited +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part= _2' + ORDER BY relname; + relname | relpersistence=20 +----------------------+---------------- + logged_part_2 | u + logged_part_2_1 | p + logged_part_2_1_pkey | p + logged_part_2_2 | u + logged_part_2_2_pkey | u + logged_part_2_3 | u + logged_part_2_3_pkey | u + logged_part_2_f1_seq | u + logged_part_2_pkey | u +(9 rows) + +DROP TABLE logged_part_3; +DROP TABLE logged_part_2; +DROP TABLE logged_part_1; -- test ADD COLUMN IF NOT EXISTS CREATE TABLE test_add_column(c1 integer); \d test_add_column diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/al= ter_table.sql index 9df5a63bdf..903e3aa217 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -2259,6 +2259,41 @@ DROP TABLE logged3; DROP TABLE logged2; DROP TABLE logged1; =20 +-- SET LOGGED/UNLOGGED with partitioned tables +CREATE TABLE logged_part_1(f1 SERIAL PRIMARY KEY) + PARTITION BY LIST (f1); -- has sequence, index +CREATE TABLE logged_part_2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES lo= gged_part_1) + PARTITION BY LIST (f1); -- foreign key +CREATE TABLE logged_part_3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES lo= gged_part_3) + PARTITION BY LIST (f1); -- self-referencing foreign key +-- Check relpersistence of all the objects created. +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part' + ORDER BY relname; +ALTER TABLE logged_part_1 SET UNLOGGED; -- fails as a foreign-key exists +ALTER TABLE logged_part_2 SET UNLOGGED; +ALTER TABLE logged_part_3 SET UNLOGGED; -- skip self-referencing foreign k= ey +-- Re-check relpersistence of all the objects created. +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part' + ORDER BY relname; +ALTER TABLE logged_part_1 SET LOGGED; -- no-op +ALTER TABLE logged_part_2 SET LOGGED; +ALTER TABLE logged_part_3 SET LOGGED; -- skip self-referencing foreign key +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part= _[2|3]' + ORDER BY relname; +-- Partitions +CREATE TABLE logged_part_2_1 PARTITION OF logged_part_2 + FOR VALUES IN (1); -- permanent, inherited +CREATE UNLOGGED TABLE logged_part_2_2 PARTITION OF logged_part_2 + FOR VALUES IN (2); -- unlogged, not inherited +ALTER TABLE logged_part_2 SET UNLOGGED; +CREATE TABLE logged_part_2_3 PARTITION OF logged_part_2 + FOR VALUES IN (3); -- unlogged, inherited +SELECT relname, relpersistence FROM pg_class WHERE relname ~ '^logged_part= _2' + ORDER BY relname; +DROP TABLE logged_part_3; +DROP TABLE logged_part_2; +DROP TABLE logged_part_1; + -- test ADD COLUMN IF NOT EXISTS CREATE TABLE test_add_column(c1 integer); \d test_add_column diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_tab= le.sgml index fe36ff82e5..b85d8dcd15 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -803,6 +803,12 @@ WITH ( MODULUS numeri= c_literal, REM (for identity or serial columns). However, it is also possible to change the persistence of such sequences separately. + + + Setting this property for a partitioned table has no direct effect, + because such tables have no storage of their own, but the configured + value will be inherited by newly-created partitions. + =20 diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_t= able.sgml index 02f31d2d6f..d1ff9d9e95 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -221,6 +221,11 @@ WITH ( MODULUS numeri= c_literal, REM If this is specified, any sequences created together with the unlogg= ed table (for identity or serial columns) are also created as unlogged. + + + When applied to a partitioned table, newly-created partitions and + their objects (sequences and indexes) will inherit this property. + =20 --=20 2.43.0 --7actJiB2aMlTdNbF-- --+BZEbbd7OfPWKCwg Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmYoshgACgkQnvQgOdby QH3CPA//dEiHBidVQJnXvgZ8+XB06YIRs0UylGeSvK0sUh37ipA4nox1JSkRsic+ AvrGb81qU660aPnWpXNPgCPBcFPGRPk9FlR/NooGD8rl+5QqkqeM7xlgeplbmiiw rwmxkJoudch96eLqQ3mbqJcJPf3Qq7wylt6aI59SSFfpQJGnxSoLcsS4pO4fooLQ FrowlnGD8oZp/vn9kVpwMNQIhmZsq0KiliRb3IVujjF5jmRqq9T25/S6hoqidVIj KY4VRcPrZDsv/Y12maNHuJ9ZqEJAzZhTZJTSj+8+mQjphifg/P3USTZ9EQH2hSSD 0qyTcRiB7Kwwsz6o5yoyaY4qcmKsmO5bNiPmbYqUV6ztP635k3uLHrwBTa3/Dg4O Jq2BiLCi4sTmD+WBf/XDpExxqGlG8J+keDnI+smJlgbpDJ06cPPC6Jy+HF6Q+g1c 0jy88NKC0cVDVOYJZVmHSc46HPcKuMX8MXMoRZWcHrmrTibD/UP6KFtEN6NRtvU7 HDY56g9Vn8chrnctT0DPx2ull5Y+OiqWXUUwe9PPcKIfY883FUf0WKTOhV4C24NW DbUkaTPJ4yNF59dKIEm8SLj+knpEjMjgyeVt0X8EfLY18LziKoVP1i2STVSO4m0g h/jgL7MvHjZYGh2Fd/Ki1IDH6p3HKR552Zc6N/w20iaHfJbo4ZA= =yW/c -----END PGP SIGNATURE----- --+BZEbbd7OfPWKCwg--