Thread: xlogdump enhancements
I'm working on Tom's xlogdump tool to add some functionality.<br />IMHO some useful improvements would be and an idea ofimplementation:<br /><br /><br />- have an options to output only the transactions with their status and some aggregatedata (transaction size). <br /><br />When the user pass a -t parameter, instead of printing echa record info theprogram uses a linked list to acumulate the total length and status info about each xid. The status begin with NOT COMMITED,it can change to COMMITED or ABORTED. If the user pass all the files in the pg_xlog directory as parameters he knowsthe current status of each transaction. <br /><br />- Find loser transactions (not commited to the end of the log)<br/><br />I though of doing this for the only transactions option (see above).<br /><br />- Have a filter to get a specifidrmid<br /><br />There's always grep, but for windows users it might be interesting to filter records by rmid. Therecan be a flag like -r (from rmname), or -o (operation) where the user can pass a parameter like: xact, heap, etc...<br />And then filter the output records.<br /><br />- Option to translate OIDs to names given a database connection<br/><br />I receive parameters to open a connection (the usual -h, -p and -U). In the startup I try to open aconnection. If the user passes the parameters of the connection the application automaticaly enter in the translate oidsmode. Then each data returning function tries to get from the connected database the objects name. <br /><br />- Extractthe exact SQL statement in cases of xlog generated by insert/update/delete. <br clear="all" /><br />This is the onewhere 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'sstructure from the system catalog to decode the statement. So this option will be available only if you have a connectionto the database. Is this right? <br /><br />I'd like to have opinions on the subject. If somebody could give mesome advice on how to start the last feature (decode the statemets) I'd apreciate very much.<br /><br />-- <br />DiogoBiazus - <a href="mailto:diogob@gmail.com" target="_blank" title="mailto:diogob@gmail.com (mailto:diogob@gmail.com)(mailto:diogob@gmail.com) (mailto:diogob@gmail.com) (mailto:diogob@gmail.com)"> diogob@gmail.com</a><br/>Móvel Consultoria <br /><a href="http://www.movelinfo.com.br" target="_blank" title="http://www.movelinfo.com.br(http://www.movelinfo.com.br) (http://www.movelinfo.com.br) (http://www.movelinfo.com.br)(http://www.movelinfo.com.br)"> http://www.movelinfo.com.br</a><br /><a href="http://www.postgresql.org.br"target="_blank" title="http://www.postgresql.org.br (http://www.postgresql.org.br) (http://www.postgresql.org.br)(http://www.postgresql.org.br) (http://www.postgresql.org.br)"> http://www.postgresql.org.br</a>
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.
On 7/14/06, Martijn van Oosterhout <kleptog@svana.org> wrote: > 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. Yep Diogo, Martijn is correct. You have to reassemble the tuple and then generate the appropriate SQL statement for it. For this, you'll definitely need the catalog information to resolve relations, type handling, etc. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On 7/14/06, Jonah H. Harris <jonah.harris@gmail.com> wrote:
Thanks for the info, I'm taking a look in that program. Is good to have a starting point :-)On 7/14/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
> 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.
Yep Diogo, Martijn is correct. You have to reassemble the tuple and
then generate the appropriate SQL statement for it. For this, you'll
definitely need the catalog information to resolve relations, type
handling, etc.
--
Diogo Biazus - diogob@gmail.com
Móvel Consultoria
http://www.movelinfo.com.br
http://www.postgresql.org.br