with broken_perms as (
    select *
    from directory_entry_file
    where perms = 100644
), conflicting as (
    select b.id as old_id, def.id as new_id
    from broken_perms b
    inner join directory_entry_file def
    on b.target = def.target and b.name = def.name and def.perms = 33188
), to_update as (
    select id
    from broken_perms
    where not exists (select 1 from conflicting where old_id = id)
), updated as (
    update directory_entry_file def
    set perms = 33188
    from to_update u
    where def.id = u.id
), deleted as (
    delete from directory_entry_file def
    using conflicting c
    where def.id = c.old_id
) select * from conflicting;

import psycopg2

ids = set(int(l.split(',')[0]) for l in open('/srv/softwareheritage/postgres/export/directory_entry_dir.tsv'))

db = psycopg2.connect('host=localhost port=5433 dbname=softwareheritage user=swhstorage password=<>')

QUERY = 'update directory set dir_entries = array(select coalesce(t.new_id, d.id) from unnest(dir_entries) d(id) left join tmp_update_dir_entries t on t.old_id = d.id) where dir_entries @> ARRAY[%s::bigint]'
i = 0

for entry in ids:
    if not (i % 1000):
        print(i)
        db.commit()
        cur = db.cursor()
    cur.execute(QUERY, (entry,))
    if cur.statusmessage != 'UPDATE 0': print(entry, cur.statusmessage)
    i += 1

db.commit()

select count(directory.id) from directory inner join directory_entry_file tablesample system(0.1) on ARRAY[CAST (directory_entry_file.id AS bigint)] <@ file_entries where directory_entry_file.perms = 100644;