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
delay_altertable_foreignkey_validation_during_table_rewrite.patch
Description: Binary data
