Re: Problems with pg_upgrade after change of unix user running db.

On Mon, Nov 30, 2015 at 4:29 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Mon, Nov 30, 2015 at 08:08:50AM +0000, Benedikt Grundmann wrote:
> >
> >
> > On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver <
> adrian(dot)klaver(at)aklaver(dot)com>
> > wrote:
> >
> > On 11/27/2015 06:07 PM, Tom Lane wrote:
> > > Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> > >> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
> > >>> My guess is you are sharing the constraint name "seqno_not_null"
> with
> > >>> multiple tables. I think you are going to have to dig into the
> system
> > >>> tables to see where that is referenced and fix it.
> > >
> > >> In the post below the OP shows the tables involved(they where
> > inherited):
> > >> http://www.postgresql.org/message-id/
> > CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ(at)mail(dot)gmail(dot)com
> > >
> > > Inherited eh? Maybe related to 074c5cfbf.
> >
> >
> > I forgot to mention this earlier. This cluster is running 9.2.6 and I'm
> > attempting to upgrade to the latest 9.4.5
>
> Well, 9.4.5 we released on October 8, 2015, and the commit mentioned
> happened
> on November 20, 2015, so that fix is not in 9.4.5:
>
> commit 074c5cfbfb4923158be9ccdb77420d6522d77538
> Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Date: Fri Nov 20 14:55:28 2015 -0500
>
> Fix handling of inherited check constraints in ALTER COLUMN TYPE
> (again).
>
> The previous way of reconstructing check constraints was to do a
> separate
> "ALTER TABLE ONLY tab ADD CONSTRAINT" for each table in an inheritance
> hierarchy. However, that way has no hope of reconstructing the check
> constraints' own inheritance properties correctly, as pointed out in
> bug #13779 from Jan Dirk Zijlstra. What we should do instead is to do
> a regular "ALTER TABLE", allowing recursion, at the topmost table that
> has a particular constraint, and then suppress the work queue entries
> for inherited instances of the constraint.
>
> Annoyingly, we'd tried to fix this behavior before, in commit
> 5ed6546cf,
> but we failed to notice that it wasn't reconstructing the pg_constraint
> field values correctly.
>
> As long as I'm touching pg_get_constraintdef_worker anyway, tweak it to
> always schema-qualify the target table name; this seems like useful
> backup
> to the protections installed by commit 5f173040.
>
> In HEAD/9.5, get rid of get_constraint_relation_oids, which is now
> unused.
> (I could alternatively have modified it to also return conislocal, but
> that
> seemed like a pretty single-purpose API, so let's not pretend it has
> some
> other use.) It's unused in the back branches as well, but I left it in
> place just in case some third-party code has decided to use it.
>
> In HEAD/9.5, also rename pg_get_constraintdef_string to
> pg_get_constraintdef_command, as the previous name did nothing to
> explain
> what that entry point did differently from others (and its comment was
> equally useless). Again, that change doesn't seem like material for
> back-patching.
>
> I did a bit of re-pgindenting in tablecmds.c in HEAD/9.5, as well.
>
> Otherwise, back-patch to all supported branches.
>
> Are you able to compile from 9.4 git head and test that? It seems
> dumping inheriting contraints from parents has not worked properly for
> some time.
>

Do I need to get the latest/head 9.2 or the latest/head 9.4 or both? For
what it is worth I just tried after upgrading to the latest *released* 9.2
(and same 9.45) and that didn't work :-(

I should certainly be able to compile from source. But the upgrade to 9.4
is by far not high on my priority stack (other than maybe some speed wins
there is nothing in 9.4 that we are eager for, there are some niceties but
I can happily live without all of them for years) and has already consumed
way more time than I had scheduled for it. So I'll return to focus on
other work for at least this week and maybe more depending on how that work
goes.

Thanks to everyone I'll certainly update this thread if / when I have more
time to devote to this.

cheers,

Bene