Thread: ALTER TABLE validate foreign key dependency problem

Hi,

I had an ALTER TABLE dependency problem reported to me.  Here's a
simplified version of it:

CREATE TABLE t (a INT, PRIMARY KEY(a));
ALTER TABLE t ADD CONSTRAINT t_fkey FOREIGN KEY (a) REFERENCES t(a) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT t_fkey, ALTER a TYPE BIGINT;

Which results in:

ERROR:  could not read block 0 in file "base/12854/16411": read only 0
of 8192 bytes
CONTEXT:  SQL statement "SELECT fk."a" FROM ONLY "public"."t" fk LEFT
OUTER JOIN ONLY "public"."t" pk ON ( pk."a" OPERATOR(pg_catalog.=)
fk."a") WHERE pk."a" IS NULL AND (fk."a" IS NOT NULL)"

What's going on here is that due to the ALTER TYPE, a table rewrite is
pending. The primary key index of the table is also due to be
rewritten which ATExecAddIndex() delays due to the pending table
rewrite.  When we process AT_PASS_MISC level changes and attempt to
validate the foreign key constraint, the table is still pending a
rewrite and the new index still does not exist.
validateForeignKeyConstraint() executes regardless of the pending
rewrite and bumps into the above error during the SPI call while
trying to check the _bt_getrootheight() in get_relation_info().

I think the fix is just to delay the foreign key validation when
there's a rewrite pending until the rewrite is complete.

I also considered that we could just delay all foreign key validations
until phase 3, but I ended up just doing then only when a rewrite is
pending.

David

On Thu, 9 Jul 2020 at 15:54, David Rowley <dgrowleyml@gmail.com> wrote:
> I think the fix is just to delay the foreign key validation when
> there's a rewrite pending until the rewrite is complete.

I looked over this again and only slightly reworded a comment.  The
problem exists as far back as 9.5 so I've attached 3 patches that,
pending any objections, I plan to push about 24 hours from now.

> I also considered that we could just delay all foreign key validations
> until phase 3, but I ended up just doing then only when a rewrite is
> pending.

I still wonder if it's best to delay the validation of the foreign key
regardless of if there's a pending table rewrite, but the patch as it
is now only delays if there's a pending rewrite.

David

On Mon, 13 Jul 2020 at 08:13, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> On Sun, 12 Jul 2020 at 05:51, David Rowley <dgrowleyml@gmail.com> wrote:
>
>>
>> > I also considered that we could just delay all foreign key validations
>> > until phase 3, but I ended up just doing then only when a rewrite is
>> > pending.
>>
>> I still wonder if it's best to delay the validation of the foreign key
>> regardless of if there's a pending table rewrite, but the patch as it
>> is now only delays if there's a pending rewrite.
>
>
> Consistency seems the better choice, so I agree we should validate later in all cases. Does changing that have any
othereffects?
 

Thanks for having a look here.

I looked at this again and noticed it wasn't just FOREIGN KEY
constraints. CHECK constraints were being validated at the wrong time
too.

I did end up going with unconditionally moving the validation until
phase 3. I've pushed fixed back to 9.5

David