[PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

[PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Matheus de Oliveira
Hi all.

I attached a patch to add support for changing ON UPDATE/DELETE actions of a constraint using ALTER TABLE ... ALTER CONSTRAINT.

Besides that, there is a another change in this patch on current ALTER CONSTRAINT about deferrability options. Previously, if the user did ALTER CONSTRAINT without specifying an option on deferrable or initdeferred, it was implied the default options, so this:

    ALTER TABLE tbl
    ALTER CONSTRAINT con_name;

Was equivalent to:

    ALTER TABLE tbl
    ALTER CONSTRAINT con_name NOT DEFERRABLE INITIALLY IMMEDIATE;

If I kept it that way, it means that changing only ON UPDATE or ON DELETE would cause deferrability options to be changed to the default. Now, I keep an information of which options has actually been changed, so only the actual changes are persisted.

But there are two exceptions (which I think that make sense):
1. If the user does only `ALTER CONSTRAINT ... INITIALLY DEFERRED`, no matter the previous value of deferrable, it will be set to true.
2. If the user does only `ALTER CONSTRAINT ... NOT DEFERRABLE`, no matter the previous value of initdeferred, it will be set to false.

I have pondered to raise an exception in the above cases instead of forcing deferrable/initdeferred to valid values, but since the same behavior happens on ADD CONSTRAINT, I think this way is simpler.

Since I'm a newbie on PG source code, this patch seems to be a bit big for me. So please, let me know what you think about it. Specially the change on Constraint struct on parsenode.h (and support to it on copyfuncs.c and outfuncs.c), I'm not 100% sure that is the best way to track if deferrability options were changed.

Thanks a lot.

Regards,
--
Matheus de Oliveira



postgresql-alter-constraint.v1.patch (226K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

fabriziomello


On Tue, Feb 20, 2018 at 12:10 PM, Matheus de Oliveira <[hidden email]> wrote:

>
> Hi all.
>
> I attached a patch to add support for changing ON UPDATE/DELETE actions of a constraint using ALTER TABLE ... ALTER CONSTRAINT.
>
> Besides that, there is a another change in this patch on current ALTER CONSTRAINT about deferrability options. Previously, if the user did ALTER CONSTRAINT without specifying an option on deferrable or initdeferred, it was implied the default options, so this:
>
>     ALTER TABLE tbl
>     ALTER CONSTRAINT con_name;
>
> Was equivalent to:
>
>     ALTER TABLE tbl
>     ALTER CONSTRAINT con_name NOT DEFERRABLE INITIALLY IMMEDIATE;
>
> If I kept it that way, it means that changing only ON UPDATE or ON DELETE would cause deferrability options to be changed to the default. Now, I keep an information of which options has actually been changed, so only the actual changes are persisted.
>
> But there are two exceptions (which I think that make sense):
> 1. If the user does only `ALTER CONSTRAINT ... INITIALLY DEFERRED`, no matter the previous value of deferrable, it will be set to true.
> 2. If the user does only `ALTER CONSTRAINT ... NOT DEFERRABLE`, no matter the previous value of initdeferred, it will be set to false.
>
> I have pondered to raise an exception in the above cases instead of forcing deferrable/initdeferred to valid values, but since the same behavior happens on ADD CONSTRAINT, I think this way is simpler.
>
> Since I'm a newbie on PG source code, this patch seems to be a bit big for me. So please, let me know what you think about it. Specially the change on Constraint struct on parsenode.h (and support to it on copyfuncs.c and outfuncs.c), I'm not 100% sure that is the best way to track if deferrability options were changed.
>
> Thanks a lot.
>

Great! 

I didn't read your patch yet but make sure to register it to the next open commitfest.

Regards,


--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Matheus de Oliveira


On Tue, Feb 20, 2018 at 12:38 PM, Fabrízio de Royes Mello <[hidden email]> wrote:


...

I didn't read your patch yet but make sure to register it to the next open commitfest.

Thanks a lot Fabrízio, I've done that already [1].

Please let me know if I did something wrong, and if you see improvements on the patch ;)



Regards,
--
Matheus de Oliveira


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Andres Freund
In reply to this post by Matheus de Oliveira
Hi,


On 2018-02-20 12:10:22 -0300, Matheus de Oliveira wrote:
> I attached a patch to add support for changing ON UPDATE/DELETE actions of
> a constraint using ALTER TABLE ... ALTER CONSTRAINT.

This patch has been submitted to the last commitfest for v11 and is not
a trivial patch. As we don't accept such patches this late, it should be
moved to the next fest.  Any arguments against?

Greetings,

Andres Freund

Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Peter Eisentraut-6
In reply to this post by Matheus de Oliveira
On 2/20/18 10:10, Matheus de Oliveira wrote:

> Besides that, there is a another change in this patch on current ALTER
> CONSTRAINT about deferrability options. Previously, if the user did
> ALTER CONSTRAINT without specifying an option on deferrable or
> initdeferred, it was implied the default options, so this:
>
>     ALTER TABLE tbl
>     ALTER CONSTRAINT con_name;
>
> Was equivalent to:
>
>     ALTER TABLE tbl
>     ALTER CONSTRAINT con_name NOT DEFERRABLE INITIALLY IMMEDIATE;

Oh, that seems wrong.  Probably, it shouldn't even accept that syntax
with an empty options list, let alone reset options that are not
mentioned.  Can you prepare a separate patch for this issue?

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

David Steele
Hi Matheus,

On 3/3/18 1:32 PM, Peter Eisentraut wrote:

> On 2/20/18 10:10, Matheus de Oliveira wrote:
>> Besides that, there is a another change in this patch on current ALTER
>> CONSTRAINT about deferrability options. Previously, if the user did
>> ALTER CONSTRAINT without specifying an option on deferrable or
>> initdeferred, it was implied the default options, so this:
>>
>>     ALTER TABLE tbl
>>     ALTER CONSTRAINT con_name;
>>
>> Was equivalent to:
>>
>>     ALTER TABLE tbl
>>     ALTER CONSTRAINT con_name NOT DEFERRABLE INITIALLY IMMEDIATE;
>
> Oh, that seems wrong.  Probably, it shouldn't even accept that syntax
> with an empty options list, let alone reset options that are not
> mentioned.  Can you prepare a separate patch for this issue?

Can you prepare the patch that Peter has requested and post on a new
thread?  Please respond here with the reference (or email me directly)
and I will add to the CF.

Meanwhile, I'll push this patch to the next CF as Andres has
recommended, hearing no arguments to the contrary.

Thanks,
--
-David
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Matheus de Oliveira
In reply to this post by Andres Freund


Em 2 de mar de 2018 08:15, "Andres Freund" <[hidden email]> escreveu:
Hi,


On 2018-02-20 12:10:22 -0300, Matheus de Oliveira wrote:
> I attached a patch to add support for changing ON UPDATE/DELETE actions of
> a constraint using ALTER TABLE ... ALTER CONSTRAINT.

This patch has been submitted to the last commitfest for v11 and is not
a trivial patch. As we don't accept such patches this late, it should be
moved to the next fest.  Any arguments against?

Sorry. My bad.

I'm OK with sending this to the next one.

Best regards,
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Matheus de Oliveira
In reply to this post by Peter Eisentraut-6


Em 3 de mar de 2018 19:32, "Peter Eisentraut" <[hidden email]> escreveu:
On 2/20/18 10:10, Matheus de Oliveira wrote:
> Besides that, there is a another change in this patch on current ALTER
> CONSTRAINT about deferrability options. Previously, if the user did
> ALTER CONSTRAINT without specifying an option on deferrable or
> initdeferred, it was implied the default options, so this:
>
>     ALTER TABLE tbl
>     ALTER CONSTRAINT con_name;
>
> Was equivalent to:
>
>     ALTER TABLE tbl
>     ALTER CONSTRAINT con_name NOT DEFERRABLE INITIALLY IMMEDIATE;

Oh, that seems wrong.  Probably, it shouldn't even accept that syntax
with an empty options list, let alone reset options that are not
mentioned.

Yeah, it felt really weird when I noticed it. And I just noticed while reading the source.

Can
you prepare a separate patch for this issue?
 
I can do that, no problem. It'll take awhile though, I'm on a trip and will be home around March 20th.

You think this should be applied to all versions that support ALTER CONSTRAINT, right?

Thanks.

Best regards,
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Álvaro Herrera
Matheus de Oliveira wrote:

> You think this should be applied to all versions that support ALTER
> CONSTRAINT, right?

This seems a bug fix to me, so yes.

> I can do that, no problem. It'll take awhile though, I'm on a trip and will
> be home around March 20th.

Please do send at your earliest convenient time.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Ashutosh Bapat
In reply to this post by Matheus de Oliveira
On Wed, Mar 7, 2018 at 11:49 PM, Matheus de Oliveira
<[hidden email]> wrote:

>
>
> Em 3 de mar de 2018 19:32, "Peter Eisentraut"
> <[hidden email]> escreveu:
>
> On 2/20/18 10:10, Matheus de Oliveira wrote:
>> Besides that, there is a another change in this patch on current ALTER
>> CONSTRAINT about deferrability options. Previously, if the user did
>> ALTER CONSTRAINT without specifying an option on deferrable or
>> initdeferred, it was implied the default options, so this:
>>
>>     ALTER TABLE tbl
>>     ALTER CONSTRAINT con_name;
>>
>> Was equivalent to:
>>
>>     ALTER TABLE tbl
>>     ALTER CONSTRAINT con_name NOT DEFERRABLE INITIALLY IMMEDIATE;
>
> Oh, that seems wrong.  Probably, it shouldn't even accept that syntax
> with an empty options list, let alone reset options that are not
> mentioned.
>
>
> Yeah, it felt really weird when I noticed it. And I just noticed while
> reading the source.
>
> Can
>
> you prepare a separate patch for this issue?
>
>
> I can do that, no problem. It'll take awhile though, I'm on a trip and will
> be home around March 20th.

Matheus,
When do you think you can provide the patch for bug fix?

Also, the patch you originally posted doesn't apply cleanly. Can you
please post a rebased version?

The patch contains 70 odd lines of  test SQL and 3600 odd lines of
output. The total patch is 4200 odd lines. I don't think that it will
be acceptable to add that huge an output to the regression test. You
will need to provide a patch with much smaller output addition and may
be a smaller test as well.

>
> You think this should be applied to all versions that support ALTER
> CONSTRAINT, right?

I think so.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Matheus de Oliveira
Hi all.

Sorry about the long delay.

On Tue, Jul 10, 2018 at 10:17 AM Ashutosh Bapat <[hidden email]> wrote:
On Wed, Mar 7, 2018 at 11:49 PM, Matheus de Oliveira
<[hidden email]> wrote:
>
>
> Em 3 de mar de 2018 19:32, "Peter Eisentraut"
> <[hidden email]> escreveu:
>
> On 2/20/18 10:10, Matheus de Oliveira wrote:
>> Besides that, there is a another change in this patch on current ALTER
>> CONSTRAINT about deferrability options. Previously, if the user did
>> ALTER CONSTRAINT without specifying an option on deferrable or
>> initdeferred, it was implied the default options, so this:
>>
>>     ALTER TABLE tbl
>>     ALTER CONSTRAINT con_name;
>>
>> Was equivalent to:
>>
>>     ALTER TABLE tbl
>>     ALTER CONSTRAINT con_name NOT DEFERRABLE INITIALLY IMMEDIATE;
>
> Oh, that seems wrong.  Probably, it shouldn't even accept that syntax
> with an empty options list, let alone reset options that are not
> mentioned.
>
>
> Yeah, it felt really weird when I noticed it. And I just noticed while
> reading the source.
>
> Can
>
> you prepare a separate patch for this issue?
>
>
> I can do that, no problem. It'll take awhile though, I'm on a trip and will
> be home around March 20th.

Matheus,
When do you think you can provide the patch for bug fix?


Attached the patch for the bug fix, all files with naming `postgresql-fix-alter-constraint-${version}.patch`, with `${version}` since 9.4, where ALTER CONSTRAINT was introduced.

Not sure if you want to apply to master/12 as well (since the other patch applies that as well), but I've attached it anyway, so you can decide.
 
Also, the patch you originally posted doesn't apply cleanly. Can you
please post a rebased version?


Attached the rebased version that applies to current master, `postgresql-alter-constraint.v3.patch`.
 
The patch contains 70 odd lines of  test SQL and 3600 odd lines of
output. The total patch is 4200 odd lines. I don't think that it will
be acceptable to add that huge an output to the regression test. You
will need to provide a patch with much smaller output addition and may
be a smaller test as well.


You are correct. I have made a test that tries all combinations of ALTER CONSTRAINT ON UPDATE/DELETE ACTION, but it caused a really huge output. I have changed that to a simple DO block, and still trying all possibilities but now I just verify if the ALTER matches the same definition on pg_trigger of a constraint that was created with the target action already, seems simpler and work for any change.

Please, let me know if you all think any change should be made on this patch.

Best regards,
--
Matheus de Oliveira



postgresql-fix-alter-constraint-9.5.patch (15K) Download Attachment
postgresql-fix-alter-constraint-9.4.patch (15K) Download Attachment
postgresql-fix-alter-constraint-9.6.patch (15K) Download Attachment
postgresql-fix-alter-constraint-11.patch (15K) Download Attachment
postgresql-fix-alter-constraint-10.patch (15K) Download Attachment
postgresql-fix-alter-constraint-12.patch (15K) Download Attachment
postgresql-alter-constraint.v2.patch (36K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Michael Paquier-2
On Mon, Sep 17, 2018 at 12:03:17AM -0300, Matheus de Oliveira wrote:
> You are correct. I have made a test that tries all combinations of ALTER
> CONSTRAINT ON UPDATE/DELETE ACTION, but it caused a really huge output. I
> have changed that to a simple DO block, and still trying all possibilities
> but now I just verify if the ALTER matches the same definition on
> pg_trigger of a constraint that was created with the target action already,
> seems simpler and work for any change.
>
> Please, let me know if you all think any change should be made on this
> patch.

The last patch set does not apply, so this is moved to next CF, waiting
on author as new status.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Matheus de Oliveira


On Tue, Oct 2, 2018 at 3:40 AM Michael Paquier <[hidden email]> wrote:

The last patch set does not apply, so this is moved to next CF, waiting
on author as new status.

Updated the last patch so it can apply cleanly on HEAD.

About the bugfixes, do you think it is better to move to another thread?
 
Best regards,
--
Matheus de Oliveira



postgresql-alter-constraint.v3.patch (40K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Dmitry Dolgov
> On Sun, Oct 14, 2018 at 8:30 PM Matheus de Oliveira <[hidden email]> wrote:
>
> Updated the last patch so it can apply cleanly on HEAD.
>
> About the bugfixes, do you think it is better to move to another thread?

I think it makes sense, this way discussions on two relatively different topics
will not interfere with each other. I would even suggest to create a new CF
item with "bugfix" type to emphasize it.

Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

José Arthur Benetasso Villanova
In reply to this post by Matheus de Oliveira

On Sun, 14 Oct 2018, Matheus de Oliveira wrote:

>
>
> Updated the last patch so it can apply cleanly on HEAD.
>
>

Hi Matheus.

I applied your patch on top of bb874e30fbf9e85bdb117bad34865a5fae29dbf6.

It compiled, worked as expected, but some tests broke executing make
check:

test create_table                 ... FAILED
      constraints                  ... FAILED
      inherit                      ... FAILED
      foreign_data                 ... FAILED
      alter_table                  ... FAILED

I didn't test the bugfix, just the v3 patch.

--
José Arthur
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Andres Freund
Hi,

On 2019-01-13 20:22:32 -0200, José Arthur Benetasso Villanova wrote:

>
> On Sun, 14 Oct 2018, Matheus de Oliveira wrote:
>
> >
> >
> > Updated the last patch so it can apply cleanly on HEAD.
> >
> >
>
> Hi Matheus.
>
> I applied your patch on top of bb874e30fbf9e85bdb117bad34865a5fae29dbf6.
>
> It compiled, worked as expected, but some tests broke executing make check:
>
> test create_table                 ... FAILED
>      constraints                  ... FAILED
>      inherit                      ... FAILED
>      foreign_data                 ... FAILED
>      alter_table                  ... FAILED
>
> I didn't test the bugfix, just the v3 patch.

Given that the patch hasn't been updated since, and the CF has ended,
I'm marking this patch as returned with feedback. Please resubmit once
that's fixed.

Greetings,

Andres Freund

Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Matheus de Oliveira


On Sun, Feb 3, 2019 at 8:28 AM Andres Freund <[hidden email]> wrote:

>
> It compiled, worked as expected, but some tests broke executing make check:
>
> test create_table                 ... FAILED
>      constraints                  ... FAILED
>      inherit                      ... FAILED
>      foreign_data                 ... FAILED
>      alter_table                  ... FAILED
>
> I didn't test the bugfix, just the v3 patch.

Given that the patch hasn't been updated since, and the CF has ended,
I'm marking this patch as returned with feedback. Please resubmit once
that's fixed.


Hi all.

Sorry for the long delay. I've rebased the patch to current master (at f2004f19ed now), attached as postgresql-alter-constraint.v4.patch. All tests passed cleanly.

Best regards,
--
Matheus de Oliveira



postgresql-alter-constraint.v4.patch (40K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Thomas Munro-5
On Tue, Mar 19, 2019 at 1:04 PM Matheus de Oliveira
<[hidden email]> wrote:
> Sorry for the long delay. I've rebased the patch to current master (at f2004f19ed now), attached as postgresql-alter-constraint.v4.patch. All tests passed cleanly.

Hi Matheus,

As the commitfest is starting, could you please send a rebased patch?

Thanks,

--
Thomas Munro
https://enterprisedb.com


Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Matheus de Oliveira


On Mon, Jul 1, 2019 at 6:21 AM Thomas Munro <[hidden email]> wrote:

Hi Matheus,

As the commitfest is starting, could you please send a rebased patch?


Hi all,

Glad to start working on that again... Follows the rebased version (at 5925e55498).

Thank you all.

Best regards,
--
Matheus de Oliveira



postgresql-alter-constraint.v5.patch (40K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [PATCH] Add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT

Tom Lane-2
Matheus de Oliveira <[hidden email]> writes:
> [ postgresql-alter-constraint.v5.patch ]

Somebody seems to have marked this Ready For Committer without posting
any review, which is not very kosher, but I took a quick look at it
anyway.

* It's failing to apply, as noted by the cfbot, because somebody added
an unrelated test to the same spot in foreign_key.sql.  I fixed that
in the attached rebase.

* It also doesn't pass "git diff --check" whitespace checks, so
I ran it through pgindent.

* Grepping around for other references to struct Constraint,
I noticed that you'd missed updating equalfuncs.c.  I did not
fix that.

The main issue I've got though is a definitional one --- I'm not at all
sold on your premise that constraint deferrability syntax should mean
different things depending on the previous state of the constraint.
We don't generally act that way in other ALTER commands and I don't see
a strong argument to start doing so here.  If you didn't do this then
you wouldn't (I think) need the extra struct Constraint fields in the
first place, which is why I didn't run off and change equalfuncs.c.

In short, I'm inclined to argue that this variant of ALTER TABLE
should replace *all* the fields of the constraint with the same
properties it'd have if you'd created it fresh using the same syntax.
This is by analogy to CREATE OR REPLACE commands, which don't
preserve any of the old properties of the replaced object.  Given
the interactions between these fields, I think you're going to end up
with a surprising mess of ad-hoc choices if you do differently.
Indeed, you already have, but I think it'll get worse if anyone
tries to extend the feature set further.

Perhaps the right way to attack it, given that, is to go ahead and
invent "ALTER TABLE t ADD OR REPLACE CONSTRAINT c ...".  At least
in the case at hand with FK constraints, we could apply suitable
optimizations (ie skip revalidation) when the new definition shares
the right properties with the old, and otherwise treat it like a
drop-and-add.

                        regards, tom lane


diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 90bf195..198c640 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -55,7 +55,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
     ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
     ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
     ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
-    ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+    ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
+      [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]
+      [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
     VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
     DROP CONSTRAINT [ IF EXISTS ]  <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
     DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fb2be10..f897986 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9012,8 +9012,43 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
  errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
  cmdcon->conname, RelationGetRelationName(rel))));
 
+ /*
+ * Verify for FKCONSTR_ACTION_UNKNOWN, if found, replace by current
+ * action. We could handle FKCONSTR_ACTION_UNKNOWN bellow, but since we
+ * already have to handle the case of changing to the same action, seems
+ * simpler to simple replace FKCONSTR_ACTION_UNKNOWN by the current action
+ * here.
+ */
+ if (cmdcon->fk_del_action == FKCONSTR_ACTION_UNKNOWN)
+ cmdcon->fk_del_action = currcon->confdeltype;
+
+ if (cmdcon->fk_upd_action == FKCONSTR_ACTION_UNKNOWN)
+ cmdcon->fk_upd_action = currcon->confupdtype;
+
+ /*
+ * Do the same for deferrable attributes. But consider that if changed
+ * only initdeferred attribute and to true, force deferrable to be also
+ * true. On the other hand, if changed only deferrable attribute and to
+ * false, force initdeferred to be also false.
+ */
+ if (!cmdcon->was_deferrable_set)
+ cmdcon->deferrable = cmdcon->initdeferred ? true : currcon->condeferrable;
+
+ if (!cmdcon->was_initdeferred_set)
+ cmdcon->initdeferred = !cmdcon->deferrable ? false : currcon->condeferred;
+
+ /*
+ * This is a safe check only, should never happen here.
+ */
+ if (cmdcon->initdeferred && !cmdcon->deferrable)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE")));
+
  if (currcon->condeferrable != cmdcon->deferrable ||
- currcon->condeferred != cmdcon->initdeferred)
+ currcon->condeferred != cmdcon->initdeferred ||
+ currcon->confdeltype != cmdcon->fk_del_action ||
+ currcon->confupdtype != cmdcon->fk_upd_action)
  {
  HeapTuple copyTuple;
  HeapTuple tgtuple;
@@ -9031,6 +9066,8 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
  copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
  copy_con->condeferrable = cmdcon->deferrable;
  copy_con->condeferred = cmdcon->initdeferred;
+ copy_con->confdeltype = cmdcon->fk_del_action;
+ copy_con->confupdtype = cmdcon->fk_upd_action;
  CatalogTupleUpdate(conrel, &copyTuple->t_self, copyTuple);
 
  InvokeObjectPostAlterHook(ConstraintRelationId,
@@ -9067,23 +9104,106 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
  otherrelids = list_append_unique_oid(otherrelids,
  tgform->tgrelid);
 
- /*
- * Update deferrability of RI_FKey_noaction_del,
- * RI_FKey_noaction_upd, RI_FKey_check_ins and RI_FKey_check_upd
- * triggers, but not others; see createForeignKeyActionTriggers
- * and CreateFKCheckTrigger.
- */
- if (tgform->tgfoid != F_RI_FKEY_NOACTION_DEL &&
- tgform->tgfoid != F_RI_FKEY_NOACTION_UPD &&
- tgform->tgfoid != F_RI_FKEY_CHECK_INS &&
- tgform->tgfoid != F_RI_FKEY_CHECK_UPD)
- continue;
-
  copyTuple = heap_copytuple(tgtuple);
  copy_tg = (Form_pg_trigger) GETSTRUCT(copyTuple);
 
+ /*
+ * Set deferrability here, but note that it may be overridden
+ * bellow if the pg_trigger entry is on the referencing table and
+ * depending on the action used for ON UPDATE/DELETE. But for
+ * check triggers (in the referenced table) it is kept as is
+ * (since ON UPDATE/DELETE actions makes no difference for the
+ * check triggers).
+ */
  copy_tg->tgdeferrable = cmdcon->deferrable;
  copy_tg->tginitdeferred = cmdcon->initdeferred;
+
+ /*
+ * Set ON DELETE action
+ */
+ if (tgform->tgfoid == F_RI_FKEY_NOACTION_DEL ||
+ tgform->tgfoid == F_RI_FKEY_RESTRICT_DEL ||
+ tgform->tgfoid == F_RI_FKEY_CASCADE_DEL ||
+ tgform->tgfoid == F_RI_FKEY_SETNULL_DEL ||
+ tgform->tgfoid == F_RI_FKEY_SETDEFAULT_DEL)
+ {
+ switch (cmdcon->fk_del_action)
+ {
+ case FKCONSTR_ACTION_NOACTION:
+ copy_tg->tgdeferrable = cmdcon->deferrable;
+ copy_tg->tginitdeferred = cmdcon->initdeferred;
+ copy_tg->tgfoid = F_RI_FKEY_NOACTION_DEL;
+ break;
+ case FKCONSTR_ACTION_RESTRICT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_RESTRICT_DEL;
+ break;
+ case FKCONSTR_ACTION_CASCADE:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_CASCADE_DEL;
+ break;
+ case FKCONSTR_ACTION_SETNULL:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETNULL_DEL;
+ break;
+ case FKCONSTR_ACTION_SETDEFAULT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETDEFAULT_DEL;
+ break;
+ default:
+ elog(ERROR, "unrecognized FK action type: %d",
+ (int) cmdcon->fk_del_action);
+ break;
+ }
+ }
+
+ /*
+ * Set ON UPDATE action
+ */
+ if (tgform->tgfoid == F_RI_FKEY_NOACTION_UPD ||
+ tgform->tgfoid == F_RI_FKEY_RESTRICT_UPD ||
+ tgform->tgfoid == F_RI_FKEY_CASCADE_UPD ||
+ tgform->tgfoid == F_RI_FKEY_SETNULL_UPD ||
+ tgform->tgfoid == F_RI_FKEY_SETDEFAULT_UPD)
+ {
+ switch (cmdcon->fk_upd_action)
+ {
+ case FKCONSTR_ACTION_NOACTION:
+ copy_tg->tgdeferrable = cmdcon->deferrable;
+ copy_tg->tginitdeferred = cmdcon->initdeferred;
+ copy_tg->tgfoid = F_RI_FKEY_NOACTION_UPD;
+ break;
+ case FKCONSTR_ACTION_RESTRICT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_RESTRICT_UPD;
+ break;
+ case FKCONSTR_ACTION_CASCADE:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_CASCADE_UPD;
+ break;
+ case FKCONSTR_ACTION_SETNULL:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETNULL_UPD;
+ break;
+ case FKCONSTR_ACTION_SETDEFAULT:
+ copy_tg->tgdeferrable = false;
+ copy_tg->tginitdeferred = false;
+ copy_tg->tgfoid = F_RI_FKEY_SETDEFAULT_UPD;
+ break;
+ default:
+ elog(ERROR, "unrecognized FK action type: %d",
+ (int) cmdcon->fk_upd_action);
+ break;
+ }
+ }
+
  CatalogTupleUpdate(tgrel, &copyTuple->t_self, copyTuple);
 
  InvokeObjectPostAlterHook(TriggerRelationId, currcon->oid, 0);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 6414ade..5c524e3 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2906,6 +2906,8 @@ _copyConstraint(const Constraint *from)
  COPY_SCALAR_FIELD(deferrable);
  COPY_SCALAR_FIELD(initdeferred);
  COPY_LOCATION_FIELD(location);
+ COPY_SCALAR_FIELD(was_deferrable_set);
+ COPY_SCALAR_FIELD(was_initdeferred_set);
  COPY_SCALAR_FIELD(is_no_inherit);
  COPY_NODE_FIELD(raw_expr);
  COPY_STRING_FIELD(cooked_expr);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 86c31a4..7e4ec65 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3445,6 +3445,8 @@ _outConstraint(StringInfo str, const Constraint *node)
  WRITE_BOOL_FIELD(deferrable);
  WRITE_BOOL_FIELD(initdeferred);
  WRITE_LOCATION_FIELD(location);
+ WRITE_BOOL_FIELD(was_deferrable_set);
+ WRITE_BOOL_FIELD(was_initdeferred_set);
 
  appendStringInfoString(str, " :contype ");
  switch (node->contype)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c97bb36..690f94f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -185,7 +185,8 @@ static void SplitColQualList(List *qualList,
  List **constraintList, CollateClause **collClause,
  core_yyscan_t yyscanner);
 static void processCASbits(int cas_bits, int location, const char *constrType,
-   bool *deferrable, bool *initdeferred, bool *not_valid,
+   bool *deferrable, bool *was_deferrable_set,
+   bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
    bool *no_inherit, core_yyscan_t yyscanner);
 static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
@@ -543,7 +544,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <ival> TableLikeOptionList TableLikeOption
 %type <list> ColQualList
 %type <node> ColConstraint ColConstraintElem ConstraintAttr
-%type <ival> key_actions key_delete key_match key_update key_action
+%type <ival> key_actions opt_key_actions
+%type <ival> key_delete key_match key_update key_action
 %type <ival> ConstraintAttributeSpec ConstraintAttributeElem
 %type <str> ExistingIndex
 
@@ -2275,7 +2277,7 @@ alter_table_cmd:
  $$ = (Node *)n;
  }
  /* ALTER TABLE <name> ALTER CONSTRAINT ... */
- | ALTER CONSTRAINT name ConstraintAttributeSpec
+ | ALTER CONSTRAINT name opt_key_actions ConstraintAttributeSpec
  {
  AlterTableCmd *n = makeNode(AlterTableCmd);
  Constraint *c = makeNode(Constraint);
@@ -2283,9 +2285,11 @@ alter_table_cmd:
  n->def = (Node *) c;
  c->contype = CONSTR_FOREIGN; /* others not supported, yet */
  c->conname = $3;
- processCASbits($4, @4, "ALTER CONSTRAINT statement",
- &c->deferrable,
- &c->initdeferred,
+ c->fk_upd_action = (char) ($4 >> 8);
+ c->fk_del_action = (char) ($4 & 0xFF);
+ processCASbits($5, @4, "ALTER CONSTRAINT statement",
+ &c->deferrable, &c->was_deferrable_set,
+ &c->initdeferred, &c->was_initdeferred_set,
  NULL, NULL, yyscanner);
  $$ = (Node *)n;
  }
@@ -3642,7 +3646,7 @@ ConstraintElem:
  n->raw_expr = $3;
  n->cooked_expr = NULL;
  processCASbits($5, @5, "CHECK",
-   NULL, NULL, &n->skip_validation,
+   NULL, NULL, NULL, NULL, &n->skip_validation,
    &n->is_no_inherit, yyscanner);
  n->initially_valid = !n->skip_validation;
  $$ = (Node *)n;
@@ -3659,8 +3663,8 @@ ConstraintElem:
  n->indexname = NULL;
  n->indexspace = $7;
  processCASbits($8, @8, "UNIQUE",
-   &n->deferrable, &n->initdeferred, NULL,
-   NULL, yyscanner);
+   &n->deferrable, NULL, &n->initdeferred, NULL,
+   NULL, NULL, yyscanner);
  $$ = (Node *)n;
  }
  | UNIQUE ExistingIndex ConstraintAttributeSpec
@@ -3674,8 +3678,8 @@ ConstraintElem:
  n->indexname = $2;
  n->indexspace = NULL;
  processCASbits($3, @3, "UNIQUE",
-   &n->deferrable, &n->initdeferred, NULL,
-   NULL, yyscanner);
+   &n->deferrable, NULL, &n->initdeferred, NULL,
+   NULL, NULL, yyscanner);
  $$ = (Node *)n;
  }
  | PRIMARY KEY '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
@@ -3690,8 +3694,8 @@ ConstraintElem:
  n->indexname = NULL;
  n->indexspace = $8;
  processCASbits($9, @9, "PRIMARY KEY",
-   &n->deferrable, &n->initdeferred, NULL,
-   NULL, yyscanner);
+   &n->deferrable, NULL, &n->initdeferred, NULL,
+   NULL, NULL, yyscanner);
  $$ = (Node *)n;
  }
  | PRIMARY KEY ExistingIndex ConstraintAttributeSpec
