To clean up those automatically created subscriptions on PostgreSQL, I've used following query:

delete
from tig_pubsub_subscriptions s where exists (
    select 1 from tig_pubsub_jids j
    inner join tig_pubsub_nodes n on s.node_id = n.node_id
    inner join tig_pubsub_service_jids sj on sj.service_id = n.service_id
    where
        j.jid_id = s.jid_id
        and lower(j.jid) = lower(sj.service_jid)
) ;

NOTE: This will delete also owners' self-subscriptions if they were manually created!

As for sending "notifications" when the presence is changed, I've modified PresenceCollectorModule to emit PresenceChangeEvent only when the user actually changed presence - become online or offline.

As for cleanup, I would consider cleaning up our installations to reduce the load on the servers, however, I'm not sure if we can do that without "some" impact on our users. Most likely this would be some impact (if any) only for advanced users as people generally do not self-subscribe nodes on PEP.

@andrzej.wojcik What impact do you estimate on our installation (mostly in terms of benefits: how many pacts are currently generated due to this "bug" and how many users could be affected)?

Any old users are affected now and if they are using OMEMO at least 2-5 nodes are sending notifications on every presence change. My fix would help with that.

Cleanup would help with initial presence processing (when resource connected and sent presence for the first time). That would reduce event message duplication, so PEP traffic from your local PEP service on "login" would be reduced by half.

As for the impact on users, I do not think anyone would use a subscription like that as most clients are using CAPS for that, so only advanced users with so custom software or manually subscribing with XMPP console.

I went ahead with the cleanup as per your recommendation. There weren't all that much subscriptions:

mysql> select count(*) from tig_pubsub_subscriptions;
+----------+
| count(*) |
+----------+
|      448 |
+----------+
1 row in set (0.00 sec)
```

Deleted roughly 80% of the entries:
```
mysql>     delete
    ->     from tig_pubsub_subscriptions s where exists (
    ->         select 1 from tig_pubsub_jids j
    ->         inner join tig_pubsub_nodes n on s.node_id = n.node_id
    ->         inner join tig_pubsub_service_jids sj on sj.service_id = n.service_id
    ->         where
    ->             j.jid_id = s.jid_id
    ->             and lower(j.jid) = lower(sj.service_jid)
    ->     ) ;
Query OK, 364 rows affected (0.01 sec)
```