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 1pcct0-00033S-3O for pgsql-general@arkaria.postgresql.org; Thu, 16 Mar 2023 01:58:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pccsy-0006bH-0C for pgsql-general@arkaria.postgresql.org; Thu, 16 Mar 2023 01:58:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pccsx-0006Zc-8o for pgsql-general@lists.postgresql.org; Thu, 16 Mar 2023 01:58:31 +0000 Received: from mailfo01.lmco.com ([192.31.106.12]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pccsr-0001Dr-O2 for pgsql-general@lists.postgresql.org; Thu, 16 Mar 2023 01:58:28 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=lmco.com; h=from : to : subject : date : message-id : content-type : mime-version; s=corpgw220615; bh=XMILI7MsF5X/4kjuHH67/8btmfyB7jHkKOsaCuiScoI=; b=IA/WDahSFUR8nkdd7hMHOUVnjKIsWcj/Fi8YHSOY4Q+EfVrRPohsY6XxOFN/ynBCfIsl 6AtqLB09WltiQeTnF5erJMutFgBWIgGfwna4oHvrkpM+CC8GnsHuIP3ZM5aTH1p8SosJ rkBk3/9UPMIpJM8thYwLHsYtjnbLWZupvtvDB+lmeUkGacb5OFb+tBlfHG7w4hzYWc2O AhAtzIylb1i6BHjL8uMmAodWvG3B0MpDlvFm8YhfJvAuEFcnvCGvFYBJJVu9BiP/rCYe Z9D2z4/h0AdPIbcdQLlFnlk9EnB8nrEM48rgZUZbQOZIADf2fmBYdXiNn+toceDilLhi Qg== Received: from lmxpdend09w02.us.lmco.com (lmxpdend09w02.ems.lmco.com [172.23.20.57]) by mailfo01.lmco.com (8.16.0.42/8.16.0.42) with ESMTP id 32G1wJ1W268423 for ; Thu, 16 Mar 2023 01:58:19 GMT Received: from lmxpdend09w04.us.lmco.com (172.23.20.59) by lmxpdend09w02.us.lmco.com (172.23.20.57) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P256) id 15.1.2308.27; Wed, 15 Mar 2023 19:58:18 -0600 Received: from lmxpdend09w04.us.lmco.com ([172.23.20.59]) by lmxpdend09w04.us.lmco.com ([172.23.20.59]) with mapi id 15.01.2308.027; Wed, 15 Mar 2023 19:58:18 -0600 From: "Dolan, Sean" To: "pgsql-general@lists.postgresql.org" Subject: "No Free extents", table using all allocated space but no rows! Thread-Topic: "No Free extents", table using all allocated space but no rows! Thread-Index: AdlXqoMc6GppTpkMSYGlXxQf6GrBDw== Date: Thu, 16 Mar 2023 01:58:18 +0000 Message-ID: <0eda9b7d13e34cc786d30c602510f3e4@lmco.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [172.23.20.7] Content-Type: multipart/alternative; boundary="_000_0eda9b7d13e34cc786d30c602510f3e4lmcocom_" MIME-Version: 1.0 X-Proofpoint-ORIG-GUID: UdIE9IJt4RXVNTreEyyagpu38ZDrakot X-Proofpoint-GUID: UdIE9IJt4RXVNTreEyyagpu38ZDrakot X-LM-Outbound: External 172.23.20.57 cntry=** g=UdIE9IJt4RXVNTreEyyagpu38ZDrakot q=32G1wJ1W268423 m=1 X-LM-OBSENDER: sean.dolan@lmco.com X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:6.0.573,18.0.942 definitions=2023-03-15_12:2023-03-14,2023-03-15 signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 phishscore=0 malwarescore=0 spamscore=0 suspectscore=0 adultscore=0 mlxlogscore=474 bulkscore=0 mlxscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.12.0-2303150002 definitions=main-2303160015 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_0eda9b7d13e34cc786d30c602510f3e4lmcocom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Environment: PostGres 13 on RedHat 7.9. I am using logical replication (publisher/subscriber) between two databases= and there are times where one of our schemas gets to 100% of allocated spa= ce (No Free Extents). I went into the schema and did a \dt+ to see the am= ount of size being used and I could see one of the tables somehow shows 16G= B, essentially the amount of allocated size. Wanting to see what is in t= hat table, I did a simple select * from the table and it returns no rows. = Doing a count(*) also returns 0 rows. How can the table be using all that space but there is nothing "in" the tab= le? I don't care about the data (as I am testing) so I can drop and recr= eate that one table. \dt+ would then show 0 bytes. Later, I will then c= reate a subscription and then I will get a No Free Extents error again and = again the table has filled up. What can I look for? Thank you --_000_0eda9b7d13e34cc786d30c602510f3e4lmcocom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Environment: PostGres 13 on RedHat 7.9.   =

 

I am using logical replication (publisher/subscriber= ) between two databases and there are times where one of our schemas gets t= o 100% of allocated space (No Free Extents).   I went into the sc= hema and did a \dt+ to see the amount of size being used and I could see one of the tables somehow shows 16GB, essential= ly the amount of allocated size.    Wanting to see what is i= n that table, I did a simple select * from the table and it returns no rows= .  Doing a count(*) also returns 0 rows.

 

How can the table be using all that space but there = is nothing “in” the table?    I don’t care= about the data (as I am testing) so I can drop and recreate that one table= .  \dt+ would then show 0 bytes.    Later, I will t= hen create a subscription and then I will get a No Free Extents error again and again the table has = filled up.

 

What can I look for?

 

Thank you

--_000_0eda9b7d13e34cc786d30c602510f3e4lmcocom_--