From purusothaman.a@gmail.com Wed May 16 05:42:06 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E78799FB562 for ; Wed, 16 May 2007 02:42:06 -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 53956-06 for ; Wed, 16 May 2007 02:42:01 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.238]) by postgresql.org (Postfix) with ESMTP id 2E2A69FB59D for ; Wed, 16 May 2007 02:42:03 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id i28so63612wxd for ; Tue, 15 May 2007 22:42:02 -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:mime-version:content-type; b=ArTmBUO6KA0VEy6J7oB7pm+iOdc8jxg9qqMwZuUv4JxRZQhIFnsuhgJ93myhC5BXU2yjr+BBMPh9fdlM3I6SjCIGKs1CpZvOU4FHFQE3KNmH4aFf5+OJK56PfBSSjcmlDA8KRuPIVZNh7gKcrNtyb3QGVe1zRQw8UyTpUYGLTVw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=MlWCav2Flt7XW6gl7BEpnP6VsU2aWL9O5lSHCpD1P/DXz6WavgkRUv0GAgpm06COoLXyjb8F89qTfvJnJ577YVaaELESN7i4xST9ROqL19HJzigBbliNeZ1NFJ/m09PQzHGUf/QcP2uE4vvbePDkBbwh2IMMNUbZ/QmlSDXvtHI= Received: by 10.90.118.12 with SMTP id q12mr7385131agc.1179294122047; Tue, 15 May 2007 22:42:02 -0700 (PDT) Received: by 10.90.34.1 with HTTP; Tue, 15 May 2007 22:42:01 -0700 (PDT) Message-ID: <3650d0d50705152242y689c0ef8u56223ec15661c821@mail.gmail.com> Date: Wed, 16 May 2007 11:12:01 +0530 From: "Purusothaman A" To: pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: OIDs - file objects, are damaged by PostgreSQL. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7362_5961264.1179294121998" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/136 X-Sequence-Number: 25456 ------=_Part_7362_5961264.1179294121998 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi all, I am using Postgresql 8.2. I am using client side api to upload/download files to/from postgresql using calls lo_export()/lo_import(); If I download a file from postgresql, few weeks later, files object's contents got damaged. I don't know why. Do any of you have encountered same problem? Regards, Purusothaman A -- http://PurusothamanA.wordpress.com/ ------=_Part_7362_5961264.1179294121998 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi all,

I am using Postgresql 8.2.

I am using client side api to upload/download files to/from postgresql using calls lo_export()/lo_import();
If I download a file from postgresql, few weeks later, files object's contents got damaged.

I don't know why.

Do any of you have encountered same problem?

Regards,
Purusothaman A


