473,320 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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

Nov 12 '05 #1
7 6845
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

Nov 12 '05 #2
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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #3
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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #4
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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #5
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

Nov 12 '05 #6
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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #7
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*******@postgresql.org

Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sam | last post by:
I'm having trouble restoring databases that have to lo type installed in /contrib/lo. The dump seems to work just fine, I get no errors when I execute the following command #pg_dump -Fc -o -b...
4
by: Mark Mikulec | last post by:
Hi there, I wonder if anyone can shed some light on a very frustrating problem. I'm running a debian linux 3.0 "woody" server, nothing special, with the latest version of postres that apt-get...
1
by: Ruth Hsieh | last post by:
The postgreSQL release used is 7.3.4. We would like to dump the objects for particular user. How? I used -U option, the result still dump all the objects with all the users. In order to...
0
by: Aienthiwan | last post by:
Hello all, I'm running a debian server and recently updated my version of PostgreSQL to "unstable", that being v7.4.1. I had no trouble at all getting everything up to date and going. But my...
0
by: Otto Blomqvist | last post by:
Hello ! I have a table that is created as follows Create Table file_200 (Record_number integer, Customer_Image OID; CREATE UNIQUE INDEX file_200_Record_Number_Key ON file_200 (record_number);...
0
by: Otto Blomqvist | last post by:
Hello ! I was just wondering if anyone knows if this is a bug or whats up. Im using psql 7.2.2 I can do full restores and dumps all day long, however when I do the following I run into...
1
by: Neil Zanella | last post by:
Hello, I have an SQL database which I create with: psql -f create.sql foodb I then access this database and perform several insertions, modifications, and deletions. Finally, I want to...
4
by: David Rysdam | last post by:
I have a bunch of data in Sybase and some of it is in image fields. We use bcp on this data transparently all the time without major issues in character mode. Is there a fundamental technical...
2
by: Jerry LeVan | last post by:
Hi, I am just getting into large objects and bytea "stuff". I created a small db called pictures and loaded some large objects and then tried to do a restore. Here is how I got the dump. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.