From: Peter Eisentraut <peter_e@gmx.net>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: cataloguing NOT NULL constraints
Date: Sat, 06 Aug 2011 13:03:13 +0300
Message-ID: <1312624993.24721.16.camel@vanquo.pezone.net> (raw)
In-Reply-To: <CAEZATCWhTAsA7RG4BESqFXuLhmRUUEvpC0tr_qv0Eok81Xa3qQ@mail.gmail.com>
References: <20110707213401.GA27098@alvh.no-ip.org>
<CAEZATCVX792JjZHrv+CdO87GmJ1uo01AH+OQbcymspEyjyhyaQ@mail.gmail.com>
<1311291819-sup-6206@alvh.no-ip.org>
<CA+TgmoaJbuai2wiWjYUMsawZQL57wOhP7Kg9q=kkVO8qc97MQQ@mail.gmail.com>
<1311367030-sup-9426@alvh.no-ip.org>
<CA+TgmobQSR7QHfvH8fQ7dvUabQMBdFc1=Ef7ceJL9qeOm8Y1iw@mail.gmail.com>
<CAEZATCW_GXNedWnkJ6Jv98-AxtiSNL44dq-jx566JBLiye-dkg@mail.gmail.com>
<CA+TgmoaT7Np=k39aH=D=dpRYKJ=gwXmRMA029BBs0jBs--fD6w@mail.gmail.com>
<1311985058-sup-952@alvh.no-ip.org>
<CAEZATCU5mSytH6Buxp2YJ_ExwNcmbrYG8TKXc3Wg-EHLO78cWQ@mail.gmail.com>
<1312342392-sup-7189@alvh.no-ip.org>
<1312480674.24208.8.camel@vanquo.pezone.net>
<CAEZATCWhTAsA7RG4BESqFXuLhmRUUEvpC0tr_qv0Eok81Xa3qQ@mail.gmail.com>
On lör, 2011-08-06 at 08:04 +0100, Dean Rasheed wrote:
> On 4 August 2011 18:57, Peter Eisentraut <peter_e@gmx.net> wrote:
> > Have you considered just cataloging NOT NULL constraints as CHECK
> > constraints and teaching the reverse parser to convert "x CHECK (x IS
> > NOT NULL)" to "x NOT NULL". It seems to me that we're adding a whole
> > lot of hoopla here that is essentially identical to the existing CHECK
> > constraint support (it must be, per SQL standard), for no additional
> > functionality.
> >
>
> With this approach, if the user explicitly wrote "CHECK (x IS NOT
> NULL)" would it end up setting attnotnull?
Yes, I would assume so.
> Presumably, since the
> pg_constraint entry would be indistinguishable, it would be difficult
> to do otherwise. From a performance standpoint that might be a good
> thing, but it's going to be bad for compatibility.
What compatibility issue are you concerned about specifically?
> What if they wrote "CHECK (NOT x IS NULL)", or "CHECK (x IS DISTINCT
> FROM NULL)"? How many variants would it reverse parse?
Well, it's already the case that the user can write check constraints in
any number of forms that have the effect of restricting null values; and
attnotnull isn't set in those cases. So in the beginning I'd be quite
happy if we just recognized CHECK (x IS NOT NULL).
Longer term, I think we could tie this in with more general nullability
detection. For example, it is occasionally asked that we can expose
nullability through views or CREATE TABLE AS. The SQL standards has
rules for what cases we should detect (which don't include the two you
give).
> What would this do to error messages when the constraint is violated?
That's a reasonable concern, although not a fatal one, and it can be
solved in any case.
> I'm not convinced this simplifies things much; it just moves the
> complexity elsewhere, and at the cost of losing compatibility with the
> current behaviour.
No, I don't think this would lose compatibility (except perhaps in cases
of error message wording).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter_e@gmx.net, dean.a.rasheed@gmail.com, alvherre@commandprompt.com, robertmhaas@gmail.com
Subject: Re: cataloguing NOT NULL constraints
In-Reply-To: <1312624993.24721.16.camel@vanquo.pezone.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox