[PGSQL] Implement PostgresEventStore

[quantranhong1999]

Why

We need an EventStore backed by Postgres (some parts of James rely on event sourcing).

How

• Table structure:

  CREATE TABLE events (
  aggregate_id TEXT,
  event_id INT,
  snapshot INT,
  event JSONB,
  PRIMARY KEY (aggregate_id, event_id)
  );

  An additional index on a single aggregate_id would help, as seems we would use the WHERE aggregate_id a lots.

• Introduce event-sourcing/event-store-postgres module and CI test for the module.

• Implement PostgresEventStore. May extract a separate DAO class.

• Testing:

  • Introduce PostgresEventStoreExtension
  • Implement contract test EventStoreContract
  • Implement contract test EventSourcingSystemTest

• Guice binding for PostgresEventStore.

DoD

Pass contract tests.

[chibenwa]

TL;DR: More complex than it seems, I would love to see a POC...

---

This is a tricky one, as we need transactionnality when inserting several events: they either all succeed or all fails. Partial application is not supported. I would love to see in this ticket...

... Is this feature actually used? Is there some features that relies on storing several events or is this just some convoluted complexity we can get rid of? ... We could implement this with a transaction for sure. But do we have a way to match desired consistency without resorting to such extremities? Eg: Apparently PG supports inserting several rows at once natively: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-insert-multiple-rows/

INSERT INTO 
    links (url, name)
VALUES
    ('https://www.google.com','Google'),
    ('https://www.yahoo.com','Yahoo'),
    ('https://www.bing.com','Bing');

[quantranhong1999]

TL;DR: More complex than it seems, I would love to see a POC...

The inserting of several rows at once query is atomic: all or nothing. We can rely on that IMO.

https://github.com/linagora/james-project/assets/55171818/197f8de2-f185-4d5f-9cd7-760c977b6776

BTW, generally, I think every SQL query is atomic too, as each is an implicit transaction anyway.

[chibenwa]

Looks great to me!

Thanks for testing!

And PG is suuuuuch an awwwwwwwwesome database!

[hungphan227]

batch could be another option

[chibenwa]

batch could be another option

And restricts our options for connection management

[hungphan227]

draft https://github.com/apache/james-project/pull/1970