From: | Pantelis Theodosiou <ypercube(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Cc: | Lætitia Avrot <laetitia(dot)avrot(at)gmail(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, bpd0018(at)gmail(dot)com, vik(dot)fearing(at)2ndquadrant(dot)com, coelho(at)cri(dot)ensmp(dot)fr, pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Constraint documentation |
Date: | 2018-08-10 05:02:41 |
Message-ID: | CAE3TBxzAVxpPW-OR5ecFfDQMHuG+DEOkih6ZcnT2qPtKMOiboA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Aug 9, 2018 at 10:32 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:
> On 2018-Aug-07, Lætitia Avrot wrote:
>
> > 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.
>
> Tom Lane pointed out in another thread that the SQL standard lists
> feature F673 "Reads SQL-data routine invocations in CHECK constraints"
> which permits CHECK constraints to examine tables, so saying "you're not
> supposed to do this", while correct from a Postgres perspective, would
> be short-sighted ISTM, because we will make ourselves liars as soon as
> we implement the feature.
>
> I agree that we should point this out in *some* way, just not sure how.
> Maybe something like "Postgres does not currently support CHECK
> constraints containing queries, therefore we recommend to avoid them."
> I would not mention pg_dump by name, just say dumps may not restore
> depending on phase of moon.
>
> (BTW I'm not sure of the term "other tables". You could have a query
> that references the same table ...)
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
I like this:
> "Postgres does not currently support CHECK constraints containing
queries, therefore we recommend to avoid them."
Perhaps adding:
> CHECK constraints are currently meant to be used as *row constraints*
only.
> Use - if possible - UNIQUE or EXCLUDE constraints. for constraints that
involve many or all rows of a table,
> and FOREIGN KEY constraints for cross table constraints.
> More complex constraints will be available when ASSERTION are implemented.
And then adding some warning about using functions in CHECK constraints to
bypass current limitations.
Pantelis Theodsoiou
From | Date | Subject | |
---|---|---|---|
Next Message | Masahiko Sawada | 2018-08-10 05:06:57 | Re: Postgres 11 release notes |
Previous Message | Tatsuro Yamada | 2018-08-10 03:23:40 | Doc patch for index access method function |