Hello, All!

I had PostgreSQL 7.0.3 (7.1 now) and one nice day I've noticed that much
number of my BLOBs are broken! Although they seems to be with good content
in file system (xinv[0-9]+ files) I was not able to get them via
lo_export... After spending some time trying to fix it, I decided to write
my own xinv2plainfile converter. I hope if someone has same troubles this
converter will help him.
Just compile it, put it in the dir with your xinv[0-9]+ files and run.
It will create new files with name eq to BLOB id from apropriate xinv.
----------------------------xinv2palinfile.c-------------------------------
#include "sys/types.h"
#include "dirent.h"
#include "stdio.h"
#include "string.h"
 
#define BLCKSIZE 8192
#define HPT_LEN 40
 
#define DEBUG
//#undef DEBUG
 
typedef unsigned short uint16;
typedef unsigned int uint32;
 
typedef struct ItemIdData
{
  unsigned lp_off:15,
    lp_flags:2,
    lp_len:15;
} ItemIdData;

typedef struct PageHeaderData
{
  uint16 pd_lower;
  uint16 pd_upper;
  uint16 pd_special;
  uint16 pd_opaque; //page size
  ItemIdData pd_linp[1];
} PageHeaderData;
 
int
extract(const char * filename)
{
  FILE * infile;
  FILE * outfile;
  ItemIdData linp;
  PageHeaderData* pg_head;
  char buff[BLCKSIZE];
  char data[BLCKSIZE];
  int tuple_no;

  //opening outpur file, if it is already presents, overwrite it!
  if ((outfile = fopen(filename + 4, "w")) == NULL)
    return -1;
 
  //opening input file
  if ((infile = fopen(filename, "r")) == NULL)
    return -1;
 
  while (fread(&buff, BLCKSIZE, 1, infile))
    {
      pg_head = (PageHeaderData*)buff;
#ifdef DEBUG
      printf("Page data: pd_lower=%d, pd_upper=%d, pd_special=%d, 
pd_opaque=%d\
             pg_head->pd_lower, pg_head->pd_upper, pg_head->pd_special,
pg_head
#endif

      for(tuple_no = 0; pg_head->pd_linp[tuple_no].lp_len; ++tuple_no)
        {
          linp = pg_head->pd_linp[tuple_no];
          memcpy(data, buff + linp.lp_off + HPT_LEN, linp.lp_len -
HPT_LEN);
          data[linp.lp_len - HPT_LEN] = 0;
          //    }
#ifdef DEBUG
          printf("Tuple %d: off=%d,\tflags=%d,\tlen=%d\n",\
                 tuple_no, linp.lp_off, linp.lp_flags, linp.lp_len);
          printf("Data:\n%s\n----------\n", data);
#endif
          fprintf(outfile, "%s", data);
        }
    }
  fclose(infile);
  fclose(outfile);
  return 0;
}

int
main(void)
{
  DIR * curdir;
  struct dirent * curdirentry;
 
  //open current directory
  curdir = opendir(".");
  if (curdir == NULL)
    {
      printf("Cannot open curdir!!!\n");
      return -1;
    }

  //search through curdir for files 'xinv[0-9]+'
  while ((curdirentry = readdir(curdir)) != NULL)
  {
    if (strstr(curdirentry->d_name, "xinv") != curdirentry->d_name)
      continue;
    //found entry with name begining with xinv.
    //let's hope this is what we are looking for :)
    printf("Trying to extract file '%s'... ", curdirentry->d_name);
    if (extract(curdirentry->d_name))
      printf("failed\n");
    else
      printf("successed\n");
  }
 
  return 0;
}

---------------------------------------------------------------------------
With Best Regards,
  Maks N. Polunin.
Brainbench: http://www.brainbench.com/transcript.jsp?pid=111472
ICQ#:18265775


Sat, 2 Jun 2001 21:50:59 +0700
"Maks N. Polunin" <pmn836@cclib.nsu.ru>
large objects dump
Hi,

> I had PostgreSQL 7.0.3 (7.1 now) and one nice day I've noticed that much
> number of my BLOBs are broken! Although they seems to be with good content
> in file system (xinv[0-9]+ files) I was not able to get them via
> lo_export... After spending some time trying to fix it, I decided to write
> my own xinv2plainfile converter. I hope if someone has same troubles this
> converter will help him.
> Just compile it, put it in the dir with your xinv[0-9]+ files and run.
> It will create new files with name eq to BLOB id from apropriate xinv.

Either use 7.1.x, or apply my patch to 7.0.3. And you will have no such 
problems at all. :-))

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Wed, 6 Jun 2001 12:07:36 +0700
Denis Perchine <dyp@perchine.com>
Re: large objects dump
I have a cronjob that does a vacuumdb -a -z every night.
When i came to work this morning i saw a lot of postgres processes hanging
on wait.

The last thing i see before it hangs is this:

-------------------------------------------------------------
NOTICE:  --Relation pg_toast_1216--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail.
Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_toast_1216_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE:  Analyzing...
-------------------------------------------------------------

is this a known problem?
Postgres is version 7.1.1.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 Programmer/Networker [|] Magnus Naeslund
 PGP Key: http://www.genline.nu/mag_pgp.txt
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



Wed, 6 Jun 2001 15:59:13 +0200
"Magnus Naeslund\(f\)" <mag@fbab.net>
vacuumdb -a -z hangs
"Magnus Naeslund\(f\)" <mag@fbab.net> writes:
> I have a cronjob that does a vacuumdb -a -z every night.
> When i came to work this morning i saw a lot of postgres processes hanging
> on wait.

Sounds to me like you have an open transaction that is holding a lock
that everybody else needs.  Very likely it's not the VACUUM that's at
fault, at least not directly.

			regards, tom lane

Wed, 06 Jun 2001 10:52:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: vacuumdb -a -z hangs 