Thread: binary data storage
Hi, Let's say we have 100 MB of binary file (for example, a zip file). How could we store this file in PostgreSQL? Which datatype could we use and how do we insert it? Best regards, -- Devrim GUNDUZ devrim@oper.metu.edu.tr devrim.gunduz@linux.org.tr devrimg@tr.net Web : http://devrim.oper.metu.edu.tr ------------------------------------------------------------------
----- Original Message ----- From: "Devrim GUNDUZ" > > Hi, > > Let's say we have 100 MB of binary file (for example, a zip file). How > could we store this file in PostgreSQL? Which datatype could we use and > how do we insert it? > search for "large objects" in the Programmer's Guide ( http://www.ca.postgresql.org/users-lounge/docs/#7.2 ) in your case Large Objects (chapter 2 from the Programmer's Guide) may be more appropriate than binary strings (chapter 3.4 from the User's Guide) hth, Marin ---- "...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. "
Hi Marin, On Thu, 4 Apr 2002, Marin Dimitrov wrote: > > search for "large objects" in the Programmer's Guide ( > http://www.ca.postgresql.org/users-lounge/docs/#7.2 ) > > in your case Large Objects (chapter 2 from the Programmer's Guide) may be > more appropriate than binary strings (chapter 3.4 from the User's Guide) > Thanks alot. Also searched php manual and wrote the following simple code: <? $conn = Pg_Connect (......); pg_exec ($conn, "begin"); $oid = pg_locreate ($conn); echo ("$oid\n"); $handle = pg_loopen ($conn, $oid, "w"); echo ("$handle\n"); pg_loimport ("/etc.tar.gz"); pg_lowrite ($handle, "/etc.tar.gz"); pg_loclose ($handle); pg_exec ($conn, "commit"); ?> Ok, I can see that the data is placed under db directory... But where is tha data kept? I mean, where is /etc.tar.gz ? BEst regards, Devrim GUNDUZ devrim@oper.metu.edu.tr devrim.gunduz@linux.org.tr devrimg@tr.net Web : http://devrim.oper.metu.edu.tr ------------------------------------------------------------------
On Jue 04 Abr 2002 06:42, you wrote: > Hi Marin, > > On Thu, 4 Apr 2002, Marin Dimitrov wrote: > > search for "large objects" in the Programmer's Guide ( > > http://www.ca.postgresql.org/users-lounge/docs/#7.2 ) > > > > in your case Large Objects (chapter 2 from the Programmer's Guide) may be > > more appropriate than binary strings (chapter 3.4 from the User's Guide) > > Thanks alot. Also searched php manual and wrote the following simple code: > > <? > $conn = Pg_Connect (......); > pg_exec ($conn, "begin"); > $oid = pg_locreate ($conn); > echo ("$oid\n"); > $handle = pg_loopen ($conn, $oid, "w"); > echo ("$handle\n"); > pg_loimport ("/etc.tar.gz"); > pg_lowrite ($handle, "/etc.tar.gz"); This is something that confuses me: What does pg_loimport do, and what does pg_lowrite do? > pg_loclose ($handle); > pg_exec ($conn, "commit"); > ?> Another question I have about using PHP and PostgreSQL Large Objects, is that in no place there is a reference to where the lo will be stored? I mean, where will /etc.tar.gz be stored in the Database? Saludos... :-) -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? ----------------------------------------------------------------- Martín Marqués | mmarques@unl.edu.ar Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral -----------------------------------------------------------------
Hi Martin,
I discourage you to use the large object data type for a file of such
dimensions... It would be better for you to store in the db the filename
and the path to the file itself.
However you can find the large object you've inserted in your data
directory (subdirectory base/oid of your database/).
Bye,
--
Doct. Eng. Denis Gasparin: denis@edistar.com
---------------------------
Programmer & System Administrator - Edistar srl
Il gio, 2002-04-04 alle 14:07, Martín Marqués ha scritto:
> On Jue 04 Abr 2002 06:42, you wrote:
> > Hi Marin,
> >
> > On Thu, 4 Apr 2002, Marin Dimitrov wrote:
> > > search for "large objects" in the Programmer's Guide (
> > > http://www.ca.postgresql.org/users-lounge/docs/#7.2 )
> > >
> > > in your case Large Objects (chapter 2 from the Programmer's Guide) may be
> > > more appropriate than binary strings (chapter 3.4 from the User's Guide)
> >
> > Thanks alot. Also searched php manual and wrote the following simple code:
> >
> > <?
> > $conn = Pg_Connect (......);
> > pg_exec ($conn, "begin");
> > $oid = pg_locreate ($conn);
> > echo ("$oid\n");
> > $handle = pg_loopen ($conn, $oid, "w");
> > echo ("$handle\n");
> > pg_loimport ("/etc.tar.gz");
> > pg_lowrite ($handle, "/etc.tar.gz");
>
> This is something that confuses me:
> What does pg_loimport do, and what does pg_lowrite do?
>
> > pg_loclose ($handle);
> > pg_exec ($conn, "commit");
> > ?>
>
> Another question I have about using PHP and PostgreSQL Large Objects, is that
> in no place there is a reference to where the lo will be stored?
>
> I mean, where will /etc.tar.gz be stored in the Database?
>
> Saludos... :-)
>
> --
> Porqué usar una base de datos relacional cualquiera,
> si podés usar PostgreSQL?
> -----------------------------------------------------------------
> Martín Marqués | mmarques@unl.edu.ar
> Programador, Administrador, DBA | Centro de Telematica
> Universidad Nacional
> del Litoral
> -----------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
On Jue 04 Abr 2002 11:32, you wrote:
> Hi Martin,
> I discourage you to use the large object data type for a file of such
> dimensions... It would be better for you to store in the db the filename
> and the path to the file itself.
> However you can find the large object you've inserted in your data
> directory (subdirectory base/oid of your database/).
I still don't get it.
If I use pg_lo* to put my binary data in the database, where will it be,
always talking about the structure of the database.
Example:
A table with 5 fields, and one is of type oid, and I want to insert data (a
new row to the table) in which I have a binary file to put in the oid field.
And how about if I have more then one oid field?
I see no specification on which field the binary object will be inserted.
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
Denis Gasparin <denis@edistar.com> writes: > Hi Martin, > I discourage you to use the large object data type for a file of such > dimensions... It would be better for you to store in the db the filename > and the path to the file itself. Why? > However you can find the large object you've inserted in your data > directory (subdirectory base/oid of your database/). Large objects are stored in a single table, not individual files. -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
=?iso-8859-15?q?Mart=EDn=20Marqu=E9s?= <martin@bugs.unl.edu.ar> writes: > I still don't get it. > If I use pg_lo* to put my binary data in the database, where will it be, > always talking about the structure of the database. > Example: > A table with 5 fields, and one is of type oid, and I want to insert data (a > new row to the table) in which I have a binary file to put in the oid field. > > And how about if I have more then one oid field? > > I see no specification on which field the binary object will be inserted. Here's the way it works: You create the LO with lo_create() or lo_import(). These give you an OID that is your "handle" for getting at the LO data. You then store the OID in a column of type 'oid' in one or more of your tables. To retrieve the LO data, fetch the OID from the table it lives in, and use lo_open() and lo_read() to fetch the actual data. Make sense? -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
On Thu, 4 Apr 2002 12:36:42 +0300 "Marin Dimitrov" <marin.dimitrov@sirma.bg> wrote: > > ----- Original Message ----- > From: "Devrim GUNDUZ" > > > > > Hi, > > > > Let's say we have 100 MB of binary file (for example, a zip file). How > > could we store this file in PostgreSQL? Which datatype could we use and > > how do we insert it? > > > > search for "large objects" in the Programmer's Guide You could also use a column with datatype "bytea", and the libpq function PQescapeBytea(). Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
On Jue 04 Abr 2002 13:08, Doug McNaught wrote:
> =?iso-8859-15?q?Mart=EDn=20Marqu=E9s?= <martin@bugs.unl.edu.ar> writes:
> > I still don't get it.
> > If I use pg_lo* to put my binary data in the database, where will it be,
> > always talking about the structure of the database.
> > Example:
> > A table with 5 fields, and one is of type oid, and I want to insert data
> > (a new row to the table) in which I have a binary file to put in the oid
> > field.
> >
> > And how about if I have more then one oid field?
> >
> > I see no specification on which field the binary object will be inserted.
>
> Here's the way it works:
>
> You create the LO with lo_create() or lo_import(). These give you an
> OID that is your "handle" for getting at the LO data. You then store
> the OID in a column of type 'oid' in one or more of your tables.
>
> To retrieve the LO data, fetch the OID from the table it lives in, and
> use lo_open() and lo_read() to fetch the actual data.
That's the problem, at least with PHP! I can't see any reference to the
column of type oid in the functions, so where is it storing the data, or
getting the data from?
Saludos... :-)
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
Martín Marqués <martin@bugs.unl.edu.ar> writes: > That's the problem, at least with PHP! I can't see any reference to the > column of type oid in the functions, so where is it storing the data, or > getting the data from? Hmmm, I'm not sure where your problem is. You need to include the OID column in whatever table you're defining--it's not magically created for you. -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
On Vie 05 Abr 2002 14:07, Doug McNaught wrote:
> Martín Marqués <martin@bugs.unl.edu.ar> writes:
> > That's the problem, at least with PHP! I can't see any reference to the
> > column of type oid in the functions, so where is it storing the data, or
> > getting the data from?
>
> Hmmm, I'm not sure where your problem is. You need to include the OID
> column in whatever table you're defining--it's not magically created
> for you.
I create a table with lots of columns, with diferent data types, and one in
particular with type oid.
Now, I want to do an insert of a row, in which I add data to all the columns
except the oid column, in which I want to insert a file. How do I make the
insert with PHP, in a way that the info of row and the file loaded in the oid
column are really in the same row.
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
Martín Marqués <martin@bugs.unl.edu.ar> writes:
> Now, I want to do an insert of a row, in which I add data to all the columns
> except the oid column, in which I want to insert a file. How do I make the
> insert with PHP, in a way that the info of row and the file loaded
> in the oid column are really in the same row.
OK, here's pseudocode. Assuming PHP has a an interface to the lo_
routines, you'd do something like this:
BEGIN WORK;
$file_oid = lo_import("myfile");
INSERT INTO mytable(field1, field2, file_oid) VALUES ('foo', 'bar', $file_oid);
COMMIT WORK;
Does this help?
-Doug
--
Doug McNaught Wireboard Industries http://www.wireboard.com/
Custom software development, systems and network consulting.
Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
For large dimensions I mean file of 20-30 MB and up... I've noticed that when storing files of such dimensions the fetch of the objects is more and more slow... I think there is a kind of index degeneration... If anyone has tips about this problem... -- Doc. Eng. Denis Gasparin: denis@edistar.com --------------------------- Programmer & System Administrator - Edistar srl Il gio, 2002-04-04 alle 17:04, Doug McNaught ha scritto: > Denis Gasparin <denis@edistar.com> writes: > > > Hi Martin, > > I discourage you to use the large object data type for a file of such > > dimensions... It would be better for you to store in the db the filename > > and the path to the file itself. > > Why? > > > However you can find the large object you've inserted in your data > > directory (subdirectory base/oid of your database/). > > Large objects are stored in a single table, not individual files. > > -Doug > -- > Doug McNaught Wireboard Industries http://www.wireboard.com/ > > Custom software development, systems and network consulting. > Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD... > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org