Large Objects

PostgreSQL has a large object facility which provides stream-style access to user data that is stored in a special large-object structure. Streaming access is useful when working with data values that are too large to manipulate conveniently as a whole.

All large objects are placed in a single system table called pg_largeobject. Each large object has its own OID in this table.

The TPgLargeObject class of PgDAC can be used to create, read, write and delete large objects. To manipulate with large objects create an instance of TPgLargeObject and specify the connection that will be used for operations with a large object. If you are working with an existent large object, specify its OID.

Creating a new object:

var
  LargeObject: TPgLargeObject;
  AData: array [1..10] of byte;
...
  PgConnection.StartTransaction;
  LargeObject := TPgLargeObject.Create(PgConnection);
  try
    LargeObject.CreateObject;
    LargeObject.Write(0, 10, &AData);
    LargeObject.WriteBlob;
    LargeObject.CloseObject;
  finally
    LargeObject.Free;
  end;
  PgConnection.Commit;

Reading an existent object:

...
  LargeObject := TPgLargeObject.Create(PgConnection);
  try
    LargeObject.OID := 12345;
    LargeObject.OpenObject;
    LargeObject.ReadBlob;
    LargeObject.Read(0, 10, &AData);
    LargeObject.CloseObject;
  finally
    LargeObject.Free;
  end;
...

Note that manipulations with large objects require a transaction. So StartTransaction is called in the example.

By default TPgLargeObject instance uses a memory buffer to hold a value of large object. On the first call to the Read method the TPgLargeObject reads the whole object value and stores it in the buffer. You can also call the ReadBlob method to read a value to the buffer.

When you write data using the Write method, the data are stored in memory buffer. You should call the WriteBlob method to pass the data to the database.

When working with very large objects, you can set the Cached property to False. In this case the memory buffer is not used, and the Read and Write methods work directly with a value in the database.

If you open a table with a column of the OID data type, TCustomPgDataSet descendant components assume that values in this column are large objects OIDs, and automatically read data from the corresponding large objects.

If OIDs are not large objects OIDs, set the OIDAsInt option of TCustomPgDataSet to True. In this case OID columns are read as simple integer columns.

You can use the DefferedBlobRead and CacheBlobs options to optimize performance and memory usage. If you set the DefferedBlobRead option to True, the dataset does not read large object data when it fetches records. When you access a value of a large object field, the data for the corresponding large object have been read.

If you set the CacheBlobs option to False, all large objects in the dataset do not cache their values. In this case the DefferedBlobRead value has no sense.