Re: View pervious versions of row

a few queries with some info:
# \d test Table "public.test" Column | Type |
Collation | Nullable | Default --------+---------+-----------+---------
-+--------- a | integer | | | b |
text | | |
wim=# table test; a | b ---+--- 3 | 3 | 5 | 4 | t 5 | b(5 rows)
# SELECT * FROM heap_page_items(get_raw_page('test', 0));
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_data -
---+--------+----------+--------+--------+--------+----------+------
--+-------------+------------+--------+--------+-------+---------------
- 1 | 8160 | 1 | 28 | 938 | 0 | 0 |
(0,1) | 1 | 2304 | 24 | | |
\x03000000 2 | 8128 | 1 | 28 | 939 | 0 | 0
| (0,2) | 1 | 2304 | 24 | | |
\x03000000 3 | 8096 | 1 | 28 | 941 | 942 | 0
| (0,3) | 8193 | 256 | 24 | | |
\x04000000 4 | 8064 | 1 | 28 | 941 | 0 | 0
| (0,4) | 1 | 2304 | 24 | | |
\x05000000 5 | 8032 | 1 | 30 | 944 | 0 | 0
| (0,5) | 2 | 2050 | 24 | | |
\x040000000574 6 | 8000 | 1 | 30 | 944 | 0
| 0 | (0,6) | 2 | 2050 | 24
| | | \x050000000562
so in this example 941 | 942 (t_xmin t_xmax) is the dead row, the
data is in the last row
more info about t_data:# SELECT tuple_data_split('test'::regclass,
t_data, t_infomask, t_infomask2, t_bits),t_xmin,t_xmax FROM
heap_page_items(get_raw_page('test', 0)); tuple_data_split |
t_xmin | t_xmax ---------------------------+--------+------
-- {"\\x03000000",NULL} | 938
| 0 {"\\x03000000",NULL} | 939
| 0 {"\\x04000000",NULL} | 941
| 942 {"\\x05000000",NULL} | 941
| 0 {"\\x04000000","\\x0574"} | 944
| 0 {"\\x05000000","\\x0562"} | 944 | 0
# update test set b = 'r' where a=5 and b is null;
# SELECT tuple_data_split('test'::regclass, t_data, t_infomask,
t_infomask2, t_bits),* FROM heap_page_items(get_raw_page('test',
0)); tuple_data_split | 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_data ---------------------------+----+--
------+----------+--------+--------+--------+----------+--------+----
---------+------------+--------+--------+-------+--------------
-- {"\\x03000000",NULL} | 1 | 8160 | 1 | 28 | 938
| 0 | 0 | (0,1) | 1 | 2304 | 24
| | | \x03000000 {"\\x03000000",NULL} | 2 | 8128
| 1 | 28 | 939 | 0 | 0 | (0,2) | 1
| 2304 | 24 | | |
\x03000000 {"\\x04000000",NULL} | 3 | 8096 | 1 | 28
| 941 | 942 | 0 | (0,3) | 8193 | 1280
| 24 | | | \x04000000 {"\\x05000000",NULL} | 4
| 8064 | 1 | 28 | 941 | 945 | 0 |
(0,7) | 16385 | 256 | 24 | | |
\x05000000 {"\\x04000000","\\x0574"} | 5 | 8032 | 1 | 30
| 944 | 0 | 0 | (0,5) | 2 | 2306
| 24 | | | \x040000000574 {"\\x05000000","\\x0562"}
| 6 | 8000 | 1 | 30 | 944 | 0 | 0 |
(0,6) | 2 | 2306 | 24 | | |
\x050000000562 {"\\x05000000","\\x0572"} | 7 | 7968 | 1
| 30 | 945 | 0 | 0 | (0,7) | 32770
| 10242 | 24 | | | \x050000000572
maybe you forget the extension?# create extension pageinspect ;
hth,Wim
Arnav schreef op vr 17-04-2020 om 17:57 [+0530]:
> hi Pgsql-Admin
>
> What is the command to view the previous version of rows (deleted or
> updated)
>
> I searched and found some functions like : . But none of them are
> working
> SELECT * FROM page_header(get_raw_page('school',0));
> SELECT * FROM heap_page_items(get_raw_page('school',0));
> ^
> Sorry if the question sounds stupid
> --
> Regards
> Ankush
>
>
>
>
> Regards
> Arnav

--
mvg,
Wim Bertels
--
Lector
UC Leuven-Limburg
--
All I know is what the words know, and dead things, and that
makes a handsome little sum, with a beginning and a middle and
an end, as in the well-built phrase and the long sonata of the dead.
-- Samuel Beckett