Thread: pg_dump/restore failure (dependency?) on BF serinus
Hi, https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=serinus&dt=2025-04-07%2017%3A45%3A28&stg=pg_upgrade-check failed in a problem-indicating way in the new dump/restore test that Ashutosh added. > # Running: pg_restore --create -j2 -d postgres /home/bf/bf-build/serinus/HEAD/pgsql.build/testrun/pg_upgrade/002_pg_upgrade/data/tmp_test_KTss/regression.dump > pg_restore: error: could not execute query: ERROR: there is no unique constraint matching given keys for referenced table"pk" > Command was: ALTER TABLE fkpart5.fk > ADD CONSTRAINT fk_a_fkey FOREIGN KEY (a) REFERENCES fkpart5.pk(a); > > > pg_restore: warning: errors ignored on restore: 2 There are no other errors visible, neither in regress_log, nor in the server log. It's hard to tell without more logging, but the most likely explanation for this seems that somehow the primary key on fkpart5.pk hasn't yet been restored. However, looking at the pg_restore -v -l of the regression test database locally, the relevant dependencies look sane on a first glance. So I'm a bit confused how this could happen? Greetings, Andres Freund
Andres Freund <andres@anarazel.de> writes: >> # Running: pg_restore --create -j2 -d postgres /home/bf/bf-build/serinus/HEAD/pgsql.build/testrun/pg_upgrade/002_pg_upgrade/data/tmp_test_KTss/regression.dump >> pg_restore: error: could not execute query: ERROR: there is no unique constraint matching given keys for referenced table"pk" >> Command was: ALTER TABLE fkpart5.fk >> ADD CONSTRAINT fk_a_fkey FOREIGN KEY (a) REFERENCES fkpart5.pk(a); > It's hard to tell without more logging, but the most likely explanation for > this seems that somehow the primary key on fkpart5.pk hasn't yet been > restored. However, looking at the pg_restore -v -l of the regression test > database locally, the relevant dependencies look sane on a first glance. This feels quite adjacent to my complaint here: https://www.postgresql.org/message-id/2045026.1743801143%40sss.pgh.pa.us though perhaps it's not exactly the same. I plan to start looking into a fix for that tomorrow. regards, tom lane
Hi, On 2025-04-08 00:11:55 -0400, Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: > >> # Running: pg_restore --create -j2 -d postgres /home/bf/bf-build/serinus/HEAD/pgsql.build/testrun/pg_upgrade/002_pg_upgrade/data/tmp_test_KTss/regression.dump > >> pg_restore: error: could not execute query: ERROR: there is no unique constraint matching given keys for referencedtable "pk" > >> Command was: ALTER TABLE fkpart5.fk > >> ADD CONSTRAINT fk_a_fkey FOREIGN KEY (a) REFERENCES fkpart5.pk(a); > > > It's hard to tell without more logging, but the most likely explanation for > > this seems that somehow the primary key on fkpart5.pk hasn't yet been > > restored. However, looking at the pg_restore -v -l of the regression test > > database locally, the relevant dependencies look sane on a first glance. > > This feels quite adjacent to my complaint here: > > https://www.postgresql.org/message-id/2045026.1743801143%40sss.pgh.pa.us > > though perhaps it's not exactly the same. That does sound rather plausible. What an odd coincidence that it failed like that so close to your email. While this specific failure probably couldn't have happened much earlier, it seems that it could have as part of pg_upgrade for longer. > I plan to start looking into a fix for that tomorrow. Cool. Greetings, Andres Freund
Andres Freund <andres@anarazel.de> writes: > On 2025-04-08 00:11:55 -0400, Tom Lane wrote: >> This feels quite adjacent to my complaint here: >> https://www.postgresql.org/message-id/2045026.1743801143%40sss.pgh.pa.us >> though perhaps it's not exactly the same. > That does sound rather plausible. What an odd coincidence that it failed like > that so close to your email. While this specific failure probably couldn't > have happened much earlier, it seems that it could have as part of pg_upgrade > for longer. I think pg_upgrade is not vulnerable to the problem, or at least not the identical problem, because it doesn't expect pg_restore to load table data. So I think we didn't previously have any test cases that would expose this :-(. What I find surprising is that we didn't get field reports much sooner. regards, tom lane