Hi Peter,
I understand what you're pointing at and I agree that it could be a good thing to be able to dump/restore a table without problem.
My point was that check constraints weren't supposed to be used that way theorically (or maybe i'm mistaken ?) so I thought maybe we should just inform the user that this kind of use of a check constraint is a misuse of that feature.
Maybe it's not the right way to say it. I can remove the part about pg_dump if it's too confusing...
Regards,
Lætitia
On 6/26/18 09:49, Lætitia Avrot wrote:
> + <note>
> + <para>
> + Check constraints are not designed to enforce business rules across tables.
> + Avoid using check constraints with a function accessing other tables and
> + use <xref linkend="triggers"/> instead. Although PostgreSQL won't prevent you
> + from doing so, beware that dumps generated by <application>pg_dump</application>
> + or <application>pg_dumpall</application> may be hard
> + to restore because of such checks, as the underlying dependencies are not
> + taken into account.
> + </para>
> + </note>
In a way, I think this is attacking the wrong problem. It is saying
that you should use triggers instead of check constraints in certain
circumstances. But triggers are also used as an implementation detail
of constraints. While it currently doesn't exist, a deferrable check
constraint would probably be implemented as a trigger. It's not the
triggerness that fixes this problem. The problem is more generally that
if a function uses a table, then pg_dump can't know about the ordering.
It happens to work for triggers because triggers are dumped after all
tables, as a performance optimization, and we could very well dump check
constraints differently as well.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Think! Do you really need to print this email ?
There is no Planet B.