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 1qnZet-00BJfG-S4 for pgsql-general@arkaria.postgresql.org; Tue, 03 Oct 2023 07:17:31 +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 1qnZes-001zSq-E0 for pgsql-general@arkaria.postgresql.org; Tue, 03 Oct 2023 07:17:30 +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 1qnZes-001zS7-2w for pgsql-general@lists.postgresql.org; Tue, 03 Oct 2023 07:17:30 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qnZeq-007vhG-0k for pgsql-general@lists.postgresql.org; Tue, 03 Oct 2023 07:17:29 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.48]) by mailout.nyi.internal (Postfix) with ESMTP id 3BA215C0318; Tue, 3 Oct 2023 03:15:17 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute7.internal (MEProxy); Tue, 03 Oct 2023 03:15:17 -0400 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:sender :subject:subject:to:to; s=fm2; t=1696317317; x=1696403717; bh=Xq /KyNLN4WkLQ1vZoCKSSqQ70Xzr7U8MGCJjMMDAy9M=; b=Kc8RDtdLkh2HuQpG9b 0mW9VYs3VsX1Ni/FzUE2cDK8kb6+sQQuITYkxHPT+wolpsX4jjn5z7JpJo1QhE64 z0Ytg3g8YV9UxZSbDDVI0h9+pnc+ZzyL4yfc9EBf8CJQOdULl0Bj4IcFyYNnOZjj uFvFoCjKfhWaX4usX9RWRdzxBNObj228c7JFpd/Cmm8fVolnk/Pm3+TG2qVxGSUO eCHMS6b3f5L5Ylo5TqKLw701uPbz4ptPofNYO7Cu/5MWKUEJwwcjCoaBUQDpH0A/ jghrASMtDdOl46KRqq+p+IoL9+SJv/o1XFwnSqvLrW3i9IV3GoXDP8exoNH6cthG QeIw== 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:sender:subject :subject:to:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; t=1696317317; x=1696403717; bh=Xq/KyNLN4WkLQ 1vZoCKSSqQ70Xzr7U8MGCJjMMDAy9M=; b=NSf6I52kIN1Oc4u7tAKQKGqY3/U7o 2RNtozrpwM7v/8k92S9wI4hFJ0W2WNu67ZrJ+TW6bBbqKtHfTm5WgyAUcFqWFy74 lHkGkcIT0OwYwHsAU9Yplvtp+4Y3In1ZGyEd2dJBtsIaTY0JIt4+mza/tvRsde3e DJ84mBxMbIPnRrnkm3x0g59VYYTmhOHQP7a35uIn4rYrfNDsJZtDrSH10/g/ywj7 HyDIlNmj1Q8/MUpc4Mwljla8uRwI55JYvxzhBWfYzNPKS2qnF6riKS+81gFfMwm5 UMns9uldZyT7KCd9PHWIzroDZrdotOnfDrf9MFHC0WxDGF9kn9EuhXd2g== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrfeehgdduvdcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenfg hrlhcuvffnffculdejtddmnecujfgurhepfffhvfevuffkfhggtggujgesghdtreertddt vdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoehmihgthhgrvghlsehprg hquhhivghrrdighiiiqeenucggtffrrghtthgvrhhnpeetleeifedufffhhfdtteelgeeg geffhfekueevteeigfduudevudetgfegiedvjeenucevlhhushhtvghrufhiiigvpedtne curfgrrhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiii X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 3 Oct 2023 03:15:15 -0400 (EDT) Date: Tue, 3 Oct 2023 16:15:12 +0900 From: Michael Paquier To: Dominique Devienne Cc: Laurenz Albe , rob stone , pgsql-general@lists.postgresql.org Subject: Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION? Message-ID: References: <069c100e455299d6e1aa80bda283a397fadf1926.camel@tpg.com.au> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="l2yB0/JLDMjF0hKL" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --l2yB0/JLDMjF0hKL Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Tue, Oct 03, 2023 at 09:08:49AM +0200, Dominique Devienne wrote: > In my case, it's OK not to be transactional, for these experiments. Is > there a way > to lock the table and do the rewriting w/o generating any WAL? I don't have > any experience > with unlogged tables, but should I take an exclusive lock on the table, > switch it to unlogged, > rewrite, and switch it back to logged? Switching a table back to be logged requires all its 8k blocks to be WAL-logged, so that would be roughly the same as a plain UPDATE. -- Michael --l2yB0/JLDMjF0hKL Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmUbv4AACgkQnvQgOdby QH0zaRAAnUg1H5dFQuHvEZH3SCmF+S78zQtTuNvb1Hl6k0njBzLvWI9XreDOFWAs 5WyNddMiN3mnZc62EFfNdyuuqZIT/apBqpfxbschqlxyRUB1ze6GEsXcCNT1z6x1 BgcmhynW9bysKsJV86QkfUJX3M+Rkz+01A/yj9YtUfFF7BH+CluC4j9itqLsE1/9 +zORq6XFwwA80cQocggGHiVE6zYnd/pQzQdsK308TVF/nLsKBd1k6hhJvRuE4mEv XStZmeXsaZ/vBu+1kQImmgNAPQ8NqBEqSVa/COJa7i0COCbCitZZoT6MofGbBUZ8 R8Tov796aUu5DojNuiThSqxW/QYw0KK5ymk1aYCSWnNfaexicmUOyjEIywWo81qY Z1yl3dabhjjhpYLSWXfYZY2NV6+no8SqhYdgmHx8b6jPER34uHEY70o3orfHvxcJ Qw9SzQSvU6RMM51tGZCOTUath+/WxaDVnWUp6LNRPm4hh/QiUGDakU3JSREZp1zf Gy19qEP966HDck8kVi1U1SGk9Vwd4eNoXT0iz+yqaaB/4hNYtWbcsmlKaGT3aF7n 5Bo7n/5fa1+wqeObqbbvI3R7HiaXvSESTKh15Rzip85cJZ73OXWWSc3oI0gWdvL4 2FsAWQmh6yZAkx059UgesG2IC7s8JoD6rBImPq8FWEhZss3y0LA= =g2AR -----END PGP SIGNATURE----- --l2yB0/JLDMjF0hKL--