@@ -3705,8 +3709,8 @@ ConstraintElem:
  n->indexname = $3;
  n->indexspace = NULL;
  processCASbits($4, @4, "PRIMARY KEY",
-   &n->deferrable, &n->initdeferred, NULL,
-   NULL, yyscanner);
+   &n->deferrable, NULL, &n->initdeferred, NULL,
+   NULL, NULL, yyscanner);
  $$ = (Node *)n;
  }
  | EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
@@ -3724,8 +3728,8 @@ ConstraintElem:
  n->indexspace = $8;
  n->where_clause = $9;
  processCASbits($10, @10, "EXCLUDE",
-   &n->deferrable, &n->initdeferred, NULL,
-   NULL, yyscanner);
+   &n->deferrable, NULL, &n->initdeferred, NULL,
+   NULL, NULL, yyscanner);
  $$ = (Node *)n;
  }
  | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name
@@ -3741,7 +3745,8 @@ ConstraintElem:
  n->fk_upd_action = (char) ($10 >> 8);
  n->fk_del_action = (char) ($10 & 0xFF);
  processCASbits($11, @11, "FOREIGN KEY",
-   &n->deferrable, &n->initdeferred,
+   &n->deferrable, NULL,
+   &n->initdeferred, NULL,
    &n->skip_validation, NULL,
    yyscanner);
  n->initially_valid = !n->skip_validation;
