Claire McLister <mclister@xxxxxxxxxxxxx> writes: > We have a database with a bunch of large objects, who's ids we > reference in a table. There is a trigger associated with inserts and > updates on the table to delete the old value when inserting a new > large object associated with a row in the table. > This causes a problem when doing a pg_dump and pg_restore. The dump > works fine, but when doing a restore it tries to trigger a delete of > an old large object. It seems that the object id is associated with > the database that was dumped, and not the one that was restored. So, > lo_unlink fails and the whole restore aborts. > Has anyone seen this behavior before? Am I doing something wrong? > Is there a workaround for this? You haven't said which PG version you're using. Pre-8.1, the deal is this: you can never have the same large object OIDs in the new database as you did in the old. There is code in pg_dump/pg_restore to try to update large-object references after the data load step. A trigger doing what you describe would probably break that update step, but you could work around it by disabling the trigger temporarily. (I thought that pg_restore was designed to not install user triggers until after it'd done the OID updating, but maybe this recollection is wrong.) 8.1 has a much nicer approach, which is that there's a variant of lo_create that allows a large object to be reloaded with the same OID it had before. This eliminates the need for the update step in pg_restore. If you're having problems in 8.1 then I'd speculate that maybe there's a logic bug in your trigger. regards, tom lane
