I've decided to inline jids of node creator (replacing creator_id with creator field) and item publisher (replacing publisher_id with publisher). I did that to be able to store jid of node creator and item publisher even after account would be removed. Node creator is to be returned by disco#info at PubSub node, while publisher is not used yet, but could be used for implementing support for https://xmpp.org/extensions/xep-0060.html#publisher-publish-success-publisher which we do not support yet.

With that in place I was able to remove references to tig_pubsub_jids and could skip storing jids there. That will slightly increase storage requirements as we would not deduplicate those jids and we will store them directly in each node and each item field. As we are usually limiting no. of items and with typical no. of nodes that shouldn't cause significant growth.

I've also ensured that when local users are removed we will remove them from tig_pubsub_jids as there will be no longer any item referencing this record.

After making those changes, I've looked at the current values stored in creator and publisher. While we have BareJID stored in creator field (that is what I've expected), I was surprised to find out that in publisher field we are storing JID (full JID).

With that in mind I've recalled issue mentioning a lot of entries in tig_pubsub_jids in which jid was in for of http@domain/f4beca38-0b97-481a-808c-41d9e5da7cea (containing resource part). tig_pubsub_jids table shouldn't contain the resource part.

With changes we've made that is now resolved, however I still wonder if:

1. Should we change publisher to store just BareJID? That would reduce amount of stored data.
2. If we would remove the resource part, in theory we would limit no. of entries with http@domain/f4beca38-0b97-481a-808c-41d9e5da7cea as we would end up with just a single http@domain. Then is the replacement and forced removed of entries in tig_pubsub_jids actually required or a good idea? In XEP-0060 in the missing Item Publisher part, there is a mention of storage of a full JID`

I do recall that a lot of entries like http@domain/f4beca38-0b97-481a-808c-41d9e5da7cea were the main issue we tried to resolve and those are not connected with removal of the user way as those are just full jids of the HTTP component that is not creating nor destroying those users. With bare JIDs we would have just a single entry, so a lot of entries wouldn't be created and maybe there would be no point in removal of entries from tig_pubsub_jids?

As this point as we have already full JID as required by Item Publisher and removal of jids from tig_pubsub_jids (at least for local users), I think we would leave it "as is". However, the only down side could be increase of storage usage due to duplication of those entries (jids in each row of node and item).

I've prepared publisher field to store 2049 chars (as for BareJID). Now I wonder if this size shouldn't be increased to 3074 chars (to make space for 1024 chars of the resource part).

@wojtek What do you think?

A different question is if we need/should change schema for DerbyDB. For now, I've skipped making changes for DerbyDB.

I do recall that a lot of entries like http@domain/f4beca38-0b97-481a-808c-41d9e5da7cea were the main issue we tried to resolve and those are not connected with removal of the user way as those are just full jids of the HTTP component that is not creating nor destroying those users. With bare JIDs we would have just a single entry, so a lot of entries wouldn't be created and maybe there would be no point in removal of entries from tig_pubsub_jids?

As this point as we have already full JID as required by Item Publisher and removal of jids from tig_pubsub_jids (at least for local users), I think we would leave it "as is". However, the only down side could be increase of storage usage due to duplication of those entries (jids in each row of node and item).

I've prepared publisher field to store 2049 chars (as for BareJID). Now I wonder if this size shouldn't be increased to 3074 chars (to make space for 1024 chars of the resource part).

OK, this was a bit "mixed" but after a bit of chat here's the gist that with inlining creator (barejid) and publisher (fulljid) we will be able to co compatible with the specification while at the same time resolve the issue with flow of FullJIDs in tig_pubsub_jids table. The leave "as is" part referred to the change already made.

IMHO this change makes sense.

A different question is if we need/should change schema for DerbyDB. For now, I've skipped making changes for DerbyDB.

+1

We already want to remove it: tigase/_server/server-core#1526

Changes were made as discussed.