@@ -3821,7 +3826,7 @@ ExclusionWhereClause:
  * We combine the update and delete actions into one value temporarily
  * for simplicity of parsing, and then break them down again in the
  * calling production.  update is in the left 8 bits, delete in the right.
- * Note that NOACTION is the default.
+ * Note that NOACTION is the default. See also opt_key_actions.
  */
 key_actions:
  key_update
@@ -3836,6 +3841,23 @@ key_actions:
  { $$ = (FKCONSTR_ACTION_NOACTION << 8) | (FKCONSTR_ACTION_NOACTION & 0xFF); }
  ;
 
+/*
+ * Basically the same as key_actions, but using FKCONSTR_ACTION_UNKNOWN
+ * as the default one instead of NOACTION.
+ */
+opt_key_actions:
+ key_update
+ { $$ = ($1 << 8) | (FKCONSTR_ACTION_UNKNOWN & 0xFF); }
+ | key_delete
+ { $$ = (FKCONSTR_ACTION_UNKNOWN << 8) | ($1 & 0xFF); }
+ | key_update key_delete
+ { $$ = ($1 << 8) | ($2 & 0xFF); }
+ | key_delete key_update
+ { $$ = ($2 << 8) | ($1 & 0xFF); }
+ | /*EMPTY*/
+ { $$ = (FKCONSTR_ACTION_UNKNOWN << 8) | (FKCONSTR_ACTION_UNKNOWN & 0xFF); }
+ ;
+
 key_update: ON UPDATE key_action { $$ = $3; }
  ;
 
