1-- 2-- Test large object support 3-- 4-- ensure consistent test output regardless of the default bytea format 5SET bytea_output TO escape; 6-- Load a file 7CREATE TABLE lotest_stash_values (loid oid, fd integer); 8-- lo_creat(mode integer) returns oid 9-- The mode arg to lo_creat is unused, some vestigal holdover from ancient times 10-- returns the large object id 11INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42); 12-- Test ALTER LARGE OBJECT 13CREATE ROLE regress_lo_user; 14DO $$ 15 BEGIN 16 EXECUTE 'ALTER LARGE OBJECT ' || (select loid from lotest_stash_values) 17 || ' OWNER TO regress_lo_user'; 18 END 19$$; 20SELECT 21 rol.rolname 22FROM 23 lotest_stash_values s 24 JOIN pg_largeobject_metadata lo ON s.loid = lo.oid 25 JOIN pg_authid rol ON lo.lomowner = rol.oid; 26 rolname 27----------------- 28 regress_lo_user 29(1 row) 30 31-- NOTE: large objects require transactions 32BEGIN; 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-- loread/lowrite names are wonky, different from other functions which are lo_* 41-- lowrite(fd integer, data bytea) returns integer 42-- the integer is the number of bytes written 43SELECT lowrite(fd, ' 44I wandered lonely as a cloud 45That floats on high o''er vales and hills, 46When all at once I saw a crowd, 47A host, of golden daffodils; 48Beside the lake, beneath the trees, 49Fluttering and dancing in the breeze. 50 51Continuous as the stars that shine 52And twinkle on the milky way, 53They stretched in never-ending line 54Along the margin of a bay: 55Ten thousand saw I at a glance, 56Tossing their heads in sprightly dance. 57 58The waves beside them danced; but they 59Out-did the sparkling waves in glee: 60A poet could not but be gay, 61In such a jocund company: 62I gazed--and gazed--but little thought 63What wealth the show to me had brought: 64 65For oft, when on my couch I lie 66In vacant or in pensive mood, 67They flash upon that inward eye 68Which is the bliss of solitude; 69And then my heart with pleasure fills, 70And dances with the daffodils. 71 72 -- William Wordsworth 73') FROM lotest_stash_values; 74 lowrite 75--------- 76 848 77(1 row) 78 79-- lo_close(fd integer) returns integer 80-- return value is 0 for success, or <0 for error (actually only -1, but...) 81SELECT lo_close(fd) FROM lotest_stash_values; 82 lo_close 83---------- 84 0 85(1 row) 86 87END; 88-- Copy to another large object. 89-- Note: we intentionally don't remove the object created here; 90-- it's left behind to help test pg_dump. 91SELECT lo_from_bytea(0, lo_get(loid)) AS newloid FROM lotest_stash_values 92\gset 93-- Add a comment to it, as well, for pg_dump/pg_upgrade testing. 94COMMENT ON LARGE OBJECT :newloid IS 'I Wandered Lonely as a Cloud'; 95-- Read out a portion 96BEGIN; 97UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer)); 98-- lo_lseek(fd integer, offset integer, whence integer) returns integer 99-- offset is in bytes, whence is one of three values: 100-- SEEK_SET (= 0) meaning relative to beginning 101-- SEEK_CUR (= 1) meaning relative to current position 102-- SEEK_END (= 2) meaning relative to end (offset better be negative) 103-- returns current position in file 104SELECT lo_lseek(fd, 104, 0) FROM lotest_stash_values; 105 lo_lseek 106---------- 107 104 108(1 row) 109 110-- loread/lowrite names are wonky, different from other functions which are lo_* 111-- loread(fd integer, len integer) returns bytea 112SELECT loread(fd, 28) FROM lotest_stash_values; 113 loread 114------------------------------ 115 A host, of golden daffodils; 116(1 row) 117 118SELECT lo_lseek(fd, -19, 1) FROM lotest_stash_values; 119 lo_lseek 120---------- 121 113 122(1 row) 123 124SELECT lowrite(fd, 'n') FROM lotest_stash_values; 125 lowrite 126--------- 127 1 128(1 row) 129 130SELECT lo_tell(fd) FROM lotest_stash_values; 131 lo_tell 132--------- 133 114 134(1 row) 135 136SELECT lo_lseek(fd, -744, 2) FROM lotest_stash_values; 137 lo_lseek 138---------- 139 104 140(1 row) 141 142SELECT loread(fd, 28) FROM lotest_stash_values; 143 loread 144------------------------------ 145 A host, on golden daffodils; 146(1 row) 147 148SELECT lo_close(fd) FROM lotest_stash_values; 149 lo_close 150---------- 151 0 152(1 row) 153 154END; 155-- Test resource management 156BEGIN; 157SELECT lo_open(loid, x'40000'::int) from lotest_stash_values; 158 lo_open 159--------- 160 0 161(1 row) 162 163ABORT; 164DO $$ 165DECLARE 166 loid oid; 167BEGIN 168 SELECT tbl.loid INTO loid FROM lotest_stash_values tbl; 169 PERFORM lo_export(loid, '@abs_builddir@/results/invalid/path'); 170EXCEPTION 171 WHEN UNDEFINED_FILE THEN RAISE NOTICE 'could not open file, as expected'; 172END; 173$$; 174NOTICE: could not open file, as expected 175-- Test truncation. 176BEGIN; 177UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer)); 178SELECT lo_truncate(fd, 11) FROM lotest_stash_values; 179 lo_truncate 180------------- 181 0 182(1 row) 183 184SELECT loread(fd, 15) FROM lotest_stash_values; 185 loread 186---------------- 187 \012I wandered 188(1 row) 189 190SELECT lo_truncate(fd, 10000) FROM lotest_stash_values; 191 lo_truncate 192------------- 193 0 194(1 row) 195 196SELECT loread(fd, 10) FROM lotest_stash_values; 197 loread 198------------------------------------------ 199 \000\000\000\000\000\000\000\000\000\000 200(1 row) 201 202SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values; 203 lo_lseek 204---------- 205 10000 206(1 row) 207 208SELECT lo_tell(fd) FROM lotest_stash_values; 209 lo_tell 210--------- 211 10000 212(1 row) 213 214SELECT lo_truncate(fd, 5000) FROM lotest_stash_values; 215 lo_truncate 216------------- 217 0 218(1 row) 219 220SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values; 221 lo_lseek 222---------- 223 5000 224(1 row) 225 226SELECT lo_tell(fd) FROM lotest_stash_values; 227 lo_tell 228--------- 229 5000 230(1 row) 231 232SELECT lo_close(fd) FROM lotest_stash_values; 233 lo_close 234---------- 235 0 236(1 row) 237 238END; 239-- Test 64-bit large object functions. 240BEGIN; 241UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer)); 242SELECT lo_lseek64(fd, 4294967296, 0) FROM lotest_stash_values; 243 lo_lseek64 244------------ 245 4294967296 246(1 row) 247 248SELECT lowrite(fd, 'offset:4GB') FROM lotest_stash_values; 249 lowrite 250--------- 251 10 252(1 row) 253 254SELECT lo_tell64(fd) FROM lotest_stash_values; 255 lo_tell64 256------------ 257 4294967306 258(1 row) 259 260SELECT lo_lseek64(fd, -10, 1) FROM lotest_stash_values; 261 lo_lseek64 262------------ 263 4294967296 264(1 row) 265 266SELECT lo_tell64(fd) FROM lotest_stash_values; 267 lo_tell64 268------------ 269 4294967296 270(1 row) 271 272SELECT loread(fd, 10) FROM lotest_stash_values; 273 loread 274------------ 275 offset:4GB 276(1 row) 277 278SELECT lo_truncate64(fd, 5000000000) FROM lotest_stash_values; 279 lo_truncate64 280--------------- 281 0 282(1 row) 283 284SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values; 285 lo_lseek64 286------------ 287 5000000000 288(1 row) 289 290SELECT lo_tell64(fd) FROM lotest_stash_values; 291 lo_tell64 292------------ 293 5000000000 294(1 row) 295 296SELECT lo_truncate64(fd, 3000000000) FROM lotest_stash_values; 297 lo_truncate64 298--------------- 299 0 300(1 row) 301 302SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values; 303 lo_lseek64 304------------ 305 3000000000 306(1 row) 307 308SELECT lo_tell64(fd) FROM lotest_stash_values; 309 lo_tell64 310------------ 311 3000000000 312(1 row) 313 314SELECT lo_close(fd) FROM lotest_stash_values; 315 lo_close 316---------- 317 0 318(1 row) 319 320END; 321-- lo_unlink(lobjId oid) returns integer 322-- return value appears to always be 1 323SELECT lo_unlink(loid) from lotest_stash_values; 324 lo_unlink 325----------- 326 1 327(1 row) 328 329TRUNCATE lotest_stash_values; 330INSERT INTO lotest_stash_values (loid) SELECT lo_import('@abs_srcdir@/data/tenk.data'); 331BEGIN; 332UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer)); 333-- verify length of large object 334SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values; 335 lo_lseek 336---------- 337 670800 338(1 row) 339 340-- with the default BLCKSZ, LOBLKSIZE = 2048, so this positions us for a block 341-- edge case 342SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values; 343 lo_lseek 344---------- 345 2030 346(1 row) 347 348-- this should get half of the value from page 0 and half from page 1 of the 349-- large object 350SELECT loread(fd, 36) FROM lotest_stash_values; 351 loread 352----------------------------------------------------------------- 353 AAA\011FBAAAA\011VVVVxx\0122513\01132\0111\0111\0113\01113\0111 354(1 row) 355 356SELECT lo_tell(fd) FROM lotest_stash_values; 357 lo_tell 358--------- 359 2066 360(1 row) 361 362SELECT lo_lseek(fd, -26, 1) FROM lotest_stash_values; 363 lo_lseek 364---------- 365 2040 366(1 row) 367 368SELECT lowrite(fd, 'abcdefghijklmnop') FROM lotest_stash_values; 369 lowrite 370--------- 371 16 372(1 row) 373 374SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values; 375 lo_lseek 376---------- 377 2030 378(1 row) 379 380SELECT loread(fd, 36) FROM lotest_stash_values; 381 loread 382----------------------------------------------------- 383 AAA\011FBAAAAabcdefghijklmnop1\0111\0113\01113\0111 384(1 row) 385 386SELECT lo_close(fd) FROM lotest_stash_values; 387 lo_close 388---------- 389 0 390(1 row) 391 392END; 393SELECT lo_export(loid, '@abs_builddir@/results/lotest.txt') FROM lotest_stash_values; 394 lo_export 395----------- 396 1 397(1 row) 398 399\lo_import '@abs_builddir@/results/lotest.txt' 400\set newloid :LASTOID 401-- just make sure \lo_export does not barf 402\lo_export :newloid '@abs_builddir@/results/lotest2.txt' 403-- This is a hack to test that export/import are reversible 404-- This uses knowledge about the inner workings of large object mechanism 405-- which should not be used outside it. This makes it a HACK 406SELECT pageno, data FROM pg_largeobject WHERE loid = (SELECT loid from lotest_stash_values) 407EXCEPT 408SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid; 409 pageno | data 410--------+------ 411(0 rows) 412 413SELECT lo_unlink(loid) FROM lotest_stash_values; 414 lo_unlink 415----------- 416 1 417(1 row) 418 419TRUNCATE lotest_stash_values; 420\lo_unlink :newloid 421\lo_import '@abs_builddir@/results/lotest.txt' 422\set newloid_1 :LASTOID 423SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2 424\gset 425SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2)); 426 ?column? 427---------- 428 t 429(1 row) 430 431SELECT lo_get(:newloid_1, 0, 20); 432 lo_get 433------------------------------------------- 434 8800\0110\0110\0110\0110\0110\0110\011800 435(1 row) 436 437SELECT lo_get(:newloid_1, 10, 20); 438 lo_get 439------------------------------------------- 440 \0110\0110\0110\011800\011800\0113800\011 441(1 row) 442 443SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex')); 444 lo_put 445-------- 446 447(1 row) 448 449SELECT lo_get(:newloid_1, 0, 20); 450 lo_get 451------------------------------------------------- 452 8800\011\257\257\257\2570\0110\0110\0110\011800 453(1 row) 454 455SELECT lo_put(:newloid_1, 4294967310, 'foo'); 456 lo_put 457-------- 458 459(1 row) 460 461SELECT lo_get(:newloid_1); 462ERROR: large object read request is too large 463SELECT lo_get(:newloid_1, 4294967294, 100); 464 lo_get 465--------------------------------------------------------------------- 466 \000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000foo 467(1 row) 468 469\lo_unlink :newloid_1 470\lo_unlink :newloid_2 471-- This object is left in the database for pg_dump test purposes 472SELECT lo_from_bytea(0, E'\\xdeadbeef') AS newloid 473\gset 474SET bytea_output TO hex; 475SELECT lo_get(:newloid); 476 lo_get 477------------ 478 \xdeadbeef 479(1 row) 480 481-- Create one more object that we leave behind for testing pg_dump/pg_upgrade; 482-- this one intentionally has an OID in the system range 483SELECT lo_create(3001); 484 lo_create 485----------- 486 3001 487(1 row) 488 489COMMENT ON LARGE OBJECT 3001 IS 'testing comments'; 490-- Clean up 491DROP TABLE lotest_stash_values; 492DROP ROLE regress_lo_user; 493