Create table with large object support
Table is optional. Even there is no table created, large object can still be created via these lo_ functions.
create table t (id int primary key, loc oid);
Populate table with different methods of large object construction
-- obtain bytea data, and create lo using lo_from_bytea
mytest=> select 'test string'::bytea;
bytea
--------------------------
\x7465737420737472696e67
(1 row)
mytest=> insert into t values (1,lo_from_bytea(0,'\x7465737420737472696e67'));
INSERT 0 1
-- combine above example into single step
mytest=> insert into t values (2,lo_from_bytea(0,'test string1'::bytea));
INSERT 0 1
-- create lo first, populate data and insert into table with lo oid
mytest=> select lo_create(0);
lo_create
-----------
16418
(1 row)
mytest=> begin;
BEGIN
mytest=*> SELECT pg_catalog.lo_open('16418',131072);
lo_open
---------
0
(1 row)
mytest=*> SELECT pg_catalog.lowrite(0,'test string2'::bytea);
lowrite
---------
12
(1 row)
mytest=*> SELECT pg_catalog.lo_close(0);
lo_close
----------
0
(1 row)
mytest=*> commit;
COMMIT
mytest=> insert into t values (3,'16418');
INSERT 0 1
-- create lo oid directly and populate data
mytest=> select max(loid) from pg_catalog.pg_largeobject;
max
-------
16418
(1 row)
mytest=> insert into t values (4, lo_create(16419));
INSERT 0 1
mytest=> select pg_catalog.lo_put('16419',0,'test string3'::bytea);
lo_put
--------
(1 row)
Examples to query data
mytest=> select * from pg_largeobject;
loid | pageno | data
-------+--------+----------------------------
16416 | 0 | \x7465737420737472696e67
16417 | 0 | \x7465737420737472696e6731
16418 | 0 | \x7465737420737472696e6732
16419 | 0 | \x7465737420737472696e6733
(4 rows)
mytest=> select * from pg_largeobject_metadata;
oid | lomowner | lomacl
-------+----------+--------
16416 | 16397 |
16417 | 16397 |
16418 | 16397 |
16419 | 16397 |
(4 rows)
mytest=> select id, encode(lo_get(loc),'escape') lo_to_text from t;
id | lo_to_text
----+--------------
1 | test string
2 | test string1
3 | test string2
4 | test string3
(4 rows)
mytest=> select id, loc, lo_get(loc), encode(lo_get(loc),'escape') lo_to_text from t;
id | loc | lo_get | lo_to_text
----+-------+----------------------------+--------------
1 | 16416 | \x7465737420737472696e67 | test string
2 | 16417 | \x7465737420737472696e6731 | test string1
3 | 16418 | \x7465737420737472696e6732 | test string2
4 | 16419 | \x7465737420737472696e6733 | test string3
(4 rows)
Delete rows and large objects
mytest=> delete from t where id=1;
DELETE 1
mytest=> select lo_unlink('16417');
lo_unlink
-----------
1
(1 row)
mytest=> select * from pg_largeobject;
loid | pageno | data
-------+--------+----------------------------
16416 | 0 | \x7465737420737472696e67
16418 | 0 | \x7465737420737472696e6732
16419 | 0 | \x7465737420737472696e6733
(3 rows)
mytest=> select id, loc, lo_get(loc) from t;
ERROR: large object 16417 does not exist
$ vacuumlo -h rdspg.ciscfgtmcrn3.us-east-1.rds.amazonaws.com mytest -v
Connected to database "mytest"
Checking loc in public.t
Successfully removed 1 large objects from database "mytest".
mytest=> delete from t where id=3;
DELETE 1
select lo_unlink('16418');
lo_unlink
-----------
1
(1 row)
Export data using pg_dump
$ pg_dump -h rdspg.ciscfgtmcrn3.us-east-1.rds.amazonaws.com
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.4
-- Dumped by pg_dump version 15.3
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: t; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.t (
id integer NOT NULL,
loc oid
);
ALTER TABLE public.t OWNER TO postgres;
--
-- Name: 16419; Type: BLOB; Schema: -; Owner: postgres
--
SELECT pg_catalog.lo_create('16419');
ALTER LARGE OBJECT 16419 OWNER TO postgres;
--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.t (id, loc) FROM stdin;
4 16419
\.
--
-- Data for Name: BLOBS; Type: BLOBS; Schema: -; Owner: -
--
BEGIN;
SELECT pg_catalog.lo_open('16419', 131072);
SELECT pg_catalog.lowrite(0, '\x7465737420737472696e6733');
SELECT pg_catalog.lo_close(0);
COMMIT;
--
-- Name: t t_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.t
ADD CONSTRAINT t_pkey PRIMARY KEY (id);
--
-- PostgreSQL database dump complete
--
Use LO Extension
-- clean up
mytest=> drop table t;
DROP TABLE
mytest=> select lo_unlink('16419');
lo_unlink
-----------
1
(1 row)
mytest=> select * from pg_largeobject;
loid | pageno | data
------+--------+------
(0 rows)
-- create extension and table using the lo extension
mytest=> create extension lo;
CREATE EXTENSION
mytest=> create table t (id int primary key, loc lo);
CREATE TABLE
mytest=> create or replace trigger t_trigger_cleanup_lo before update or delete on t
mytest-> for each row execute function lo_manage(loc);
CREATE TRIGGER
-- populate data
mytest=> insert into t values (1,lo_from_bytea(0,'test string1'::bytea));
INSERT 0 1
mytest=> insert into t values (2,lo_from_bytea(0,'test string2'::bytea));
INSERT 0 1
mytest=> insert into t values (3,lo_from_bytea(0,'test string3'::bytea));
INSERT 0 1
mytest=> insert into t values (4,lo_from_bytea(0,'test string4'::bytea));
INSERT 0 1
mytest=> select * from pg_largeobject;
loid | pageno | data
-------+--------+----------------------------
16438 | 0 | \x7465737420737472696e6731
16439 | 0 | \x7465737420737472696e6732
16440 | 0 | \x7465737420737472696e6733
16441 | 0 | \x7465737420737472696e6734
(4 rows)
-- delete data
mytest=> delete from t where id in (1,3);
DELETE 2
-- verify data, both data and lo delete without orphan records
mytest=> select * from t;
id | loc
----+-------
2 | 16439
4 | 16441
(2 rows)
mytest=> select * from pg_largeobject;
loid | pageno | data
-------+--------+----------------------------
16439 | 0 | \x7465737420737472696e6732
16441 | 0 | \x7465737420737472696e6734
(2 rows)
