Thread: ALTER TABLE .... make constraint DEFERRABLE

ALTER TABLE .... make constraint DEFERRABLE

From
Simon Riggs
Date:
Deferrable unique constraints seem an interesting feature, though I have
either some questions or some issues, not sure which.

I don't seem to be able to find any way to do an ALTER TABLE that adds
this new capability to an existing table.

There is no way to add a constraint via a CREATE TABLE AS SELECT, so
that means there is no way to use the feature at all in that case.

Also, foreign keys can't be defined that refer to a deferrable primary
key. That isn't mentioned at all in the manual with regard to the
DEFERRABLE clause, though it is mentioned in the FK section. You get
this error message
ERROR:  cannot use a deferrable unique constraint for referenced table

The use case for this feature looks a little narrow at present. Can we
do something about usability?

Am I missing something?

-- Simon Riggs           www.2ndQuadrant.com



Re: ALTER TABLE .... make constraint DEFERRABLE

From
Tom Lane
Date:
Simon Riggs <simon@2ndQuadrant.com> writes:
> Am I missing something?

That we're long past feature freeze?  Some of these might be reasonable
TODO items.  None of them are happening for 9.0.
        regards, tom lane


Re: ALTER TABLE .... make constraint DEFERRABLE

From
Bruce Momjian
Date:
Simon Riggs wrote:
> 
> Deferrable unique constraints seem an interesting feature, though I have
> either some questions or some issues, not sure which.
> 
> I don't seem to be able to find any way to do an ALTER TABLE that adds
> this new capability to an existing table.

I was able to do it:
test=> create table test (x int unique DEFERRABLE INITIALLY DEFERRED);NOTICE:  CREATE TABLE / UNIQUE will create
implicitindex "test_x_key"for table "test"CREATE TABLE
 
test=> alter table test add column y int;ALTER TABLE
test=> alter table test add unique (y) DEFERRABLE INITIALLY DEFERRED;NOTICE:  ALTER TABLE / ADD UNIQUE will create
implicitindex"test_y_key" for table "test"ALTER TABLE
 

Is that what you were asking?

> There is no way to add a constraint via a CREATE TABLE AS SELECT, so
> that means there is no way to use the feature at all in that case.

Uh, CREATE TABLE AS SELECT seems to be very limited, but I have not
heard any complaints about it before.

> Also, foreign keys can't be defined that refer to a deferrable primary
> key. That isn't mentioned at all in the manual with regard to the
> DEFERRABLE clause, though it is mentioned in the FK section. You get
> this error message
> ERROR:  cannot use a deferrable unique constraint for referenced table
> 
> The use case for this feature looks a little narrow at present. Can we
> do something about usability?

Not sure why that was a limitation.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + None of us is going to be here forever. +


Re: ALTER TABLE .... make constraint DEFERRABLE

From
Dean Rasheed
Date:
On 3 June 2010 02:06, Bruce Momjian <bruce@momjian.us> wrote:
>> Also, foreign keys can't be defined that refer to a deferrable primary
>> key. That isn't mentioned at all in the manual with regard to the
>> DEFERRABLE clause, though it is mentioned in the FK section. You get
>> this error message
>> ERROR:  cannot use a deferrable unique constraint for referenced table
>>
>> The use case for this feature looks a little narrow at present. Can we
>> do something about usability?
>
> Not sure why that was a limitation.
>

That's in accordance with the SQL spec.

I didn't think of this case originally, but all sorts of complications
would arise if we were to allow FKs to refer to deferrable PKs. For
example, if there are 2 temporarily duplicated PKs, and you update one
of them, what would the FK's ON UPDATE actions do? I'm not convinced
there is any sensible answer to this question.

Regards,
Dean


Re: ALTER TABLE .... make constraint DEFERRABLE

