Horton

Re: ALTER CONSTRAINT on a partitioned FK isn't working

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Date: 2020-12-07T23:34:14Z
Lists: pgsql-bugs 
On 2020-Dec-07, Tom Lane wrote:

> then everything behaves as expected.  So something is broken
> about propagating deferred-ness to partition triggers in an
> ALTER CONSTRAINT.  Oddly, it *looks* like it worked if you
> examine the child tables with "\d+".  I surmise that ALTER CONSTRAINT
> fixes whatever catalog fields psql looks at, but there's some other
> fields that also need to be updated and aren't being.

Yeah, this query shows that the tgdeferrable and tginitdeferred columns
ought to be dropped:

select tgname, tgdeferrable, tginitdeferred from pg_trigger;

These are all 'true' when the constraint is created correctly, and false
when changed by ALTER CONSTRAINT.

Let me see about fixing that ...

Commits

  1. Have ALTER CONSTRAINT recurse on partitioned tables

  2. Fix OID passed to object-alter hook during ALTER CONSTRAINT