Re: Corruption of files in PostgreSQL

What OS are you running ?

Linux(32 or 64 Bit)? Ext 3 Filesystem ? Wich Kernel Version ?
Bug in Ext 3/Linux Kernel/Hardware(Raid Controller ?) ?

Does the error only happens under heavy load ?

regards,

-Franz

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] Im Auftrag von Paolo Bizzarri
Gesendet: Samstag, 2. Juni 2007 07:46
An: Purusothaman A
Cc: Richard Huxton; pgsql-general(at)postgresql(dot)org
Betreff: Re: [GENERAL] Corruption of files in PostgreSQL

Hi everyone,

a little update.

We have upgraded our system to 7.4.17. The problem of truncated files
seems now better, but it is still present. We have not found a clearly
understandable pattern on why this happens.

Just to provide some further information:

- we create a file and store on the DB;

- we give the file to the user, and he can modify at its wish the file;

- we store back the modified file on the DB;

- the last two points can happen several times.

Any hint?

Best regards.

Paolo Bizzarri
Icube S.r.l.

On 5/30/07, Purusothaman A <purusothaman(dot)a(at)gmail(dot)com> wrote:
> Paolo Bizzarri,
>
> I am also using postgresql in my application and also facing file object
> corruption problem.
>
> I already discussed several times with Richard Huxton, and ended without
any
> clue.
>
> Here I am briefing my problem, see if u find any clue about it.
> I am storing/retrieving my file in postgresql using lo_export() and
> lo_import() api.
>
> after few weeks (as application is being used - number of file objects in
> database also grows) my file object gets corrupted. And I have no clue
about
> which causes this problem.
>
> I confirmed the file corruption by the following query,
>
> sfrs2=> select loid, pageno, length(data) from pg_largeobject where loid =
> 101177 and pageno = 630;
> loid | pageno | length
> --------+--------+--------
> 101177 | 630 | 181
> (1 row)
>
> But actually the result of the above query before corruption(ie,
immediately
> after file object added to table)
>
> fasp_test=> select loid, pageno, length(data) from pg_largeobject where
loid
> = 106310 and pageno = 630;
> loid | pageno | length
> --------+--------+--------
> 106310 | 630 | 205
> (1 row)
>
> I uploaded same file in both(sfrs2, fasp_test) databases. The first one
> result is after the corruption. and the later is before corruption.
>
> You also confirm you problem like this. And I strongly believe that, there
> is some bug in PostgreSQL.
>
> Kindly don't forget to alert me once u find solution/cause.
>
> Regards,
> Purusothaman A
>
>
> On 5/30/07, Paolo Bizzarri <pibizza(at)gmail(dot)com> wrote:
> >
> > On 5/30/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
> > > Paolo Bizzarri wrote:
> > > > We use postgres as a backend, and we are experimenting some
corruption
> > > > problems on openoffice files.
> > >
> > > 1. How are you storing these files?
> >
> > Files are stored as large objects. They are written with an lo_write
> > and its contents is passed as a Binary object.
> >
> > > 2. What is the nature of the corruption?
> >
> > Apparently, files get truncated.
> >
> > > > As our application is rather complex (it includes Zope as an
> > > > application server, OpenOffice as a document server and as a client)
> > > > we need some info on how to check that we are interacting correctly
> > > > with Postgres.
> > >
> > > Shouldn't matter.
> >
> > I hope so...
> >
> > > > We are currently using:
> > > >
> > > > - PostgreSQL 7.4.8;
> > >
> > > Well, you need to upgrade this - version 7.4.17 is the latest in the
7.4
> > > series. You are missing 9 separate batches of bug and security fixes.
> >
> > Ok. We will upgrade and see if this can help solve the problem.
> >
> > >
> > > > - pyscopg 1.1.11 ;
> > > > - Zope 2.7.x;
> > > > - Openoffice 2.2.
> > >
> > > None of this should matter really, unless there's some subtle bug in
> > > psycopg causing corruption of data in-transit.
> > >
> > > Let's get some details on the two questions above and see if there's a
> > > pattern to your problems.
> >
> > Ok. Thank you.
> >
> > Paolo Bizzarri
> > Icube S.r.l.
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
>
>
> --
> http://PurusothamanA.wordpress.com/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match