summaryrefslogtreecommitdiffstats
path: root/contrib/lo
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/lo')
-rw-r--r--contrib/lo/.gitignore4
-rw-r--r--contrib/lo/Makefile20
-rw-r--r--contrib/lo/expected/lo.out50
-rw-r--r--contrib/lo/lo--1.0--1.1.sql6
-rw-r--r--contrib/lo/lo--1.1.sql25
-rw-r--r--contrib/lo/lo.c111
-rw-r--r--contrib/lo/lo.control6
-rw-r--r--contrib/lo/lo_test.sql79
-rw-r--r--contrib/lo/sql/lo.sql30
9 files changed, 331 insertions, 0 deletions
diff --git a/contrib/lo/.gitignore b/contrib/lo/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/contrib/lo/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/lo/Makefile b/contrib/lo/Makefile
new file mode 100644
index 0000000..7168938
--- /dev/null
+++ b/contrib/lo/Makefile
@@ -0,0 +1,20 @@
+# contrib/lo/Makefile
+
+MODULES = lo
+
+EXTENSION = lo
+DATA = lo--1.1.sql lo--1.0--1.1.sql
+PGFILEDESC = "lo - management for large objects"
+
+REGRESS = lo
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/lo
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/lo/expected/lo.out b/contrib/lo/expected/lo.out
new file mode 100644
index 0000000..c63e4b1
--- /dev/null
+++ b/contrib/lo/expected/lo.out
@@ -0,0 +1,50 @@
+CREATE EXTENSION lo;
+CREATE TABLE image (title text, raster lo);
+CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
+ FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);
+SELECT lo_create(43213);
+ lo_create
+-----------
+ 43213
+(1 row)
+
+SELECT lo_create(43214);
+ lo_create
+-----------
+ 43214
+(1 row)
+
+INSERT INTO image (title, raster) VALUES ('beautiful image', 43213);
+SELECT lo_get(43213);
+ lo_get
+--------
+ \x
+(1 row)
+
+SELECT lo_get(43214);
+ lo_get
+--------
+ \x
+(1 row)
+
+UPDATE image SET raster = 43214 WHERE title = 'beautiful image';
+SELECT lo_get(43213);
+ERROR: large object 43213 does not exist
+SELECT lo_get(43214);
+ lo_get
+--------
+ \x
+(1 row)
+
+-- test updating of unrelated column
+UPDATE image SET title = 'beautiful picture' WHERE title = 'beautiful image';
+SELECT lo_get(43214);
+ lo_get
+--------
+ \x
+(1 row)
+
+DELETE FROM image;
+SELECT lo_get(43214);
+ERROR: large object 43214 does not exist
+DROP TABLE image;
diff --git a/contrib/lo/lo--1.0--1.1.sql b/contrib/lo/lo--1.0--1.1.sql
new file mode 100644
index 0000000..10a4ea2
--- /dev/null
+++ b/contrib/lo/lo--1.0--1.1.sql
@@ -0,0 +1,6 @@
+/* contrib/lo/lo--1.0--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION lo UPDATE TO '1.1'" to load this file. \quit
+
+ALTER FUNCTION lo_oid(lo) PARALLEL SAFE;
diff --git a/contrib/lo/lo--1.1.sql b/contrib/lo/lo--1.1.sql
new file mode 100644
index 0000000..c817cb4
--- /dev/null
+++ b/contrib/lo/lo--1.1.sql
@@ -0,0 +1,25 @@
+/* contrib/lo/lo--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION lo" to load this file. \quit
+
+--
+-- Create the data type ... now just a domain over OID
+--
+
+CREATE DOMAIN lo AS pg_catalog.oid;
+
+--
+-- For backwards compatibility, define a function named lo_oid.
+--
+-- The other functions that formerly existed are not needed because
+-- the implicit casts between a domain and its underlying type handle them.
+--
+CREATE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid AS
+'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE;
+
+-- This is used in triggers
+CREATE FUNCTION lo_manage()
+RETURNS pg_catalog.trigger
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
diff --git a/contrib/lo/lo.c b/contrib/lo/lo.c
new file mode 100644
index 0000000..457be26
--- /dev/null
+++ b/contrib/lo/lo.c
@@ -0,0 +1,111 @@
+/*
+ * PostgreSQL definitions for managed Large Objects.
+ *
+ * contrib/lo/lo.c
+ *
+ */
+
+#include "postgres.h"
+
+#include "commands/trigger.h"
+#include "executor/spi.h"
+#include "utils/builtins.h"
+#include "utils/rel.h"
+
+PG_MODULE_MAGIC;
+
+
+/*
+ * This is the trigger that protects us from orphaned large objects
+ */
+PG_FUNCTION_INFO_V1(lo_manage);
+
+Datum
+lo_manage(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ int attnum; /* attribute number to monitor */
+ char **args; /* Args containing attr name */
+ TupleDesc tupdesc; /* Tuple Descriptor */
+ HeapTuple rettuple; /* Tuple to be returned */
+ bool isdelete; /* are we deleting? */
+ HeapTuple newtuple; /* The new value for tuple */
+ HeapTuple trigtuple; /* The original value of tuple */
+
+ if (!CALLED_AS_TRIGGER(fcinfo)) /* internal error */
+ elog(ERROR, "lo_manage: not fired by trigger manager");
+
+ if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) /* internal error */
+ elog(ERROR, "%s: must be fired for row",
+ trigdata->tg_trigger->tgname);
+
+ /*
+ * Fetch some values from trigdata
+ */
+ newtuple = trigdata->tg_newtuple;
+ trigtuple = trigdata->tg_trigtuple;
+ tupdesc = trigdata->tg_relation->rd_att;
+ args = trigdata->tg_trigger->tgargs;
+
+ if (args == NULL) /* internal error */
+ elog(ERROR, "%s: no column name provided in the trigger definition",
+ trigdata->tg_trigger->tgname);
+
+ /* tuple to return to Executor */
+ if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ rettuple = newtuple;
+ else
+ rettuple = trigtuple;
+
+ /* Are we deleting the row? */
+ isdelete = TRIGGER_FIRED_BY_DELETE(trigdata->tg_event);
+
+ /* Get the column we're interested in */
+ attnum = SPI_fnumber(tupdesc, args[0]);
+
+ if (attnum <= 0)
+ elog(ERROR, "%s: column \"%s\" does not exist",
+ trigdata->tg_trigger->tgname, args[0]);
+
+ /*
+ * Handle updates
+ *
+ * Here, if the value of the monitored attribute changes, then the large
+ * object associated with the original value is unlinked.
+ */
+ if (newtuple != NULL &&
+ bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber, trigdata->tg_updatedcols))
+ {
+ char *orig = SPI_getvalue(trigtuple, tupdesc, attnum);
+ char *newv = SPI_getvalue(newtuple, tupdesc, attnum);
+
+ if (orig != NULL && (newv == NULL || strcmp(orig, newv) != 0))
+ DirectFunctionCall1(be_lo_unlink,
+ ObjectIdGetDatum(atooid(orig)));
+
+ if (newv)
+ pfree(newv);
+ if (orig)
+ pfree(orig);
+ }
+
+ /*
+ * Handle deleting of rows
+ *
+ * Here, we unlink the large object associated with the managed attribute
+ */
+ if (isdelete)
+ {
+ char *orig = SPI_getvalue(trigtuple, tupdesc, attnum);
+
+ if (orig != NULL)
+ {
+ DirectFunctionCall1(be_lo_unlink,
+ ObjectIdGetDatum(atooid(orig)));
+
+ pfree(orig);
+ }
+ }
+
+ return PointerGetDatum(rettuple);
+}
diff --git a/contrib/lo/lo.control b/contrib/lo/lo.control
new file mode 100644
index 0000000..f73f8b5
--- /dev/null
+++ b/contrib/lo/lo.control
@@ -0,0 +1,6 @@
+# lo extension
+comment = 'Large Object maintenance'
+default_version = '1.1'
+module_pathname = '$libdir/lo'
+relocatable = true
+trusted = true
diff --git a/contrib/lo/lo_test.sql b/contrib/lo/lo_test.sql
new file mode 100644
index 0000000..7e52362
--- /dev/null
+++ b/contrib/lo/lo_test.sql
@@ -0,0 +1,79 @@
+/* contrib/lo/lo_test.sql */
+
+-- Adjust this setting to control where the objects get created.
+SET search_path = public;
+
+--
+-- This runs some common tests against the type
+--
+-- It's used just for development
+--
+-- XXX would be nice to turn this into a proper regression test
+--
+
+-- Check what is in pg_largeobject
+SELECT count(oid) FROM pg_largeobject_metadata;
+
+-- ignore any errors here - simply drop the table if it already exists
+DROP TABLE a;
+
+-- create the test table
+CREATE TABLE a (fname name,image lo);
+
+-- insert a null object
+INSERT INTO a VALUES ('empty');
+
+-- insert a large object based on a file
+INSERT INTO a VALUES ('/etc/group', lo_import('/etc/group')::lo);
+
+-- now select the table
+SELECT * FROM a;
+
+-- check that coercion to plain oid works
+SELECT *,image::oid from a;
+
+-- now test the trigger
+CREATE TRIGGER t_a
+BEFORE UPDATE OR DELETE ON a
+FOR EACH ROW
+EXECUTE PROCEDURE lo_manage(image);
+
+-- insert
+INSERT INTO a VALUES ('aa', lo_import('/etc/hosts'));
+SELECT * FROM a
+WHERE fname LIKE 'aa%';
+
+-- update
+UPDATE a SET image=lo_import('/etc/group')::lo
+WHERE fname='aa';
+SELECT * FROM a
+WHERE fname LIKE 'aa%';
+
+-- update the 'empty' row which should be null
+UPDATE a SET image=lo_import('/etc/hosts')
+WHERE fname='empty';
+SELECT * FROM a
+WHERE fname LIKE 'empty%';
+UPDATE a SET image=null
+WHERE fname='empty';
+SELECT * FROM a
+WHERE fname LIKE 'empty%';
+
+-- delete the entry
+DELETE FROM a
+WHERE fname='aa';
+SELECT * FROM a
+WHERE fname LIKE 'aa%';
+
+-- This deletes the table contents. Note, if you comment this out, and
+-- expect the drop table to remove the objects, think again. The trigger
+-- doesn't get fired by drop table.
+DELETE FROM a;
+
+-- finally drop the table
+DROP TABLE a;
+
+-- Check what is in pg_largeobject ... if different from original, trouble
+SELECT count(oid) FROM pg_largeobject_metadata;
+
+-- end of tests
diff --git a/contrib/lo/sql/lo.sql b/contrib/lo/sql/lo.sql
new file mode 100644
index 0000000..7703950
--- /dev/null
+++ b/contrib/lo/sql/lo.sql
@@ -0,0 +1,30 @@
+CREATE EXTENSION lo;
+
+CREATE TABLE image (title text, raster lo);
+
+CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
+ FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);
+
+SELECT lo_create(43213);
+SELECT lo_create(43214);
+
+INSERT INTO image (title, raster) VALUES ('beautiful image', 43213);
+
+SELECT lo_get(43213);
+SELECT lo_get(43214);
+
+UPDATE image SET raster = 43214 WHERE title = 'beautiful image';
+
+SELECT lo_get(43213);
+SELECT lo_get(43214);
+
+-- test updating of unrelated column
+UPDATE image SET title = 'beautiful picture' WHERE title = 'beautiful image';
+
+SELECT lo_get(43214);
+
+DELETE FROM image;
+
+SELECT lo_get(43214);
+
+DROP TABLE image;
generated by cgit v1.2.3 (git 2.39.1) at 2024-07-14 20:02:53 +0000