From
Simon Riggs
Date:
On Wed, 2010-06-02 at 21:06 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> > 
> > Deferrable unique constraints seem an interesting feature, though I have
> > either some questions or some issues, not sure which.
> > 
> > I don't seem to be able to find any way to do an ALTER TABLE that adds
> > this new capability to an existing table.
> 
> I was able to do it:
> 
>     test=> create table test (x int unique DEFERRABLE INITIALLY DEFERRED);
>     NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_x_key"
>     for table "test"
>     CREATE TABLE
> 
>     test=> alter table test add column y int;
>     ALTER TABLE
> 
>     test=> alter table test add unique (y) DEFERRABLE INITIALLY DEFERRED;
>     NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index
>     "test_y_key" for table "test"
>     ALTER TABLE
> 
> Is that what you were asking?

No. I wanted to defer an existing UNIQUE constraint. That doesn't seem
to be possible. You *can* add a whole new constraint and then drop the
old one, though that's not quite as cool.

> > There is no way to add a constraint via a CREATE TABLE AS SELECT, so
> > that means there is no way to use the feature at all in that case.
> 
> Uh, CREATE TABLE AS SELECT seems to be very limited, but I have not
> heard any complaints about it before.
> 
> > Also, foreign keys can't be defined that refer to a deferrable primary
> > key. That isn't mentioned at all in the manual with regard to the
> > DEFERRABLE clause, though it is mentioned in the FK section. You get
> > this error message
> > ERROR:  cannot use a deferrable unique constraint for referenced table
> > 
> > The use case for this feature looks a little narrow at present. Can we
> > do something about usability?
> 
> Not sure why that was a limitation.

Regrettably it makes it an unusable limitation for many people.

All large tables are referenced in a typical database that uses PKs/FKs.

-- Simon Riggs           www.2ndQuadrant.com



Re: ALTER TABLE .... make constraint DEFERRABLE

From
Bruce Momjian
Date:
Simon Riggs wrote:
> On Wed, 2010-06-02 at 21:06 -0400, Bruce Momjian wrote:
> > Simon Riggs wrote:
> > > 
> > > Deferrable unique constraints seem an interesting feature, though I have
> > > either some questions or some issues, not sure which.
> > > 
> > > I don't seem to be able to find any way to do an ALTER TABLE that adds
> > > this new capability to an existing table.
> > 
> > I was able to do it:
> > 
> >     test=> create table test (x int unique DEFERRABLE INITIALLY DEFERRED);
> >     NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_x_key"
> >     for table "test"
> >     CREATE TABLE
> > 
> >     test=> alter table test add column y int;
> >     ALTER TABLE
> > 
> >     test=> alter table test add unique (y) DEFERRABLE INITIALLY DEFERRED;
> >     NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index
> >     "test_y_key" for table "test"
> >     ALTER TABLE
> > 
> > Is that what you were asking?
> 
> No. I wanted to defer an existing UNIQUE constraint. That doesn't seem
> to be possible. You *can* add a whole new constraint and then drop the
> old one, though that's not quite as cool.

Do we allow any kind of constraint modification via ALTER TABLE?  I
don't see much listed in the manual.

> > > There is no way to add a constraint via a CREATE TABLE AS SELECT, so
> > > that means there is no way to use the feature at all in that case.
> > 
> > Uh, CREATE TABLE AS SELECT seems to be very limited, but I have not
> > heard any complaints about it before.
> > 
> > > Also, foreign keys can't be defined that refer to a deferrable primary
> > > key. That isn't mentioned at all in the manual with regard to the
> > > DEFERRABLE clause, though it is mentioned in the FK section. You get
> > > this error message
> > > ERROR:  cannot use a deferrable unique constraint for referenced table
> > > 
> > > The use case for this feature looks a little narrow at present. Can we
> > > do something about usability?
> > 
> > Not sure why that was a limitation.
> 
> Regrettably it makes it an unusable limitation for many people.
> 
> All large tables are referenced in a typical database that uses PKs/FKs.

Yeah, no question.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + None of us is going to be here forever. +