Lists: | pgsql-hackers |
---|
From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-08 20:45:24 |
Message-ID: | CA+U5nML4d_HJfp2+hcbYQcGA_Y30nxt+Yu-44KVtQgcSJB09+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
While fiddling with FK tuning, it was useful to be able to enable and
disable the DEFERRED mode of constraints.
That is not currently possible in SQL, so I wrote this patch. Without
this you have to drop and then re-add a constraint, which is
impractical for large tables.
e.g.
CREATE TABLE fktable (id integer, fk integer REFERENCES pktable (id));
ALTER TABLE foo
ALTER CONSTRAINT fktable_fk_fkey DEFERRED INITIALLY IMMEDIATE;
Includes docs and tests.
Currently works for FKs only. Potentially other constraints can be
supported in future.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachment | Content-Type | Size |
---|---|---|
alter_table_alter_constraint.v1.sql | application/octet-stream | 11.3 KB |
From: | Andres Freund <andres(at)2ndquadrant(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-09 12:50:17 |
Message-ID: | 20130609125017.GB24178@alap2.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 2013-06-08 21:45:24 +0100, Simon Riggs wrote:
> While fiddling with FK tuning, it was useful to be able to enable and
> disable the DEFERRED mode of constraints.
>
> That is not currently possible in SQL, so I wrote this patch. Without
> this you have to drop and then re-add a constraint, which is
> impractical for large tables.
>
> e.g.
> CREATE TABLE fktable (id integer, fk integer REFERENCES pktable (id));
>
> ALTER TABLE foo
> ALTER CONSTRAINT fktable_fk_fkey DEFERRED INITIALLY IMMEDIATE;
>
> Includes docs and tests.
>
> Currently works for FKs only. Potentially other constraints can be
> supported in future.
I haven't looked at the patch in detail, but I am very, very much in
favor of the feature in general… I have wished for this more than once,
and it certainly cost me more time working around it than it would have
cost to implement it.
Thanks,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Andres Freund <andres(at)2ndquadrant(dot)com>, Simon Riggs <simon(at)2ndQuadrant(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-09 14:49:00 |
Message-ID: | 1370789340.6240.YahooMailNeo@web162904.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2013-06-08 21:45:24 +0100, Simon Riggs wrote:
>> ALTER TABLE foo
>> ALTER CONSTRAINT fktable_fk_fkey DEFERRED INITIALLY IMMEDIATE;
> I haven't looked at the patch in detail, but I am very, very much in
> favor of the feature in general… I have wished for this more than once
+1
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From: | Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> |
---|---|
To: | Andres Freund <andres(at)2ndquadrant(dot)com> |
Cc: | Simon Riggs <simon(at)2ndQuadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-10 14:06:46 |
Message-ID: | m2ehcaawll.fsf@2ndQuadrant.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> I haven't looked at the patch in detail, but I am very, very much in
> favor of the feature in general… I have wished for this more than once,
+1
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
From: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
---|---|
To: | Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> |
Cc: | Andres Freund <andres(at)2ndquadrant(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-11 00:27:18 |
Message-ID: | CAB7nPqQEzNPVREyV5yxKA8cfo43=tVqZyCa3Ts+EKSGci6=axw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Mon, Jun 10, 2013 at 11:06 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr>wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > I haven't looked at the patch in detail, but I am very, very much in
> > favor of the feature in general… I have wished for this more than once,
>
> +1
>
+1. It will be useful.
--
Michael
From: | Abhijit Menon-Sen <ams(at)2ndQuadrant(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-24 03:58:49 |
Message-ID: | 20130624035849.GA31613@toroid.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
At 2013-06-08 21:45:24 +0100, simon(at)2ndQuadrant(dot)com wrote:
>
> ALTER TABLE foo
> ALTER CONSTRAINT fktable_fk_fkey DEFERRED INITIALLY IMMEDIATE;
I read the patch (looks good), applied it on HEAD (fine), ran make check
(fine), and played with it in a test database. It looks great, and from
previous responses it's something a lot of people have wished for.
I'm marking this ready for committer.
-- Abhijit
From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com> |
Cc: | Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-24 20:42:45 |
Message-ID: | CAMkU=1xjGqkeC4gmp_bz0YBoXznkzBvV0WUA18ZqqXRzM14big@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Sun, Jun 23, 2013 at 8:58 PM, Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com>wrote:
> At 2013-06-08 21:45:24 +0100, simon(at)2ndQuadrant(dot)com wrote:
> >
> > ALTER TABLE foo
> > ALTER CONSTRAINT fktable_fk_fkey DEFERRED INITIALLY IMMEDIATE;
>
> I read the patch (looks good), applied it on HEAD (fine), ran make check
> (fine), and played with it in a test database. It looks great, and from
> previous responses it's something a lot of people have wished for.
>
> I'm marking this ready for committer.
>
After the commit, I'm now getting the compiler warning:
tablecmds.c: In function 'ATPrepCmd':
tablecmds.c:2953: warning: 'pass' may be used uninitialized in this function
case AT_AlterConstraint (line 3130) is the only case branch that does not
set pass.
Cheers,
Jeff
From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-24 21:17:14 |
Message-ID: | CA+U5nMKVCsn0ckF4hgExF0JJxw-BsNvx3wsGe4rOEuO=FOmj1A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 24 June 2013 21:42, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Sun, Jun 23, 2013 at 8:58 PM, Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com>wrote:
>
>> At 2013-06-08 21:45:24 +0100, simon(at)2ndQuadrant(dot)com wrote:
>> >
>> > ALTER TABLE foo
>> > ALTER CONSTRAINT fktable_fk_fkey DEFERRED INITIALLY IMMEDIATE;
>>
>> I read the patch (looks good), applied it on HEAD (fine), ran make check
>> (fine), and played with it in a test database. It looks great, and from
>> previous responses it's something a lot of people have wished for.
>>
>> I'm marking this ready for committer.
>>
>
> After the commit, I'm now getting the compiler warning:
>
> tablecmds.c: In function 'ATPrepCmd':
> tablecmds.c:2953: warning: 'pass' may be used uninitialized in this
> function
>
>
> case AT_AlterConstraint (line 3130) is the only case branch that does not
> set pass.
>
The investigation is into why my current compiler didn't report that.
Thanks though.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-28 16:09:59 |
Message-ID: | CA+U5nMJCp65tmCA6j-_rCkLVHQ3NW2uG9XYc1-JnY+RUMce1ag@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 24 June 2013 22:17, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 24 June 2013 21:42, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
>> On Sun, Jun 23, 2013 at 8:58 PM, Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com>wrote:
>>
>>> At 2013-06-08 21:45:24 +0100, simon(at)2ndQuadrant(dot)com wrote:
>>> >
>>> > ALTER TABLE foo
>>> > ALTER CONSTRAINT fktable_fk_fkey DEFERRED INITIALLY IMMEDIATE;
>>>
>>> I read the patch (looks good), applied it on HEAD (fine), ran make check
>>> (fine), and played with it in a test database. It looks great, and from
>>> previous responses it's something a lot of people have wished for.
>>>
>>> I'm marking this ready for committer.
>>>
>>
>> After the commit, I'm now getting the compiler warning:
>>
>> tablecmds.c: In function 'ATPrepCmd':
>> tablecmds.c:2953: warning: 'pass' may be used uninitialized in this
>> function
>>
>>
>> case AT_AlterConstraint (line 3130) is the only case branch that does not
>> set pass.
>>
>
> The investigation is into why my current compiler didn't report that.
> Thanks though.
>
Looks like that really is a deficiency in my tool chain on OSX, rather than
some bug/user error. Even at the very latest, very shiny version.
Latest versions of gcc trap the error, so I'll have to investigate an
alternative.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
Cc: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE ... ALTER CONSTRAINT |
Date: | 2013-06-28 16:42:44 |
Message-ID: | 20130628164244.GW3757@eldon.alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Simon Riggs escribió:
> Looks like that really is a deficiency in my tool chain on OSX, rather than
> some bug/user error. Even at the very latest, very shiny version.
>
> Latest versions of gcc trap the error, so I'll have to investigate an
> alternative.
Funnily enough, on Debian Wheezy with gcc 4.7.2 I don't get the warning,
and Andres with gcc 4.7.3 (from Debian unstable) does see it. (Of
course, the 4.8 version shipped with unstable also shows it.)
Clang similarly requires pretty new versions to show the warning.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services