Introduction to KingbaseES Large Object of Renmin University of Jincang Database

CREATE TABLE image (

name text,

raster oid

);

SELECT lo_creat(-1); -- returns OID of new, empty large object

SELECT lo_create(43213); -- attempts to create large object with OID 43213

SELECT lo_unlink(173454); -- deletes large object with OID 173454

INSERT INTO image (name, raster) VALUES ('beautiful image', lo_import('/etc/motd'));

INSERT INTO image (name, raster) VALUES ('beautiful image', lo_import('/etc/motd', 68583)); -- same as above, but specify OID to use

SELECT lo_export(, '/tmp/motd') FROM image WHERE name = 'beautiful image';

create table t1 (i int, file_id OID);

insert into t1 values (1, lo_import('/home/zn/tmp/'));

select * from t1;

i | file_id

---+---------

1 | 16909

(1 row)

select lo_export(16909, '/home/zn/tmp/blob_output');

lo_export

-----------

1

(1 row)

\lo_list

Large objects

ID | Owner | Description

-------+----------+-------------

16909 | postgres |

(1 row)

select lo_unlink( 16909 );

lo_unlink

-----------

1

(1 row)

\lo_list

Large objects

ID | Owner | Description

----+-------+-------------

(0 rows)

select * from t2;

i | photo_id

---+----------

1 | 16909

(1 row)

select lo_export(16909, '/home/zn/tmp/blob_output');

ERROR: large object 16909 does not exist

// src/include/dbserver/kernel/meta_data/sys_largeobject.h

METADATA(_lob,2613,LargeObjectRelationId)

{

Oid loid;; /* Large object identifier */

int32 pageno;; /* Page number (starting from 0) */

/* The length of the data is variable, but we allow direct access; see inv_api.c */

bytea data BKI_FORCE_NOT_NULL;; /* Page data (may be zero length) */

} FormData_largeobject;; /* data has variable length, but we allow direct access; see inv_api.c */

bytea data BKI_FORCE_NOT_NULL; /* Data for page (may be zero-length) */

} FormData_pg_largeobject;

Name

Type

References

Description

loid

oid

sys_largeobject_metadata.oid

Identifier of the large object that includes this page

pageno

int4

number of this page within its large object (counting from zero)

data

bytea

Actual data stored in the large object. This will never be more than LOBLKSIZE bytes and might be less.

// src/include/dbserver/kernel/meta_data/sys_largeobject_metadata.h

METADATA(_lob_meta,2995,LargeObjectMetadataRelationId)

{

Oid oid;; /* oid */

Oid lomowner;; /* OID of the large object owner */

#ifdef METADATA_VARLEN /* Variable length field starts here */

aclitem lomacl[1];; /* Access permissions */

#endif

} FormData_largeobject_metadata;;

Name

Type

References

Description

loid

oid

Row identifier (hidden attribute; must be explicitly selected)

lomowner

oid

sys_authid.oid

Owner of the large object

lomacl

aclitem[]

Access privileges; see GRANT and REVOKE for details

// src/include/dbserver/sdk/datatype/

typedef struct LoDescData

{

Oid id;; /* LO identifier */

Snapshot snapshot;; /* Snapshot to use */

SubTransactionId subid;; /* Have subtransaction ID */

uint64 offset;; /* Current search pointer */

int flags;; /* See the following marking bits */

/*

* bits in the flag:

*/

#define IFS_READLOCK (1 << 0) /* LO open reading */

#define IFS_WRITELOCK (1 << 1) /* LO Open Writing */

} LoDescData;;

/*

* Each "page" (tuple) of a large object can hold this much data

*/

#define LOBLOCKSIZE (BLCKSZ / 4) // 8192 / 4 = 2048 = 2K

/*

* Maximum length in bytes for a large object.

*/

#define MAX_LO_SIZE ((int64) INT_MAX * LOBLOCKSIZE) // 2^31 * 2K = 2^31 * 2^11 = 2^42 = 4T

zn=# \df lo_*

List of functions

Schema | Name | Result data type | Argument data types | Type

------------+---------------+------------------+---------------------------+------

sys_catalog | lo_close | integer | integer | func

sys_catalog | lo_creat | oid | integer | func

sys_catalog | lo_create | oid | oid | func

sys_catalog | lo_export | integer | oid, text | func

sys_catalog | lo_from_bytea | oid | oid, bytea | func

sys_catalog | lo_get | bytea | oid | func

sys_catalog | lo_get | bytea | oid, bigint, integer | func

sys_catalog | lo_import | oid | text | func

sys_catalog | lo_import | oid | text, oid | func

sys_catalog | lo_lseek | integer | integer, integer, integer | func

sys_catalog | lo_lseek64 | bigint | integer, bigint, integer | func

sys_catalog | lo_open | integer | oid, integer | func

sys_catalog | lo_put | void | oid, bigint, bytea | func

sys_catalog | lo_tell | integer | integer | func

sys_catalog | lo_tell64 | bigint | integer | func

sys_catalog | lo_truncate | integer | integer, integer | func

sys_catalog | lo_truncate64 | integer | integer, bigint | func

sys_catalog | lo_unlink | integer | oid | func

(18 rows)

# src/include/dbserver/kernel/meta_data/sys_proc.dat

{

oid => '952',

descr => 'large object open',

proname => 'lo_open',

provolatile => 'v',

proparallel => 'u',

prorettype => 'int4',

proargtypes => 'oid int4',

prosrc => 'be_lo_open' },

src/dbconnector/libkci/front_lobj.c: lo_create()...

src/dbserver/kernel/comm/libkci/backend_fsstubs.c: be_lo_create()...

CREATE TEMP TABLE vacuum_l AS SELECT oid AS lo FROM sys_largeobject_metadata;

ANALYZE vacuum_l;

SELECT , ,

FROM sys_class c, sys_attribute a, sys_namespace s, sys_type t

WHERE > 0 AND NOT

AND =

AND =

AND =

AND in ('oid', 'lo', 'blob', 'clob')

AND in ('r', 'm')

AND !~ '^sys_';

DELETE FROM vacuum_l WHERE lo IN (SELECT %s FROM %s.%s) -- field, schema, table

-- 1000 rows are deleted each time

begin;

DECLARE myportal CURSOR WITH HOLD FOR SELECT lo FROM vacuum_l;

while (1)

FETCH FORWARD 1000 IN myportal;

-- for each line in result set, call lo_unlink(conn, lo);

commit; -- commit once for 1000 deleted lo

begin;

commit;