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

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
>> and here is the function that leads to the schema having a
>> dependancy on table data:

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.

regards, tom lane