--
http://PurusothamanA.wordpress.com/ ------=_Part_7362_5961264.1179294121998-- From dev@archonet.com Wed May 16 10:00:20 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 702A39FBC98; Wed, 16 May 2007 07:00:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 28827-02; Wed, 16 May 2007 07:00:16 -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 581299FB766; Wed, 16 May 2007 07:00:16 -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 1DCFD74505; Wed, 16 May 2007 11:00:15 +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 4F3B427411C; Wed, 16 May 2007 11:00:11 +0100 (BST) Message-ID: <464AD62B.9000003@archonet.com> Date: Wed, 16 May 2007 11:00:11 +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> In-Reply-To: <3650d0d50705152242y689c0ef8u56223ec15661c821@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/138 X-Sequence-Number: 25458 Purusothaman A wrote: > Hi all, > > I am using Postgresql 8.2. 8.2.which? > I am using client side api to upload/download files to/from postgresql > using > calls lo_export()/lo_import(); > If I download a file from postgresql, few weeks later, files object's > contents got damaged. > > I don't know why. > > Do any of you have encountered same problem? It's not impossible that PostgreSQL is corrupting your large-objects, but it does seem unlikely. In what way were the large-object's contents corrupted? Was the file-length the same? Do you know exactly when it got corrupted? -- Richard Huxton Archonet Ltd From purusothaman.a@gmail.com Wed May 23 08:34:26 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 45FDB9FB566 for ; Wed, 23 May 2007 05:34:26 -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 34305-05 for ; Wed, 23 May 2007 05:34:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.237]) by postgresql.org (Postfix) with ESMTP id F078A9FB886 for ; Wed, 23 May 2007 05:34:16 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id i29so112244wxd for ; Wed, 23 May 2007 01:34:15 -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=idaXOmk8fqUi3bIIxuL3vZHhcf7CsV9JN27ecZQ+0ZnmSzeZhaOf8WcUcm3br7qjknMtrOtqMAzKxBTtdo9KSU53kr45bPKcAPaoV1xa/5NH0JwjAsQ5XPuB+dsUQIqKlpuN0TNf0rYlmAWxRwIRRfWcT1pyBjPShKJJ9yQ9Qtk= 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=JEJK/FJ/jFWI5blAiAkk8/rY03ZP49EAZrwq5PCTTOglb1nIFjSOgiV6FABPP1paZ+chFE/RXjlIDZALtcaSSlo2myBkm/7mfaXXm8Ucv0Yarlq92VN0sjzPIaQIwhaAFdRnOHhD9J+/s130pYeZnjZyILOulJVfFs/1vIsad2E= Received: by 10.90.99.20 with SMTP id w20mr131634agb.1179909254877; Wed, 23 May 2007 01:34:14 -0700 (PDT) Received: by 10.90.86.9 with HTTP; Wed, 23 May 2007 01:34:09 -0700 (PDT) Message-ID: <3650d0d50705230134n15b993aat8bbc631836eef65e@mail.gmail.com> Date: Wed, 23 May 2007 14:04:09 +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: <464AD62B.9000003@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_65849_12309523.1179909249806" References: <3650d0d50705152242y689c0ef8u56223ec15661c821@mail.gmail.com> <464AD62B.9000003@archonet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/234 X-Sequence-Number: 25554 ------=_Part_65849_12309523.1179909249806 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks Richard Huxton for your reply. I use client side api for uploading and downloading files. Its not happening immediately. But when database grows with data, file object got corrupted. My table structure is as follows. Table "public.conf" Column | Type | Modifiers --------+------------------------+----------- key | character varying(50) | not null value | character varying(100) | Indexes: "conf_pkey" PRIMARY KEY, btree ("key") Content of this table is, key | value ---------------------+-------- HX | 101800 MASK | 101801 Rockey4ND | 101802 Threshold | 60 Authentication Mode | 2 (5 rows) In the above, value of HX, MASK, Rockey4ND is 101800, 101801, 101802 (which was returned by lo_import()); Actually for some peculiar reason I kept "Value" field as var char instead of oid. (this could be reason?...) This problem occurs only few weeks after uploading files. Regards, Purusothaman A On 5/16/07, Richard Huxton wrote: > > Purusothaman A wrote: > > Hi all, > > > > I am using Postgresql 8.2. > > 8.2.which? > > > I am using client side api to upload/download files to/from postgresql > > using > > calls lo_export()/lo_import(); > > If I download a file from postgresql, few weeks later, files object's > > contents got damaged. > > > > I don't know why. > > > > Do any of you have encountered same problem? > > It's not impossible that PostgreSQL is corrupting your large-objects, > but it does seem unlikely. In what way were the large-object's contents > corrupted? Was the file-length the same? Do you know exactly when it got > corrupted? > > -- > Richard Huxton > Archonet Ltd > -- http://PurusothamanA.wordpress.com/ ------=_Part_65849_12309523.1179909249806 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks Richard Huxton for your reply.

I use client side api for uploading and downloading files.

Its not happening immediately. But when database grows with data, file object got corrupted.

My table structure is as follows.
             Table "public.conf"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 key    | character varying(50)  | not null
 value  | character varying(100) |
Indexes:
    "conf_pkey" PRIMARY KEY, btree ("key")

Content of this table is,
         key         | value
---------------------+--------
 HX                  | 101800
 MASK                | 101801
 Rockey4ND           | 101802
 Threshold           | 60
 Authentication Mode | 2
(5 rows)

In the above, value of HX, MASK, Rockey4ND is 101800, 101801, 101802 (which was returned by lo_import());

