Large object in PostgreSQL

1. First knowledge of PostgreSQL large object

A colleague feels very slow when recovering from a file backed up using Pg_dump (using the plain format), so let me analyze what the reason is.

I got his. bak file, and the file has 1 more than one G. To facilitate the analysis, I used the split tool to split the file into multiple small files. In the. bak file, I found a large number of such SQL:

By querying the document, I know that lo represents large object. Not to mention, this is the first time I have touched the large object in PostgreSQL. Because of the impact of the LOB concept in Oracle, I would of course assume that large object is also a data type in PostgreSQL, like a blob or CLOB in Oracle. But after looking through the reference books and official documents, I found that there was no corresponding field type. Some people on the internet say that the large object in PostgreSQL has the same field type as OID. This makes me really puzzling, only to the end, until I figure out how large object is going back, I realize that people who say this probably know a little, but should not understand thoroughly.

In the process of querying the official documentation, two system tables related to the large object are exposed:

Simply put, the Pg_largeobject_metadata table is a metadata table of large object that records the OID (object identifier), owner, and access permissions for each large object. The Pg_largeobject table is a table that stores the large object specifically, which is stored as: Large ojbect is divided into multiple units in page (2K), and the first unit is represented as 1 records in Pg_largeobject.

However, I did not find any explanation to explain how to use the large object as the field value.

2. How to understand PostgreSQL large object

In Oracle, suppose we define a table with a field type of blob, for example

Create table T (desc BLOB)

Then we can insert a record into the field: INSERT into t values (' 0xadfe4358942c ')

Finally, when we query, the direct select desc from T will return:

0xadfe4358942c

I was wrong in the default of PostgreSQL is so fun. But what?

In fact, there is no large object field in PostgreSQL (at least to version 9.6). Large object exists as an object in PostgreSQL and can be referenced with OIDs using specific methods. Let's take a real-world example to illustrate: If you compare every record of a database table to 1 people and lob analogy to 1 dogs, Oracle handles 1 people with 1 dogs, and the dog is in the database. And about how the dog came, it was made out of thin air, genetically cloned, or his own mud, Oracle regardless of it. PostgreSQL large object is managed differently, the database must first have 1 dogs or more dogs, and then this person can handle the adoption formalities, but adoption formalities only complete adoption registration, said the dog belongs to you, but the dog you can not really take home. Because the dog is not really brought back, so many people can register to adopt the same dog, only need to give the adoption number to the same dog ID.

3. Use of Postgresql large object

Above is just a metaphor, that PostgreSQL large object how to use it? What do you do with each step in the process?

We'll use the procedure in. Bak to do the example.

1. Create large object (the database has a dog, but now the dog is just a concept, not an entity)

2. After the procedure is executed, 1 records are generated in Pg_largeobject_metadata, and the OID field value is 1000001. The Pg_largeobject table does not generate the corresponding record because the large object is now empty.

3. Open Large object (find this dog, ready to give life to it)-- the need to open a transaction before performing this step: begin;
5. Now, the dog has, but it was just alone at that time. We need someone to register for adoption now.

6. we create a new table:

   Then insert a record into the table, the value of the Lo field is 1000001

   
   

   Well, We are done with the adoption formalities now. Maybe someone's going to say, "No, it's over." Yes, that's it. It can be seen from here that people know which dog he adopted, but the dog doesn't know who adopted it. In computer terminology, the reference is known to the referrer, but the quoted person does not know the referrer.

   > Now the collar is adopted, but what's the point? Just by identifying the reference, is it possible to see the contents of the large object through the Lo field of the test table? Let's take a look:

   Unfortunately, what you see is a reference value that does not see the specific value of the large object. In fact, in PostgreSQL, the use of large object requires a specialized method that cannot be viewed or used directly using a reference field.

7. use after the adoption registration

   
   

   After the adoption registration, the following methods are available:

   
   

   1. Lo_export (OID loid, text filename)--Exports the data of a large object loid to a server file filename, returning the length of the export (integer type).
   2. Lo_unlink (OID loid)--Delete a large object with address Loid, return integer 1-Success-1-failed.
   3. Lo_open (OID loid, Integer open_mode)--opens a large object with an address of loid, prepares for read and write, Open_mode for open type: Inv_write (write, Value 131072), Inv_read (read, A value of 262144) or inv_write|inv_read (read and write with a value of 393216). Returns the file handle FD (integer), which fails if FD is negative.
   4. Loread (integer fd, Integer len)--reads the Len-sized data at the beginning of the current position of the FD, and returns the data content (Bytea type).
   5. Lowrite (integer fd, Bytea buf)--Writes the binary data buf to a large object at the current position of the handle FD, returning the length of the write (integer type).
   6. Lo_lseek (integer fd, integer offset, integer whence)--changes the current read and write position of the handle FD. Whence is the addressing method, Seek_set (value 0) starts with the object header, Seek_cur (value 1) starts at the current read and write position, Seek_end (value 2) starts at the end of the object and offset is the offsets dimension. Returns the new read-write location (int), 1 indicates an error.

   7. Note: Loread, Lowrite will automatically change the current reading and writing position, so if the order read and write, Lo_lseek This command is useless.

   8. Lo_tell (integer FD)--Returns the current position of the handle FD (int)
   9. Lo_truncate (integer fd, Integer len)--intercept handle FD opens a large object length of len size. If Len is larger than the length of the original large object, it will have a ' \ s ' character in the large object suffix. A successful return of 0 failed with a negative number.
   10. Lo_close (integer FD)--Closes the handle FD, returns 0 successfully, and fails with a negative number.

       The above function involves the DF handle, which must be done within a transaction, that is, the handle FD is only valid within a single transaction, and it shuts down automatically when the transaction ends.

8. Use case

For example, we can lo_export method to export the large object corresponding to the Test Table Lo field to local.

View Test.f File

By querying the file format corresponding to the 1f 8b 08 file header, it is known that the Test.f file is a GZ compressed file. Extracting Test.f files via Gunzip

OK, now let's see what it's clear. This is actually a kind of vector tile's plaintext format.

Large object

in PostgreSQL