On Thu, Aug 9, 2018 at 10:32 PM, Alvaro Herrera <alvhe...@2ndquadrant.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
