Обсуждение: Large object restore problem w/triggers

Hi,

  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?

  Thanks for your help.

Claire

PS. The dump command was pg_dump --b -Ft -o db > db.tar
         Restore command: pg_restore -O -d db db.tar

  --
  Claire McLister                        mclister@zeesource.net
  1684 Nightingale Avenue     Suite 201
  Sunnyvale, CA 94087            408-733-2737(fax)

                      http://www.zeemaps.com

Claire McLister <mclister@zeesource.net> 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

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@zeesource.net> 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

Claire McLister <mclister@zeesource.net> writes:
> 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?

Yeah, it looks like that's the easiest answer in 7.4 :-(.  I had thought
that pg_restore's --disable-triggers might help you, but it looks like
it won't fire unless you use a data-only restore, which is generally not
a preferable way to do things.

            regards, tom lane