Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id C14599FB40C for ; Thu, 24 May 2007 02:54:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 29867-06 for ; Thu, 24 May 2007 02:54:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.239]) by postgresql.org (Postfix) with ESMTP id 6827A9FB3A5 for ; Thu, 24 May 2007 02:54:07 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id s16so18902wxc for ; Wed, 23 May 2007 22:54:06 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ADoXlI0wiq3ySQI2m89x3xBf/9CRjsQivkT1H61ei7lgDlVxCVQAb8yZBIiDh9DMxJkDjbFHF2pVEstzm0jng35hlkRhUYUyTeIebIyjQzldEscodImIBXS+hv0tlPDTnBvB+hdQu+o+bCnLUcPs7BDeRItDVm1w5/4c2vsSxH4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ONfbOmwgKH39xlsk6LNTdVcVbEqXnH+Z6f/aDkeQRFVAIbwOPXNmT3hsO7ACVOF9aYaXmQXHQn6rBrmUsyqTBWblmjEIbn+Upnl/GIZP3QuqvHCu+s4+76Uw0IjgaKZ+5PDUGZFTiRbZj5h88voi+hSLEZGV+AkKRGa6NqF6iek= Received: by 10.90.51.17 with SMTP id y17mr1485877agy.1179986045953; Wed, 23 May 2007 22:54:05 -0700 (PDT) Received: by 10.90.86.9 with HTTP; Wed, 23 May 2007 22:54:05 -0700 (PDT) Message-ID: <3650d0d50705232254w265b94b0yaaf5163b92567403@mail.gmail.com> Date: Thu, 24 May 2007 11:24:05 +0530 From: "Purusothaman A" To: "Richard Huxton" Subject: Re: [GENERAL] OIDs - file objects, are damaged by PostgreSQL. Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <4654621C.8010700@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_74837_15945207.1179986045913" References: <3650d0d50705152242y689c0ef8u56223ec15661c821@mail.gmail.com> <464AD62B.9000003@archonet.com> <3650d0d50705230134n15b993aat8bbc631836eef65e@mail.gmail.com> <46540766.2030108@archonet.com> <3650d0d50705230756g5fdf8bdava3de463eb6f82ad9@mail.gmail.com> <4654621C.8010700@archonet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/254 X-Sequence-Number: 25574 ------=_Part_74837_15945207.1179986045913 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Richard Huxton, Thanks for your detailed reply. I am maintaining various database of same kind in postgresql. Here I have shown various corrupted last line of output of select * from pg_largeobject where oid = xxxxxx; in 5 databases. I have used '\o e:\\filename.xml' before executing query and inspected the output in that file. Kindly look at the end of line in all 5 outputs. First 3 output shows few missing characters. But last 2 output is the perfect one. 1. Output of SFRS2 database: -------------------------------------------- 101177 | 630 | ight_val>\015\012 - 2.9928278923034668\015\012 23\015\012 -1\015\012 - 2.9928278923034668\015\012 23\015\012 -1\015\012\015\012 - 2.9928278923034668\015\012 23\015\012 -1\015\012\015\012 - 2.9928278923034668\015\012 23\015\012 -1\015\012\015\012 (631 rows) 5. Output of FASP_TEST database: (Not yet corrupted) -------------------------------------------------------------------------------- 106310 | 630 | ight_val>\015\012 - 2.9928278923034668\015\012 23\015\012 -1\015\012\015\012 (631 rows) Can you figure out the reason behind that. I am waiting for your valuable suggestion. Thanks. Regards, Purusothaman A On 5/23/07, Richard Huxton wrote: > > 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 > -- http://PurusothamanA.wordpress.com/ ------=_Part_74837_15945207.1179986045913 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Richard Huxton,

Thanks for your detailed reply.

I am maintaining various database of same kind in postgresql.
Here I have shown various corrupted last line of output of select * from pg_largeobject where oid = xxxxxx; in 5 databases.

I have used '\o e:\\filename.xml' before executing query and inspected the output in that file.

Kindly look at the end of line in all 5 outputs.
First 3 output shows few missing characters.
But last 2 output is the perfect one.

1. Output of SFRS2 database:
--------------------------------------------
 101177 |    630 | ight_val></_></_></trees>\015\012      <stage_threshold>-2.9928278923034668</stage_threshold>\015\012      <parent>23</parent>\015\012      <next>-1</next></_></stages></haarcascade_frontalface_defau
(631 rows)

2. Output of SFRS1 database:
---------------------------------------------
 41642 |    630 | ight_val></_></_></trees>\015\012      <stage_threshold>-2.9928278923034668 </stage_threshold>\015\012      <parent>23</parent>\015\012      <next>-1</next></_></stages></haarcascade_frontalface_default>\015\012</openc
(631 rows)

3. Output of FASP_AVT database:
--------------------------------------------------
 101800 |    630 | ight_val></_></_></trees>\015\012      <stage_threshold>-2.9928278923034668</stage_threshold>\015\012      <parent>23</parent>\015\012      <next>-1</next></_></stages></haarcascade_frontalface_default>\015\012</openc
(631 rows)

4. Output of SFRS database: (not yet corrupted)
----------------------------------------------------------------------
 24038 |    630 | ight_val></_></_></trees>\015\012      <stage_threshold>- 2.9928278923034668</stage_threshold>\015\012      <parent>23</parent>\015\012      <next>-1</next></_></stages></haarcascade_frontalface_default>\015\012</opencv_storage>\015\012
(631 rows)

5. Output of FASP_TEST database: (Not yet corrupted)
--------------------------------------------------------------------------------
 106310 |    630 | ight_val></_></_></trees>\015\012      <stage_threshold>- 2.9928278923034668</stage_threshold>\015\012      <parent>23</parent>\015\012      <next>-1</next></_></stages></haarcascade_frontalface_default>\015\012</opencv_storage>\015\012
(631 rows)

Can you figure out the reason behind that.
I am waiting for your valuable suggestion.

Thanks.

Regards,
Purusothaman A

On 5/23/07, Richard Huxton <dev@archonet.com> wrote:
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



--
http://PurusothamanA.wordpress.com/ ------=_Part_74837_15945207.1179986045913--