Feedback-ID: i0fe9450f:Fastmail
Date: Tue, 3 Sep 2024 16:59:18 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	Postgres hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Partitioned tables and [un]loggedness
Message-ID: <Zta_CFeFCx7bMBL8@paquier.xyz>
References: <CAKFQuwY3RTTrp7x4OmEruMmOghdorhq4=eC0ZfG0NM+HnBWFsw@mail.gmail.com>
 <ZimXRKOSHMjQjAy3@paquier.xyz>
 <CAKFQuwZwMOkw-7112u1-gXEhRWuXiZe9otp801Z3dPVvarKdsA@mail.gmail.com>
 <Zimb7xfc79Har77d@paquier.xyz>
 <ZjMte53zQpZNfTWv@paquier.xyz>
 <Zs4-xnNDMEdiKQXo@nathan>
 <ZtAY3ZkRVAIp3sxG@paquier.xyz>
 <ZtCKiJZaoYBHBv05@nathan>
 <ZtZW4gEJOPmeP-nT@paquier.xyz>
 <ZtZn0-JVQuBUbpWw@nathan>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="ZFkFdjW3fjWqcVd1"
Content-Disposition: inline
In-Reply-To: <ZtZn0-JVQuBUbpWw@nathan>
Archived-At: <https://www.postgresql.org/message-id/Zta_CFeFCx7bMBL8%40paquier.xyz>
Precedence: bulk


--ZFkFdjW3fjWqcVd1
Content-Type: multipart/mixed; boundary="rnZniTnZrxzBO4ae"
Content-Disposition: inline


--rnZniTnZrxzBO4ae
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Mon, Sep 02, 2024 at 08:35:15PM -0500, Nathan Bossart wrote:
> On Tue, Sep 03, 2024 at 09:22:58AM +0900, Michael Paquier wrote:
>> Okay.  This is going to be disruptive if we do nothing about pg_dump,
>> unfortunately.  How about tweaking dumpTableSchema() so as we'd never
>> issue UNLOGGED for a partitioned table?  We could filter that out as
>> there is tbinfo->relkind.
>=20
> That's roughly what I had in mind.

An idea is attached.  The pgbench bit was unexpected.
--
Michael

--rnZniTnZrxzBO4ae
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="unlogged-part-v3.patch"
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablec=
mds.c
index b3cc6f8f69..b1cc635498 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -728,6 +728,12 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid own=
erId,
 	else
 		partitioned =3D false;
=20
+	if (relkind =3D=3D RELKIND_PARTITIONED_TABLE &&
+		stmt->relation->relpersistence =3D=3D RELPERSISTENCE_UNLOGGED)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("partitioned tables cannot be unlogged")));
+
 	/*
 	 * Look up the namespace in which we are supposed to create the relation,
 	 * check we have permission to create there, lock it against concurrent
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index dacb033e98..c60d2994f2 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15896,8 +15896,13 @@ dumpTableSchema(Archive *fout, const TableInfo *tb=
info)
 			binary_upgrade_set_pg_class_oids(fout, q,
 											 tbinfo->dobj.catId.oid);
=20
+		/*
+		 * PostgreSQL 18 has disabled UNLOGGED for partitioned tables, so
+		 * ignore it when dumping if it was set in this case.
+		 */
 		appendPQExpBuffer(q, "CREATE %s%s %s",
-						  tbinfo->relpersistence =3D=3D RELPERSISTENCE_UNLOGGED ?
+						  tbinfo->relpersistence =3D=3D RELPERSISTENCE_UNLOGGED &&
+						  tbinfo->relkind !=3D RELKIND_PARTITIONED_TABLE ?
 						  "UNLOGGED " : "",
 						  reltypename,
 						  qualrelname);
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 61618f2e18..dc4d7408cd 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -4865,7 +4865,7 @@ initCreateTables(PGconn *con)
=20
 		/* Construct new create table statement. */
 		printfPQExpBuffer(&query, "create%s table %s(%s)",
-						  unlogged_tables ? " unlogged" : "",
+						  unlogged_tables && partition_method =3D=3D PART_NONE ? " unlogged"=
 : "",
 						  ddl->table,
 						  (scale >=3D SCALE_32BIT_THRESHOLD) ? ddl->bigcols : ddl->smcols);
