Re: pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

On Fri, Jan 08, 2016 at 12:53:24PM -0500, Tom Lane wrote:

> Hm. So, by having installed this function as a check constraint, you have
> created a data dependency that pg_dump has no way to know about. It's
> going to load the tables in some order that's chosen without regard to the
> need for dem.staff to be populated first. This is not a pg_dump bug.
>
> In general, embedding lookups of other tables into CHECK constraints
> is going to cause you all kinds of grief quite aside from pg_dump
> not understanding it, because the backend doesn't really understand it
> either. If the other table changes, causing the CHECK expression to
> fail, that will *not* cause anything to happen to the table with the
> CHECK constraint. It could well be that pg_dump is loading the tables
> in the right order by chance, and the reason you're seeing a failure
> is that one or more rows have modified_by values corresponding to
> people who no longer are in the staff table.
>
> Can you get rid of dem.staff in favor of something like creating a
> "staff" role and GRANT'ing that to appropriate users?
>
> Alternatively, maybe you can make the modified_by column be a foreign
> key referencing a table of users (it probably couldn't be defined
> quite like "staff", but you get the idea). The presence of the foreign
> key would be enough to cue pg_dump about load order.

Just a crazy thought:

If I create a foreign key from *.*.modified_by towards
dem.staff.db_user but then DISABLE that FK -- would that still
cue in pg_dump to order the tables appropriately ?

Thanks,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346