diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
commit | 6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch) | |
tree | 657d8194422a5daccecfd42d654b8a245ef7b4c8 /contrib/lo | |
parent | Initial commit. (diff) | |
download | postgresql-13-upstream.tar.xz postgresql-13-upstream.zip |
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r-- | contrib/lo/.gitignore | 4 | ||||
-rw-r--r-- | contrib/lo/Makefile | 20 | ||||
-rw-r--r-- | contrib/lo/expected/lo.out | 50 | ||||
-rw-r--r-- | contrib/lo/lo--1.0--1.1.sql | 6 | ||||
-rw-r--r-- | contrib/lo/lo--1.1.sql | 25 | ||||
-rw-r--r-- | contrib/lo/lo.c | 111 | ||||
-rw-r--r-- | contrib/lo/lo.control | 6 | ||||
-rw-r--r-- | contrib/lo/lo_test.sql | 79 | ||||
-rw-r--r-- | contrib/lo/sql/lo.sql | 30 |
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; |