2 -- Test large object support
6 CREATE TABLE lotest_stash_values (loid oid, fd integer);
7 -- lo_creat(mode integer) returns oid
8 -- The mode arg to lo_creat is unused, some vestigal holdover from ancient times
9 -- returns the large object id
10 INSERT INTO lotest_stash_values (loid) SELECT lo_creat(42);
12 -- NOTE: large objects require transactions
15 -- lo_open(lobjId oid, mode integer) returns integer
16 -- The mode parameter to lo_open uses two constants:
18 -- INV_WRITE = 0x40000
19 -- The return value is a file descriptor-like value which remains valid for the
21 UPDATE lotest_stash_values SET fd = lo_open(loid, CAST(x'20000' | x'40000' AS integer));
23 -- loread/lowrite names are wonky, different from other functions which are lo_*
24 -- lowrite(fd integer, data bytea) returns integer
25 -- the integer is the number of bytes written
27 Whose woods these are I think I know,
28 His house is in the village though.
29 He will not see me stopping here,
30 To watch his woods fill up with snow.
32 My little horse must think it queer,
33 To stop without a farmhouse near,
34 Between the woods and frozen lake,
35 The darkest evening of the year.
37 He gives his harness bells a shake,
38 To ask if there is some mistake.
39 The only other sound''s the sweep,
40 Of easy wind and downy flake.
42 The woods are lovely, dark and deep,
43 But I have promises to keep,
44 And miles to go before I sleep,
45 And miles to go before I sleep.
48 ') FROM lotest_stash_values;
50 -- lo_close(fd integer) returns integer
51 -- return value is 0 for success, or <0 for error (actually only -1, but...)
52 SELECT lo_close(fd) FROM lotest_stash_values;
58 UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
60 -- lo_lseek(fd integer, offset integer, whence integer) returns integer
61 -- offset is in bytes, whence is one of three values:
62 -- SEEK_SET (= 0) meaning relative to beginning
63 -- SEEK_CUR (= 1) meaning relative to current position
64 -- SEEK_END (= 2) meaning relative to end (offset better be negative)
65 -- returns current position in file
66 SELECT lo_lseek(fd, 422, 0) FROM lotest_stash_values;
68 -- loread/lowrite names are wonky, different from other functions which are lo_*
69 -- loread(fd integer, len integer) returns bytea
70 SELECT loread(fd, 35) FROM lotest_stash_values;
72 SELECT lo_lseek(fd, -19, 1) FROM lotest_stash_values;
74 SELECT lowrite(fd, 'n') FROM lotest_stash_values;
76 SELECT lo_tell(fd) FROM lotest_stash_values;
78 SELECT lo_lseek(fd, -156, 2) FROM lotest_stash_values;
80 SELECT loread(fd, 35) FROM lotest_stash_values;
82 SELECT lo_close(fd) FROM lotest_stash_values;
88 UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
90 SELECT lo_truncate(fd, 10) FROM lotest_stash_values;
91 SELECT loread(fd, 15) FROM lotest_stash_values;
93 SELECT lo_truncate(fd, 10000) FROM lotest_stash_values;
94 SELECT loread(fd, 10) FROM lotest_stash_values;
95 SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
96 SELECT lo_tell(fd) FROM lotest_stash_values;
98 SELECT lo_truncate(fd, 5000) FROM lotest_stash_values;
99 SELECT lo_lseek(fd, 0, 2) FROM lotest_stash_values;
100 SELECT lo_tell(fd) FROM lotest_stash_values;
102 SELECT lo_close(fd) FROM lotest_stash_values;
105 -- lo_unlink(lobjId oid) returns integer
106 -- return value appears to always be 1
107 SELECT lo_unlink(loid) from lotest_stash_values;
109 TRUNCATE lotest_stash_values;
111 INSERT INTO lotest_stash_values (loid) SELECT lo_import('@abs_srcdir@/data/tenk.data');
114 UPDATE lotest_stash_values SET fd=lo_open(loid, CAST(x'20000' | x'40000' AS integer));
116 -- with the default BLKSZ, LOBLKSZ = 2048, so this positions us for a block
118 SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
120 -- this should get half of the value from page 0 and half from page 1 of the
122 SELECT loread(fd, 36) FROM lotest_stash_values;
124 SELECT lo_tell(fd) FROM lotest_stash_values;
126 SELECT lo_lseek(fd, -26, 1) FROM lotest_stash_values;
128 SELECT lowrite(fd, 'abcdefghijklmnop') FROM lotest_stash_values;
130 SELECT lo_lseek(fd, 2030, 0) FROM lotest_stash_values;
132 SELECT loread(fd, 36) FROM lotest_stash_values;
134 SELECT lo_close(fd) FROM lotest_stash_values;
137 SELECT lo_export(loid, '@abs_builddir@/results/lotest.txt') FROM lotest_stash_values;
139 \lo_import 'results/lotest.txt'
141 \set newloid :LASTOID
143 -- just make sure \lo_export does not barf
144 \lo_export :newloid 'results/lotest2.txt'
146 -- This is a hack to test that export/import are reversible
147 -- This uses knowledge about the inner workings of large object mechanism
148 -- which should not be used outside it. This makes it a HACK
149 SELECT pageno, data FROM pg_largeobject WHERE loid = (SELECT loid from lotest_stash_values)
151 SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
154 SELECT lo_unlink(loid) FROM lotest_stash_values;
157 TRUNCATE lotest_stash_values;