Skip to content

Commit f6ad1f4

Browse files
author
Nikita Glukhov
committed
Add contrib/jsonb_toaster
1 parent 95870c0 commit f6ad1f4

File tree

7 files changed

+2851
-30
lines changed

7 files changed

+2851
-30
lines changed

‎contrib/jsonb_toaster/Makefile

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
# contrib/jsonb_toaster/Makefile
2+
3+
MODULE_big = jsonb_toaster
4+
OBJS = \
5+
$(WIN32RES) \
6+
jsonb_toaster.o
7+
8+
EXTENSION = jsonb_toaster
9+
DATA = jsonb_toaster--1.0.sql
10+
PGFILEDESC = "jsonb_toaster - updatable jsonb toaster"
11+
12+
REGRESS = jsonb_toaster
13+
14+
ifdef USE_PGXS
15+
PG_CONFIG = pg_config
16+
PGXS := $(shell $(PG_CONFIG) --pgxs)
17+
include $(PGXS)
18+
else
19+
subdir = contrib/jsonb_toaster
20+
top_builddir = ../..
21+
include $(top_builddir)/src/Makefile.global
22+
include $(top_srcdir)/contrib/contrib-global.mk
23+
endif

‎contrib/jsonb_toaster/expected/jsonb_toaster.out

