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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Doug McNaught <doug(at)wireboard(dot)com> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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