BLOBs, pg_dump & pg_restore

[Howard Lowndes]

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_nam e>` 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_nam e>` 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Howard Lowndes <la****@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Howard Lowndes]

On Wed, 1 Oct 2003, Tom Lane wrote:

Howard Lowndes <la****@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Howard Lowndes <la****@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Howard Lowndes]

On Thu, 2 Oct 2003, Tom Lane wrote:

Howard Lowndes <la****@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Doug McNaught]

Howard Lowndes <la****@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Howard Lowndes]

On 2 Oct 2003, Doug McNaught wrote:

Howard Lowndes <la****@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Howard Lowndes <la****@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org