On Fri, Jan 08, 2016 at 12:53:24PM -0500, Tom Lane wrote:
> 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.
That has pretty much been the very intent of the constraint function:
Allowing only "postgres", the owner of the database, or
people _currently_ on staff to insert/update table data.
There may well be database accounts which used to be
associated with staff rows but are no longer listed as staff
(because they aren't). There will still be table data
associated with those accounts - their former staff entries
can be gotten from the audit system (that's why dem.staff
itself is being audited).
I realize that being able to foreign key into system tables
would not have helped with the part where only _current_
staff is to insert into/update data tables. That's why I
haven't moaned about it but rather written my own
(misguided?) attempt at enforcing such a constraint.
Would I be better of rewriting the constraint as an ON INSERT
OR UPDATE trigger ?
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346