ALTER TABLE validate foreign key dependency problem

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: ALTER TABLE validate foreign key dependency problem
Date: 2020-07-09 03:54:01
Message-ID: CAApHDvp=ZXv8wiRyk_0rWr00skhGkt8vXDrHJYXRMft3TjkxCA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
delay_altertable_foreignkey_validation_during_table_rewrite.patch application/octet-stream 6.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2020-07-09 03:57:48 Re: pgsql-hackers archive broken?
Previous Message Amit Kapila 2020-07-09 03:30:36 Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions