From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Claire McLister <mclister(at)zeesource(dot)net> |
Cc: | pgsql general list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Large object restore problem w/triggers |
Date: | 2006-01-12 18:23:14 |
Message-ID: | 6501.1137090194@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Claire McLister <mclister(at)zeesource(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Claire McLister | 2006-01-12 18:28:24 | Re: Large object restore problem w/triggers |
Previous Message | Claire McLister | 2006-01-12 18:12:39 | Large object restore problem w/triggers |