Re: Backing up databases with large objects DOESN'T WORK?

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Doug McNaught <doug@wireboard.com> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> writes:
> >> 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)
>
> Ah, that's why.  That's just plain wrong.  What do you think will happen
> when you get past 2 billion OIDs?

Ummm...  Disaster?  Frogs raining down from the sky?

> > fixing the former will be a royal pain (the SQL to create the tables
> > is generated by a fiendishly complicated Java app).
>
> It may be fiendishly complicated, but that doesn't make it any less
> wrong.

Agreed.  I do have source for the darn thing; I just need to figure
out the easiest way to fix it--it's one of those DB-independent object
abstraction layers that seduces you into using it because it generates
a lot of code for you...

Or maybe I'll just run a sed script over the generated SQL before
executing it.  That would be *really* nasty but a LOT easier.  ;)

> > 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?
>
> The referencing columns have to be type OID or LO (LO is not standard,
> but you might prefer it if you use any of the contrib utilities for
> LO maintenance).

Do I also need an RI constraint?  That's relatively easy to do,
especially if I'm doing the horrible sed thing.

> It is documented someplace or other, but perhaps not in the most useful
> places (ie, where you'd look in this context).

OK, I don't feel so bad.  ;)

> Come to look at it, pg_dump -C emits the *wrong thing*.  Will fix.

Cool, turned up an actual bug!

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863