Set not null for a newly created column in PostgreSQL via Flyway SQL script

Question

I have a Flyway migration script (in our Spring Boot application):

alter table bar
    add column foo_id int,
    add constraint fk_bar_foo_id foreign key (foo_id)
        references foo (id);

update bar
set foo_id = foo.id
from foo
where bar.foo_pid = foo.pid;

alter table bar
    alter column foo_id set not null;

It basically: adds a new column, makes it a FK to another table, fills it using existing data from the other table, and sets not null constraint. Everything works as expected when I start the application backed with development DB. However the application fails to start during tests with:

Migration V210111__foo_id.sql failed
----------------------------------------------------------
SQL State  : 23502
Error Code : 0
Message    : ERROR: column "foo_id" contains null values
Location   : db/migration/V210111__foo_id.sql
Line       : 11
Statement  : alter table bar
    alter column foo_id set not null

When I comment line #11 (and #12) the script succeeds, and when I look at the test DB (embedded postgres) I see that the record has indeed foo_id = null:

# foo table
id  pid
0   00000000-0000-0000-0000-000000000000

# bar table
id  foo_id  foo_pid
0   <null>  0000000-0000-0000-0000-000000000000

Notes:

1. The migration script works on some DBs but not others. Can't figure out which one is which!
2. There's no missing data. I can run the update part of the script myself while tests are paused and everything works just fine. It's just Flyway that can't run the migration.
3. Again, it's impossible that any bar row ends up with null foo_id, because, foo.id is the PK of foo table, foo.pid was the PK of foo table (and still is not null). Also foo_pid is not null and a FK to foo table.
4. There's a lot of migration before this one, adding new columns, removing existing ones, changing PKs, etc.

I'm not sure what could be the issue. Maybe it has something to do with the way Flyway runs migration scripts? The reason is when I run the migration against development DB there's only one migration script to execute (meaning the DB has everything already, including foo.id and foo.pid). But for tests, Flyway has to run all scripts (because it's a new empty DB). But as far as I can tell, Flyway runs each script in one transaction, so the data should be final when the next script gets executed.

Does anyone have any idea what the problem might be? Is there anyway to troubleshoot this more?

Update 1

Again, it's impossible that any bar row ends up with null...

It's fair to trust PG over my words (even though I reviewed the schema multiple times). To eliminate any doubt about possibility of null values, I changed the update part of the script to:

--update bar
--set foo_id = foo.id
--from foo
--where bar.foo_pid = foo.pid;

update bar
set foo_id = 0;

And it still fails with the same error ????!

Update 2

There's one trigger which I don't think is relevant:

CREATE OR REPLACE FUNCTION public.update_modified_at()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    if row (NEW.*) is distinct from row (OLD.*) then
        NEW.modified_at = now();
        return NEW;
    else
        return OLD;
    end if;
end;
$function$
;

and each table has a modified_at column:

create trigger tgr_update_modified_at before
update on bar for each row execute function update_modified_at();

Answer 1

This part will probably not do anything:

update bar
set foo_id = foo.id
from foo
where bar.foo_pid = foo.pid;

since you just added the foo_pid column in table bar. The clause

where bar.foo_pid = foo.pid;   

is comparing null to foo.pid wich always fails and the statement will not update anything.