Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 1990D9FB7A3; Wed, 23 May 2007 12:48:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 70681-01; Wed, 23 May 2007 12:48:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id A410F9FB565; Wed, 23 May 2007 12:48:45 -0300 (ADT) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 06E7F76E0E; Wed, 23 May 2007 16:47:41 +0100 (BST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id DFA00274116; Wed, 23 May 2007 16:47:40 +0100 (BST) Message-ID: <4654621C.8010700@archonet.com> Date: Wed, 23 May 2007 16:47:40 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.0 (X11/20070326) MIME-Version: 1.0 To: Purusothaman A Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [GENERAL] OIDs - file objects, are damaged by PostgreSQL. References: <3650d0d50705152242y689c0ef8u56223ec15661c821@mail.gmail.com> <464AD62B.9000003@archonet.com> <3650d0d50705230134n15b993aat8bbc631836eef65e@mail.gmail.com> <46540766.2030108@archonet.com> <3650d0d50705230756g5fdf8bdava3de463eb6f82ad9@mail.gmail.com> In-Reply-To: <3650d0d50705230756g5fdf8bdava3de463eb6f82ad9@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/252 X-Sequence-Number: 25572 Purusothaman A wrote: > Dear Richard Huxton, > > Thanks for your quick reply. > > only the first 3 values(HX, MASK, Rockey4ND) are file object's oid value. > the other two are are not oid values. Umm - OK. Can I suggest perhaps having different tables for different types of data? > I have shown original output values displayed by postgresql client. > > I can explain more. > > 1. HX is a XML file. after downloading that file I opened that file in word > pad application. > In that I have noticed that nearly 20 characters of last line lost. > 2. Rockey4ND is a dll file. I was unable to use that dll in my application. > > In both cases, I checked file size. Corrupted files are smaller when > compare > to the original one what I uploaded to postgresql. OK, so the data seems OK up until that point? > Usually this problem arises only after the database become large. I can't think of any reason why that would make a difference. But, this does give us a clue. If you have successfully downloaded these files before, that rules out certain forms of failure. > Any suggestion to rectify this problem would be nice of you. Reading through recent release notes, I can't see anything mentioning lo_import/export, large objects or similar. http://www.postgresql.org/docs/8.2/static/release-8-2-4.html Well, your data is stored in pg_largeobject. If you run a SELECT you can see how it's broken into chunks. SELECT loid,pageno,length(data) FROM pg_largeobject ; On my system, a full chunk is 2048 bytes long. What does the last chunk of your HX object (101800) look like? Is it a full chunk? Does it end where your downloaded file ends? If the data is OK in pg_largeobject then we know we have a problem with lo_export-ing or saving to a file. If not, then we know we have a problem with something deleting or overwriting chunks in pg_largeobject. That would surprise me, because I don't think there's anything special about pg_largeobject - it's just a table with chunks of bytea data in it. Just to recap - you're using lo_import() and lo_export from C (or at least via libpq) to read/write these files directly to your filesystem. You've not been seeing crashes and you don't think you've got hardware problems. -- Richard Huxton Archonet Ltd