Logging the feature of SQL-level read/write commits

[Ronny Ko]

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. Thanks for anybody's comments.  Ronny