Thanks for your quick response. Sorry for the omission, the PG
version is 7.4.8
How do I temporarily disable the trigger while doing the restore?
Should I remove the trigger, do the dump, and then work from there?
It's difficult to move to 8.1 right now, so I'd prefer a workaround
if we can do that.
Claire
On Jan 12, 2006, at 10:23 AM, Tom Lane wrote:
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
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