Actually for some peculiar reason I  kept "Value" field as var char instead of oid. (this could be reason?...)

This problem occurs only few weeks after uploading files.

Regards,
Purusothaman A
 
On 5/16/07, Richard Huxton <dev@archonet.com> wrote:
Purusothaman A wrote:
> Hi all,
>
> I am using Postgresql 8.2.

8.2.which?

> I am using client side api to upload/download files to/from postgresql
> using
> calls lo_export()/lo_import();
> If I download a file from postgresql, few weeks later, files object's
> contents got damaged.
>
> I don't know why.
>
> Do any of you have encountered same problem?

It's not impossible that PostgreSQL is corrupting your large-objects,
but it does seem unlikely. In what way were the large-object's contents
corrupted? Was the file-length the same? Do you know exactly when it got
corrupted?

--
   Richard Huxton
   Archonet Ltd



--
http://PurusothamanA.wordpress.com/ ------=_Part_65849_12309523.1179909249806-- From dev@archonet.com Wed May 23 09:23:58 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E21269FB625; Wed, 23 May 2007 06:23:58 -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 66852-04; Wed, 23 May 2007 06:23:50 -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 5C70E9FAA6E; Wed, 23 May 2007 06:23:54 -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 5899E75133; Wed, 23 May 2007 10:20:39 +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 2665627411B; Wed, 23 May 2007 10:20:39 +0100 (BST) Message-ID: <46540766.2030108@archonet.com> Date: Wed, 23 May 2007 10:20:38 +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> In-Reply-To: <3650d0d50705230134n15b993aat8bbc631836eef65e@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/236 X-Sequence-Number: 25556 Purusothaman A wrote: > Thanks Richard Huxton for your reply. > > I use client side api for uploading and downloading files. > > Its not happening immediately. But when database grows with data, file > object got corrupted. Yes, but *HOW* - is it a different file, length is different, what? > My table structure is as follows. > Table "public.conf" > Column | Type | Modifiers > --------+------------------------+----------- > key | character varying(50) | not null > value | character varying(100) | > Indexes: > "conf_pkey" PRIMARY KEY, btree ("key") > > Content of this table is, > key | value > ---------------------+-------- > HX | 101800 > MASK | 101801 > Rockey4ND | 101802 > Threshold | 60 > Authentication Mode | 2 > (5 rows) > > In the above, value of HX, MASK, Rockey4ND is 101800, 101801, 101802 (which > was returned by lo_import()); I find it unlikely that "2" and "60" were returned by lo_import() as OIDs available for large-objects. You've either got: 1. Some other part of your application(s) overwriting "value" 2. Old data still in "value" 3. On-disk corruption due to crashes/hardware malfunction. 4. You're not showing real values > Actually for some peculiar reason I kept "Value" field as var char instead > of oid. (this could be reason?...) Hmm - well it's clearly not right, but I don't see how it can cause errors like this. > This problem occurs only few weeks after uploading files. You still haven't said precisely what the problem is. -- Richard Huxton Archonet Ltd From purusothaman.a@gmail.com Wed May 23 14:56:27 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id DC6459FA603 for ; Wed, 23 May 2007 11:56:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49110-03 for ; Wed, 23 May 2007 11:56:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.249]) by postgresql.org (Postfix) with ESMTP id 5E0C49FAC9B for ; Wed, 23 May 2007 11:56:23 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so52700ana for ; Wed, 23 May 2007 07:56:22 -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=eqyrnzKtkM1cDV1Qb1vFHX2vZ/GtlUCADEUa/nuWELpqEf8kZ/1VquIOXGulGBq1o89SDFCT/8Pa/9HuKJ+E7bCvaA2HbU0PQQAoayTvs8ruuP+zTXyZoE5h4+1TZ1uA0oOsyNVL4q2WAcd373XvDqjX/tx6uFRCv3gB1pYLnmg= 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=oaceJzGVxr7XPsDxzJ5fDk3K72HueUs21n4m7nN6IUef5mMKkBVzEYpxgnc+4JyukiRFosxgV2OWz1Czpj+UagMdbb5hJ5R/j9UXIL0aqBccbqp7ucSY/SLWrieenz30z+OsObPL5CkT82OslcbymY/w4RFVo6ZkvwbO2F8VP/w= Received: by 10.90.113.20 with SMTP id l20mr520313agc.1179932181169; Wed, 23 May 2007 07:56:21 -0700 (PDT) Received: by 10.90.86.9 with HTTP; Wed, 23 May 2007 07:56:21 -0700 (PDT) Message-ID: <3650d0d50705230756g5fdf8bdava3de463eb6f82ad9@mail.gmail.com> Date: Wed, 23 May 2007 20:26:21 +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: <46540766.2030108@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_69127_29541725.1179932181125" References: <3650d0d50705152242y689c0ef8u56223ec15661c821@mail.gmail.com> <464AD62B.9000003@archonet.com> <3650d0d50705230134n15b993aat8bbc631836eef65e@mail.gmail.com> <46540766.2030108@archonet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/251 X-Sequence-Number: 25571 ------=_Part_69127_29541725.1179932181125 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. 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. Usually this problem arises only after the database become large. Any suggestion to rectify this problem would be nice of you. Thanks. Regards, Purusothaman A On 5/23/07, Richard Huxton wrote: > > Purusothaman A wrote: > > Thanks Richard Huxton for your reply. > > > > I use client side api for uploading and downloading files. > > > > Its not happening immediately. But when database grows with data, file > > object got corrupted. > > Yes, but *HOW* - is it a different file, length is different, what? > > > My table structure is as follows. > > Table "public.conf" > > Column | Type | Modifiers > > --------+------------------------+----------- > > key | character varying(50) | not null > > value | character varying(100) | > > Indexes: > > "conf_pkey" PRIMARY KEY, btree ("key") > > > > Content of this table is, > > key | value > > ---------------------+-------- > > HX | 101800 > > MASK | 101801 > > Rockey4ND | 101802 > > Threshold | 60 > > Authentication Mode | 2 > > (5 rows) > > > > In the above, value of HX, MASK, Rockey4ND is 101800, 101801, 101802 > (which > > was returned by lo_import()); > > I find it unlikely that "2" and "60" were returned by lo_import() as > OIDs available for large-objects. You've either got: > 1. Some other part of your application(s) overwriting "value" > 2. Old data still in "value" > 3. On-disk corruption due to crashes/hardware malfunction. > 4. You're not showing real values > > > Actually for some peculiar reason I kept "Value" field as var char > instead > > of oid. (this could be reason?...) > > Hmm - well it's clearly not right, but I don't see how it can cause > errors like this. > > > This problem occurs only few weeks after uploading files. > > You still haven't said precisely what the problem is. > > -- > Richard Huxton > Archonet Ltd > -- http://PurusothamanA.wordpress.com/ ------=_Part_69127_29541725.1179932181125 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.

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.

