MIME-Version: 1.0
From: Gaurav Pant <gauravpant145@gmail.com>
Date: Wed, 1 May 2024 00:26:41 +0530
Message-ID: <CAG53p5L7=K7qOCneELDCyLQ4oZcd3d8EWhrEHpzav-zHNM+RZg@mail.gmail.com>
Subject: Introduction of a new field in pg_class indicating presence of a
 large object in a table
To: pgsql-general@lists.postgresql.org
Cc: Gaurav Pant <gauravpant145@gmail.com>
Content-Type: multipart/alternative; boundary="0000000000006633b8061754f0a1"
Archived-At: <https://www.postgresql.org/message-id/CAG53p5L7%3DK7qOCneELDCyLQ4oZcd3d8EWhrEHpzav-zHNM%2BRZg%40mail.gmail.com>
Precedence: bulk

--0000000000006633b8061754f0a1
Content-Type: text/plain; charset="UTF-8"

Hi All,

I hope you're all doing well!

Recently, I have across a scenario where one of our client was unable to
perform a major version upgrade of their PostgreSQL instance as it was
failing due to OOM errors. When reviewed the upgrade log files and queried
pg_largeobject_metadata, we found a significant number of large objects
(more than a million) due to which the upgrade seems to fail. As the client
had a different vendor in the past, they are unsure of why large objects
were created in the first place. However, in order to further dive deep,
the requirement is to identify the tables (and the columns) which contain
these large objects to decide on the next steps. I checked for online
resources but could not find any information that helps in this regard.

Although quite different from the above specified use case, pg_class does
contain a field: 'reltoastrelid' that helps us to identify the presence of
a toast table for any relation in PostgreSQL. I wanted to know if there is
any such system table that we can use to identify and map the fields
containing large objects and the respective tables and if it is not already
there, do we have any plans to incorporate the same in pg_class like we
have for pg_toast?

Thanks and Regards,
Gaurav Pant

--0000000000006633b8061754f0a1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi All,<div><br></div><div>I hope you&#39;re all doing=C2=
=A0well!</div><div><br></div><div>Recently, I have across a scenario=C2=A0w=
here one of our=C2=A0client was unable to perform a major version upgrade o=
f their PostgreSQL instance as it was failing due to OOM errors. When revie=
wed the upgrade log files and queried pg_largeobject_metadata, we found a s=
ignificant number of large objects (more than a million) due to which the u=
pgrade seems to fail. As the client had a different vendor in the past, the=
y are unsure of why large objects were created in the first=C2=A0place. How=
ever, in order to further dive deep, the requirement is to identify the tab=
les (and the columns) which contain these large objects to decide on the ne=
xt steps. I checked for online resources but could not find any information=
 that helps in this=C2=A0regard.=C2=A0</div><div><br></div><div>Although qu=
ite different from the above specified use case, pg_class does contain a fi=
eld: &#39;reltoastrelid&#39; that helps us to identify the presence of a to=
ast table for any relation in PostgreSQL. I wanted to know if there is any =
such system table that we can use to identify and map the fields containing=
 large objects and the respective tables and if it is not already there, do=
 we have any plans to incorporate the same in pg_class like we have for pg_=
toast?</div><div><br></div><div>Thanks and Regards,</div><div>Gaurav Pant</=
div></div>

--0000000000006633b8061754f0a1--