Re: BLOBs, pg_dump & pg_restore

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