FTR, I dropped that patch from production for now. There's simply
too many affected copr_chroots IMO.

There are two types of chroots that we want to remove:

1) The first have delete_after and delete_notify equal to null, these are those projects for which it was not possible to contact the user by email.

select copr.id, os_release, os_version, arch, cc.delete_after, delete_notify, is_active from copr join copr_chroot cc on copr.id = cc.copr_id join mock_chroot mc on cc.mock_chroot_id = mc.id where copr.deleted is false and is_active is false and cc.delete_notify is null and cc.delete_after is null and mc.arch!='ppc64le';

I intentionally omitted the ppc64le architecture because we currently have it disabled in copr.

2) the second type are chroots that have delete_after null, but delete_notify is not null, however, these chroots are no longer active and would need to be deleted, this is probably a remnant of a time when we had no EOL implementation or I don't know how it happened.

select copr.id, os_release, os_version, arch, cc.delete_after, delete_notify, is_active from copr join copr_chroot cc on copr.id = cc.copr_id join mock_chroot mc on cc.mock_chroot_id = mc.id where copr.deleted is false and is_active is false and cc.delete_notify is not null and cc.delete_after is null and mc.arch!='ppc64le' order by delete_notify;

These are these mock chroots:

select distinct os_release, os_version from copr join copr_chroot cc on copr.id = cc.copr_id join mock_chroot mc on cc.mock_chroot_id = mc.id where copr.deleted is false and is_active is false and cc.delete_notify is not null and cc.delete_after is null and mc.arch!='ppc64le' order by os_version;

So i suggest iterating over the ids here of those selected copr chroots and setting delete_after to the end of February. Users will then be able to expire the chroots themselves.

do
$$
declare
    selected_id integer;
begin
    for selected_id in select cc.id
        from copr join copr_chroot cc on copr.id = cc.copr_id join mock_chroot mc on cc.mock_chroot_id = mc.id where copr.deleted is false and is_active is false and cc.delete_notify is not null and cc.delete_after is null and mc.arch!='ppc64le'
    loop
        update copr_chroot set delete_after=TO_DATE('28/2/2021', 'DD/MM/YYYY') where id=selected_id;
    end loop;
end;
$$;

I really didn't think we'll have to play with anything newer than fedora 29 :-(

Any idea why there exist fedora-31-x86_64 copr_chroots that have delete_after null?

One of the major reasons is that we copy EOLed chroots when forking. :/

Ok, that seems to be the problem, when I modify @schlupov's query to select those which have forked_from_id null, only those are present:

coprdb=# select os_release, os_version, arch, is_active from copr join copr_chroot cc on copr.id = cc.copr_id join mock_chroot mc on cc.mock_chroot_id = mc.id where copr.deleted is false and is_active is false and cc.delete_notify is null and cc.delete_after is null and mc.arch!='ppc64le' and copr.forked_from_id is null group by os_release, os_version, arch, is_active;
┌────────────┬────────────┬─────────┬───────────┐
│ os_release │ os_version │  arch   │ is_active │
├────────────┼────────────┼─────────┼───────────┤
│ fedora     │ 28         │ i386    │ f         │
│ epel       │ 7          │ i386    │ f         │
│ epel       │ 6          │ aarch64 │ f         │
│ fedora     │ eln        │ armhfp  │ f         │
│ fedora     │ 28         │ x86_64  │ f         │
│ mageia     │ 8          │ aarch64 │ f         │
└────────────┴────────────┴─────────┴───────────┘

The question is if the forked builds have also forked data on backend, or if that's just a problem in FE database.

It looks like the problem is only in the database, but please check it too.

It is bug in project forking as well, see #1655.

I'm not sure there's some clever solution to not disturb people too much
... :/ if we knew the date of EOL (we don't, it is not in database) we
could automatically delete all affected copr_chroots that were added to
copr projects which were created "post" EOL time.

So to stay on the safe side, we probably have to set delete_after for
all of them, there isn't anyhow large <800 affected copr_chroots, <250
affected projects. The only thing people will have to do is to hit the
expire button.

@frostyx, for the ppc64le ... we'll have to eventually EOL even the older
chroots once we have ppc64le boxes again?

WDYT? Applicable post-release:
update copr_chroot set delete_after = '2021-02-28' where id in (select cc.id from copr join copr_chroot cc on copr.id = cc.copr_id join mock_chroot mc on cc.mock_chroot_id = mc.id where copr.deleted is false and is_active is false and cc.delete_notify is null and cc.delete_after is null and arch != 'ppc64le' group by cc.id);

WDYT? Applicable post-release:
update copr_chroot set delete_after = '2021-02-28' where id in (select cc.id from copr join copr_chroot cc on copr.id = cc.copr_id join mock_chroot mc on cc.mock_chroot_id = mc.id where copr.deleted is false and is_active is false and cc.delete_notify is null and cc.delete_after is null and arch != 'ppc64le' group by cc.id);

looks ok, I agree.

And as for the second variant I mentioned? We will not solve it?

@frostyx, for the ppc64le ... we'll have to eventually EOL even the older
chroots once we have ppc64le boxes again?

In case I didn't make a mistake in the SQL query, I think we will need to mark the following ppc64le chroots as EOL (once it is the time)

coprdb=# select distinct mock_chroot.os_release, mock_chroot.os_version, mock_chroot.arch 
         from copr_chroot 
         join mock_chroot on copr_chroot.mock_chroot_id=mock_chroot.id 
         join copr on copr_chroot.copr_id=copr.id 
         where mock_chroot.arch='ppc64le'
         and copr_chroot.delete_after is NULL 
         and copr_chroot.delete_notify is NULL 
         and copr.forked_from_id is NULL;

┌────────────┬────────────┬─────────┐
│ os_release │ os_version │  arch   │
├────────────┼────────────┼─────────┤
│ centos     │ stream     │ ppc64le │
│ custom     │ 1          │ ppc64le │
│ epel       │ 7          │ ppc64le │
│ epel       │ 8          │ ppc64le │
│ fedora     │ 31         │ ppc64le │
│ fedora     │ 32         │ ppc64le │
│ fedora     │ 33         │ ppc64le │
│ fedora     │ rawhide    │ ppc64le │
│ opensuse   │ tumbleweed │ ppc64le │
└────────────┴────────────┴─────────┘
(9 rows)

Commit d0e5cfa relates to this ticket

Don't we want to remove only fedora 31 (maybe fedora 32, epel 7, depends on timing)? I understood that once https://pagure.io/fedora-infrastructure/issue/9059 is resolved, the ppc64le architecture will turn on again, and then we will have EOLed os_release and os_version that we want to support, like fedora 33 or fedora rawhide for ppc64le.

In case I didn't make a mistake in the SQL query, I think we will need to mark the following ppc64le chroots as EOL (once it is the time)

Well we are still wating on ppc64le hardware. https://pagure.io/fedora-infrastructure/issue/9059

So what if we run this? (15 more days)
update copr_chroot set delete_after = '2021-03-15' where id in (select cc.id from copr join copr_chroot cc on copr.id = cc.copr_id join mock_chroot mc on cc.mock_chroot_id = mc.id where copr.deleted is false and is_active is false and cc.delete_notify is null and cc.delete_after is null and arch != 'ppc64le' group by cc.id);

where copr.deleted

Regardless of anything else in that query, it should IMHO be safe to run because it should affect only deleted projects (that nobody cares about)

But there's where copr.deleted is false.

SQL aplied in production => DONE.