Skip site navigation (1) Skip section navigation (2)

Re: how to create a non-inherited CHECK constraint in CREATE TABLE

From: Nikhil Sontakke <nikkhils(at)gmail(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Jim Nasby <jim(at)nasby(dot)net>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: how to create a non-inherited CHECK constraint in CREATE TABLE
Date: 2012-04-11 18:07:45
Message-ID: CANgU5Zf=8wjAaD7gFD_Q6ZcsKDAKHFBN_MWaFpenYVagdGMk=A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

So, I have a patch for this. This patch introduces support for

CHECK ONLY syntax while doing a CREATE TABLE as well as during the usual
ALTER TABLE command.

Example:

create table atacc7 (test int, test2 int CHECK ONLY (test>0), CHECK
(test2>10));
create table atacc8 () inherits (atacc7);

postgres=# \d+ atacc7
                Table "public.atacc7"
 Column |  Type   | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
 test   | integer |           | plain   |
 test2  | integer |           | plain   |
Check constraints:
    "atacc7_test2_check" CHECK (test2 > 10)
    "atacc7_test_check" CHECK ONLY (test > 0)
Child tables: atacc8
Has OIDs: no

postgres=# \d+ atacc8
                Table "public.atacc8"
 Column |  Type   | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
 test   | integer |           | plain   |
 test2  | integer |           | plain   |
Check constraints:
    "atacc7_test2_check" CHECK (test2 > 10)
Inherits: atacc7
Has OIDs: no


This patch removes the support for :

ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0);

and uses

ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK ONLY (b > 0);

Is this what we want? Or we would want the earlier support in place for
backward compatibility as well? We are actually introducing this in 9.2 so
I guess we can remove this.

This is a much cleaner implementation and we might not even need the
changes in pg_dump now because the pg_get_constraintdef can provide the
info about the ONLY part too. So some cleanup can be done if needed.

I know it's a bit late in the commitfest, but if this patch makes this
feature more "complete", maybe we should consider...

Thoughts?

P.S Here's the discussion thread in its entirety for reference:
http://postgresql.1045698.n5.nabble.com/how-to-create-a-non-inherited-CHECK-constraint-in-CREATE-TABLE-td5152184.html

Regards,
Nikhils

On Thu, Feb 2, 2012 at 1:32 AM, Peter Eisentraut
<peter_e(at)gmx(dot)net> wrote:

> On ons, 2012-01-18 at 18:17 -0500, Robert Haas wrote:
> > I agree with Peter that we should have we should have CHECK ONLY.
> > ONLY is really a property of the constraint, not the ALTER TABLE
> > command -- if it were otherwise, we wouldn't need to store it the
> > system catalogs, but of course we do.  The fact that it's not a
> > standard property isn't a reason not to have proper syntax for it.
>
> Clearly, we will eventually want to support inherited and non-inherited
> constraints of all types.  Currently, each type of constraint has an
> implicit default regarding this property:
>
> check - inherited
> not null - inherited
> foreign key - not inherited
> primary key - not inherited
> unique - not inherited
> exclusion - not inherited
>
> As discussed above, we need to have a syntax that is attached to the
> constraint, not the table operation that creates the constraint, so that
> we can also create these in CREATE TABLE.
>
> How should we resolve these different defaults?
>
> Also, in ALTER TABLE, if you want to add either an inherited or not
> inherited constraint to a parent table, you should really say ALTER
> TABLE ONLY in either case.  Because it's conceivably valid that ALTER
> TABLE foo ADD CHECK () NOINHERIT would add an independent, not inherited
> check constraint to each child table.
>
> So, there are all kinds of inconsistencies and backward compatibility
> problems lurking here.  We might need either a grand transition plan or
> document the heck out of these inconsistencies.
>
>
>

Attachment: check_constraint_create_table_support.patch
Description: application/octet-stream (19.8 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2012-04-11 18:14:11
Subject: Re: [GENERAL] [streaming replication] 9.1.3 streaming replication bug ?
Previous:From: Robert HaasDate: 2012-04-11 17:59:15
Subject: Re: Last gasp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group