Usually this problem arises only after the database become large.

Any suggestion to rectify this problem would be nice of you.

Thanks.

Regards,
Purusothaman A

On 5/23/07, Richard Huxton <dev@archonet.com> wrote:
Purusothaman A wrote:
> Thanks Richard Huxton for your reply.
>
> I use client side api for uploading and downloading files.
>
> Its not happening immediately. But when database grows with data, file
> object got corrupted.

Yes, but *HOW* - is it a different file, length is different, what?

> My table structure is as follows.
>             Table "public.conf"
> Column |          Type          | Modifiers
> --------+------------------------+-----------
> key    | character varying(50)  | not null
> value  | character varying(100) |
> Indexes:
>    "conf_pkey" PRIMARY KEY, btree ("key")
>
> Content of this table is,
>         key         | value
> ---------------------+--------
> HX                  | 101800
> MASK                | 101801
> Rockey4ND           | 101802
> Threshold           | 60
> Authentication Mode | 2
> (5 rows)
>
> In the above, value of HX, MASK, Rockey4ND is 101800, 101801, 101802 (which
> was returned by lo_import());

I find it unlikely that "2" and "60" were returned by lo_import() as
OIDs available for large-objects. You've either got:
1. Some other part of your application(s) overwriting "value"
2. Old data still in "value"
3. On-disk corruption due to crashes/hardware malfunction.
4. You're not showing real values

> Actually for some peculiar reason I  kept "Value" field as var char instead
> of oid. (this could be reason?...)

Hmm - well it's clearly not right, but I don't see how it can cause
errors like this.

> This problem occurs only few weeks after uploading files.

You still haven't said precisely what the problem is.

--
   Richard Huxton
   Archonet Ltd



--
http://PurusothamanA.wordpress.com/ ------=_Part_69127_29541725.1179932181125-- From dev@archonet.com Wed May 23 15:48:50 2007 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 From purusothaman.a@gmail.com Thu May 24 05:54:11 2007 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-- From dev@archonet.com Thu May 24 06:42:01 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id CA1079FBD4C; Thu, 24 May 2007 03:42:01 -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 71784-02; Thu, 24 May 2007 03:41:57 -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 DA6C49FBD34; Thu, 24 May 2007 03:41:57 -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 0DC8B74F04; Thu, 24 May 2007 07:41:56 +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 D3184274119; Thu, 24 May 2007 07:41:55 +0100 (BST) Message-ID: <465533B3.5060704@archonet.com> Date: Thu, 24 May 2007 07:41:55 +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> <4654621C.8010700@archonet.com> <3650d0d50705232254w265b94b0yaaf5163b92567403@mail.gmail.com> In-Reply-To: <3650d0d50705232254w265b94b0yaaf5163b92567403@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/258 X-Sequence-Number: 25578 Purusothaman A wrote: > 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: > 2. Output of SFRS1 database: > 3. Output of FASP_AVT database: Let's have a look at the last few bytes of these three rows. For example, below is a very short file that finishes with "end". SELECT loid,pageno,length(data),encode(data, 'hex') FROM pg_largeobject WHERE loid=340161 AND pageno=0; loid | pageno | length | encode --------+--------+--------+---------------------------------------------------------------- 340161 | 0 | 31 | 54686973206973206120746573742066696c650a6c696e6520320a656e640a (1 row) It doesn't seem to be random garbage at the end of your rows, so lets see what it is. Oh - things we can rule out: 1. Index corruption. We're not missing the last row. Adding the length check above will show us if the row is truncated or full of spaces. -- Richard Huxton Archonet Ltd From purusothaman.a@gmail.com Thu May 24 07:35:33 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 43E819FB666 for ; Thu, 24 May 2007 04:35:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 37636-09 for ; Thu, 24 May 2007 04:35:26 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.224]) by postgresql.org (Postfix) with ESMTP id 23CBF9FB86B for ; Thu, 24 May 2007 04:35:28 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id s16so35892wxc for ; Thu, 24 May 2007 00:35:27 -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=Jn6u1fteJC6Sds+e0WjNxWkUf/RhN43iGx9l7SzWxDHP+S+q3Awc0WTpusKTrfnKg5PeWgGbMaIxNBfsUiSsN8eb8+fUYnivuAH8UnmIPAFspDYmVi7rHOpeIyuy7QHX9IYP5LZiI9ZYO8ib9/1HHZgr4gDMonTtGNw74wH9YHQ= 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=HvxEvjSwfacuZQQhXmx+5TYR/RQgqNWRjzfWeqLgrhfzMtXGvsFFVDNfHNwO4KWe/hdGxTSGQFrO5bATnM6ys156DTCh0qH30wVwe2+JKl4K/HMX9q5fpBdAHBT+qwHqYDRyRruszMLtP3U002jASu9X2bu7jvN7SKMj3sC5U9Q= Received: by 10.90.32.14 with SMTP id f14mr1529443agf.1179992127313; Thu, 24 May 2007 00:35:27 -0700 (PDT) Received: by 10.90.86.9 with HTTP; Thu, 24 May 2007 00:35:27 -0700 (PDT) Message-ID: <3650d0d50705240035h795f935do6312ebe0866577fb@mail.gmail.com> Date: Thu, 24 May 2007 13:05:27 +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: <465533B3.5060704@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_75413_10591369.1179992127268" 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> <3650d0d50705232254w265b94b0yaaf5163b92567403@mail.gmail.com> <465533B3.5060704@archonet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/259 X-Sequence-Number: 25579 ------=_Part_75413_10591369.1179992127268 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Richard Huxton, In my system also its 2048 bytes chunk. The below output shows clearly that the last chunk differs in its length. You might have noticed in my previous mail that the string "\015\012\015\012" is missing some characters in SFRS2, SFRS1 and FASP_AVT database outputs. Have a look at it, In this mail I have bolded the corrucpted part. sfrs2=> select loid, pageno, length(data) from pg_largeobject where loid = 101177 and pageno = 630; loid | pageno | length --------+--------+-------- 101177 | 630 | 181 (1 row) sfrs1=> select loid, pageno, length(data) from pg_largeobject where loid = 41642 and pageno = 630; loid | pageno | length -------+--------+-------- 41642 | 630 | 193 (1 row) fasp_avt=> select loid, pageno, length(data) from pg_largeobject where loid = 101800 and pageno = 630; loid | pageno | length --------+--------+-------- 101800 | 630 | 193 (1 row) sfrs=> select loid, pageno, length(data) from pg_largeobject where loid = 24038 and pageno = 630; loid | pageno | length -------+--------+-------- 24038 | 630 | 205 (1 row) fasp_test=> select loid, pageno, length(data) from pg_largeobject where loid = 106310 and pageno = 630; loid | pageno | length --------+--------+-------- 106310 | 630 | 205 (1 row) 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< /haarcascade_frontalface_default>\015\012\015\012 - 2.9928278923034668\015\012 23\015\012 -1< /haarcascade_frontalface_default>\015\012\015\012 - 2.9928278923034668\015\012 23\015\012 -1< /haarcascade_frontalface_default>\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< /haarcascade_frontalface_default>\015\012\015\012 (631 rows) Have you noticed the bolded strings? What could have caused to damage that part? Is it bug in Posstgresql or is they any way to solve this problem. Thanks. Regards, Purusothaman A On 5/24/07, Richard Huxton wrote: > > Purusothaman A wrote: > > 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: > > 2. Output of SFRS1 database: > > 3. Output of FASP_AVT database: > > Let's have a look at the last few bytes of these three rows. For > example, below is a very short file that finishes with "end". > > SELECT loid,pageno,length(data),encode(data, 'hex') FROM pg_largeobject > WHERE loid=340161 AND pageno=0; > loid | pageno | length | encode > > --------+--------+--------+---------------------------------------------------------------- > 340161 | 0 | 31 | > 54686973206973206120746573742066696c650a6c696e6520320a656e640a > (1 row) > > > It doesn't seem to be random garbage at the end of your rows, so lets > see what it is. > > Oh - things we can rule out: > 1. Index corruption. We're not missing the last row. > > Adding the length check above will show us if the row is truncated or > full of spaces. > > -- > Richard Huxton > Archonet Ltd > -- http://PurusothamanA.wordpress.com/ ------=_Part_75413_10591369.1179992127268 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Richard Huxton,

