Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pdJdP-00042N-5h for pgsql-general@arkaria.postgresql.org; Fri, 17 Mar 2023 23:37:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pdJdO-0007c2-0v for pgsql-general@arkaria.postgresql.org; Fri, 17 Mar 2023 23:37:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pdJdN-0007bp-Mb for pgsql-general@lists.postgresql.org; Fri, 17 Mar 2023 23:37:17 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pdJdI-0003ec-1b for pgsql-general@lists.postgresql.org; Fri, 17 Mar 2023 23:37:16 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id 32E8D1CB2B; Sat, 18 Mar 2023 00:37:10 +0100 (CET) Date: Sat, 18 Mar 2023 00:37:10 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: EXTERNAL: Re: "No Free extents", table using all allocated space but no rows! Message-ID: <20230317233710.em2fpiwom7s35cel@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com> <6eec8fc3dfa743ff9b19d8ce0e87763d@lmco.com> <47d0b525ad3ba570b4100e7b8d2a2b002ceba232.camel@cybertec.at> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="g26ztw2sslori4bm" Content-Disposition: inline In-Reply-To: <47d0b525ad3ba570b4100e7b8d2a2b002ceba232.camel@cybertec.at> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --g26ztw2sslori4bm Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2023-03-16 20:24:49 +0100, Laurenz Albe wrote: > On Thu, 2023-03-16 at 13:20 +0000, Dolan, Sean wrote: > > I messed up and confused issues.=A0=A0 The error is :=A0=20 > > ERROR: Could not extend pg_tblspc/16555/PG_13_20200/xxxx/xxxx=A0 No spa= ce left on device > > HINT: Check free disk space > >=20 > > So the schema is "full" and the offender is this one table. > > I can't TRUNCATE as there needs to be space to perform the action. > > Is there a way to see if there is a transaction on that table like you = allude to? >=20 > If you don't have enough space to run TRUNCATE, and you don't feel like > extending the disk space, DROP TABLE would be a convenient alternative. If you need to get just a little bit free space, you may be able to reduce the reserved space on the file system. For example, on ext4 there are typically 5 % reserved for root, so by reducing that to 2 % you get about 3 % of extra space: kitty:~/tmp 0:26 :-) 19% dd if=3D/dev/zero of=3Dfill bs=3D1024k dd: error writing 'fill': No space left on device kitty:~/tmp 0:27 :-( 20% df -h . Filesystem Size Used Avail Use% Mounted on /dev/mapper/kitty--vg-home 4.8G 4.5G 0 100% /home kitty:~/tmp 0:27 :-) 21% echo test > small echo: write error: no space left on device kitty# tune2fs -m 2 /dev/kitty-vg/home tune2fs 1.46.2 (28-Feb-2021) Setting reserved blocks percentage to 2% (25784 blocks) kitty:~/tmp 0:27 :-( 22% df -h . Filesystem Size Used Avail Use% Mounted on /dev/mapper/kitty--vg-home 4.8G 4.5G 152M 97% /home kitty:~/tmp 0:29 :-) 23% echo test > small kitty:~/tmp 0:29 :-) 24% cat small test (You should restore the reserved space to the default afterwards. The BSD file system and its descendants (like ext4) don't like getting completely full.) hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --g26ztw2sslori4bm Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmQU+Z4ACgkQ8g5IURL+ KF166A/7BJ9l/BFghGFeHyOpdqnlckPcENpx4Ad79SXLk95sI9hXCBiCQDVmLhiu qTb6yS0ilA2Bx7xcYvM0guiApS8qc5rZiM1gsnpZerGlKnEHDn5cODa1f12mfTAM bxuaqTgfGzPnLOjVsGyVDDXv+T5SfNXqszKFEwqxp2tHd/LSFnJt2VmzpL5XalQN JXEH9LRcbBBmSF81xoi0PiBJbt6Dkj0I4lfmk5Rkstr1y+Bft0JO8Z9XQ8YGlXdC p3kA4RbiCG5Cun3r+deKa4lZgmn2C2nNHuxZd6KcXAclzg92F5a6GEjKbPtWpydZ dg2aS26Rwu6kmpGzYweSqJUJRChHSjBE55QyJODic4XK9usMWLqEyo8S9plpX5hp nEz4W4nBz6JMUz5xm1XlwH4t4hNMWe7krJBfnlcPveTg2qFt2jRTn2oXBeR/YIuX UNen5LwD9mhq7xtECiaAx6VDHbSjIPhVMp9C9x2ouNU8lkc+axdegb9hcyINhF8y a0arySdr+l8Lx9+h0qbm5rC5bg9BrJ0X1v/m2iPCLiIFjXlm5aui06KSunOBwM6k tz2SzdTqUBWBAwEYBUfzXdmxt5J4ZU+5wo0zZg/F9uCemo0TEZVlv2UEUOLMjLx9 bZJMfOWIeuCpH0QOsk9yUN0U7Aui+meCe7o4YBwHJCu2Xk8yED8= =vB3o -----END PGP SIGNATURE----- --g26ztw2sslori4bm--