1-- 2-- Test large object support 3-- 4 5-- ensure consistent test output regardless of the default bytea format 6SET bytea_output TO escape; 7 8-- Load a file 9CREATE TABLE lotest_stash_values (loid oid, fd integer); 10-- lo_creat(mode integer) returns oid 11-- The mode arg to lo_creat is unused, some vestigal holdover from ancient times 12-- returns the large object id 13INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42); 14 15-- Test ALTER LARGE OBJECT 16CREATE ROLE regress_lo_user; 17DO $$ 18 BEGIN 19 EXECUTE 'ALTER LARGE OBJECT ' || (select loid from lotest_stash_values) 20 || ' OWNER TO regress_lo_user'; 21 END 22$$; 23SELECT 24 rol.rolname 25FROM 26 lotest_stash_values s 27 JOIN pg_largeobject_metadata lo ON s.loid = lo.oid 28 JOIN pg_authid rol ON lo.lomowner = rol.oid; 29 30-- NOTE: large objects require transactions 31BEGIN; 32 33-- lo_open(lobjId oid, mode integer) returns integer 34-- The mode parameter to lo_open uses two constants: 35-- INV_READ = 0x20000 36-- INV_WRITE = 0x40000 37-- The return value is a file descriptor-like value which remains valid for the 38-- transaction. 39UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer)); 40 41-- loread/lowrite names are wonky, different from other functions which are lo_* 42-- lowrite(fd integer, data bytea) returns integer 43-- the integer is the number of bytes written 44SELECT lowrite(fd, ' 45I wandered lonely as a cloud 46That floats on high o''er vales and hills, 47When all at once I saw a crowd, 48A host, of golden daffodils; 49Beside the lake, beneath the trees, 50Fluttering and dancing in the breeze. 51 52Continuous as the stars that shine 53And twinkle on the milky way, 54They stretched in never-ending line 55Along the margin of a bay: 56Ten thousand saw I at a glance, 57Tossing their heads in sprightly dance. 58 59The waves beside them danced; but they 60Out-did the sparkling waves in glee: 61A poet could not but be gay, 62In such a jocund company: 63I gazed--and gazed--but little thought 64What wealth the show to me had brought: 65 66For oft, when on my couch I lie 67In vacant or in pensive mood, 68They flash upon that inward eye 69Which is the bliss of solitude; 70And then my heart with pleasure fills, 71And dances with the daffodils. 72 73 -- William Wordsworth 74') FROM lotest_stash_values; 75 76-- lo_close(fd integer) returns integer 77-- return value is 0 for success, or <0 for error (actually only -1, but...) 78SELECT lo_close(fd) FROM lotest_stash_values; 79 80END; 81 82-- Copy to another large object. 83-- Note: we intentionally don't remove the object created here; 84-- it's left behind to help test pg_dump. 85 86SELECT lo_from_bytea(0, lo_get(loid)) AS newloid FROM lotest_stash_values 87\gset 88 89-- Add a comment to it, as well, for pg_dump/pg_upgrade testing. 90COMMENT ON LARGE OBJECT :newloid IS 'I Wandered Lonely as a Cloud'; 91 92-- Read out a portion 93BEGIN; 94UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer)); 95 96-- lo_lseek(fd integer, offset integer, whence integer) returns integer 97-- offset is in bytes, whence is one of three values: 98-- SEEK_SET (= 0) meaning relative to beginning 99-- SEEK_CUR (= 1) meaning relative to current position 100-- SEEK_END (= 2) meaning relative to end (offset better be negative) 101-- returns current position in file 102SELECT lo_lseek(fd, 104, 0) FROM lotest_stash_values; 103 104-- loread/lowrite names are wonky, different from other functions which are lo_* 105-- loread(fd integer, len integer) returns bytea 106SELECT loread(fd, 28) FROM lotest_stash_values; 107 108SELECT lo_lseek(fd, -19, 1) FROM lotest_stash_values; 109 110SELECT lowrite(fd, 'n') FROM lotest_stash_values; 111 112SELECT lo_tell(fd) FROM lotest_stash_values; 113 114SELECT lo_lseek(fd, -744, 2) FROM lotest_stash_values; 115 116SELECT loread(fd, 28) FROM lotest_stash_values; 117 118SELECT lo_close(fd) FROM lotest_stash_values; 119 120END; 121 122-- Test resource management 123BEGIN; 124SELECT lo_open(loid, x'40000'::int) from lotest_stash_values; 125ABORT; 126 127DO $$ 128DECLARE 129 loid oid; 130BEGIN 131 SELECT tbl.loid INTO loid FROM lotest_stash_values tbl; 132 PERFORM lo_export(loid, '@abs_builddir@/results/invalid/path'); 133EXCEPTION 134 WHEN UNDEFINED_FILE THEN RAISE NOTICE 'could not open file, as expected'; 135END; 136$$; 137 138-- Test truncation. 139BEGIN; 140UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer)); 141 142SELECT lo_truncate(fd, 11) FROM lotest_stash_values; 143SELECT loread(fd, 15) FROM lotest_stash_values; 144 145SELECT lo_truncate(fd, 10000) FROM lotest_stash_values; 146SELECT loread(fd, 10) FROM lotest_stash_values; 147SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values; 148SELECT lo_tell(fd) FROM lotest_stash_values; 149 150SELECT lo_truncate(fd, 5000) FROM lotest_stash_values; 151SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values; 152SELECT lo_tell(fd) FROM lotest_stash_values; 153 154SELECT lo_close(fd) FROM lotest_stash_values; 155END; 156 157-- Test 64-bit large object functions. 158BEGIN; 159UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer)); 160 161SELECT lo_lseek64(fd, 4294967296, 0) FROM lotest_stash_values; 162SELECT lowrite(fd, 'offset:4GB') FROM lotest_stash_values; 163SELECT lo_tell64(fd) FROM lotest_stash_values; 164 165SELECT lo_lseek64(fd, -10, 1) FROM lotest_stash_values; 166SELECT lo_tell64(fd) FROM lotest_stash_values; 167SELECT loread(fd, 10) FROM lotest_stash_values; 168 169SELECT lo_truncate64(fd, 5000000000) FROM lotest_stash_values; 170SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values; 171SELECT lo_tell64(fd) FROM lotest_stash_values; 172 173SELECT lo_truncate64(fd, 3000000000) FROM lotest_stash_values; 174SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values; 175SELECT lo_tell64(fd) FROM lotest_stash_values; 176 177SELECT lo_close(fd) FROM lotest_stash_values; 178END; 179 180-- lo_unlink(lobjId oid) returns integer 181-- return value appears to always be 1 182SELECT lo_unlink(loid) from lotest_stash_values; 183 184TRUNCATE lotest_stash_values; 185 186INSERT INTO lotest_stash_values (loid) SELECT lo_import('@abs_srcdir@/data/tenk.data'); 187 188BEGIN; 189UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer)); 190 191-- verify length of large object 192SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values; 193 194-- with the default BLCKSZ, LOBLKSIZE = 2048, so this positions us for a block 195-- edge case 196SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values; 197 198-- this should get half of the value from page 0 and half from page 1 of the 199-- large object 200SELECT loread(fd, 36) FROM lotest_stash_values; 201 202SELECT lo_tell(fd) FROM lotest_stash_values; 203 204SELECT lo_lseek(fd, -26, 1) FROM lotest_stash_values; 205 206SELECT lowrite(fd, 'abcdefghijklmnop') FROM lotest_stash_values; 207 208SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values; 209 210SELECT loread(fd, 36) FROM lotest_stash_values; 211 212SELECT lo_close(fd) FROM lotest_stash_values; 213END; 214 215SELECT lo_export(loid, '@abs_builddir@/results/lotest.txt') FROM lotest_stash_values; 216 217\lo_import '@abs_builddir@/results/lotest.txt' 218 219\set newloid :LASTOID 220 221-- just make sure \lo_export does not barf 222\lo_export :newloid '@abs_builddir@/results/lotest2.txt' 223 224-- This is a hack to test that export/import are reversible 225-- This uses knowledge about the inner workings of large object mechanism 226-- which should not be used outside it. This makes it a HACK 227SELECT pageno, data FROM pg_largeobject WHERE loid = (SELECT loid from lotest_stash_values) 228EXCEPT 229SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid; 230 231SELECT lo_unlink(loid) FROM lotest_stash_values; 232 233TRUNCATE lotest_stash_values; 234 235\lo_unlink :newloid 236 237\lo_import '@abs_builddir@/results/lotest.txt' 238 239\set newloid_1 :LASTOID 240 241SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2 242\gset 243 244SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2)); 245 246SELECT lo_get(:newloid_1, 0, 20); 247SELECT lo_get(:newloid_1, 10, 20); 248SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex')); 249SELECT lo_get(:newloid_1, 0, 20); 250 251SELECT lo_put(:newloid_1, 4294967310, 'foo'); 252SELECT lo_get(:newloid_1); 253SELECT lo_get(:newloid_1, 4294967294, 100); 254 255\lo_unlink :newloid_1 256\lo_unlink :newloid_2 257 258-- This object is left in the database for pg_dump test purposes 259SELECT lo_from_bytea(0, E'\\xdeadbeef') AS newloid 260\gset 261 262SET bytea_output TO hex; 263SELECT lo_get(:newloid); 264 265-- Create one more object that we leave behind for testing pg_dump/pg_upgrade; 266-- this one intentionally has an OID in the system range 267SELECT lo_create(3001); 268 269COMMENT ON LARGE OBJECT 3001 IS 'testing comments'; 270 271-- Clean up 272DROP TABLE lotest_stash_values; 273 274DROP ROLE regress_lo_user; 275