Consider using a single table for checkpoints

[bwaidelich]

Currently we create six tables per CR instance to store the checkpoints of projections.
We should consider using a single table for those instead, since they already use a unique subscriber id (= FQN projection class name).

Note: We might have chosen this approach to make the locking mechanism work (better) – but I'm not sure

[skurfuerst]

@bwaidelich IMHO there were good reasons for the current approach - making every projection completely self-contained; and I think there were problems with the other approach (I don't remember them fully though).

I am not sure if we should again change this - I don't see any benefits in changing this.

[bwaidelich]

@dlubitz brought it up and I could not answer why we need 19 tables (instead of 13) for a CR instance.
IMO projections would still be self-contained even if they shared a table (they'd have the same schema and the first one would create the table upon cr:setup).

But I'm totally happy with keeping it for now!

[bwaidelich]

FYI: I had used a single table for a customer project of mine and got a lot of Failed to acquire checkpoint lock for subscriber "..." because it is acquired already errors in production.
When switching to individual tables, the errors were gone..
I need to investigate more closely, but apparently the SELECT ... FOR UPDATE does not reliably lock single rows.

If that's the case we should consider removing the $subscribedId parameter from the DoctrineCheckpointStorage

UPDATE: Nevermind, it was a copy/paste mistake in my case and I used the same subscriber id twice ¯\(ツ)/¯

[skurfuerst]

Phew, good 😊

[bwaidelich]

Closing for now, because the current implementation has no severe drawbacks

This might be addressed with #4746 (keyword: subscription engine)

[mhsdesign]

indeed with the subscription engine we are thinking about doing this: #5321

the problem so far is how to get the transactions right. As even on fatal errors we want the subscription position (checkpoint) to match what the projection has done.