Re: Pg_upgrade and toast tables bug discovered

On Fri, Jul 11, 2014 at 12:18:40AM -0400, Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > On Thu, Jul 10, 2014 at 06:38:26PM -0400, Bruce Momjian wrote:
>
> > > I have thought some more on this. I thought I would need to open
> > > pg_class in C and do complex backend stuff, but I now realize I can do
> > > it from libpq, and just call ALTER TABLE and I think that always
> > > auto-checks if a TOAST table is needed. All I have to do is query
> > > pg_class from libpq, then construct ALTER TABLE commands for each item,
> > > and it will optionally create the TOAST table if needed. I just have to
> > > use a no-op ALTER TABLE command, like SET STATISTICS.
> >
> > Attached is the backend part of the patch. I will work on the
> > pg_upgrade/libpq/ALTER TABLE part later.
>
> Uh, why does this need to be in ALTER TABLE? Can't this be part of
> table creation done by pg_dump?

Uh, I think you need to read the thread. We have to delay the toast
creation part so we don't use an oid that will later be required by
another table from the old cluster. This has to be done after all
tables have been created.

We could have pg_dump spit out those ALTER lines at the end of the dump,
but it seems simpler to do it in pg_upgrade.

Even if we have pg_dump create all the tables that require pre-assigned
TOAST oids first, then the other tables that _might_ need a TOAST table,
those later tables might create a toast oid that matches a later
non-TOAST-requiring table, so I don't think that fixes the problem.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +