diff options
Diffstat (limited to '')
| -rw-r--r-- | src/test/regress/expected/largeobject.out | 563 |
1 files changed, 563 insertions, 0 deletions
diff --git a/src/test/regress/expected/largeobject.out b/src/test/regress/expected/largeobject.out new file mode 100644 index 0000000..4921dd7 --- /dev/null +++ b/src/test/regress/expected/largeobject.out @@ -0,0 +1,563 @@ +-- +-- Test large object support +-- +-- directory paths are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR +\getenv abs_builddir PG_ABS_BUILDDIR +-- ensure consistent test output regardless of the default bytea format +SET bytea_output TO escape; +-- Test ALTER LARGE OBJECT OWNER +CREATE ROLE regress_lo_user; +SELECT lo_create(42); + lo_create +----------- + 42 +(1 row) + +ALTER LARGE OBJECT 42 OWNER TO regress_lo_user; +-- Test GRANT, COMMENT as non-superuser +SET SESSION AUTHORIZATION regress_lo_user; +GRANT SELECT ON LARGE OBJECT 42 TO public; +COMMENT ON LARGE OBJECT 42 IS 'the ultimate answer'; +RESET SESSION AUTHORIZATION; +-- Test psql's \lo_list et al (we assume no other LOs exist yet) +\lo_list + Large objects + ID | Owner | Description +----+-----------------+--------------------- + 42 | regress_lo_user | the ultimate answer +(1 row) + +\lo_list+ + Large objects + ID | Owner | Access privileges | Description +----+-----------------+------------------------------------+--------------------- + 42 | regress_lo_user | regress_lo_user=rw/regress_lo_user+| the ultimate answer + | | =r/regress_lo_user | +(1 row) + +\lo_unlink 42 +\dl + Large objects + ID | Owner | Description +----+-------+------------- +(0 rows) + +-- 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); +-- NOTE: large objects require transactions +BEGIN; +-- lo_open(lobjId oid, mode integer) returns integer +-- The mode parameter to lo_open uses two constants: +-- INV_WRITE = 0x20000 +-- INV_READ = 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; +\set filename :abs_builddir '/results/invalid/path' +\set dobody 'DECLARE loid oid; BEGIN ' +\set dobody :dobody 'SELECT tbl.loid INTO loid FROM lotest_stash_values tbl; ' +\set dobody :dobody 'PERFORM lo_export(loid, ' :'filename' '); ' +\set dobody :dobody 'EXCEPTION WHEN UNDEFINED_FILE THEN ' +\set dobody :dobody 'RAISE NOTICE ''could not open file, as expected''; END' +DO :'dobody'; +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; +\set filename :abs_srcdir '/data/tenk.data' +INSERT INTO lotest_stash_values (loid) SELECT lo_import(:'filename'); +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; +\set filename :abs_builddir '/results/lotest.txt' +SELECT lo_export(loid, :'filename') FROM lotest_stash_values; + lo_export +----------- + 1 +(1 row) + +\lo_import :filename +\set newloid :LASTOID +-- just make sure \lo_export does not barf +\set filename :abs_builddir '/results/lotest2.txt' +\lo_export :newloid :filename +-- 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 +\set filename :abs_builddir '/results/lotest.txt' +\lo_import :filename +\set newloid_1 :LASTOID +SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2 +\gset +SELECT fipshash(lo_get(:newloid_1)) = fipshash(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'; +-- Test writes on large objects in read-only transactions +START TRANSACTION READ ONLY; +-- INV_READ ... ok +SELECT lo_open(2121, x'40000'::int); + lo_open +--------- + 0 +(1 row) + +-- INV_WRITE ... error +SELECT lo_open(2121, x'20000'::int); +ERROR: cannot execute lo_open(INV_WRITE) in a read-only transaction +ROLLBACK; +START TRANSACTION READ ONLY; +SELECT lo_create(42); +ERROR: cannot execute lo_create() in a read-only transaction +ROLLBACK; +START TRANSACTION READ ONLY; +SELECT lo_creat(42); +ERROR: cannot execute lo_creat() in a read-only transaction +ROLLBACK; +START TRANSACTION READ ONLY; +SELECT lo_unlink(42); +ERROR: cannot execute lo_unlink() in a read-only transaction +ROLLBACK; +START TRANSACTION READ ONLY; +SELECT lowrite(42, 'x'); +ERROR: cannot execute lowrite() in a read-only transaction +ROLLBACK; +START TRANSACTION READ ONLY; +SELECT lo_import(:'filename'); +ERROR: cannot execute lo_import() in a read-only transaction +ROLLBACK; +START TRANSACTION READ ONLY; +SELECT lo_truncate(42, 0); +ERROR: cannot execute lo_truncate() in a read-only transaction +ROLLBACK; +START TRANSACTION READ ONLY; +SELECT lo_truncate64(42, 0); +ERROR: cannot execute lo_truncate64() in a read-only transaction +ROLLBACK; +START TRANSACTION READ ONLY; +SELECT lo_from_bytea(0, 'x'); +ERROR: cannot execute lo_from_bytea() in a read-only transaction +ROLLBACK; +START TRANSACTION READ ONLY; +SELECT lo_put(42, 0, 'x'); +ERROR: cannot execute lo_put() in a read-only transaction +ROLLBACK; +-- Clean up +DROP TABLE lotest_stash_values; +DROP ROLE regress_lo_user; |