Tom Lane <tgl@sss.pgh.pa.us> writes:
> > This of course breaks the references to pg_largeobject in my other
> > tables.
>
> pg_restore is supposed to take care of fixing those for you. If that
> failed, we should investigate why.
Well, the column type of the referencing field is int4 (rather than
OID) and there is no RI constraint. I can add the latter by hand, but
fixing the former will be a royal pain (the SQL to create the tables
is generated by a fiendishly complicated Java app).
Given the above, it's not surprising to me that pg_restore doesn't fix
the reference--what needs to be changed in my schema?
> > $ pg_dump -b -Fc mydb > outfile
> > $ dropdb mydb
> > $ createdb mydb
> > $ pg_restore -d mydb < outfile
> > pg_restore: [archiver (db)] could not execute query: ERROR: function plpgsql_call_handler already exists with same
argumenttypes
>
> Hmm, do you have plpgsql installed into template1? You are supposed to
> use template0 as the template when creating a database to be restored
> by pg_restore.
I must have missed that--it's certainly not mentioned in the 'pg_dump'
and 'pg_restore' manpages, and the 'CREATE DATABASE' reference
doesn't directly mention the issue (though it talks about the
difference between template0 and template1).
Makes sense once I think about it.
> It occurs to me that pg_restore shouldn't necessarily abandon ship after
> getting an SQL error. The normal behavior of a SQL-script-type pg_dump
> dump is that it'll keep plugging after an error, and this frequently
> is good not bad (eg, GRANTs to nonexistent users shouldn't abort the
> restore).
Agreed.
Tom, as usual you come through. If you're ever in Atlanta I will buy
you many beers. :)
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
