regress/output/largeobject.source

--
-- 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(3001);
 lo_create
-----------
      3001
(1 row)

COMMENT ON LARGE OBJECT 3001 IS 'testing comments';
-- Clean up
DROP TABLE lotest_stash_values;
DROP ROLE regress_lo_user;