Re: Pg_upgrade and toast tables bug discovered

On Thu, Jul 3, 2014 at 11:55:40PM +0200, Andres Freund wrote:
> 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.

I had not considered this.

I don't remember ever seeing such a report. We have had oid mismatch
reports, but we now know the cause of those.

> 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.

Wow, that is going to be kind of odd in that there really isn't a good
way to create toast tables except perhaps add a dummy TEXT column and
remove it. There also isn't an easy way to not create a toast table,
but also find out that one was needed. I suppose we would have to
insert some dummy value in the toast pg_class column and come back later
to clean it up.

I am wondering what the probability of having a table that didn't need a
toast table in the old cluster, and needed one in the new cluster, and
there being an oid collision.

I think the big question is whether we want to go down that route.

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

+ Everyone has their own god. +