Re: Pg_upgrade and toast tables bug discovered

On 2014-07-03 17:09:41 -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > I have had trouble getting a table schema that is causing problems, but
> > received a report via EDB support recently that had a simple schema
> > (anonymized):
> > ...
> > needs_toast_table() computes the length of this table as 2024 bytes in
> > 9.0, and 2064 bytes on 9.1, with the TOAST threshold being 2032 bytes.
>
> > My initial idea is to to allow for toast tables in the new cluster that
> > aren't in the old cluster by skipping over the extra toast tables. This
> > would only be for pre-9.1 old clusters.
>
> TBH, it has never been more than the shakiest of assumptions that the new
> version could not create toast tables where the old one hadn't. I think
> you should just allow for that case, independently of specific PG
> versions. Fortunately it seems easy enough, since you certainly don't
> need to put any data into the new toast tables.

I don't think it's just that simple unfortunately. If pg_class entries
get created that didn't exist on the old server there's a chance for oid
conflicts. Consider

SELECT binary_upgrade.set_next_heap_pg_class_oid('17094'::pg_catalog.oid);
CREATE TABLE table_without_toast_in_old_server(...);
-- heap oid 17094, toast oid 16384

SELECT binary_upgrade.set_next_heap_pg_class_oid('16384'::pg_catalog.oid);
CREATE TABLE another_table(...);
ERROR: could not create file ...: File exists

I think we even had reports of such a problem.

The most robust, but not trivial, approach seems to be to prevent toast
table creation if there wasn't a set_next_toast_pg_class_oid(). Then,
after all relations are created, iterate over all pg_class entries that
possibly need toast tables and recheck if they now might need one.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services