Re: ALTER TABLE lock downgrades have broken pg_upgrade

On 3 May 2016 at 18:07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Or at least, it did until Simon decided that ALTER TABLE RESET
> doesn't require AccessExclusiveLock.

On reflection, this still seems like a good idea.

> Now you get a failure.
>

Failure condition as an exception to that.

> I haven't tried to construct a pre-9.1 database that would trigger
> this, but you can make it happen by applying the attached patch
> to create a toast-table-less table in the regression tests,
> and then doing "make check" in src/bin/pg_upgrade. You get this:
>
> ...
> Restoring database schemas in the new cluster
> ok
> Creating newly-required TOAST tables SQL command
> failed
> ALTER TABLE "public"."i_once_had_a_toast_table" RESET
> (binary_upgrade_dummy_option);
> ERROR: AccessExclusiveLock required to add toast table.
>
> Failure, exiting
>

It appears that pg_upgrade is depending upon an undocumented side-effect of
ALTER TABLE RESET.

I would say this side-effect should not exist, which IIUC is the same
conclusion on your latest post.

If pg_upgrade needs this, we should implement a specific function that does
what pg_upgrade needs. That way we can isolate the requirement for an
AccessExclusiveLock to the place that needs it: pg_upgrade. That will also
make it less fragile in the future. I don't think that needs a specific
command, just a function.

I accept that it is my bug and should fix it.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services