Обсуждение: BLOBs, pg_dump & pg_restore
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
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
Howard Lowndes <lannet@lannet.com.au> writes:
> 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
No, because pg_restore has logic to adjust the references to match the
new BLOB OIDs. If you have a test case where this fails to work, let's
see it ...
> 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.
psql is out on a limb claiming that LOs can have comments --- there's no
support for that in the backend or any other client application. It's
doing it by direct manual injection of entries into the pg_description
system catalog, which is why superuser privilege is needed. It's a
useful hack if you only use psql, but still a hack. Feel free to
contribute a patch for backend COMMENT ON LARGE OBJECT support, if you'd
like to see a better level of support for this.
regards, tom lane
On Wed, 1 Oct 2003, Tom Lane wrote: > Howard Lowndes <lannet@lannet.com.au> writes: > > 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 > > No, because pg_restore has logic to adjust the references to match the > new BLOB OIDs. If you have a test case where this fails to work, let's > see it ... No, I don't have any example, it is an enquiry. What I am reading into the above however is that the loid column in my table should have a CONSTRAINT REFERENCES clause to whereever in the system large objects table. Correct? > > > 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. > > psql is out on a limb claiming that LOs can have comments --- there's no > support for that in the backend or any other client application. It's > doing it by direct manual injection of entries into the pg_description > system catalog, which is why superuser privilege is needed. It's a > useful hack if you only use psql, but still a hack. Feel free to > contribute a patch for backend COMMENT ON LARGE OBJECT support, if you'd > like to see a better level of support for this. Sorry, way beyond my competency level. -- 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
Howard Lowndes <lannet@lannet.com.au> writes:
> On Wed, 1 Oct 2003, Tom Lane wrote:
>> No, because pg_restore has logic to adjust the references to match the
>> new BLOB OIDs. If you have a test case where this fails to work, let's
>> see it ...
> No, I don't have any example, it is an enquiry. What I am reading into
> the above however is that the loid column in my table should have a
> CONSTRAINT REFERENCES clause to whereever in the system large objects
> table. Correct?
No. No doubt if Postgres had had foreign keys when the large-object stuff
was invented, it would have required such a constraint for LO
references, but it didn't and it doesn't. The pg_restore code simply
goes through all "oid" columns (and all "lo" columns if you've installed
the contrib/lo datatype) and looks for matches to LO OIDs that existed
in the dumped database. When it finds a match, it replaces that value
with the new BLOB's OID. Simple, effective, crufty ...
regards, tom lane
On Thu, 2 Oct 2003, Tom Lane wrote: > Howard Lowndes <lannet@lannet.com.au> writes: > > On Wed, 1 Oct 2003, Tom Lane wrote: > >> No, because pg_restore has logic to adjust the references to match the > >> new BLOB OIDs. If you have a test case where this fails to work, let's > >> see it ... > > > No, I don't have any example, it is an enquiry. What I am reading into > > the above however is that the loid column in my table should have a > > CONSTRAINT REFERENCES clause to whereever in the system large objects > > table. Correct? > > No. No doubt if Postgres had had foreign keys when the large-object stuff > was invented, it would have required such a constraint for LO > references, but it didn't and it doesn't. The pg_restore code simply > goes through all "oid" columns (and all "lo" columns if you've installed > the contrib/lo datatype) and looks for matches to LO OIDs that existed > in the dumped database. When it finds a match, it replaces that value > with the new BLOB's OID. Simple, effective, crufty ... OK, I'm convinced, except for one small, but not insignificant hiccup. When you dump a database with the BLOBs, even with the -c option, and then restore that database again with the -c option, you get double the BLOBs. The original BLOBs are there as are the new copies, and the cross referenced oids are updated. It looks as if there should be some way of removing the old BLOB at restore time once the new BLOB is in place. I don't know the detail of how pg_restore works but it does create a table solely for the purpose of cross referencing the oids. This of course means that each dump and subsequent restore doubles up on the BLOBs and since BLOBs are by nature Large there could be disk space problems. -- 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
Howard Lowndes <lannet@lannet.com.au> writes: > OK, I'm convinced, except for one small, but not insignificant hiccup. > When you dump a database with the BLOBs, even with the -c option, and then > restore that database again with the -c option, you get double the BLOBs. > The original BLOBs are there as are the new copies, and the cross > referenced oids are updated. It looks as if there should be some way of > removing the old BLOB at restore time once the new BLOB is in place. I > don't know the detail of how pg_restore works but it does create a table > solely for the purpose of cross referencing the oids. > > This of course means that each dump and subsequent restore doubles up on > the BLOBs and since BLOBs are by nature Large there could be disk space > problems. If you blow away the database (DROP DATABASE) and recreate it before doing the restore, those LOs will be gone. If not, something is very wrong. pg_restore basically assumes a virgin database. If you just clear out the tables before the restore, you should also clear out the pg_largeobject table. It's not hard to keep garbage LOs from hanging around by putting an ON DELETE trigger on the referencing table. -Doug
On 2 Oct 2003, Doug McNaught wrote: > Howard Lowndes <lannet@lannet.com.au> writes: > > > OK, I'm convinced, except for one small, but not insignificant hiccup. > > When you dump a database with the BLOBs, even with the -c option, and then > > restore that database again with the -c option, you get double the BLOBs. > > The original BLOBs are there as are the new copies, and the cross > > referenced oids are updated. It looks as if there should be some way of > > removing the old BLOB at restore time once the new BLOB is in place. I > > don't know the detail of how pg_restore works but it does create a table > > solely for the purpose of cross referencing the oids. > > > > This of course means that each dump and subsequent restore doubles up on > > the BLOBs and since BLOBs are by nature Large there could be disk space > > problems. > > If you blow away the database (DROP DATABASE) and recreate it before > doing the restore, those LOs will be gone. If not, something is very > wrong. pg_restore basically assumes a virgin database. > > If you just clear out the tables before the restore, you should also > clear out the pg_largeobject table. It's not hard to keep garbage LOs > from hanging around by putting an ON DELETE trigger on the referencing > table. Tks -- 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
Howard Lowndes <lannet@lannet.com.au> writes:
> When you dump a database with the BLOBs, even with the -c option, and then
> restore that database again with the -c option, you get double the BLOBs.
> The original BLOBs are there as are the new copies, and the cross
> referenced oids are updated.
Yeah. I don't believe "-c" causes anything much to be done with BLOBs.
It would be fairly risky to try, since the premise of "-c" is that you
don't want the *whole* database wiped out, only the objects you are
replacing. I'd worry about zapping BLOBs that are still referenced
elsewhere ...
regards, tom lane