aboutsummaryrefslogtreecommitdiff
path: root/contrib/test_decoding/expected/toast.out
blob: 322afdb4539b19c08bbe02156c35f4277c34ffc5 (plain)
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
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
-- predictability
SET synchronous_commit = on;
DROP TABLE IF EXISTS xpto;
NOTICE:  table "xpto" does not exist, skipping
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
 ?column? 
----------
 init
(1 row)

CREATE SEQUENCE xpto_rand_seq START 79 INCREMENT 1499; -- portable "random"
CREATE TABLE xpto (
    id serial primary key,
    toasted_col1 text,
    rand1 float8 DEFAULT nextval('xpto_rand_seq'),
    toasted_col2 text,
    rand2 float8 DEFAULT nextval('xpto_rand_seq')
);
-- uncompressed external toast data
INSERT INTO xpto (toasted_col1, toasted_col2) SELECT string_agg(g.i::text, ''), string_agg((g.i*2)::text, '') FROM generate_series(1, 2000) g(i);
-- compressed external toast data
INSERT INTO xpto (toasted_col2) SELECT repeat(string_agg(to_char(g.i, 'FM0000'), ''), 50) FROM generate_series(1, 500) g(i);
-- update of existing column
UPDATE xpto SET toasted_col1 = (SELECT string_agg(g.i::text, '') FROM generate_series(1, 2000) g(i)) WHERE id = 1;
UPDATE xpto SET rand1 = 123.456 WHERE id = 1;
DELETE FROM xpto WHERE id = 1;
DROP TABLE IF EXISTS toasted_key;
NOTICE:  table "toasted_key" does not exist, skipping
CREATE TABLE toasted_key (
    id serial,
    toasted_key text PRIMARY KEY,
    toasted_col1 text,
    toasted_col2 text
);
ALTER TABLE toasted_key ALTER COLUMN toasted_key SET STORAGE EXTERNAL;
ALTER TABLE toasted_key ALTER COLUMN toasted_col1 SET STORAGE EXTERNAL;
INSERT INTO toasted_key(toasted_key, toasted_col1) VALUES(repeat('1234567890', 200), repeat('9876543210', 200));
-- test update of a toasted key without changing it
UPDATE toasted_key SET toasted_col2 = toasted_col1;
-- test update of a toasted key, changing it
UPDATE toasted_key SET toasted_key = toasted_key || '1';
DELETE FROM toasted_key;
-- Test that HEAP2_MULTI_INSERT insertions with and without toasted
-- columns are handled correctly
CREATE TABLE toasted_copy (
    id int primary key, -- no default, copy didn't use to handle that with multi inserts
    data text
);
ALTER TABLE toasted_copy ALTER COLUMN data SET STORAGE EXTERNAL;
\copy toasted_copy FROM STDIN
SELECT substr(data, 1, 200) FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0');
                                                                                                  substr                                                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 BEGIN
 COMMIT
 BEGIN
 COMMIT
 BEGIN
 table public.xpto: INSERT: id[integer]:1 toasted_col1[text]:'1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374
 COMMIT
 BEGIN
 table public.xpto: INSERT: id[integer]:2 toasted_col1[text]:null rand1[double precision]:3077 toasted_col2[text]:'00010002000300040005000600070008000900100011001200130014001500160017001800190020002100
 COMMIT
 BEGIN
 table public.xpto: UPDATE: id[integer]:1 toasted_col1[text]:'1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374
 COMMIT
 BEGIN
 table public.xpto: UPDATE: id[integer]:1 toasted_col1[text]:unchanged-toast-datum rand1[double precision]:123.456 toasted_col2[text]:unchanged-toast-datum rand2[double precision]:1578
 COMMIT
 BEGIN
 table public.xpto: DELETE: id[integer]:1
 COMMIT
 BEGIN
 COMMIT
 BEGIN
 COMMIT
 BEGIN
 COMMIT
 BEGIN
 table public.toasted_key: INSERT: id[integer]:1 toasted_key[text]:'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123
 COMMIT
 BEGIN
 table public.toasted_key: UPDATE: id[integer]:1 toasted_key[text]:unchanged-toast-datum toasted_col1[text]:unchanged-toast-datum toasted_col2[text]:'987654321098765432109876543210987654321098765432109
 COMMIT
 BEGIN
 table public.toasted_key: UPDATE: old-key: toasted_key[text]:'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678
 COMMIT
 BEGIN
 table public.toasted_key: DELETE: toasted_key[text]:'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567
 COMMIT
 BEGIN
 COMMIT
 BEGIN
 COMMIT
 BEGIN
 table public.toasted_copy: INSERT: id[integer]:1 data[text]:'untoasted1'
 table public.toasted_copy: INSERT: id[integer]:2 data[text]:'toasted1-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
 table public.toasted_copy: INSERT: id[integer]:3 data[text]:'untoasted2'
 table public.toasted_copy: INSERT: id[integer]:4 data[text]:'toasted2-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
 COMMIT
(47 rows)

SELECT pg_drop_replication_slot('regression_slot');
 pg_drop_replication_slot 
--------------------------
 
(1 row)