0

Using Postgres 10.6.

My objective is to find the max size

If the datatype of a column in oid, I understand from https://www.postgresql.org/docs/9.2/largeobjects.html that it can be concluded to be an LOB column. Can I conclude that if the datatype of a column in not oid, then the column is not an LOB?

If not, how do I go about finding the LOB columns in the schema and their max size?

If yes, I found here (Get size of large object in PostgreSQL query?) how to find size of an LOB given an oid. The best approach I can think of as of now, to find the size of the largest LOB value in a schema, is to loop through all LOB rows in all tables, and take the max. Is there a better approach?

Surya Tamraparni
  • 28
  • 6
  • A `bytea` is a "LOB" as well (and the preferred type for this). The max size for that is 1GB –  Mar 24 '20 at 06:08

1 Answers1

0

The conclusion is not safe in either direction. I can certainly create a column of type OID, and populate it with integers from a sequence, or from a random number generator, or from generate_series. I could also create LOs, and stuff the resulting OIDs into columns of other types, like int (int are signed and so would overflow/wrap around eventually, but that would be a long way off), bigint, or even text.

It would not really make sense to do those things, but people sometimes do things that don't make sense.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • I do not think there is a supported data type in PG of type `OID`. Instead, that is the object identifier. Please correct me if I am wrong. Additionally, it would be nice if you could provide an example to go with it. – J Weezy Jan 11 '23 at 20:32