Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE EXTENSION lo;
CREATE TABLE image (title text, raster lo);
CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);
SELECT lo_create(43213);
lo_create
-----------
43213
(1 row)
SELECT lo_create(43214);
lo_create
-----------
43214
(1 row)
INSERT INTO image (title, raster) VALUES ('beautiful image', 43213);
SELECT lo_get(43213);
lo_get
--------
\x
(1 row)
SELECT lo_get(43214);
lo_get
--------
\x
(1 row)
UPDATE image SET raster = 43214 WHERE title = 'beautiful image';
SELECT lo_get(43213);
ERROR: large object 43213 does not exist
SELECT lo_get(43214);
lo_get
--------
\x
(1 row)
-- test updating of unrelated column
UPDATE image SET title = 'beautiful picture' WHERE title = 'beautiful image';
SELECT lo_get(43214);
lo_get
--------
\x
(1 row)
DELETE FROM image;
SELECT lo_get(43214);
ERROR: large object 43214 does not exist
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
-- Now let's try it with an AFTER trigger
DROP TRIGGER t_raster ON image;
CREATE CONSTRAINT TRIGGER t_raster AFTER UPDATE OR DELETE ON image
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);
SELECT lo_create(43223);
lo_create
-----------
43223
(1 row)
SELECT lo_create(43224);
lo_create
-----------
43224
(1 row)
SELECT lo_create(43225);
lo_create
-----------
43225
(1 row)
INSERT INTO image (title, raster) VALUES ('beautiful image', 43223);
SELECT lo_get(43223);
lo_get
--------
\x
(1 row)
UPDATE image SET raster = 43224 WHERE title = 'beautiful image';
SELECT lo_get(43223); -- gone
ERROR: large object 43223 does not exist
SELECT lo_get(43224);
lo_get
--------
\x
(1 row)
-- test updating of unrelated column
UPDATE image SET title = 'beautiful picture' WHERE title = 'beautiful image';
SELECT lo_get(43224);
lo_get
--------
\x
(1 row)
-- this case used to be buggy
BEGIN;
UPDATE image SET title = 'beautiful image' WHERE title = 'beautiful picture';
UPDATE image SET raster = 43225 WHERE title = 'beautiful image';
SELECT lo_get(43224);
lo_get
--------
\x
(1 row)
COMMIT;
SELECT lo_get(43224); -- gone
ERROR: large object 43224 does not exist
SELECT lo_get(43225);
lo_get
--------
\x
(1 row)
DELETE FROM image;
SELECT lo_get(43225); -- gone
ERROR: large object 43225 does not exist