On Fri, Jul 14, 2006 at 10:43:15AM -0300, Diogo Biazus wrote:
> I'm working on Tom's xlogdump tool to add some functionality.
> IMHO some useful improvements would be and an idea of implementation:
<snip>
Neato. Looks like good stuff there.
> - Extract the exact SQL statement in cases of xlog generated by
> insert/update/delete.
>
> This is the one where I need help from you the most. I'm not certain of how
> to implement. But as I can see, I'll need to get object's structure from the
> system catalog to decode the statement. So this option will be available
> only if you have a connection to the database. Is this right?
Well, obvously you can only create dummy statements that acheives the
same effect, you can't get exactly the statements executed. That's
still useful though.
Decoding tuples is tricky, if done externally. If you're inside the
backend you could use the functions there. What you need to do is build
a table of all the columns and their types. For each type you need to
get the info needed to decode it. Finally, you extract the data and
convert it to a readable form.
If you really want to tackle this the hard way, find some other program
that does it. Here one written in Perl that can decode most tuples, but
not all. It fails because it doesn't recognise all the types.
http://svana.org/kleptog/pgsql/pgfsck.html
Have a ncie day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.