0

I have a table called tblA and its having two columns

id_ (numeric 50,0), obj_ oid

For example, tblA having 1 entries

id_  | obj_
1    | 1001

In pg_largeobject table, there are three entries against loid 1001 (3 page nos) which is fine. But, pg_largeobject table is having three columns (

loid  | pageno    | data
(oid)   (integer)   (bytea)

I read about oids and bytea, both are different data types and being used in that way. But I am confused about pg_largeobject table structure, why its using both data types for same data?

Is postgres internally storing oids as bytea, and just returning oids for reference? If its working in this manner, why can't use just numeric type for reference instead of oid type.

If not, what is the use of oid here and is any data sitting behind the oid somewhere in the database, and what is the bytea column data about.?

1

oid as a type is a 32-bit unsigned integer that stands for object id, and is used by the system as an all-purpose surrogate key to refer to different objects and sometimes rows in pg_catalog. Being the type for keys to large objects is just one of its many uses. Other "objects" like tables, sequences, types and more are refered to by their oid.

But I am confused about pg_largeobject table structure, why its using both data types for same data? Is postgres internally storing oids as bytea, and just returning oids for reference? If its working in this manner, why can't use just numeric type for reference instead of oid type.

It stores the contents of large objects as small bytea pages (~2000 bytes)in the pg_largeobject table, with loid as the ID or the large object, and (loid,pageno) as the unique key for that table. The generator of new values for loid can be compared to a integer sequence, except that after wrapping around at 2^32, it can avoid conflicts with existing values. As users, we don't have to care about this, it's managed by the internals.

If not, what is the use of oid here and is any data sitting behind the oid somewhere in the database, and what is the bytea column data about.?

The oid is used as a handle and the bytea column holds the actual binary data.

  • loid wont exceeds 2^32 (4294967296)? – Spike Aug 22 '16 at 8:33
  • @Spike: no they won't, the max value for the oid type is (2^32)-1. – Daniel Vérité Aug 22 '16 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.