CODEWITHSUNDEEP
postgresqlvarchar
scameeer
scameeer

Reputation: 11

How does postgres stores varchar attribute value in heap table as tuple

I having been going through how postgres actually stores data in heap table tuple

I initially created table, and populated sample values. Later tried to print the attributes stored

test=# SELECT * FROM heap_page_item_attrs(get_raw_page('sectable', 0), 'sectable'::regclass);^C
test=# CREATE table exp(id int, value varchar(10));
CREATE TABLE
test=# insert into exp(id, value) values (1, 'cat');
INSERT 0 1
test=# select * from exp;
 id | value 
----+-------
  1 | cat
(1 row)

test=# SELECT * FROM heap_page_item_attrs(get_raw_page('exp', 0), 'exp'::regclass);
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |            t_attrs            
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+-------------------------------
  1 |   8160 |        1 |     32 |    758 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | {"\\x01000000","\\x09636174"}
(1 row)

test=# insert into exp(id, value) values (2, 'x');
INSERT 0 1
test=# SELECT * FROM heap_page_item_attrs(get_raw_page('exp', 0), 'exp'::regclass);
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |            t_attrs            
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+-------------------------------
  1 |   8160 |        1 |     32 |    758 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | {"\\x01000000","\\x09636174"}
  2 |   8128 |        1 |     30 |    759 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | {"\\x02000000","\\x0578"}
(2 rows)

The value cat is stored as: \x09636174 (t_data field) And value 'x' is stored as \x0578

The issue is why there is extra 09 at start for cat value, since 636174 when converted from HEX to TEXT gives CAT Similarly, for 'x' its HEX value is 78, but extra 05 appended at the beginning i am not able to understand.

The value cat stored should \x636174 And value 'x' stored should \x78

I tried searching the postgres code, but couldnt find anything regarding this.

Upvotes: 0

Views: 97

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

The regular way of storing variable-length data types like character varying is with a four byte header that contains the length of the data.

The TOAST mechanism of PostgreSQL shortens that to a single byte for short data, and the extra bit indicates TOAST.

If you are interested in the implementation, look for the keywords "varlena" and "toast" in the source.

Upvotes: 0

Related Questions