Re: Logging the feature of SQL-level read/write commits - Mailing list pgsql-hackers

On Sat, May 04, 2019 at 02:32:06PM +0900, Ronny Ko wrote:
>   Hi,                                                                        
>                                                                              
>   I am trying to efficiently rollback a manually selectedd subset of         
>   committed SQL transactions by scanning an SQL transaction log. This        
>   feature is useful when a database administrator wants to rollback not the  
>   entire database system, but only particular SQL statements that affect a   
>   certain set of SQL tables. Unfortunately, this is impossible in the        
>   current PostgreSQL setup, because PostgreSQL's WAL(Write-Ahead Log) file   
>   doesn't provide any SQL statement-level redo records, but only physical    
>   block-level redo records.                                                  
>                                                                              
>   To this end, my goal is to improve PostgreSQL to produce augmented         
>   transaction logs. In particular, the augmented transaction log's every     
>   committed transaction ID will contain an additional section called         
>   "rollback SQL statements", which is a minimal series of DELETE & INSERT    
>   SQL statements that effectively rolls back one transaction to its          
>   immediately previous transaction. For example, suppose that we have the    
>   following SQL table:                                                       
>                                                                              
>   =================                                                          
>                                                                              
>   Table1                                                                     
>                                                                              
>   column1 | column2                                                          
>                                                                              
>   1             | 20                                                         
>                                                                              
>   2             | 30                                                         
>                                                                              
>   3             | 40                                                         
>                                                                              
>   3             | 40                                                         
>                                                                              
>   4             | 50                                                         
>                                                                              
>   =================                                                          
>                                                                              
>   And suppose that the following 100th transaction was committed:            
>                                                                              
>   UPDATE Table1                                                              
>   SET column1 = 10, column2 = 20                                             
>   WHERE colum2 > 20 ;                                                        
>                                                                              
>   Then, the augmented transaction log file will generate the following log   
>   entry for the above committed transaction:                                 
>                                                                              
>   Committed Transaction ID = 100                                             
>                                                                              
>   Rollback SQL Statements =                                                  
>                                                                              
>   -   DELETE FROM Table1 WHERE column1 = 2 AND column2 = 30                  
>                                                                              
>   -   INSERT INTO TABLE Table1 VALUES(column1, column2) (10, 20)             
>                                                                              
>   -   DELETE FROM Table1 WHERE column1 = 3 AND column2 = 40                  
>                                                                              
>   -   INSERT INTO TABLE Table1 VALUES(column1, column2) (10, 20)             
>                                                                              
>   -   DELETE FROM Table1 WHERE column1 = 4 AND column2 = 50                  
>                                                                              
>   -   INSERT INTO TABLE Table1 VALUES(column1, column2) (10, 20)             
>                                                                              
>   Note that the above Rollback SQL statements are in the simplest forms      
>   without involving any complex SQL operations such as JOIN or sub-queries.  
>   Also note that we cannot create the above Rollback SQL statements purely   
>   based on original consecutive SQL transactions, because we don't know      
>   which rows of Table1 will need to be DELETED without actually scanning the 
>   entire Table1 and evaluating Transction #100's WHERE clause (i.e., colum2  
>   > 20) on every single row of Table1. Therefore, to generate a list of      
>   simple Rollback SQL statements like the above, we have no choice but to    
>   embed this logging feature in the PostgreSQL's source code where the       
>   WAL(Write-Ahead Log) file is being updated.                                
>                                                                              
>                                                                              
>                                                                              
>   Since the current PostgreSQL doesn't support this feature, I plan to       
>   implement the above feature in the source code. But I have never worked on 
>   PostgreSQL source code in my  life, and I wonder if anybody could give me  
>   a hint on which source code files (and functions) are about recording redo 
>   records in the WAL file. In particular, when the SQL server records the    
>   information of updated block location & values into the WAL file for each  
>   SQL statement that modifies any relations, we can additionally make the    
>   SQL server also write the list of the simplest INSERT & DELETE SQL         
>   statements that effectively enforces such SQL table write operations. If   
>   such an SQL-level inforcement information is available in the WAL file,    
>   one can easily conjecture what will be the corresponding Rollback (i.e.,   
>   inverse) SQL statements from there.                                        
>                                                                              

You probably need to look at ./src/backend/access/transam/, particularly
xlog.c and xact.c. That being said, this seems like a rather difficult
task for someone who never worked with PostgreSQL source code.

What's worse, I have serious doubts it's possible to implement the
feature you proposed - for a number of reasons. Firstly, WAL is a redo
log, so it seems like a rather poor fit for what is essentially an undo.
Secondly, those "undo" records may require quite a bit of space, and I
wonder if generating all of that at commit time may cause issues e.g.
for very large transactions (it'll certainly add significant overhead to
all transactions, because you don't know what might be rolled back
later). And if it's in WAL, it's subject to WAL rotation, limits, etc.
So if the admin does not initiate the rollback within three checkpoints,
it's pretty much game over because the WAL may be already over.

But the most serious prboblem is that this assumes the database knows
how to generate the "undo commands". And it can't, because it's very
application specific.

For example if you have two transactions, updating the same row:

  T1: UPDATE t SET v = 100 WHERE id = 1;
  T1: COMMIT

  T2: UPDATE t SET v = 200 WHERE id = 1;
  T2: COMMIT

and then you decide to "rollback" T1, how do you do that? That depends
on whether T2 just written entirely new value into column "v" (ignoring
the original value written by T1) or whether it for example incremented
the original value. In the first case the "undo" is "do nothing", in the
second case it's "UPDATE t SET v = v - 100 WHERE id = 1". Or perhaps
something even more complex.

And no, I don't think the database can deduce this - those operations
often happen in the application code, outside the database. This is why
applications (e.g. banking systems) implement this functionality as
pretty much new transaction, doing application-specific things.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services