In my system also its 2048 bytes chunk.

The below output shows clearly that the last chunk differs in its length.

You might have noticed in my previous mail that the string
"</haarcascade_frontalface_default>\015\012</opencv_storage>\015\012" is missing some characters in SFRS2, SFRS1 and FASP_AVT database outputs.
Have a look at it, In this mail I have bolded the corrucpted part.

sfrs2=> select loid, pageno, length(data) from pg_largeobject where loid = 101177 and pageno = 630;
  loid  | pageno | length
--------+--------+--------
 101177 |    630 |    181
(1 row)

sfrs1=> select loid, pageno, length(data) from pg_largeobject where loid = 41642 and pageno = 630;
 loid  | pageno | length
-------+--------+--------
 41642 |    630 |    193
(1 row)

fasp_avt=> select loid, pageno, length(data) from pg_largeobject where loid = 101800 and pageno = 630;
  loid  | pageno | length
--------+--------+--------
 101800 |    630 |    193
(1 row)

sfrs=> select loid, pageno, length(data) from pg_largeobject where loid = 24038 and pageno = 630;
 loid  | pageno | length
-------+--------+--------
 24038 |    630 |    205
(1 row)

fasp_test=> select loid, pageno, length(data) from pg_largeobject where loid = 106310 and pageno = 630;
  loid  | pageno | length
