X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7EEBAD1B8EB for ; Thu, 24 Jun 2004 00:41:31 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78975-05 for ; Thu, 24 Jun 2004 03:41:30 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B87D4D1B8BB for ; Thu, 24 Jun 2004 00:41:29 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5O3fQLW008398; Wed, 23 Jun 2004 23:41:26 -0400 (EDT) To: "Shea,Dan [CIS]" Cc: pgsql-performance@postgresql.org Subject: Re: after using pg_resetxlog, db lost In-reply-to: <644D07D3D59D8F408CD01AC2F833D8C62B9210@cisxa.cmc.int.ec.gc.ca> References: <644D07D3D59D8F408CD01AC2F833D8C62B9210@cisxa.cmc.int.ec.gc.ca> Comments: In-reply-to "Shea,Dan [CIS]" message dated "Wed, 23 Jun 2004 22:46:49 -0400" Date: Wed, 23 Jun 2004 23:41:26 -0400 Message-ID: <8397.1088048486@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/249 X-Sequence-Number: 7307 "Shea,Dan [CIS]" writes: > Tom I see you from past emails that you reference using -i -f with > pg_filedump. I have tried this, but do not know what I am looking at. What you want to look at is valid XMIN and XMAX values. In this example: > Item 1 -- Length: 196 Offset: 4292 (0x10c4) Flags: USED > XID: min (2) CMIN|XMAX: 211 CMAX|XVAC: 469 > Block Id: 0 linp Index: 1 Attributes: 24 Size: 28 > infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) the infomask shows XMIN_COMMITTED, so xmin (here 2) is valid, but it also shows XMAX_INVALID, so the putative XMAX (211) should be ignored. In general the xmin field should be valid, but xmax shares storage with cmin and so you have to look at the infomask bits to know whether to believe that the cmin/xmax field represents a transaction ID. The cmax/xvac field could also hold a transaction ID. If I had only the above data to go on, I'd guess that the current transaction counter is at least 469. Under normal circumstances, command counter values (cmin or cmax) are unlikely to exceed a few hundred, while the transaction IDs you are looking for are likely to be much larger. So you could get away with just computing the max of *all* the numbers you see in xmin, cmin/xmax, or cmax/cvac, and then using something a million or so bigger for safety factor. regards, tom lane