Lines changed: 219 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,219 @@
1+
CREATE EXTENSION bytea_toaster;
2+
CREATE TABLE tst_failed (
3+
t jsonb TOASTER bytea_toaster
4+
);
5+
CREATE TABLE tst1 (
6+
t bytea TOASTER bytea_toaster
7+
);
8+
CREATE TABLE tst2 (
9+
t bytea
10+
);
11+
ALTER TABLE tst2 ALTER COLUMN t SET TOASTER bytea_toaster;
12+
CREATE TABLE test_bytea_append (id int, a bytea STORAGE EXTERNAL);
13+
ALTER TABLE test_bytea_append ALTER a SET TOASTER bytea_toaster;
14+
INSERT INTO test_bytea_append SELECT i, repeat('a', 10000)::bytea FROM generate_series(1, 10) i;
15+
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
16+
SAVEPOINT p1;
17+
UPDATE test_bytea_append SET a = a || repeat('b', 3000)::bytea;
18+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 12990, 20), 'UTF8') FROM test_bytea_append;
19+
id | convert_from
20+
----+---------------------------------
21+
1 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
22+
2 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
23+
3 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
24+
4 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
25+
5 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
26+
6 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
27+
7 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
28+
8 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
29+
9 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
30+
10 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
31+
(10 rows)
32+
33+
SAVEPOINT p2;
34+
UPDATE test_bytea_append SET a = a || repeat('c', 2000)::bytea;
35+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 12990, 20) || substr(a, 14990, 20), 'UTF8') FROM test_bytea_append;
36+
id | convert_from
37+
----+-----------------------------------------------------
38+
1 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
39+
2 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
40+
3 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
41+
4 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
42+
5 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
43+
6 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
44+
7 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
45+
8 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
46+
9 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
47+
10 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
48+
(10 rows)
49+
50+
ROLLBACK TO SAVEPOINT p2;
51+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 12990, 20), 'UTF8') FROM test_bytea_append;
52+
id | convert_from
53+
----+---------------------------------
54+
1 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
55+
2 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
56+
3 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
57+
4 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
58+
5 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
59+
6 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
60+
7 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
61+
8 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
62+
9 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
63+
10 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
64+
(10 rows)
65+
66+
UPDATE test_bytea_append SET a = a || repeat('d', 4000)::bytea;
67+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 12990, 20) || substr(a, 16990, 20), 'UTF8') FROM test_bytea_append;
68+
id | convert_from
69+
----+-----------------------------------------------------
70+
1 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
71+
2 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
72+
3 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
73+
4 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
74+
5 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
75+
6 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
76+
7 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
77+
8 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
78+
9 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
79+
10 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbdddddddddddddddddddd
80+
(10 rows)
81+
82+
ROLLBACK TO SAVEPOINT p1;
83+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 12990, 20), 'UTF8') FROM test_bytea_append;
84+
id | convert_from
85+
----+--------------
86+
1 | aaaaaaaaaaa
87+
2 | aaaaaaaaaaa
88+
3 | aaaaaaaaaaa
89+
4 | aaaaaaaaaaa
90+
5 | aaaaaaaaaaa
91+
6 | aaaaaaaaaaa
92+
7 | aaaaaaaaaaa
93+
8 | aaaaaaaaaaa
94+
9 | aaaaaaaaaaa
95+
10 | aaaaaaaaaaa
96+
(10 rows)
97+
98+
UPDATE test_bytea_append SET a = a || repeat('e', 5000)::bytea;
99+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 14990, 20), 'UTF8') FROM test_bytea_append;
100+
id | convert_from
101+
----+---------------------------------
102+
1 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
103+
2 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
104+
3 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
105+
4 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
106+
5 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
107+
6 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
108+
7 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
109+
8 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
110+
9 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
111+
10 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
112+
(10 rows)
113+
114+
COMMIT;
115+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 14990, 20), 'UTF8') FROM test_bytea_append;
116+
id | convert_from
117+
----+---------------------------------
118+
1 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
119+
2 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
120+
3 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
121+
4 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
122+
5 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
123+
6 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
124+
7 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
125+
8 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
126+
9 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
127+
10 | aaaaaaaaaaaeeeeeeeeeeeeeeeeeeee
128+
(10 rows)
129+
130+
TRUNCATE test_bytea_append;
131+
INSERT INTO test_bytea_append SELECT i, repeat('a', 10000)::bytea FROM generate_series(1, 10) i;
132+
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
133+
UPDATE test_bytea_append SET a = a || repeat('b', 3000)::bytea;
134+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 12990, 20), 'UTF8') FROM test_bytea_append;
135+
id | convert_from
136+
----+---------------------------------
137+
1 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
138+
2 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
139+
3 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
140+
4 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
141+
5 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
142+
6 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
143+
7 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
144+
8 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
145+
9 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
146+
10 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
147+
(10 rows)
148+
149+
UPDATE test_bytea_append SET a = a || repeat('c', 2000)::bytea;
150+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 12990, 20) || substr(a, 14990, 20), 'UTF8') FROM test_bytea_append;
151+
id | convert_from
152+
----+-----------------------------------------------------
153+
1 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
154+
2 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
155+
3 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
156+
4 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
157+
5 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
158+
6 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
159+
7 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
160+
8 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
161+
9 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
162+
10 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
163+
(10 rows)
164+
165+
UPDATE test_bytea_append SET a = a || repeat('d', 4000)::bytea;
166+
SELECT id, convert_from(substr(a, 9990, 20) || substr(a, 12990, 20) || substr(a, 14990, 20) || substr(a, 18990, 20), 'UTF8') FROM test_bytea_append;
167+
id | convert_from
168+
----+-------------------------------------------------------------------------
169+
1 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
170+
2 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
171+
3 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
172+
4 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
173+
5 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
174+
6 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
175+
7 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
176+
8 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
177+
9 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
178+
10 | aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
179+
(10 rows)
180+
181+
CREATE FUNCTION test_bytea_append_func() RETURNS void AS
182+
$$
183+
DECLARE
184+
a0 bytea;
185+
a1 bytea;
186+
a2 bytea;
187+
a3 bytea;
188+
BEGIN
189+
TRUNCATE test_bytea_append;
190+
INSERT INTO test_bytea_append SELECT i, repeat('a', 10000)::bytea FROM generate_series(1, 10) i;
191+
SELECT a INTO a0 FROM test_bytea_append LIMIT 1;
192+
193+
UPDATE test_bytea_append SET a = a || repeat('b', 3000)::bytea;
194+
SELECT a INTO a1 FROM test_bytea_append LIMIT 1;
195+
196+
UPDATE test_bytea_append SET a = a || repeat('c', 2000)::bytea;
197+
SELECT a INTO a2 FROM test_bytea_append LIMIT 1;
198+
199+
UPDATE test_bytea_append SET a = a || repeat('d', 4000)::bytea;
200+
SELECT a INTO a3 FROM test_bytea_append LIMIT 1;
201+
202+
RAISE NOTICE '%', convert_from(substr(a0, 9990, 20), 'UTF8');
203+
RAISE NOTICE '%', convert_from(substr(a1, 9990, 20) || substr(a1, 12990, 20), 'UTF8');
204+
RAISE NOTICE '%', convert_from(substr(a2, 9990, 20) || substr(a2, 12990, 20) || substr(a2, 14990, 20), 'UTF8');
205+
RAISE NOTICE '%', convert_from(substr(a3, 9990, 20) || substr(a3, 12990, 20) || substr(a3, 14990, 20) || substr(a3, 18990, 20), 'UTF8');
206+
END;
207+
$$ LANGUAGE plpgsql;
208+
SELECT test_bytea_append_func();
209+
NOTICE: aaaaaaaaaaa
210+
NOTICE: aaaaaaaaaaabbbbbbbbbbbbbbbbbbbb
211+
NOTICE: aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbcccccccccccccccccccc
212+
NOTICE: aaaaaaaaaaabbbbbbbbbbbbbbbbbbbbccccccccccccccccccccdddddddddddddddddddd
213+
test_bytea_append_func
214+
------------------------
215+
216+
(1 row)
217+
218+
COMMIT;
219+
DROP TABLE test_bytea_append;

‎contrib/jsonb_toaster/jsonb_toaster--1.0.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
/* contrib/jsonb_toaster/jsonb_toaster--1.0.sql */
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use "CREATE EXTENSION jsonb_toaster" to load this file. \quit
5+
6+
CREATE FUNCTION jsonb_toaster_handler(internal)
7+
RETURNS toaster_handler
8+
AS 'MODULE_PATHNAME'
9+
LANGUAGE C;
10+
11+
CREATE TOASTER jsonb_toaster HANDLER jsonb_toaster_handler;
12+
13+
COMMENT ON TOASTER jsonb_toaster IS 'jsonb_toaster is a updatable jsonb toaster';
14+

0 commit comments

Comments
 (0)