Horton

Thread

Commits

  1. Simplify syntax for ALTER TABLE ALTER CONSTRAINT NO INHERIT

  2. Add ALTER TABLE ... ALTER CONSTRAINT ... SET [NO] INHERIT

  1. simplifying grammar for ALTER CONSTRAINT .. SET [NO] INHERIT

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-03-25T16:02:49Z

    With commit f4e53e10b6ce we introduced a way to flip the NO INHERIT bit
on not-null constraints.  However, because of the way the grammar
dealt with ALTER CONSTRAINT, we were too blind to see a way to implement
it using the existing production.  It turns out that we can remove it,
so the commands would be

ALTER TABLE tab ALTER CONSTRAINT constr INHERIT
ALTER TABLE tab ALTER CONSTRAINT constr NO INHERIT

i.e. the word SET is no longer needed.

Do people find this better?

A proposed patch is attached.  One thing not fully clear to me is that
now the entry for ALTER CONSTRAINT in the sgml docs is awkward, because
it describes the INHERIT flag separate from the deferrability flags.


After this patch, it's a bit more obvious that the error messages we're
throwing now aren't ideal:

55432 18devel 2953943=# create table foo (a int not null);
CREATE TABLE

55432 18devel 2953943=# alter table foo alter constraint foo_a_not_null no inherit deferrable;
ERROR:  constraint "foo_a_not_null" of relation "foo" is not a foreign key constraint

55432 18devel 2953943=# create table bar (a int primary key references bar);
CREATE TABLE

55432 18devel 2953943=# alter table bar alter constraint bar_a_fkey deferrable no inherit;
ERROR:  constraint "bar_a_fkey" of relation "bar" is not a not-null constraint


-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Duclós)

  2. Re: simplifying grammar for ALTER CONSTRAINT .. SET [NO] INHERIT

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-03-25T16:05:17Z

    On 2025-Mar-25, Álvaro Herrera wrote:

> With commit f4e53e10b6ce we introduced a way to flip the NO INHERIT bit
> on not-null constraints.  However, because of the way the grammar
> dealt with ALTER CONSTRAINT, we were too blind to see a way to implement
> it using the existing production.

Patch attached.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Someone said that it is at least an order of magnitude more work to do
production software than a prototype. I think he is wrong by at least
an order of magnitude."                              (Brian Kernighan)

  3. Re: simplifying grammar for ALTER CONSTRAINT .. SET [NO] INHERIT

    Peter Eisentraut <peter@eisentraut.org> — 2025-03-26T11:55:47Z

    On 25.03.25 17:02, Álvaro Herrera wrote:
> With commit f4e53e10b6ce we introduced a way to flip the NO INHERIT bit
> on not-null constraints.  However, because of the way the grammar
> dealt with ALTER CONSTRAINT, we were too blind to see a way to implement
> it using the existing production.  It turns out that we can remove it,
> so the commands would be
> 
> ALTER TABLE tab ALTER CONSTRAINT constr INHERIT
> ALTER TABLE tab ALTER CONSTRAINT constr NO INHERIT
> 
> i.e. the word SET is no longer needed.
> 
> Do people find this better?

This seems better, considering that the SQL-standard syntax for ENFORCED is:

ALTER TABLE tab ALTER CONSTRAINT constr ENFORCED
ALTER TABLE tab ALTER CONSTRAINT constr NOT ENFORCED

also without "SET".

  4. Re: simplifying grammar for ALTER CONSTRAINT .. SET [NO] INHERIT

    Suraj Kharage <suraj.kharage@enterprisedb.com> — 2025-03-26T14:23:36Z

    On Tue, Mar 25, 2025 at 9:32 PM Álvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> With commit f4e53e10b6ce we introduced a way to flip the NO INHERIT bit
> on not-null constraints.  However, because of the way the grammar
> dealt with ALTER CONSTRAINT, we were too blind to see a way to implement
> it using the existing production.  It turns out that we can remove it,
> so the commands would be
>
> ALTER TABLE tab ALTER CONSTRAINT constr INHERIT
> ALTER TABLE tab ALTER CONSTRAINT constr NO INHERIT
>
> i.e. the word SET is no longer needed.
>
> Do people find this better?
>

 Yes, I agree. As Peter said, it is now inline with other commands.

I have reviewed the patch and it looks good to me.

Since we are removing the SET keyword, how about removing that from the
below comment as well.

/*
* Propagate the change to children.  For SET NO INHERIT, we don't
* recursively affect children, just the immediate level.
*/

This is the comment from ATExecAlterConstrInheritability().

  5. Re: simplifying grammar for ALTER CONSTRAINT .. SET [NO] INHERIT

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-03-27T08:27:00Z

    On 2025-Mar-26, Suraj Kharage wrote:

>  Yes, I agree. As Peter said, it is now inline with other commands.
> 
> I have reviewed the patch and it looks good to me.

Thanks for reviewing!

> Since we are removing the SET keyword, how about removing that from the
> below comment as well.
> 
> /*
> * Propagate the change to children.  For SET NO INHERIT, we don't
> * recursively affect children, just the immediate level.
> */
> 
> This is the comment from ATExecAlterConstrInheritability().

Ah right.  Fixed that and pushed.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/