=20
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/=
expected/create_table.out
index 284a7fb85c..2654891524 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -50,6 +50,8 @@ ERROR:  cannot create temporary relation in non-temporary=
 schema
 LINE 1: CREATE TEMP TABLE public.temp_to_perm (a int primary key);
                           ^
 DROP TABLE unlogged1, public.unlogged2;
+CREATE UNLOGGED TABLE unlogged1 (a int) PARTITION BY RANGE (a); -- fail
+ERROR:  partitioned tables cannot be unlogged
 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind =3D 'r';
 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind =3D 'r';
 ERROR:  relation "as_select1" already exists
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/c=
reate_table.sql
index 1fd4cbfa7e..5dbb25a092 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -30,6 +30,8 @@ CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key)=
;		-- also OK
 CREATE TEMP TABLE public.temp_to_perm (a int primary key);		-- not OK
 DROP TABLE unlogged1, public.unlogged2;
=20
+CREATE UNLOGGED TABLE unlogged1 (a int) PARTITION BY RANGE (a); -- fail
+
 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind =3D 'r';
 CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind =3D 'r';
 CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relk=
ind =3D 'r';
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_t=
able.sgml
index 93b3f664f2..0859afd75e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -220,6 +220,10 @@ WITH ( MODULUS <replaceable class=3D"parameter">numeri=
c_literal</replaceable>, REM
       If this is specified, any sequences created together with the unlogg=
ed
       table (for identity or serial columns) are also created as unlogged.
      </para>
+
+     <para>
+      This option is not supported for partitioned tables.
+     </para>
     </listitem>
    </varlistentry>
=20

--rnZniTnZrxzBO4ae--

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

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmbWwdYACgkQnvQgOdby
QH1WuhAAqo14I+/ssqS1eU1gKKa/RN/PfShvwUnIj0ZHmWNxGomWyYXerW5Whl4u
+ImAbpc7slnwltVcDFZJau21JL5OyrTFvOhiMDPsoyQc5w9wJ9Bn4RWnWKoA+4Fp
pqCd7egxkXO2W9fooADJqA6FENt7qqATaavaImnrxoSxwoj8oL12edplfJa6xPGa
5cVdSdV8qroPQL2PydT2fexP50m3ghvOx9Y1N86BxoICPWz1j1Oor2Ih9FwOocai
I3RurvH+PE0l+yMaCO+cr3fm6xYYYr/jYnlUnUN+IcJPm2lRpV6h9KNtB3G5bChq
XbDb5QAzp1IDGKMc6kmLDFVUmIrHL9Ljv/RiVq3484oMV509MT2V2kB5djdtvlaf
3TccCyl6vP0i5Wxo+xGOnzBbB1wtVWuP7K7DAKgDcXHWGiZTpOSytspz0KLahF/k
TG9dpDB+GSKUhy3jRDS6tNXz43mrKT2vh9Q8mpBnPMMLM0iXL08t5xOuU2qIsiDu
M8t6W4NqAQ8ulsBDpIds6sgoNJ9p4SLXWyrtKn1WW+5RZ46tsIiSdtJMddg5hxbs
J+PqK0tXA8AIY6RVK50nNuExxY1c60XI+tMtP7a1sZcy0eOHD2F7btLeGHon7guM
kxVfMx1EBXAMv2b0kPvmtUUjY0tRSC660W+JZ0tSgmrPtn9p0sQ=
=GpTi
-----END PGP SIGNATURE-----

--ZFkFdjW3fjWqcVd1--