Re: Please help me in Postgres - Mailing list pgsql-interfaces

On Tue, 17 Nov 1998, Hitesh Kumar Gulati wrote:

> To store the data file I am using  lo_import function of large
> object interface. As I  am storing(importing into database) the file
> even 100 bytes only. During the importing process it creates two files
> into the database area xinv*,xinx*.I don't know why it is taking  the
> space of  16384 Bytes for each xinv* xinx* file  in the database.

Internally, each large object is a table & index pair. The xinv* file is
the table, and the xinx* file is the index. The data is then stored in the
table, one row per block.

The size is related to the page size (currently 8k). As a block is stored,
a row is inserted into the table containing the data. If the block is too
large to fit the space remaining in the current page, a new page is
created. If the block is >8k then it's broken down into 8k chunks. (It's
actually just less than 8k, because there is a small overhead per row, and
per page).

The index is used to get at the correct blocks, in the correct sequence.

It's not that good in the ammount of disk space used, nor the access
speed, but it works well.

>  I HAVE SEEN THE FAQ.Please at least tell me the person who can help me
>  in this matter.

The best place is the interfaces email list. I'm CCing that list now.

My involvement with large obhects has been with Java, rather than C, but
the principles are almost identical (I used the C source as the basis of
the Java implementation).

As for your example:

>  I am sending a sample source code.
> >      1  #include <stdio.h>
> >      2  #include "libpq-fe.h"
> >      3  #include <stdlib.h>
> >      4  char shotno[10];
> >      5  main()
> >      6  {
> >      7  char filename[35];
> >      8  char    *pghost,*pgport,*pgoptions,*pgtty,query2[100];
> >      9  char    *dbName, shotno[10];
> >     10  PGconn *conn;
> >     11  PGresult *res2;
> >     12  int   shot,gchno;
> >     13  pghost = "sun9"; /* host name of the backend server */
> >     14  pgport = "5432"; /* port of the backend server */
> >     15  pgoptions = NULL;/* special options to start up the backend */
> >     16  pgtty = NULL;    /* debugging tty for the backend server */
> >     17  dbName = "aditya";
> >     18  /* make a connection to the database */
> >     19   conn = PQsetdb(pghost, pgport, pgoptions, pgtty,dbName);
> >     20  /* check to see that the connection was successfully made */
> >     21  if (PQstatus(conn) == CONNECTION_BAD)
> >     22  {
> >     23     printf("Connection to database '%s' failed.\n",dbName);
> >     24     printf("%s", PQerrorMessage(conn));
> >     25     exit(0);
> >     26  }
> >     27  printf("\nEnter the shot no... ");
> >     28  scanf("%s",shotno);
> >     29  shot=atoi(shotno);
> >     30  gchno=200;
> >     31  filename="/users13/aditya/vax/a05966.lgg";
> >     32  sprintf(query2,"insert into ch_data values ( %d ,%d ,
> > lo_import('%s')) ; " , shot ,gchno , filename);
> >     33  res2 = PQexec(conn,query2);
> >     34  }
> >     35  printf("\n The Data File For This Shot has transferred 'OK'
> > \n");
> >     36  }
> > - --
> > In this code the file I am importing  "/users13/aditya/vax/a05966.lgg"
> > has a size of 3328 bytes only. This file consumes the space in database
> > is of 2*16384 bytes. It creates two files xinv* and xinx* in database of
> > size 16384 bytes each.
> > sun9:/work/postgres/local/data/base/aditya>$ ls -laF
> > - -rw-------   1 postgres guest      16384 Nov 12 10:37 xinx154230
> > - -rw-------   1 postgres guest      16384 Nov 12 10:37 xinv154230
> > How can I reduce the size of the file in database.

This is normal, using the inversion file system (the name of using a
table/index pair for storing files).

What type of data is stored in these files? You could use VarChar to store
the data if they are ascii, or convert the data into hex, then store in
the table.

If most of your data is small, then using the other storage types would be
far better than using large objects.

> --
>                     Hitesh kumar Gulati
>                     E-mail-:hkg@plasma.ernet.in
>                     Ph. (079)-2864023 (O)
>                     Engineer - SC
>                     Institute For Plasma Research
>                     Near Indira Bridge
>                     Bhat Gandhinagar(GUJARAT)
>

--
       Peter T Mount peter@retep.org.uk
      Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
 Java PDF Generator: http://www.retep.org.uk/pdf