public PostgreSqlQueryExecutor(PostgreSqlQueryConfiguration configuration, Akka.Serialization.Serialization serialization) : base(configuration, serialization) { CreateSnapshotTableSql = $@" DO $do$ BEGIN IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{Configuration.SchemaName}' AND TABLE_NAME = '{Configuration.SnapshotTableName}') THEN CREATE TABLE {Configuration.FullSnapshotTableName} ( {Configuration.PersistenceIdColumnName} VARCHAR(255) NOT NULL, {Configuration.SequenceNrColumnName} BIGINT NOT NULL, {Configuration.TimestampColumnName} BIGINT NOT NULL, {Configuration.ManifestColumnName} VARCHAR(500) NOT NULL, {Configuration.PayloadColumnName} {configuration.StoredAs.ToString().ToUpperInvariant()} NOT NULL, {Configuration.SerializerIdColumnName} INTEGER NULL, CONSTRAINT {Configuration.SnapshotTableName}_pk PRIMARY KEY ({Configuration.PersistenceIdColumnName}, {Configuration.SequenceNrColumnName}) ); CREATE INDEX {Configuration.SnapshotTableName}_{Configuration.SequenceNrColumnName}_idx ON {Configuration.FullSnapshotTableName}({Configuration.SequenceNrColumnName}); CREATE INDEX {Configuration.SnapshotTableName}_{Configuration.TimestampColumnName}_idx ON {Configuration.FullSnapshotTableName}({Configuration.TimestampColumnName}); END IF; END $do$"; InsertSnapshotSql = $@" WITH upsert AS ( UPDATE {Configuration.FullSnapshotTableName} SET {Configuration.TimestampColumnName} = @Timestamp, {Configuration.PayloadColumnName} = @Payload WHERE {Configuration.PersistenceIdColumnName} = @PersistenceId AND {Configuration.SequenceNrColumnName} = @SequenceNr RETURNING *) INSERT INTO {Configuration.FullSnapshotTableName} ( {Configuration.PersistenceIdColumnName}, {Configuration.SequenceNrColumnName}, {Configuration.TimestampColumnName}, {Configuration.ManifestColumnName}, {Configuration.PayloadColumnName}, {Configuration.SerializerIdColumnName}) SELECT @PersistenceId, @SequenceNr, @Timestamp, @Manifest, @Payload, @SerializerId WHERE NOT EXISTS (SELECT * FROM upsert)"; switch (configuration.StoredAs) { case StoredAsType.ByteA: _serialize = ss => { var serializer = Serialization.FindSerializerFor(ss); return(new SerializationResult(NpgsqlDbType.Bytea, serializer.ToBinary(ss), serializer)); }; _deserialize = (type, serialized, manifest, serializerId) => { if (serializerId.HasValue) { return(Serialization.Deserialize((byte[])serialized, serializerId.Value, manifest)); } else { // Support old writes that did not set the serializer id var deserializer = Serialization.FindSerializerForType(type, Configuration.DefaultSerializer); return(deserializer.FromBinary((byte[])serialized, type)); } }; break; case StoredAsType.JsonB: _serialize = ss => new SerializationResult(NpgsqlDbType.Jsonb, JsonConvert.SerializeObject(ss, configuration.JsonSerializerSettings), null); _deserialize = (type, serialized, manifest, serializerId) => JsonConvert.DeserializeObject((string)serialized, type, configuration.JsonSerializerSettings); break; case StoredAsType.Json: _serialize = ss => new SerializationResult(NpgsqlDbType.Json, JsonConvert.SerializeObject(ss, configuration.JsonSerializerSettings), null); _deserialize = (type, serialized, manifest, serializerId) => JsonConvert.DeserializeObject((string)serialized, type, configuration.JsonSerializerSettings); break; default: throw new NotSupportedException($"{configuration.StoredAs} is not supported Db type for a payload"); } }