Thread: Re: Implementation of Flashback Query

font{ line-height: 1.6; } ul,ol{ padding-left: 20px; list-style-position: inside; }

font{ line-height: 1.6; } ul,ol{ padding-left: 20px; list-style-position: inside; }

Hello,

We know that in the absence of backups recovery can be cumbersome if the data is erroneously deleted or modified and the transaction is committed. For this reason, we realize the flashback query function based on PostgreSQL10.5, query the misoperated data, at the same time, we also want to know the ideas and suggestions of the flashback feature, hope that the postgres function is more powerful.

Take a look at the following example:

# create table test(id int, tx text, ts timestamp);

# insert into test values (1, 'tx1', now());

# update test set tx = 'tx2', ts = now();

# update test set tx = 'tx3', ts = now();

# update test set tx = 'tx4', ts = now();

# SELECT lp, t_ctid AS ctid, t_xmin AS xmin, t_xmax AS xmax,

(t_infomask & 128)::boolean AS xmax_is_lock,

(t_infomask & 1024)::boolean AS xmax_committed

FROM heap_page_item_attrs(get_raw_page('test', 0), 'test');

lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed

----+-------+------+------+--------------+----------------

1 | (0,2) | 6773 | 6774 | f | t

2 | (0,3) | 6774 | 6775 | f | t

3 | (0,4) | 6775 | 6776 | f | f

4 | (0,4) | 6776 | 0 | f | f

(4 rows)

# select xmin,xmax,* from test flashback between xid 6773 and 6776;

xmin | xmax | id | tx | ts

------+------+----+-----+----------------------------

6773 | 6774 | 1 | tx1 | 2018-10-16 15:43:17.288023

6774 | 6775 | 1 | tx2 | 2018-10-16 15:43:40.528367

6775 | 6776 | 1 | tx3 | 2018-10-16 15:43:46.943483

6776 | 0 | 1 | tx4 | 2018-10-16 15:44:23.295938

(4 rows)

# select xmin,xmax,* from test flashback timestamp '2018-10-16 15:43:18';

xmin | xmax | id | tx | ts

------+------+----+-----+----------------------------

6773 | 6774 | 1 | tx1 | 2018-10-16 15:43:17.288023

(1 row)

# select xmin,xmax,* from test flashback xid 6773;

xmin | xmax | id | tx | ts

------+------+----+-----+----------------------------

6773 | 6774 | 1 | tx1 | 2018-10-16 15:43:17.288023

(1 row)

# select xmin,xmax,* from test flashback between timestamp '2018-10-16 15:43:45' and '2018-10-16 15:43:50';

xmin | xmax | id | tx | ts

------+------+----+-----+----------------------------

6774 | 6775 | 1 | tx2 | 2018-10-16 15:43:40.528367

6775 | 6776 | 1 | tx3 | 2018-10-16 15:43:46.943483

(2 rows)

To achieve: 

MVCC retains an older version of the data item (dead tuple). 

Deferred VACUUM, ensures that misoperated data has not been garbage collected. 

vacuum_defer_cleanup_age = 1000000. 

Turn on transaction commit time tracking to ensure that transaction commit time is available from xid. 

track_commit_timestamp = on. 

Add the syntax for flashback queries and flashback version queries. 

SELECT column_name FROM table_name. 

FLASHBACK TIMESTAMP/XID expression. 

WHERE condition

SELECT column_name FROM table_name. 

FLASHBACK BETWEEN TIMESTAMP/XID expression AND expression. 

WHERE condition

When a flashback query reads data, the appropriate data item is selected through the visibility rule.

Performance: 

The above realizes the function of flashback query, but there is an inevitable problem, after setting the vacuum_defer_cleanup_age parameter, the, dead tuple accumulates too much, which leads to the bloat problem and obvious performance degradation.

I have the following questions.

Delayed cleanup, resulting in performance degradation, what are the solutions recommended?

What do you suggest for the flashback feature?

Although postgres has excellent backup and restore capabilities, have you considered adding flashbacks?

Looking forward to your suggestions and improvements

---

font{ line-height: 1.6; } Yang Jie

Highgo Software 

http://www.highgo.com/

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company