2
-- Test large object support
5
-- ensure consistent test output regardless of the default bytea format
6
SET bytea_output TO escape;
9
CREATE TABLE lotest_stash_values (loid oid, fd integer);
10
-- lo_creat(mode integer) returns oid
11
-- The mode arg to lo_creat is unused, some vestigal holdover from ancient times
12
-- returns the large object id
13
INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42);
15
-- Test ALTER LARGE OBJECT
16
CREATE ROLE regress_lo_user;
19
EXECUTE 'ALTER LARGE OBJECT ' || (select loid from lotest_stash_values)
20
|| ' OWNER TO regress_lo_user';
27
JOIN pg_largeobject_metadata lo ON s.loid = lo.oid
28
JOIN pg_authid rol ON lo.lomowner = rol.oid;
30
-- NOTE: large objects require transactions
33
-- lo_open(lobjId oid, mode integer) returns integer
34
-- The mode parameter to lo_open uses two constants:
36
-- INV_WRITE = 0x40000
37
-- The return value is a file descriptor-like value which remains valid for the
39
UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer));
41
-- loread/lowrite names are wonky, different from other functions which are lo_*
42
-- lowrite(fd integer, data bytea) returns integer
43
-- the integer is the number of bytes written
45
I wandered lonely as a cloud
46
That floats on high o''er vales and hills,
47
When all at once I saw a crowd,
48
A host, of golden daffodils;
49
Beside the lake, beneath the trees,
50
Fluttering and dancing in the breeze.
52
Continuous as the stars that shine
53
And twinkle on the milky way,
54
They stretched in never-ending line
55
Along the margin of a bay:
56
Ten thousand saw I at a glance,
57
Tossing their heads in sprightly dance.
59
The waves beside them danced; but they
60
Out-did the sparkling waves in glee:
61
A poet could not but be gay,
62
In such a jocund company:
63
I gazed--and gazed--but little thought
64
What wealth the show to me had brought:
66
For oft, when on my couch I lie
67
In vacant or in pensive mood,
68
They flash upon that inward eye
69
Which is the bliss of solitude;
70
And then my heart with pleasure fills,
71
And dances with the daffodils.
74
') FROM lotest_stash_values;
76
-- lo_close(fd integer) returns integer
77
-- return value is 0 for success, or <0 for error (actually only -1, but...)
78
SELECT lo_close(fd) FROM lotest_stash_values;
82
-- Copy to another large object.
83
-- Note: we intentionally don't remove the object created here;
84
-- it's left behind to help test pg_dump.
86
SELECT lo_from_bytea(0, lo_get(loid)) AS newloid FROM lotest_stash_values
89
-- Add a comment to it, as well, for pg_dump/pg_upgrade testing.
90
COMMENT ON LARGE OBJECT :newloid IS 'I Wandered Lonely as a Cloud';
94
UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
96
-- lo_lseek(fd integer, offset integer, whence integer) returns integer
97
-- offset is in bytes, whence is one of three values:
98
-- SEEK_SET (= 0) meaning relative to beginning
99
-- SEEK_CUR (= 1) meaning relative to current position
100
-- SEEK_END (= 2) meaning relative to end (offset better be negative)
101
-- returns current position in file
102
SELECT lo_lseek(fd, 104, 0) FROM lotest_stash_values;
104
-- loread/lowrite names are wonky, different from other functions which are lo_*
105
-- loread(fd integer, len integer) returns bytea
106
SELECT loread(fd, 28) FROM lotest_stash_values;
108
SELECT lo_lseek(fd, -19, 1) FROM lotest_stash_values;
110
SELECT lowrite(fd, 'n') FROM lotest_stash_values;
112
SELECT lo_tell(fd) FROM lotest_stash_values;
114
SELECT lo_lseek(fd, -744, 2) FROM lotest_stash_values;
116
SELECT loread(fd, 28) FROM lotest_stash_values;
118
SELECT lo_close(fd) FROM lotest_stash_values;
122
-- Test resource management
124
SELECT lo_open(loid, x'40000'::int) from lotest_stash_values;
129
UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
131
SELECT lo_truncate(fd, 11) FROM lotest_stash_values;
132
SELECT loread(fd, 15) FROM lotest_stash_values;
134
SELECT lo_truncate(fd, 10000) FROM lotest_stash_values;
135
SELECT loread(fd, 10) FROM lotest_stash_values;
136
SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
137
SELECT lo_tell(fd) FROM lotest_stash_values;
139
SELECT lo_truncate(fd, 5000) FROM lotest_stash_values;
140
SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
141
SELECT lo_tell(fd) FROM lotest_stash_values;
143
SELECT lo_close(fd) FROM lotest_stash_values;
146
-- Test 64-bit large object functions.
148
UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer));
150
SELECT lo_lseek64(fd, 4294967296, 0) FROM lotest_stash_values;
151
SELECT lowrite(fd, 'offset:4GB') FROM lotest_stash_values;
152
SELECT lo_tell64(fd) FROM lotest_stash_values;
154
SELECT lo_lseek64(fd, -10, 1) FROM lotest_stash_values;
155
SELECT lo_tell64(fd) FROM lotest_stash_values;
156
SELECT loread(fd, 10) FROM lotest_stash_values;
158
SELECT lo_truncate64(fd, 5000000000) FROM lotest_stash_values;
159
SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values;
160
SELECT lo_tell64(fd) FROM lotest_stash_values;
162
SELECT lo_truncate64(fd, 3000000000) FROM lotest_stash_values;
163
SELECT lo_lseek64(fd, 0, 2) FROM lotest_stash_values;
164
SELECT lo_tell64(fd) FROM lotest_stash_values;
166
SELECT lo_close(fd) FROM lotest_stash_values;
169
-- lo_unlink(lobjId oid) returns integer
170
-- return value appears to always be 1
171
SELECT lo_unlink(loid) from lotest_stash_values;
173
TRUNCATE lotest_stash_values;
175
INSERT INTO lotest_stash_values (loid) SELECT lo_import('@abs_srcdir@/data/tenk.data');
178
UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
180
-- verify length of large object
181
SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
183
-- with the default BLKSZ, LOBLKSZ = 2048, so this positions us for a block
185
SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
187
-- this should get half of the value from page 0 and half from page 1 of the
189
SELECT loread(fd, 36) FROM lotest_stash_values;
191
SELECT lo_tell(fd) FROM lotest_stash_values;
193
SELECT lo_lseek(fd, -26, 1) FROM lotest_stash_values;
195
SELECT lowrite(fd, 'abcdefghijklmnop') FROM lotest_stash_values;
197
SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
199
SELECT loread(fd, 36) FROM lotest_stash_values;
201
SELECT lo_close(fd) FROM lotest_stash_values;
204
SELECT lo_export(loid, '@abs_builddir@/results/lotest.txt') FROM lotest_stash_values;
206
\lo_import 'results/lotest.txt'
208
\set newloid :LASTOID
210
-- just make sure \lo_export does not barf
211
\lo_export :newloid 'results/lotest2.txt'
213
-- This is a hack to test that export/import are reversible
214
-- This uses knowledge about the inner workings of large object mechanism
215
-- which should not be used outside it. This makes it a HACK
216
SELECT pageno, data FROM pg_largeobject WHERE loid = (SELECT loid from lotest_stash_values)
218
SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
220
SELECT lo_unlink(loid) FROM lotest_stash_values;
222
TRUNCATE lotest_stash_values;
226
\lo_import 'results/lotest.txt'
228
\set newloid_1 :LASTOID
230
SELECT lo_from_bytea(0, lo_get(:newloid_1)) AS newloid_2
233
SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
235
SELECT lo_get(:newloid_1, 0, 20);
236
SELECT lo_get(:newloid_1, 10, 20);
237
SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
238
SELECT lo_get(:newloid_1, 0, 20);
240
SELECT lo_put(:newloid_1, 4294967310, 'foo');
241
SELECT lo_get(:newloid_1);
242
SELECT lo_get(:newloid_1, 4294967294, 100);
244
\lo_unlink :newloid_1
245
\lo_unlink :newloid_2
247
-- This object is left in the database for pg_dump test purposes
248
SELECT lo_from_bytea(0, E'\\xdeadbeef') AS newloid
251
SET bytea_output TO hex;
252
SELECT lo_get(:newloid);
254
-- Create one more object that we leave behind for testing pg_dump/pg_upgrade;
255
-- this one intentionally has an OID in the system range
256
SELECT lo_create(3001);
258
COMMENT ON LARGE OBJECT 3001 IS 'testing comments';
261
DROP TABLE lotest_stash_values;
263
DROP ROLE regress_lo_user;