Re: pg_upgrade < 9.3 -> >=9.3 misses a step around multixacts

On Thu, Jun 19, 2014 at 06:04:25PM -0400, Alvaro Herrera wrote:
> Bruce Momjian wrote:
>
> > I wasn't happy with having that delete code added there when we do
> > directory delete in the function above. I instead broke apart the
> > delete and copy code and called the delete code where needed, in the
> > attached patch.
>
> Makes sense, yeah. I didn't look closely enough to realize that the
> function that does the copying also does the rmtree part.

OK. Should I apply my patch so at least pg_upgrade is good going
forward?

> I also now realize why the other case (upgrade from 9.3 to 9.4) does not
> have a bug: we are already deleting the files in that path.

Right, and I think the patch makes it clearer why we need those 'rm'
function calls because they mirror the 'copy' ones.

> > OK, so the xid has to be beyond 2^31 during pg_upgrade to trigger a
> > problem? That might explain the rare reporting of this bug. What would
> > the test query look like so we can tell people when to remove the '0000'
> > files? Would we need to see the existence of '0000' and high-numbered
> > files? How high? What does a 2^31 file look like?
>
> I misspoke.
>
> I ran a few more upgrades, and then tried vacuuming all databases, which
> is when the truncate code is run. Say the original cluster had an
> oldestmulti of 10 million. If you just run VACUUM in the new cluster
> after the upgrade, the 0000 file is not deleted: it's not yet old enough
> in terms of multixact age. An error is not thrown, because we're still
> not attempting a truncate. But if you lower the
> vacuum_multixact_freeze_table_age to 10 million minus one, then we will
> try the deletion and that will raise the error.
>
> I think (didn't actually try) if you just let 150 million multixacts be
> generated, that's the first time you will get the error.
>
> Now if you run a VACUUM FREEZE after the upgrade, the file will be
> deleted with no error.
>
> I now think that the reason most people haven't hit the problem is that
> they don't generate enough multis after upgrading a database that had
> enough multis in the old database. This seems a bit curious

OK, that does make more sense. A user would need to have the to
exceeded 0000 to the point where it was removed from their old cluster,
and _then_ run far enough past the freeze horizon to again require file
removal. This does make sense why we are seeing the bug only now, and
while a quick minor release with a query to fix this will get us out of
the problem with minimal impact.

> > Also, is there a reason you didn't remove the 'members/0000' file in your
> > patch? I have removed it in my version.
>
> There's no point. That file is the starting point for new multis
> anyway, and it's compatible with the new format (because it's all
> zeroes).

I think it should be done for consistency with the 'copy' function calls
above.

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

+ Everyone has their own god. +