Re: BLOBs, pg_dump & pg_restore - Mailing list pgsql-general

On Wed, 1 Oct 2003, Joshua D. Drake wrote:

> Hello,
>
>   We usually have a table called something like file_objects that
> contains information like the loid, content-type, filesize etc...
> that we reference.

Yes, that's what I also have got:

test=> \d images
                  Table "images"
  Column   |           Type           | Modifiers
-----------+--------------------------+-----------
 id        | text                     | not null
 loid      | oid                      | not null
 imagetype | integer                  | not null
 imagesize | integer                  | not null
 imagex    | integer                  | not null
 imagey    | integer                  | not null
 caption   | text                     |
 timestamp | timestamp with time zone | not null


and a sample if the data is:

test=> select * from images;
   id   |  loid   | imagetype | imagesize | imagex | imagey |     caption
|           timestamp
--------+---------+-----------+-----------+--------+--------+-----------------+-------------------------------
 100732 | 2085885 |         2 |     27215 |    576 |    432 | Paint Job
| 2003-10-01 09:47:01.254781+10
 100732 | 2085887 |         2 |     36606 |    500 |    357 | Out of
Africa   | 2003-10-01 11:37:23.791189+10
 100732 | 2085893 |         1 |     34958 |     54 |    135 | An animated
gif | 2003-10-01 22:26:24.63995+10
 100732 | 2085895 |         3 |     45727 |    523 |    100 | A png image
| 2003-10-01 22:30:44.0359+10
(4 rows)


The BLOBs are:

test=> \lo_list
     Large objects
   ID    | Description
---------+-------------
 2085885 |
 2085887 |
 2085893 |
 2085895 |
(4 rows)


My concern is the the relationship between id and loid in images will be
lost by reason of a pg_dump -c -b and a subsequent pg_restore causing the
BLOBs to locate into different loids.

Is there some way of constraining loid in images to ID in Large Objects?

>
>
> Howard Lowndes wrote:
>
> >My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2
> >
> >I have no difficulty inserting and managing BLOBs into the Large Object
> >system table, and I have a user table called images which maintains the
> >relationship between the BLOB loid and the identity that relates to it in
> >my user tables.  So far so good.
> >
> >When I RTFM obout psql it refers to the \lo_import, \lo_list, \lo_export
> >and \lo_unlink functions.
> >
> >The syntax for the \lo_import function indicates that a comment may be
> >appended to the BLOB entry in the large object system table.  What is not
> >mentioned is that this will only occur if psql is run as the PostgreSQL
> >superuser.
> >
> >Now, my concern is that if I use pg_dump with the --clean or --create, and
> >the --blobs options, and then try a pg_restore from the resulting archive
> >file, I believe the BLOBs will take up a different loid to the one they
> >came from, and hence the relation in my user table will be broken and I
> >will not be able to relocate the BLOBs using my identifier in my images
> >table.
> >
> >My other problem is that the various functions in PHP, namely the various
> >pg_lo_* functions do not appear to have the ability to include the comment
> >option that is available to \lo_import under psql.
> >
> >I suppose one workaround, though not very elegant, would be to use under
> >PHP something like `psql \lo_export <known_file_name>` whilst running
> >through the records in the images table, and not to use the --blobs option
> >under pg_dump, then use `psql \lo_import <known_file_name>` called from
> >PHP to reload them after a pg_restore has been run, at the same time
> >updating the loids in my images table.  As I say very inelegant.
> >
> >I guess this must be a shortfall in both PHP, in as much as it doesn't
> >appear to handle BLOBs to cleanly, and PostgreSQL in its way that it
> >handles the description column in the large opjects system table.
> >
> >Am I right or wrong, or is there a better workaround?
> >
> >
> >
>
>

--
Howard.
LANNet Computing Associates - Your Linux people <http://www.lannetlinux.com>
------------------------------------------
Flatter government, not fatter government - Get rid of the Australian states.
------------------------------------------
If all economists were laid end to end, they would not reach a conclusion
- George Bernard Shaw