Thread: 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