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
|
-- PG14+ output in toast.out/_3.out (32-bit); PG13- output in toast_1.out/_4.out
create table toast (
description text,
data text
);
insert into toast values ('short inline', 'xxx');
insert into toast values ('long inline uncompressed', repeat('x', 200));
alter table toast alter column data set storage external;
insert into toast values ('external uncompressed', repeat('0123456789 8< ', 200));
alter table toast alter column data set storage extended;
insert into toast values ('inline compressed pglz', repeat('0123456789 8< ', 200));
insert into toast values ('extended compressed pglz', repeat('0123456789 8< ', 20000));
alter table toast alter column data set compression lz4;
ERROR: syntax error at or near "compression"
LINE 1: alter table toast alter column data set compression lz4;
^
insert into toast values ('inline compressed lz4', repeat('0123456789 8< ', 200));
insert into toast values ('extended compressed lz4', repeat('0123456789 8< ', 50000));
vacuum toast;
checkpoint;
-- copy tables where client can read it
\set relname 'toast'
select oid as datoid from pg_database where datname = current_database() \gset
select relfilenode, reltoastrelid from pg_class where relname = :'relname' \gset
select lo_import(format('base/%s/%s', :'datoid', :'relfilenode')) as loid \gset
\set output :relname '.heap'
\lo_export :loid :output
select lo_import(format('base/%s/%s', :'datoid', :'reltoastrelid')) as toast_loid \gset
\set output :reltoastrelid
\lo_export :toast_loid :output
\setenv relname :relname
\! pg_filedump -D text,text $relname.heap | ./sed.sh
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: toast.heap
* Options used: -D text,text
*******************************************************************
Block 0 ********************************************************
<Header> -----
Block Offset: 0x00000000 Offsets: Lower 52 (0x0034)
Block: Size 8192 Version 4 Upper 7476 (0x1d34)
LSN: logid ...... recoff 0x........ Special 8192 (0x2000)
Items: 7 Free Space: 7424
Checksum: 0x.... Prune XID: 0x00000000 Flags: 0x0004 (ALL_VISIBLE)
Length (including item array): 52
<Data> -----
Item 1 -- Length: 41 Offset: 8148 (0x1fd4) Flags: NORMAL
COPY: short inline xxx
Item 2 -- Length: 256 Offset: 7892 (0x1ed4) Flags: NORMAL
COPY: long inline uncompressed xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Item 3 -- Length: 64 Offset: 7828 (0x1e94) Flags: NORMAL
COPY: external uncompressed (TOASTED,uncompressed)
Item 4 -- Length: 107 Offset: 7720 (0x1e28) Flags: NORMAL
COPY: inline compressed pglz 0123456789 8< [snipped]
Item 5 -- Length: 67 Offset: 7652 (0x1de4) Flags: NORMAL
COPY: extended compressed pglz (TOASTED,pglz)
Item 6 -- Length: 107 Offset: 7544 (0x1d78) Flags: NORMAL
COPY: inline compressed lz4 0123456789 8< [snipped]
Item 7 -- Length: 66 Offset: 7476 (0x1d34) Flags: NORMAL
COPY: extended compressed lz4 (TOASTED,pglz)
*** End of File Encountered. Last Block Read: 0 ***
\! pg_filedump -D text,text -t $relname.heap | ./sed.sh
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: toast.heap
* Options used: -D text,text -t
*******************************************************************
Block 0 ********************************************************
<Header> -----
Block Offset: 0x00000000 Offsets: Lower 52 (0x0034)
Block: Size 8192 Version 4 Upper 7476 (0x1d34)
LSN: logid ...... recoff 0x........ Special 8192 (0x2000)
Items: 7 Free Space: 7424
Checksum: 0x.... Prune XID: 0x00000000 Flags: 0x0004 (ALL_VISIBLE)
Length (including item array): 52
<Data> -----
Item 1 -- Length: 41 Offset: 8148 (0x1fd4) Flags: NORMAL
COPY: short inline xxx
Item 2 -- Length: 256 Offset: 7892 (0x1ed4) Flags: NORMAL
COPY: long inline uncompressed xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Item 3 -- Length: 64 Offset: 7828 (0x1e94) Flags: NORMAL
TOAST value. Raw size: 2804, external size: 2800, value id: ......, toast relation id: ......, chunks: 2
COPY: external uncompressed 0123456789 8< [snipped]
Item 4 -- Length: 107 Offset: 7720 (0x1e28) Flags: NORMAL
COPY: inline compressed pglz 0123456789 8< [snipped]
Item 5 -- Length: 67 Offset: 7652 (0x1de4) Flags: NORMAL
TOAST value. Raw size: 280004, external size: 3226, value id: ......, toast relation id: ......, chunks: 2
COPY: extended compressed pglz 0123456789 8< [snipped]
Item 6 -- Length: 107 Offset: 7544 (0x1d78) Flags: NORMAL
COPY: inline compressed lz4 0123456789 8< [snipped]
Item 7 -- Length: 66 Offset: 7476 (0x1d34) Flags: NORMAL
TOAST value. Raw size: 700004, external size: 8035, value id: ......, toast relation id: ......, chunks: 5
COPY: extended compressed lz4 0123456789 8< [snipped]
*** End of File Encountered. Last Block Read: 0 ***
|
