Re: View pervious versions of row

Поиск
Список
Период
Сортировка
От Wim Bertels
Тема Re: View pervious versions of row
Дата
Msg-id 904691b6c7df23ffb4439572ed2b8ba99185877f.camel@ucll.be
обсуждение исходный текст
Ответ на View pervious versions of row  (Arnav <justdba03@gmail.com>)
Список pgsql-admin
Дерево обсуждения
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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Laurenz Albe
Дата:
Сообщение: Re: checkpoint process use too much memory
Следующее
От: Prashant Kulkarni
Дата:
Сообщение: Exporting TBs of data in Oracle schema/database into CSV format tomigrate into PostgreSQL