Migration fails on user_portal_portal_fkey constraint

[KitsuneRal]

Here's the relevant piece in the logs:

Feb 07 11:50:02 clup matrix-postgres[3699196]: 2024-02-07 10:50:02.366 UTC [360111] ERROR:  foreign key constraint "user_portal_portal_fkey" cannot be implemented
Feb 07 11:50:02 clup matrix-postgres[3699196]: 2024-02-07 10:50:02.366 UTC [360111] DETAIL:  Key columns "portal_receiver" and "receiver" are of incompatible types: uuid and text.
Feb 07 11:50:02 clup matrix-postgres[3699196]: 2024-02-07 10:50:02.366 UTC [360111] STATEMENT:  ALTER TABLE "user" ADD COLUMN space_room TEXT;
Feb 07 11:50:02 clup matrix-postgres[3699196]:         
Feb 07 11:50:02 clup matrix-postgres[3699196]:         DROP TABLE IF EXISTS user_portal;
Feb 07 11:50:02 clup matrix-postgres[3699196]:         CREATE TABLE user_portal (
Feb 07 11:50:02 clup matrix-postgres[3699196]:             user_mxid       TEXT,
Feb 07 11:50:02 clup matrix-postgres[3699196]:             portal_chat_id  TEXT,
Feb 07 11:50:02 clup matrix-postgres[3699196]:             portal_receiver uuid,
Feb 07 11:50:02 clup matrix-postgres[3699196]:             last_read_ts    BIGINT  NOT NULL DEFAULT 0,
Feb 07 11:50:02 clup matrix-postgres[3699196]:             in_space        BOOLEAN NOT NULL DEFAULT false,
Feb 07 11:50:02 clup matrix-postgres[3699196]:         
Feb 07 11:50:02 clup matrix-postgres[3699196]:             PRIMARY KEY (user_mxid, portal_chat_id, portal_receiver),
Feb 07 11:50:02 clup matrix-postgres[3699196]:             CONSTRAINT user_portal_user_fkey FOREIGN KEY (user_mxid)
Feb 07 11:50:02 clup matrix-postgres[3699196]:                 REFERENCES "user"(mxid) ON UPDATE CASCADE ON DELETE CASCADE,
Feb 07 11:50:02 clup matrix-postgres[3699196]:             CONSTRAINT user_portal_portal_fkey FOREIGN KEY (portal_chat_id, portal_receiver)
Feb 07 11:50:02 clup matrix-postgres[3699196]:                 REFERENCES portal(chat_id, receiver) ON UPDATE CASCADE ON DELETE CASCADE
Feb 07 11:50:02 clup matrix-postgres[3699196]:         );

Using the bridge snapshot from de8c8d97c23dc1982686a698162b45f1f112155b. Looking at the actual tables, portal.receiver is text but user_portal.portal_receiver is uuid.

[tulir]

portal.receiver is changed to be uuid in the previous migration 🤔 https://github.com/mautrix/signal/blob/main/database/upgrades/16-refactor-postgres.sql#L58

[KitsuneRal]

Finally got back to this - turns out, that previous one (the specific statement you linked to) fails with

ERROR:  null value in column "receiver" of relation "portal" violates not-null constraint

I might miss something obvious but receiver is already checked before updating the table... I naïvely tried to replace receiver<>'' with receiver IS NOT NULL and that helped literally nothing. Any idea what could give?

[KitsuneRal]

Oh I think I know what's going on - the receiver check happens on the original receiver value, not the one coming from the inner SELECT, which may bring nothing - and that's exactly what's happening here (user doesn't have the respective receiver).

I actually see quite a few receivers in portal that have no matching row in user - what should be done with those?

[tulir]

They're deleted 10 lines earlier in the migration: https://github.com/mautrix/signal/blob/main/database/upgrades/16-refactor-postgres.sql#L47-L48

[KitsuneRal]

Ah! I really should look through the whole script, and probably try to execute it one by one...

[KitsuneRal]

I could not figure out at which point this migration script choked and why but that very deletion somehow did not occur before. Once I executed it and the rest of the script manually, the bridge went up in the air. Thanks for helping!