Investigate SQL calls that expect a row to already exist

[anoadragon453]

#6311 was found as a result of an UDPATE call expecting a row to already exist in Synapse's database. While this row was created in a delta file, when we squished everything into a full_schema, the INSERT got lost.

All UPDATE statements that expect a row to already exist in a table in the DB should be checked, starting with appservice_stream. Starting by looking at the DB tables rather than the codebase may be easiest.

[anoadragon453]

Actually grep'ing through things there may be a lot to fix:

✗ grep -r -i "insert into" synapse/storage/data_stores/main/schema/delta/
synapse/storage/data_stores/main/schema/delta/40/event_push_summary.sql:INSERT INTO event_push_summary_stream_ordering (stream_ordering) VALUES (0);
synapse/storage/data_stores/main/schema/delta/40/device_inbox.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/40/device_inbox.sql:INSERT into background_updates (update_name, progress_json, depends_on)
synapse/storage/data_stores/main/schema/delta/40/current_state_idx.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/40/pushers.sql:INSERT INTO pushers2 SELECT * FROM PUSHERS;
synapse/storage/data_stores/main/schema/delta/44/expire_url_cache.sql:INSERT INTO local_media_repository_url_cache_new
synapse/storage/data_stores/main/schema/delta/46/drop_unique_deleted_pushers.sql:INSERT INTO deleted_pushers2 (stream_id, app_id, pushkey, user_id)
synapse/storage/data_stores/main/schema/delta/46/user_dir_null_room_ids.sql:INSERT INTO user_directory2(user_id, room_id, display_name, avatar_url)
synapse/storage/data_stores/main/schema/delta/46/group_server.sql:INSERT INTO groups_new
synapse/storage/data_stores/main/schema/delta/46/local_media_repository_url_idx.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/22/user_threepids_unique.sql:INSERT INTO user_threepids2
synapse/storage/data_stores/main/schema/delta/31/invites.sql:INSERT INTO local_invites SELECT
synapse/storage/data_stores/main/schema/delta/31/search_update.py:            "INSERT into background_updates (update_name, progress_json)"
synapse/storage/data_stores/main/schema/delta/31/pushers.py:            INSERT into pushers2 (
synapse/storage/data_stores/main/schema/delta/18/server_keys_bigger_ints.sql:INSERT INTO new_server_keys_json
synapse/storage/data_stores/main/schema/delta/20/pushers.py:            INSERT into pushers2 (
synapse/storage/data_stores/main/schema/delta/36/readd_public_rooms.sql:INSERT INTO stream_ordering_to_exterm (stream_ordering, room_id, event_id)
synapse/storage/data_stores/main/schema/delta/37/remove_auth_idx.py:INSERT INTO event_auth_new
synapse/storage/data_stores/main/schema/delta/41/event_search_event_id_idx.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/41/device_list_stream_idx.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/16/users.sql:INSERT INTO new_access_tokens
synapse/storage/data_stores/main/schema/delta/16/users.sql:INSERT INTO new_users SELECT name, password_hash, creation_ts, admin FROM users;
synapse/storage/data_stores/main/schema/delta/16/users.sql:INSERT INTO new_user_ips
synapse/storage/data_stores/main/schema/delta/48/add_user_ips_last_seen_index.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/33/event_fields.py:            "INSERT into background_updates (update_name, progress_json)"
synapse/storage/data_stores/main/schema/delta/33/user_ips_index.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/33/devices_for_e2e_keys.sql:INSERT INTO devices
synapse/storage/data_stores/main/schema/delta/33/access_tokens_device_index.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/55/users_alter_deactivated.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/25/tags.sql:INSERT INTO private_user_data_max_stream_id (stream_id) VALUES (0);
synapse/storage/data_stores/main/schema/delta/25/fts.py:            "INSERT into background_updates (update_name, progress_json)"
synapse/storage/data_stores/main/schema/delta/39/membership_profile.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/39/event_push_index.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/39/federation_out_position.sql: INSERT INTO federation_stream_position (type, stream_id) VALUES ('federation', -1);
synapse/storage/data_stores/main/schema/delta/39/federation_out_position.sql: INSERT INTO federation_stream_position (type, stream_id) SELECT 'events', coalesce(max(stream_ordering), -1) FROM events;
synapse/storage/data_stores/main/schema/delta/15/v15.sql:INSERT INTO pushers2 (id, user_name, profile_tag, kind, app_id, app_display_name, device_display_name, pushkey, ts, lang, data, last_token, last_success, failing_since)
synapse/storage/data_stores/main/schema/delta/49/add_user_ips_last_seen_only_index.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/56/devices_last_seen.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/56/hidden_devices_fix.sql.sqlite:INSERT INTO devices2 SELECT * FROM devices;
synapse/storage/data_stores/main/schema/delta/56/redaction_censor3_fix_update.sql.postgres:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/56/redaction_censor3_fix_update.sql.postgres:INSERT into background_updates (update_name, progress_json, depends_on)
synapse/storage/data_stores/main/schema/delta/56/device_stream_id.sql:INSERT INTO device_max_stream_id (stream_id)
synapse/storage/data_stores/main/schema/delta/56/event_labels_background_update.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/56/unique_user_filter_index.py:            INSERT INTO user_filters_migration (user_id, filter_id, filter_json)
synapse/storage/data_stores/main/schema/delta/56/room_membership_idx.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/56/redaction_censor2.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/56/current_state_events_membership_mk2.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/56/stats_separated.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/56/stats_separated.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/56/stats_separated.sql:INSERT INTO stats_incremental_position (
synapse/storage/data_stores/main/schema/delta/53/user_threepid_id.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/53/user_ips_index.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/53/user_ips_index.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/53/user_ips_index.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/53/user_ips_index.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/53/user_dir_populate.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/53/user_dir_populate.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/53/user_dir_populate.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/53/user_dir_populate.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/47/postgres_fts_gin.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/50/add_creation_ts_users_index.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/54/delete_forward_extremities.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/54/stats2.sql:INSERT INTO background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/54/stats.sql:INSERT INTO stats_stream_pos (stream_id) VALUES (null);
synapse/storage/data_stores/main/schema/delta/54/stats.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/54/stats.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/54/stats.sql:INSERT INTO background_updates (update_name, progress_json, depends_on) VALUES
synapse/storage/data_stores/main/schema/delta/52/add_event_to_state_group_index.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/52/e2e_room_keys.sql:INSERT INTO e2e_room_keys_versions_new
synapse/storage/data_stores/main/schema/delta/52/e2e_room_keys.sql:INSERT INTO e2e_room_keys_new
synapse/storage/data_stores/main/schema/delta/52/device_list_streams_unique_idx.sql:INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/52/device_list_streams_unique_idx.sql:INSERT into background_updates (update_name, progress_json, depends_on)
synapse/storage/data_stores/main/schema/delta/52/device_list_streams_unique_idx.sql:INSERT into background_updates (update_name, progress_json, depends_on)
synapse/storage/data_stores/main/schema/delta/42/event_auth_state_only.sql:INSERT INTO background_updates (update_name, progress_json) VALUES
synapse/storage/data_stores/main/schema/delta/42/user_dir.py:INSERT INTO user_directory_stream_pos (stream_id) VALUES (null);
synapse/storage/data_stores/main/schema/delta/42/device_list_last_id.sql:INSERT INTO device_lists_outbound_last_success
synapse/storage/data_stores/main/schema/delta/38/postgres_fts_gist.sql:-- INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/27/ts.py:            "INSERT into background_updates (update_name, progress_json)"
synapse/storage/data_stores/main/schema/delta/34/appservice_stream.sql:INSERT INTO appservice_stream_position (stream_ordering)
synapse/storage/data_stores/main/schema/delta/35/event_push_actions_index.sql: INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/35/add_state_index.sql:INSERT into background_updates (update_name, progress_json, depends_on)
synapse/storage/data_stores/main/schema/delta/35/device_stream_id.sql:INSERT INTO device_max_stream_id (stream_id)
synapse/storage/data_stores/main/schema/delta/35/stream_order_to_extrem.sql:INSERT INTO stream_ordering_to_exterm (stream_ordering, room_id, event_id)
synapse/storage/data_stores/main/schema/delta/35/contains_url.sql: INSERT into background_updates (update_name, progress_json)
synapse/storage/data_stores/main/schema/delta/35/public_room_list_change_stream.sql:INSERT INTO public_room_list_stream (stream_id, room_id, visibility)
synapse/storage/data_stores/main/schema/delta/35/state_dedupe.sql:INSERT into background_updates (update_name, progress_json)

[richvdh]

@anoadragon453 what is left to be done here?

[anoadragon453]

I did an investigation and it seemed only device_ids were missing here. In any case the new way to generate full schemas should catch any future missing insert statements.