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