diff options
Diffstat (limited to 'src/test/regress/output/largeobject.source')
| -rw-r--r-- | src/test/regress/output/largeobject.source | 492 |
1 files changed, 492 insertions, 0 deletions
diff --git a/src/test/regress/output/largeobject.source b/src/test/regress/output/largeobject.source new file mode 100644 index 0000000..91d33b4 --- /dev/null +++ b/src/test/regress/output/largeobject.source @@ -0,0 +1,492 @@ +-- +-- Test large object support +-- +-- ensure consistent test output regardless of the default bytea format +SET bytea_output TO escape; +-- Load a file +CREATE TABLE lotest_stash_values (loid oid, fd integer); +-- lo_creat(mode integer) returns oid +-- The mode arg to lo_creat is unused, some vestigal holdover from ancient times +-- returns the large object id +INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42); +-- Test ALTER LARGE OBJECT +CREATE ROLE regress_lo_user; +DO $$ + BEGIN + EXECUTE 'ALTER LARGE OBJECT ' || (select loid from lotest_stash_values) + || ' OWNER TO regress_lo_user'; + END +$$; +SELECT + rol.rolname +FROM + lotest_stash_values s + JOIN pg_largeobject_metadata lo ON s.loid = lo.oid + JOIN pg_authid rol ON lo.lomowner = rol.oid; + rolname +----------------- + regress_lo_user +(1 row) + +-- NOTE: large objects require transactions +BEGIN; +-- lo_open(lobjId oid, mode integer) returns integer +-- The mode parameter to lo_open uses two constants: +-- INV_READ = 0x20000 +-- INV_WRITE = 0x40000 +-- The return value is a file descriptor-like value which remains valid for the +-- transaction. +UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer)); +-- loread/lowrite names are wonky, different from other functions which are lo_* +-- lowrite(fd integer, data bytea) returns integer +-- the integer is the number of bytes written +SELECT lowrite(fd, ' +I wandered lonely as a cloud +That floats on high o''er vales and hills, +When all at once I saw a crowd, +A host, of golden daffodils; +Beside the lake, beneath the trees, +Fluttering and dancing in the breeze. + +Continuous as the stars that shine +And twinkle on the milky way, +They stretched in never-ending line +Along the margin of a bay: +Ten thousand saw I at a glance, +Tossing their heads in sprightly dance. + +The waves beside them danced; but they +Out-did the sparkling waves in glee: +A poet could not but be gay, +In such a jocund company: +I gazed--and gazed--but little thought +What wealth the show to me had brought: + +For oft, when on my couch I lie +In vacant or in pensive mood, +They flash upon that inward eye +Which is the bliss of solitude; +And then my heart with pleasure fills, +And dances with the daffodils. + + -- William Wordsworth +') FROM lotest_stash_values; + lowrite +--------- + 848 +(1 row) + +-- lo_close(fd integer) returns integer +-- return value is 0 for success, or <0 for error (actually only -1, but...) +SELECT lo_close(fd) FROM lotest_stash_values; + lo_close +---------- + 0 +(1 row) + +END; +-- Copy to another large object. +-- Note: we intentionally don't remove the object created here; +-- it's left behind to help test pg_dump. +SELECT lo_from_bytea(0, lo_get(loid)) AS newloid FROM lotest_stash_values +\gset +-- Add a comment to it, as well, for pg_dump/pg_upgrade testing. +COMMENT ON LARGE OBJECT :newloid IS 'I Wandered Lonely as a Cloud'; +-- Read out a portion +BEGIN; +UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer)); +-- lo_lseek(fd integer, offset integer, whence integer) returns integer +-- offset is in bytes, whence is one of three values: +-- SEEK_SET (= 0) meaning relative to beginning +-- SEEK_CUR (= 1) meaning relative to current position +-- SEEK_END (= 2) meaning relative to end (offset better be negative) +-- returns current position in file +SELECT lo_lseek(fd, 104, 0) FROM lotest_stash_values; + lo_lseek +---------- + 104 +(1 row) + +-- loread/lowrite names are wonky, different from other functions which are lo_* +-- loread(fd integer, len integer) returns bytea +SELECT loread(fd, 28) FROM lotest_stash_values; + loread +------------------------------ + A host, of golden daffodils; +(1 row) + +SELECT lo_lseek(fd, -19, 1) FROM lotest_stash_values; + lo_lseek +---------- + 113 +(1 row) + +SELECT lowrite(fd, 'n') FROM lotest_stash_values; + lowrite +--------- + 1 +(1 row) + +SELECT lo_tell(fd) FROM lotest_stash_values; + lo_tell +--------- + 114 +(1 row) + +SELECT lo_lseek(fd, -744, 2) FROM lotest_stash_values; + lo_lseek +---------- + 104 +(1 row) + +SELECT loread(fd, 28) FROM lotest_stash_values; + loread +------------------------------ + A host, on golden daffodils; +(1 row) + +SELECT lo_close(fd) FROM lotest_stash_values; + lo_close +---------- + 0 +(1 row) + +END; +-- Test resource management +BEGIN; +SELECT lo_open(loid, x'40000'::int) from lotest_stash_values; + lo_open +--------- + 0 +(1 row) + +ABORT; +DO $$ +DECLARE + loid oid; +BEGIN + SELECT tbl.loid INTO loid FROM lotest_stash_values tbl; + PERFORM lo_export(loid, '@abs_builddir@/results/invalid/path'); +EXCEPTION + WHEN UNDEFINED_FILE THEN RAISE NOTICE 'could not open file, as expected'; +END; +$$; +NOTICE: could not open file, as expected +-- Test truncation. +BEGIN; +UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer)); +SELECT lo_truncate(fd, 11) FROM lotest_stash_values; + lo_truncate +------------- + 0 +(1 row) + +SELECT loread(fd, 15) FROM lotest_stash_values; + loread +---------------- + \012I wandered +(1 row) + +SELECT lo_truncate(fd, 10000) FROM lotest_stash_values; + lo_truncate +------------- + 0 +(1 row) + +SELECT loread(fd, 10) FROM lotest_stash_values; + loread +------------------------------------------ + \000\000\000\000\000\000\000\000\000\000 +(1 row) + +SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values; + lo_lseek +---------- + 10000 +(1 row) + +SELECT lo_tell(fd) FROM lotest_stash_values; + lo_tell +--------- + 10000 +(1 row) + +SELECT lo_truncate(fd, 5000) FROM lotest_stash_values; + lo_truncate +------------- + 0 +(1 row) + +SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values; + lo_lseek +---------- + 5000 +(1 row) + +SELECT lo_tell(fd) FROM lotest_stash_values; + lo_tell +--------- + 5000 +(1 row) + +SELECT lo_close(fd) FROM lotest_stash_values; + lo_close +---------- + 0 +(1 row) + +END; +-- Test 64-bit large object functions. +BEGIN; +UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer)); +SELECT lo_lseek64(fd, 4294967296, 0) FROM lotest_stash_values; + lo_lseek64 +------------ + 4294967296 +(1 row) + +SELECT lowrite(fd, 'offset:4GB') FROM lotest_stash_values; + lowrite +--------- + 10 +(1 row) + +SELECT lo_tell64(fd) FROM lotest_stash_values; + lo_tell64 +------------ + 4294967306 +(1 row) + +SELECT lo_lseek64(fd, -10, 1) FROM lotest_stash_values; + lo_lseek64 +------------ + 4294967296 +(1 row) + +SELECT lo_tell64(fd) FROM lotest_stash_values; + lo_tell64 +------------ + 4294967296 +(1 row) + +SELECT loread(fd, 10) FROM lotest_stash_values; + loread +------------ + offset:4GB +(1 row) + +SELECT lo_truncate64(fd, 5000000000) FROM lotest_stash_values; + lo_truncate64 +--------------- + 0 +(1 row) + +SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values; + lo_lseek64 +------------ + 5000000000 +(1 row) + +SELECT lo_tell64(fd) FROM lotest_stash_values; + lo_tell64 +------------ + 5000000000 +(1 row) + +SELECT lo_truncate64(fd, 3000000000) FROM lotest_stash_values; + lo_truncate64 +--------------- + 0 +(1 row) + +SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values; + lo_lseek64 +------------ + 3000000000 +(1 row) + +SELECT lo_tell64(fd) FROM lotest_stash_values; + lo_tell64 +------------ + 3000000000 +(1 row) + +SELECT lo_close(fd) FROM lotest_stash_values; + lo_close +---------- + 0 +(1 row) + +END; +-- lo_unlink(lobjId oid) returns integer +-- return value appears to always be 1 +SELECT lo_unlink(loid) from lotest_stash_values; + lo_unlink +----------- + 1 +(1 row) + +TRUNCATE lotest_stash_values; +INSERT INTO lotest_stash_values (loid) SELECT lo_import('@abs_srcdir@/data/tenk.data'); +BEGIN; +UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer)); +-- verify length of large object +SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values; + lo_lseek +---------- + 670800 +(1 row) + +-- with the default BLCKSZ, LOBLKSIZE = 2048, so this positions us for a block +-- edge case +SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values; + lo_lseek +---------- + 2030 +(1 row) + +-- this should get half of the value from page 0 and half from page 1 of the +-- large object +SELECT loread(fd, 36) FROM lotest_stash_values; + loread +----------------------------------------------------------------- + AAA\011FBAAAA\011VVVVxx\0122513\01132\0111\0111\0113\01113\0111 +(1 row) + +SELECT lo_tell(fd) FROM lotest_stash_values; + lo_tell +--------- + 2066 +(1 row) + +SELECT lo_lseek(fd, -26, 1) FROM lotest_stash_values; + lo_lseek +---------- + 2040 +(1 row) + +SELECT lowrite(fd, 'abcdefghijklmnop') FROM lotest_stash_values; + lowrite +--------- + 16 +(1 row) + +SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values; + lo_lseek +---------- + 2030 +(1 row) + +SELECT loread(fd, 36) FROM lotest_stash_values; + loread +----------------------------------------------------- + AAA\011FBAAAAabcdefghijklmnop1\0111\0113\01113\0111 +(1 row) + +SELECT lo_close(fd) FROM lotest_stash_values; + lo_close +---------- + 0 +(1 row) + +END; +SELECT lo_export(loid, '@abs_builddir@/results/lotest.txt') FROM lotest_stash_values; + lo_export +----------- + 1 +(1 row) + +\lo_import '@abs_builddir@/results/lotest.txt' +\set newloid :LASTOID +-- just make sure \lo_export does not barf +\lo_export :newloid '@abs_builddir@/results/lotest2.txt' +-- This is a hack to test that export/import are reversible +-- This uses knowledge about the inner workings of large object mechanism +-- which should not be used outside it. This makes it a HACK +SELECT pageno, data FROM pg_largeobject WHERE loid = (SELECT loid from lotest_stash_values) +EXCEPT +SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid; + pageno | data +--------+------ +(0 rows) + +SELECT lo_unlink(loid) FROM lotest_stash_values; + lo_unlink +----------- + 1 +(1 row) + +TRUNCATE lotest_stash_values; +\lo_unlink :newloid +\lo_import '@abs_builddir@/results/lotest.txt' +\set newloid_1 :LASTOID +SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2 +\gset +SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2)); + ?column? +---------- + t +(1 row) + +SELECT lo_get(:newloid_1, 0, 20); + lo_get +------------------------------------------- + 8800\0110\0110\0110\0110\0110\0110\011800 +(1 row) + +SELECT lo_get(:newloid_1, 10, 20); + lo_get +------------------------------------------- + \0110\0110\0110\011800\011800\0113800\011 +(1 row) + +SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex')); + lo_put +-------- + +(1 row) + +SELECT lo_get(:newloid_1, 0, 20); + lo_get +------------------------------------------------- + 8800\011\257\257\257\2570\0110\0110\0110\011800 +(1 row) + +SELECT lo_put(:newloid_1, 4294967310, 'foo'); + lo_put +-------- + +(1 row) + +SELECT lo_get(:newloid_1); +ERROR: large object read request is too large +SELECT lo_get(:newloid_1, 4294967294, 100); + lo_get +--------------------------------------------------------------------- + \000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000foo +(1 row) + +\lo_unlink :newloid_1 +\lo_unlink :newloid_2 +-- This object is left in the database for pg_dump test purposes +SELECT lo_from_bytea(0, E'\\xdeadbeef') AS newloid +\gset +SET bytea_output TO hex; +SELECT lo_get(:newloid); + lo_get +------------ + \xdeadbeef +(1 row) + +-- Create one more object that we leave behind for testing pg_dump/pg_upgrade; +-- this one intentionally has an OID in the system range +SELECT lo_create(2121); + lo_create +----------- + 2121 +(1 row) + +COMMENT ON LARGE OBJECT 2121 IS 'testing comments'; +-- Clean up +DROP TABLE lotest_stash_values; +DROP ROLE regress_lo_user; |