[HACKERS] xlog viewer proposal

[Diogo Biazus]

I'm developing the summer of code project to create a xlog viewer.
The tool we want to create is a DBA tool used for inspect the xlog files, looking for some operations, statistcs of database usage and status of transactions.

Some use cases:
* Some user made a mistake and commited it to the database. Now the DBA wants to find out the exact xid to restore to a point in time where this mistake was yet to happen.

* A long running transaction changed lots of data and we want to undo that. Show the most recent transactions, with their commit times and total size of WAL for each transaction, so we can see the big transaction's xid.

* How many write transactions per second am I getting?

The idea I've been discussing with Simon Riggs is to create a set of functions that can be called from within the database.
It seems that we would need to extract 2 relations from the log files: transactions and xlog entries.
The functions that would extract the entries could easily look into any xlog segment passed as a parameter and return a relation containing these entries. But the functions needed to extract the transactions would need to look untill the last segment of the xlog to know the status of every transaction.

The function to extract the xlog entries would read the XLogRecord structures (with all data associated), get a TupleDesc with get_call_result_type() and return a tuple for each XLogRecord.

Another problem is how to present the specific data returned in each operation?
We can make functions to present this data in a humam readable format like: get_heap_data(), get_btree_data(), etc.
For example: SELECT get_btree_data(data) FROM xlogviewer_file(xxx) WHERE operation = 'BTREE';

Other problem is how to sparete implict ABORTs from explict ones? It seems that will be necessary to have separate functions for transaction info extraction. As I wrote above, this functions would have to read the xlogs all the way to the present moment to know which transactions are implicitly aborted, and which ones are still active.

This design gives lots of flexibility, we can use all the SQL power to query the xlog files. The one drawback is that you have to use a working backend to inspect the xlog, but in cases where the database cluster is lost you could always use another cluster. It would be easy to create a wrapper program (like createdb and createuser) to connect to a database and return the xlog info.

Other advantage is the possibility of query remote xlogs trought a pg connection, it makes the remote managing easier and machine cluster managing easier to (don't have to make ssh accounts on all nodes or map a remote filesystem).

Oracle has a similar tool called logminer (there goes an article about it http://www.oracle.com/technology/oramag/oracle/05-jul/o45dba.html ). This postgresql xlogviewer would be also good for people migrating from oracle.

Besides, if we create as a separate program this would imply having useful functions related to backend data (the xlogs) not available to other backend modules. It would be easier to create redundant code also. And I've read some emails about having already duplicate code for extracting text out of xlogs (the xlogs debug functions).

Why a contrib module?
Because It sounds safer to me to create a contrib module and it seems that I wont need to change the existing backend code.
So all the code I'm planning to write is new, and I wont need changes in the backend. Another bonus is not to bloat the backend and let this feature to be installed by those who really need it. Afterwards they can be integrated in the backend if needed.

Given this design I would create some functions like (plus the data formating functions):

xlogviewer_file(char *file_path)
Return all entries in a given xlog segment

xlogviewer_file_offset(char *file_path, uint32 offset)
Return all entries in a given xlog segment from an offset

xlogviewer_dir(char *directory_path)
Return all entries in all xlog segments inside a directory

xlogviewer_transactions(char *file_path)
Return all transactions from the directory containing the segment passed as parameter starting from this segment.

One example of it's use:
SELECT * FROM xlogviewer_file('00000001000000000000000F') xlog WHERE xlog.operation = 'BTREE';
The resultset would be something like:
xlog_record  |  previous_xlog_record  |  xid  |  operation  |  data
-------------+------------------------+-------+-------------+-------
 0/00000220  | 0/000001F0             | 4     | BTREE       |


We could also query for a list of committed transactions:
SELECT
    xlog.xid, count(1), sum(xlog.record_size)
FROM
    xlogviewer_file('00000001000000000000000F') xlog
WHERE
    xlog.operation = 'XACT'
    xlog.info = 'COMMIT'
GROUP BY
    xlog.xid



--
Diogo Biazus - [EMAIL PROTECTED]
Móvel Consultoria
http://www.movelinfo.com.br
http://www.postgresql.org.br