@@ -5361,8 +5383,8 @@ CreateTrigStmt:
  n->transitionRels = NIL;
  n->isconstraint  = true;
  processCASbits($10, @10, "TRIGGER",
-   &n->deferrable, &n->initdeferred, NULL,
-   NULL, yyscanner);
+   &n->deferrable, NULL, &n->initdeferred, NULL,
+   NULL, NULL, yyscanner);
  n->constrrel = $9;
  $$ = (Node *)n;
  }
@@ -16218,7 +16240,8 @@ SplitColQualList(List *qualList,
  */
 static void
 processCASbits(int cas_bits, int location, const char *constrType,
-   bool *deferrable, bool *initdeferred, bool *not_valid,
+   bool *deferrable, bool *was_deferrable_set,
+   bool *initdeferred, bool *was_initdeferred_set, bool *not_valid,
    bool *no_inherit, core_yyscan_t yyscanner)
 {
  /* defaults */
@@ -16229,6 +16252,14 @@ processCASbits(int cas_bits, int location, const char *constrType,
  if (not_valid)
  *not_valid = false;
 
+ if (was_deferrable_set)
+ *was_deferrable_set = cas_bits & (CAS_DEFERRABLE
+  | CAS_NOT_DEFERRABLE) ? true : false;
+
+ if (was_initdeferred_set)
+ *was_initdeferred_set = cas_bits & (CAS_INITIALLY_DEFERRED
+ | CAS_INITIALLY_IMMEDIATE) ? true : false;
+
  if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED))
  {
  if (deferrable)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 94ded3c..a6d70d4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2117,6 +2117,8 @@ typedef enum ConstrType /* types of constraints */
 #define FKCONSTR_ACTION_CASCADE 'c'
 #define FKCONSTR_ACTION_SETNULL 'n'
 #define FKCONSTR_ACTION_SETDEFAULT 'd'
+#define FKCONSTR_ACTION_UNKNOWN 'u' /* unknown is used only for ALTER
+ * CONSTRAINT */
 
 /* Foreign key matchtype codes */
 #define FKCONSTR_MATCH_FULL 'f'
@@ -2134,6 +2136,10 @@ typedef struct Constraint
  bool initdeferred; /* INITIALLY DEFERRED? */
  int location; /* token location, or -1 if unknown */
 
+ /* Fields used by ALTER CONSTRAINT to verify if a change was actually made */
+ bool was_deferrable_set; /* Was DEFERRABLE informed? */
+ bool was_initdeferred_set; /* Was INITIALLY DEFERRED informed? */
+
  /* Fields used for constraints with expressions (CHECK and DEFAULT): */
  bool is_no_inherit; /* is constraint non-inheritable? */
  Node   *raw_expr; /* expr, as untransformed parse tree */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index e5407bb..32ea16c 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -736,6 +736,28 @@ ORDER BY 1,2,3;
  fknd2   | "RI_FKey_check_upd" |     17 | f            | f
 (12 rows)
 
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+                                      pg_get_constraintdef                                      
+-------------------------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+                             pg_get_constraintdef                            
+------------------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE DEFERRABLE
+(1 row)
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+                       pg_get_constraintdef                        
+-------------------------------------------------------------------
+ FOREIGN KEY (ftest1) REFERENCES pktable(ptest1) ON DELETE CASCADE
+(1 row)
+
 -- temp tables should go away by themselves, need not drop them.
 -- test check constraint adding
 create table atacc1 ( test int );
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index f0ecf4b..4f226aa 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -2394,3 +2394,127 @@ DROP SCHEMA fkpart8 CASCADE;
 NOTICE:  drop cascades to 2 other objects
 DETAIL:  drop cascades to table fkpart8.tbl1
 drop cascades to table fkpart8.tbl2
+-- ALTER CONSTRAINT changing ON UPDATE/DELETE.
+-- Try all combinations and validate the diff with a created constraint
+CREATE SCHEMA createtest; -- created constraints with target action, validation
+CREATE SCHEMA altertest;  -- created with source and altered to target, test
+DO
+$test_alter_con$
+DECLARE
+    v_result json;
+    method text;
+    from_action text;
+    to_action text;
+BEGIN
+    FOR method, from_action, to_action IN
+        WITH act(action) AS (
+            SELECT unnest('{NO ACTION,RESTRICT,CASCADE,SET DEFAULT,SET NULL}'::text[])
+        )
+        SELECT
+            m.method, a1.action, a2.action
+        FROM unnest('{UPDATE,DELETE}'::text[]) AS m(method), act a1, act a2
+    LOOP
+        EXECUTE format(
+            $sql$
+                 -- Alter from ON %1$s %2$s to ON %1$s %3$s
+                CREATE TABLE createtest.foo(id integer primary key);
+                CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON %1$s %3$s, val text);
+
+                CREATE TABLE altertest.foo(id integer primary key);
+                INSERT INTO altertest.foo VALUES(0),(1),(2),(3);
+
+                CREATE TABLE altertest.bar(foo_id integer DEFAULT 0 REFERENCES altertest.foo ON %1$s %2$s, val text);
+
+                ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON %1$s %3$s;
+
+            $sql$, method, from_action, to_action);
+
+        SELECT json_agg(t)
+        INTO v_result
+        FROM (
+            -- Do EXCEPT of the "altertest" and "createtest" constraints, if they are equal (as expected), it should return empty
+            SELECT
+                rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+                tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+                regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+            FROM pg_trigger tg
+                JOIN pg_constraint con ON con.oid = tg.tgconstraint
+                JOIN pg_class rel ON tg.tgrelid = rel.oid
+            WHERE tg.tgrelid IN ('altertest.foo'::regclass, 'altertest.bar'::regclass)
+            EXCEPT
+            SELECT
+                rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+                tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+                regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+            FROM pg_trigger tg
+                JOIN pg_constraint con ON con.oid = tg.tgconstraint
+                JOIN pg_class rel ON tg.tgrelid = rel.oid
+            WHERE tg.tgrelid IN ('createtest.foo'::regclass, 'createtest.bar'::regclass)
+        ) t;
+
+        DROP TABLE createtest.bar;
+        DROP TABLE createtest.foo;
+        DROP TABLE altertest.bar;
+        DROP TABLE altertest.foo;
+
+        IF (v_result IS NULL) THEN
+            RAISE INFO 'ON % from % to %: OK.', method, from_action, to_action;
+        ELSE
+            RAISE EXCEPTION 'ON % from % to %. FAILED! Unmatching rows: %', method, from_action, to_action, v_result;
+        END IF;
+    END LOOP;
+END;
+$test_alter_con$
+;
+INFO:  ON UPDATE from NO ACTION to NO ACTION: OK.
+INFO:  ON UPDATE from RESTRICT to NO ACTION: OK.
+INFO:  ON UPDATE from CASCADE to NO ACTION: OK.
+INFO:  ON UPDATE from SET DEFAULT to NO ACTION: OK.
+INFO:  ON UPDATE from SET NULL to NO ACTION: OK.
+INFO:  ON DELETE from NO ACTION to NO ACTION: OK.
+INFO:  ON DELETE from RESTRICT to NO ACTION: OK.
+INFO:  ON DELETE from CASCADE to NO ACTION: OK.
+INFO:  ON DELETE from SET DEFAULT to NO ACTION: OK.
+INFO:  ON DELETE from SET NULL to NO ACTION: OK.
+INFO:  ON UPDATE from NO ACTION to RESTRICT: OK.
+INFO:  ON UPDATE from RESTRICT to RESTRICT: OK.
+INFO:  ON UPDATE from CASCADE to RESTRICT: OK.
+INFO:  ON UPDATE from SET DEFAULT to RESTRICT: OK.
+INFO:  ON UPDATE from SET NULL to RESTRICT: OK.
+INFO:  ON DELETE from NO ACTION to RESTRICT: OK.
+INFO:  ON DELETE from RESTRICT to RESTRICT: OK.
+INFO:  ON DELETE from CASCADE to RESTRICT: OK.
+INFO:  ON DELETE from SET DEFAULT to RESTRICT: OK.
+INFO:  ON DELETE from SET NULL to RESTRICT: OK.
+INFO:  ON UPDATE from NO ACTION to CASCADE: OK.
+INFO:  ON UPDATE from RESTRICT to CASCADE: OK.
+INFO:  ON UPDATE from CASCADE to CASCADE: OK.
+INFO:  ON UPDATE from SET DEFAULT to CASCADE: OK.
+INFO:  ON UPDATE from SET NULL to CASCADE: OK.
+INFO:  ON DELETE from NO ACTION to CASCADE: OK.
+INFO:  ON DELETE from RESTRICT to CASCADE: OK.
+INFO:  ON DELETE from CASCADE to CASCADE: OK.
+INFO:  ON DELETE from SET DEFAULT to CASCADE: OK.
+INFO:  ON DELETE from SET NULL to CASCADE: OK.
+INFO:  ON UPDATE from NO ACTION to SET DEFAULT: OK.
+INFO:  ON UPDATE from RESTRICT to SET DEFAULT: OK.
+INFO:  ON UPDATE from CASCADE to SET DEFAULT: OK.
+INFO:  ON UPDATE from SET DEFAULT to SET DEFAULT: OK.
+INFO:  ON UPDATE from SET NULL to SET DEFAULT: OK.
+INFO:  ON DELETE from NO ACTION to SET DEFAULT: OK.
+INFO:  ON DELETE from RESTRICT to SET DEFAULT: OK.
+INFO:  ON DELETE from CASCADE to SET DEFAULT: OK.
+INFO:  ON DELETE from SET DEFAULT to SET DEFAULT: OK.
+INFO:  ON DELETE from SET NULL to SET DEFAULT: OK.
+INFO:  ON UPDATE from NO ACTION to SET NULL: OK.
+INFO:  ON UPDATE from RESTRICT to SET NULL: OK.
+INFO:  ON UPDATE from CASCADE to SET NULL: OK.
+INFO:  ON UPDATE from SET DEFAULT to SET NULL: OK.
+INFO:  ON UPDATE from SET NULL to SET NULL: OK.
+INFO:  ON DELETE from NO ACTION to SET NULL: OK.
+INFO:  ON DELETE from RESTRICT to SET NULL: OK.
+INFO:  ON DELETE from CASCADE to SET NULL: OK.
+INFO:  ON DELETE from SET DEFAULT to SET NULL: OK.
+INFO:  ON DELETE from SET NULL to SET NULL: OK.
+DROP SCHEMA createtest;
+DROP SCHEMA altertest;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 99af0b8..5223142 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -528,6 +528,16 @@ FROM pg_trigger JOIN pg_constraint con ON con.oid = tgconstraint
 WHERE tgrelid = 'fktable'::regclass
 ORDER BY 1,2,3;
 
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 INITIALLY IMMEDIATE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
+ALTER TABLE FKTABLE ALTER CONSTRAINT fkdi2 NOT DEFERRABLE;
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'fktable'::regclass AND conname = 'fkdi2';
+
 -- temp tables should go away by themselves, need not drop them.
 
 -- test check constraint adding
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index b67bef0..ae8b235 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1679,3 +1679,80 @@ INSERT INTO fkpart8.tbl2 VALUES(1);
 ALTER TABLE fkpart8.tbl2 DROP CONSTRAINT tbl2_f1_fkey;
 COMMIT;
 DROP SCHEMA fkpart8 CASCADE;
+
+-- ALTER CONSTRAINT changing ON UPDATE/DELETE.
+-- Try all combinations and validate the diff with a created constraint
+CREATE SCHEMA createtest; -- created constraints with target action, validation
+CREATE SCHEMA altertest;  -- created with source and altered to target, test
+
+DO
+$test_alter_con$
+DECLARE
+    v_result json;
+    method text;
+    from_action text;
+    to_action text;
+BEGIN
+    FOR method, from_action, to_action IN
+        WITH act(action) AS (
+            SELECT unnest('{NO ACTION,RESTRICT,CASCADE,SET DEFAULT,SET NULL}'::text[])
+        )
+        SELECT
+            m.method, a1.action, a2.action
+        FROM unnest('{UPDATE,DELETE}'::text[]) AS m(method), act a1, act a2
+    LOOP
+        EXECUTE format(
+            $sql$
+                 -- Alter from ON %1$s %2$s to ON %1$s %3$s
+                CREATE TABLE createtest.foo(id integer primary key);
+                CREATE TABLE createtest.bar(foo_id integer DEFAULT 0 REFERENCES createtest.foo ON %1$s %3$s, val text);
+
+                CREATE TABLE altertest.foo(id integer primary key);
+                INSERT INTO altertest.foo VALUES(0),(1),(2),(3);
+
+                CREATE TABLE altertest.bar(foo_id integer DEFAULT 0 REFERENCES altertest.foo ON %1$s %2$s, val text);
+
+                ALTER TABLE altertest.bar ALTER CONSTRAINT bar_foo_id_fkey ON %1$s %3$s;
+
+            $sql$, method, from_action, to_action);
+
+        SELECT json_agg(t)
+        INTO v_result
+        FROM (
+            -- Do EXCEPT of the "altertest" and "createtest" constraints, if they are equal (as expected), it should return empty
+            SELECT
+                rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+                tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+                regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+            FROM pg_trigger tg
+                JOIN pg_constraint con ON con.oid = tg.tgconstraint
+                JOIN pg_class rel ON tg.tgrelid = rel.oid
+            WHERE tg.tgrelid IN ('altertest.foo'::regclass, 'altertest.bar'::regclass)
+            EXCEPT
+            SELECT
+                rel.relname, replace(tg.tgname, tg.oid::text, 'OID') AS tgname,
+                tg.tgfoid::regproc, con.conname, con.confupdtype, con.confdeltype, tg.tgdeferrable,
+                regexp_replace(pg_get_constraintdef(con.oid), '(createtest\.|altertest\.)', '') AS condef
+            FROM pg_trigger tg
+                JOIN pg_constraint con ON con.oid = tg.tgconstraint
+                JOIN pg_class rel ON tg.tgrelid = rel.oid
+            WHERE tg.tgrelid IN ('createtest.foo'::regclass, 'createtest.bar'::regclass)
+        ) t;
+
+        DROP TABLE createtest.bar;
+        DROP TABLE createtest.foo;
+        DROP TABLE altertest.bar;
+        DROP TABLE altertest.foo;
+
+        IF (v_result IS NULL) THEN
+            RAISE INFO 'ON % from % to %: OK.', method, from_action, to_action;
+        ELSE
+            RAISE EXCEPTION 'ON % from % to %. FAILED! Unmatching rows: %', method, from_action, to_action, v_result;
+        END IF;
+    END LOOP;
+END;
+$test_alter_con$
+;
+
+DROP SCHEMA createtest;
+DROP SCHEMA altertest;
12