2
-- Test large object support
4
-- ensure consistent test output regardless of the default bytea format
5
SET bytea_output TO escape;
7
CREATE 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
11
INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42);
12
-- NOTE: large objects require transactions
14
-- lo_open(lobjId oid, mode integer) returns integer
15
-- The mode parameter to lo_open uses two constants:
17
-- INV_WRITE = 0x40000
18
-- The return value is a file descriptor-like value which remains valid for the
20
UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer));
21
-- loread/lowrite names are wonky, different from other functions which are lo_*
22
-- lowrite(fd integer, data bytea) returns integer
23
-- the integer is the number of bytes written
25
Whose woods these are I think I know,
26
His house is in the village though.
27
He will not see me stopping here,
28
To watch his woods fill up with snow.
30
My little horse must think it queer,
31
To stop without a farmhouse near,
32
Between the woods and frozen lake,
33
The darkest evening of the year.
35
He gives his harness bells a shake,
36
To ask if there is some mistake.
37
The only other sound''s the sweep,
38
Of easy wind and downy flake.
40
The woods are lovely, dark and deep,
41
But I have promises to keep,
42
And miles to go before I sleep,
43
And miles to go before I sleep.
46
') FROM lotest_stash_values;
52
-- lo_close(fd integer) returns integer
53
-- return value is 0 for success, or <0 for error (actually only -1, but...)
54
SELECT lo_close(fd) FROM lotest_stash_values;
63
UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
64
-- lo_lseek(fd integer, offset integer, whence integer) returns integer
65
-- offset is in bytes, whence is one of three values:
66
-- SEEK_SET (= 0) meaning relative to beginning
67
-- SEEK_CUR (= 1) meaning relative to current position
68
-- SEEK_END (= 2) meaning relative to end (offset better be negative)
69
-- returns current position in file
70
SELECT lo_lseek(fd, 422, 0) FROM lotest_stash_values;
76
-- loread/lowrite names are wonky, different from other functions which are lo_*
77
-- loread(fd integer, len integer) returns bytea
78
SELECT loread(fd, 35) FROM lotest_stash_values;
80
-------------------------------------
81
The woods are lovely, dark and deep
84
SELECT lo_lseek(fd, -19, 1) FROM lotest_stash_values;
90
SELECT lowrite(fd, 'n') FROM lotest_stash_values;
96
SELECT lo_tell(fd) FROM lotest_stash_values;
102
SELECT lo_lseek(fd, -156, 2) FROM lotest_stash_values;
108
SELECT loread(fd, 35) FROM lotest_stash_values;
110
-------------------------------------
111
The woods are lonely, dark and deep
114
SELECT lo_close(fd) FROM lotest_stash_values;
121
-- Test resource management
123
SELECT lo_open(loid, x'40000'::int) from lotest_stash_values;
132
UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
133
SELECT lo_truncate(fd, 10) FROM lotest_stash_values;
139
SELECT loread(fd, 15) FROM lotest_stash_values;
145
SELECT lo_truncate(fd, 10000) FROM lotest_stash_values;
151
SELECT loread(fd, 10) FROM lotest_stash_values;
153
------------------------------------------
154
\000\000\000\000\000\000\000\000\000\000
157
SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
163
SELECT lo_tell(fd) FROM lotest_stash_values;
169
SELECT lo_truncate(fd, 5000) FROM lotest_stash_values;
175
SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
181
SELECT lo_tell(fd) FROM lotest_stash_values;
187
SELECT lo_close(fd) FROM lotest_stash_values;
194
-- lo_unlink(lobjId oid) returns integer
195
-- return value appears to always be 1
196
SELECT lo_unlink(loid) from lotest_stash_values;
202
TRUNCATE lotest_stash_values;
203
INSERT INTO lotest_stash_values (loid) SELECT lo_import('@abs_srcdir@/data/tenk.data');
205
UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
206
-- with the default BLKSZ, LOBLKSZ = 2048, so this positions us for a block
208
SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
214
-- this should get half of the value from page 0 and half from page 1 of the
216
SELECT loread(fd, 36) FROM lotest_stash_values;
218
-----------------------------------------------------------------
219
AAA\011FBAAAA\011VVVVxx\0122513\01132\0111\0111\0113\01113\0111
222
SELECT lo_tell(fd) FROM lotest_stash_values;
228
SELECT lo_lseek(fd, -26, 1) FROM lotest_stash_values;
234
SELECT lowrite(fd, 'abcdefghijklmnop') FROM lotest_stash_values;
240
SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
246
SELECT loread(fd, 36) FROM lotest_stash_values;
248
-----------------------------------------------------
249
AAA\011FBAAAAabcdefghijklmnop1\0111\0113\01113\0111
252
SELECT lo_close(fd) FROM lotest_stash_values;
259
SELECT lo_export(loid, '@abs_builddir@/results/lotest.txt') FROM lotest_stash_values;
265
\lo_import 'results/lotest.txt'
266
\set newloid :LASTOID
267
-- just make sure \lo_export does not barf
268
\lo_export :newloid 'results/lotest2.txt'
269
-- This is a hack to test that export/import are reversible
270
-- This uses knowledge about the inner workings of large object mechanism
271
-- which should not be used outside it. This makes it a HACK
272
SELECT pageno, data FROM pg_largeobject WHERE loid = (SELECT loid from lotest_stash_values)
274
SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
279
SELECT lo_unlink(loid) FROM lotest_stash_values;
286
TRUNCATE lotest_stash_values;