--------+--------+--------
 106310 |    630 |    205
(1 row)

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)


Have you noticed the bolded strings?  What could have caused to damage that part?

Is it bug in Posstgresql or is they any way to solve this problem.

Thanks.

Regards,
Purusothaman A


On 5/24/07, Richard Huxton <dev@archonet.com> wrote:
Purusothaman A wrote:
> 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:
> 2. Output of SFRS1 database:
> 3. Output of FASP_AVT database:

Let's have a look at the last few bytes of these three rows. For
example, below is a very short file that finishes with "end<NL>".

SELECT loid,pageno,length(data),encode(data, 'hex') FROM pg_largeobject
WHERE loid=340161 AND pageno=0;
   loid  | pageno | length |                             encode
--------+--------+--------+----------------------------------------------------------------
  340161 |      0 |     31 |
54686973206973206120746573742066696c650a6c696e6520320a656e640a
(1 row)


It doesn't seem to be random garbage at the end of your rows, so lets
see what it is.

Oh - things we can rule out:
1. Index corruption. We're not missing the last row.

Adding the length check above will show us if the row is truncated or
full of spaces.

--
   Richard Huxton
   Archonet Ltd



--
http://PurusothamanA.wordpress.com/ ------=_Part_75413_10591369.1179992127268-- From dev@archonet.com Thu May 24 08:21:00 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id ABB429FBD4F; Thu, 24 May 2007 05:21:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 01295-03; Thu, 24 May 2007 05:20:54 -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 D13D39FBD1F; Thu, 24 May 2007 05:20:56 -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 8156573E33; Thu, 24 May 2007 09:20:55 +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 56357274116; Thu, 24 May 2007 09:20:55 +0100 (BST) Message-ID: <46554AE7.2060600@archonet.com> Date: Thu, 24 May 2007 09:20:55 +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> <4654621C.8010700@archonet.com> <3650d0d50705232254w265b94b0yaaf5163b92567403@mail.gmail.com> <465533B3.5060704@archonet.com> <3650d0d50705240035h795f935do6312ebe0866577fb@mail.gmail.com> In-Reply-To: <3650d0d50705240035h795f935do6312ebe0866577fb@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/260 X-Sequence-Number: 25580 Purusothaman A wrote: > Richard Huxton, > > In my system also its 2048 bytes chunk. > > The below output shows clearly that the last chunk differs in its length. > > You might have noticed in my previous mail that the string > "\015\012\015\012" is > missing some characters in SFRS2, SFRS1 and FASP_AVT database outputs. > Have a look at it, In this mail I have bolded the corrucpted part. Yep, spotted that. Hence asking for the length, and it looks like... > loid | pageno | length > --------+--------+-------- > 101177 | 630 | 181 > 41642 | 630 | 193 > 101800 | 630 | 193 > 24038 | 630 | 205 The data has just been truncated rather than corrupted. > Is it bug in Posstgresql or is they any way to solve this problem. Well, something is setting the length too short on these entries. Can you tell me whether the following statements are all correct? 1. Each database is on a separate machine (that would rule out a hardware problem) 2. All systems are running on Windows 2000/XP/2003. 3. All systems are version 8.2.4 (if not, please give details) 4. You upload the data with lo_import (once) and download it with lo_export (many times) and don't alter it in-between. 5. Where the data has been truncated, you know for a fact you downloaded it OK before (or do you just suspect it was OK?) If you're not changing the data, and you know it was OK at some point then there are only two things I can think of: 1. A hardware problem (which we might rule out above) 2. A bug in PostgreSQL's vacuum code Nothing else should be writing to those blocks. If it looks like a bug in vacuum, we can try to reproduce it, and also examine the actual contents of the on-disk files (to see if the data is there on the disk or not). I'll also copy this message over to the hackers list and see what the developers have to say. -- Richard Huxton Archonet Ltd