Thread: 8.5devel: alter constraint ?

Hi,

I'm playing with 8.5devel.

My question: is it possible to alter a constraint without drop and
rebuilding it?

For instance, i have a table test with a primary key, but i have
forgotten to declare the constraint as DEFERRABLE. I can drop and
recreate that, but the index are also dropped and recreated and i think,
this is not necessary.



test=# \d test
     Table "public.test"
 Column │  Type   │ Modifiers
────────┼─────────┼───────────
 i      │ integer │ not null
Indexes:
    "test_pkey" PRIMARY KEY, btree (i)

test=# alter table test drop constraint test_pkey ;
ALTER TABLE
Time: 1,127 ms
test=*# alter table test add primary key (i) DEFERRABLE INITIALLY DEFERRED;
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_pkey" for table "test"
ALTER TABLE
Time: 281,479 ms
test=*# \d test
     Table "public.test"
 Column │  Type   │ Modifiers
────────┼─────────┼───────────
 i      │ integer │ not null
Indexes:
    "test_pkey" PRIMARY KEY, btree (i) DEFERRABLE INITIALLY DEFERRED



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Sat, 2009-11-14 at 17:42 +0100, Andreas Kretschmer wrote:
> I'm playing with 8.5devel.
>
> My question: is it possible to alter a constraint without drop and
> rebuilding it?
>
> For instance, i have a table test with a primary key, but i have
> forgotten to declare the constraint as DEFERRABLE. I can drop and
> recreate that, but the index are also dropped and recreated and i think,
> this is not necessary.
>

Right now that is not possible.

While developing my operator exclusion constraints patch, I looked into
decoupling the constraint from the underlying index, which would make
this kind of thing possible (as well as some other cool stuff, like
multiple constraints using the same index). However, it requires work,
and user-visible syntax, and the use case is uncommon.

I may explore this further in the future.

Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> wrote:

> On Sat, 2009-11-14 at 17:42 +0100, Andreas Kretschmer wrote:
> > I'm playing with 8.5devel.
> >
> > My question: is it possible to alter a constraint without drop and
> > rebuilding it?
> >
> > For instance, i have a table test with a primary key, but i have
> > forgotten to declare the constraint as DEFERRABLE. I can drop and
> > recreate that, but the index are also dropped and recreated and i think,
> > this is not necessary.
> >
>
> Right now that is not possible.

Okay, thx for the answer and good luck for your work!


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°