Обсуждение: Introduction of a new field in pg_class indicating presence of a large object in a table
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
Re: Introduction of a new field in pg_class indicating presence of a large object in a table
От
"David G. Johnston"
Дата:
On Tue, Apr 30, 2024 at 11:57 AM Gaurav Pant <gauravpant145@gmail.com> wrote:
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?
Large Objects are nothing like TOAST. There is no system level association between large objects and tables. Sure, the DBA can choose to store a large object OID in a table, but how you'd go about figuring out which columns contain those is going to be installation specific. Though hopefully they used a bigint data type and maybe added "oid" to the column name...I suppose it would be interesting if one could define a FK on a table and point it at pg_largeobject_metadata but that I suspect would be the extent to which we'd do something along the lines of your request.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Apr 30, 2024 at 11:57 AM Gaurav Pant <gauravpant145@gmail.com>
> wrote:
>> 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?
> Large Objects are nothing like TOAST. There is no system level association
> between large objects and tables. Sure, the DBA can choose to store a
> large object OID in a table, but how you'd go about figuring out which
> columns contain those is going to be installation specific.
Yeah. You might want to look at contrib/vacuumlo, but realize that
that's fairly heuristic.
> Though
> hopefully they used a bigint data type and maybe added "oid" to the column
> name...I suppose it would be interesting if one could define a FK on a
> table and point it at pg_largeobject_metadata but that I suspect would be
> the extent to which we'd do something along the lines of your request.
That would solve the opposite problem, of preventing a column from
containing any OIDs that *weren't* large object OIDs. Given that
recording a large object OID elsewhere in the database is purely
an application decision, I don't think there's a reasonable way
for the system to track it.
regards, tom lane